problem with ogr oracle spatial layer

[Frank Warmerdam]

Mark Adams wrote:
> An update on my OGR OCI woes: I have got it working. When originally
> setting up the test to connect to my oracle spatial layer I'd followed
> instructions on the mapserver website that called for the creation of 3
> environment variables, for ORACLE_HOME, ORACLE_SID, and TNS_ADMIN. I
> deleted those environment variables, rebooted the machine, and now I can
> connect through ogrinfo and mapserver.

Mark,

I'll have to make a mental note to suggest avoiding those in the future
if people have odd Oracle problems.

> However, I am finding the performance of all ogr approaches to my oracle
> data to be very slow. I'd started out with using OVF virtual data
> connections, and found that to be too slow to be workable: about 6
> seconds to draw 6000 geometries but timing out on querybyrectangle
> functions. So the move to oracle spatial was a test to see if
> performance is better. With the OCI connection it now takes 3 or 4
> seconds to display the layer, which I'd consider to still be very slow,
> but at least the queries don't time out. 
> 
> A shapefile with the same geometries displays in half a second. I'd
> understood that the OVF approach meant no spatial indexing is involved,
> but the same shouldn't be true for the oracle spatial approach - there
> is an rtree index on the layer. 
> 
> Is there a way to improve the OGR OCI performance? Alternately, would
> the mapserver native oracle spatial support be a better way to go?

One thing to establish is how much of the slowness is connection time and
also to be aware of how many connections are being made to the database.
For this I would encourage you to do some tests at the commandline with
ogrinfo.

For instance do an ogrinfo on your database to just list the tables.  If
that takes a long time (ie. more than 1 second) then it is likely that
most of the performance is being lost in making the initial connection
and/or building schema information about the tables in the database.

If there are a lot of layers reported, then it may help to explicitly
list the table(s) you are interested in within the datasource name so
OGR doesn't have to build a schema for everything.

If the simple listing of layers with ogrinfo doesn't take too long,
then the next step would be to query features and see if this is slow
especially with a spatial restriction.  You can use the -spat commandline
option to ogrinfo to restrict the query to a small region.  This should
give you a sense of whether ogr can extract a small spatial area with
only a few features efficiently.  It should be able to.

Ultimately, if connection cost is the major issue, you might want to
investigate using FastCGI.  However, I don't know if there is an
equivelent for PHP applications.

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    | President OSGF, http://osgeo.org