[Mapserver-dev] Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance

[Paul Ramsey]

Any reason not to simply apply this change to the standard source?

On Sunday, September 26, 2004, at 05:47 AM, Smith, Michael  
ERDC-CRREL-NH wrote:

> It is also very important for performance reasons to increase the  
> array size in maporaclespatial.c and recompile. We generally recommend  
> setting array size to ~ 1000. We generally see an order of magnitude  
> increase in speed with this change.
>
> Mike Smith
> USACE-CRREL
> Remote Sensing GIS Center of Expertise
> Hanover, NH
>
> -----Original Message-----
> From: Frank Warmerdam
> To: MAPSERVER-USERS at LISTS.UMN.EDU
> Sent: 9/25/2004 7:34 PM
> Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance
>
> Vladimir Guzmán wrote:
> > Hello, List.
> >
> > I don't know if this is an off-topic question but, I really need some
> help.
> > I've worked a lot with mapserver, specially with phpmapscript, and
> with
> > shape files and postgis data.
> > Finally, about 6 months ago, we received a request from a customer in
> > order to develop a web application that would be able to connect with
> > oracle data.
> > Well, after face all kinds of issues with oracle spatial, everything
> is
> > working fine now.  Except the speed.
> > Every map request to oracle spatial takes at least 20 seconds, with
> very
> > small geometries, and in some cases it takes 2 minutes or more.
> > I would like you to give me some clue, because I've worked with
> postgis
> > and shps and I've never had this kind of problems.
> > All the geometries are indexed.
> > The time responses are almost the same working with ORACLESPATIAL and
> > OGR connection.
> >
> > For example:
> > The command:
> > ogrinfo OCI:xxx/xxx at xxx -sql "SELECT ID, ORA_GEOMETRY, NOMBRE FROM
> > SIG_DEPTOS"
> > Takes 1 minute.
> > The same geometry in shp:
> > ogrinfo -al departamentos.shp
> > Takes less than one second.
>
> Vladimir,
>
> I can think of a couple of issues.
>
> First, the connection overhead to Oracle tends to be pretty expensive.
> In my work it was several seconds to a local Oracle instance.  When
> using
> MapServer in "cgi" mode where a new process is forked and needs to
> connect
> to Oracle for each map request the extra overhead can be quite
> devestating.
> The presume solution to this is to keep the process resident, for
> instance
> by using FastCGI and caching the oracle connection.  This is currently
> not supported by MapServer, but I am working on FastCGI support again  
> on
> behalf of Refractions and so a solution may become available in the
> coming
> months.
>
> Second, by default OGR will collect schema information from all the
> tables (or at least spatial tables) in your database. This can be very
> expensive for a database with alot of tables.  However, this issue is
> unique to OGR connections to Oracle, and should not occur with the more
> direct Oracle support in MapServer.
>
> Best regards,
> --
> --------------------------------------- 
> +--------------------------------
> ------
> I set the clouds in motion - turn up   | Frank Warmerdam,
> warmerdam at pobox.com
> light and sound - activate the windows | http://pobox.com/~warmerdam
> and watch the world go round - Rush    | Geospatial Programmer for Rent
>
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632