Oracle Spatial Performance

[Frank Warmerdam]

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