Oracle Spatial Performance

[Smith, Michael ERDC-CRREL-NH]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20040926/132f6278/attachment.html