Performance with oraclespatial using ogr

[Frank Warmerdam]

Merlijn Simonse wrote:
> Frank,
>
> Thanks for your respond. In my database I have about ten different tables
> with spatial data. The PK fields have a different name in every table. As
> the OCI_FID CONFIG parameter is the same for all tables I probably better
> add an OGR_FID field to the necessary tables.
>
> To test if a unique indexed OGR_FID column improves performance I added
> this column to my table and also changed my DATA parameter to:
> DATA "SELECT OGR_FID, GEOM, LOCATIE_ID from LOCATIE where LOCATIESOORT_ID
> = 10" (I was wondering if "SELECT LOCATIE_ID as OGR_FID..." could also
> work)
>
> I didn't notice any differencin performance. I also tried:
> DATA "SELECT OGR_FID, GEOM, LOCATIE_ID from LOCATIE"
>
> Without the where clause it's very slow as well.
> Am I not using the right DATA parameter to let it use my OGR_FID field?

Merlijn,

In fact, on reflection, I don't think OGR's OCI driver (nor any other driver
for that matter) can do fast feature fetches from a user defined SQL operation.

You could just use the table directly instead of providing your own SQL statement,
and do the "winnowing" of results with normal MapServer mechanism (an EXPRESSION
in a CLASS?). The downside of this is that all the records will be read the
first time through, but the subsequent feature fetch should be fast.

Generally speaking, when you use user defined SQL with OGR, you lose all benefits
of it's own spatial searching and feature id based fetches.  It is much better
to "direct" table access letting OGR manage some of these things.  But currently
there is no mechanism via MapServer to pass off an attribute query when doing
direct table access. What I really ought to do is change the MapServer OGR
binding so that you can say "<tablename> WHERE <query>" in the DATA statement
and have this be interpreted as direct table access, applying the requested
query.  This is really the case that OGR is optimized for.  But currently there
is no way to accomplish this.

In fact, I could hack this in quite easily with a PROCESSING statement that
could hold the spatial query.  I wonder if Daniel would blow a gasket if I
hacked that in today before beta3 is snapped!

I would also echo the other response you got mentioning that the direct
Oracle Spatial support in MapServer now apparently has query support.  If that
can do the job for you, it is likely to be faster than even an optimized
OGR implementation.

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