problem with ogr oracle spatial layer

[Mark Adams]

Frank, 

Running ogrinfo on the command line, the connection is fast (especially
if the layer name is specified). In general the results from spatial
and/or attribute queries run at the command line are fast as well, i.e.
the result back from oracle happens right away, after which it's really
just the time it takes to flash the results on screen. 

Despite all this, it still takes 3 to 4 seconds to draw the layer at
full extents (I'm going by the debug info from mapserver). If the OGR
work is taking less than a second, the rest is the actual map rendering
time? Could this be related to the data itself? (In my case the data
covers a huge swath of northern Canada, with lots of polygons
overlapping and covering large geographical areas. But then again, it
draws in a quarter second if I convert to a shapefile.)

We are already specifying the layer name explicitly, and have PHP set up
to use fastcgi. The mapserver build we're using is also compiled with
fastcgi support.

------------------------------
Mark Adams
Senior Analyst & Project Manager
Cuesta Systems (DPRA Canada)
5230 South Service Road
Burlington, ON L7L 5K2
Phone: 905-333-4544 x14
Fax: 905-333-0455
Email: mark.adams at dpra.com


-----Original Message-----
From: Frank Warmerdam [mailto:fwarmerdam at gmail.com] On Behalf Of Frank
Warmerdam
Sent: June 7, 2006 2:19 PM
To: Mark Adams
Cc: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] problem with ogr oracle spatial layer

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