Performance with oraclespatial using ogr

[Merlijn Simonse]

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?

Kind regards,
Merlijn

> -----Original Message-----
> From: Frank Warmerdam [mailto:warmerdam at pobox.com]
> Sent: Thursday, November 18, 2004 15:57
> To: Merlijn Simonse
> Cc: MAPSERVER-USERS at LISTS.UMN.EDU
> Subject: Re: [UMN_MAPSERVER-USERS] Performance with
> oraclespatial using ogr
>
> Merlijn,
>
> I think you are running into an unfortunate interaction of
> limitations.  The MapServer query architecture uses a two
> pass method.  First it reads the shapes and identifies which
> ones meet the criteria for the query.  Then to do something
> with the query results they are fetched one at a time by their ids.
>
> However, if the OGR OCI driver cannot identify a column that
> it considers to be a unique FID it finds it very hard to
> fetch features by FID.  In particular, it will assign
> sequential FIDs and if you fetch a feature by FID it
> essentially has to read the whole resultset counting its way
> through to the desired feature.  This exhibits a quadratic
> performance in the number of features in the set.  That is,
> operating on twice as many shapes will take 4 times as long.
>
> Do your records have a unique (ie. primary key) column that
> could be used as FID?  If it is indexed then OGR can fetch
> individual records much faster, but you would still need a
> way to tell OGR what field to use.  Currently it will
> automatically use a field called OGR_FID as the FID if it is
> found, and if you use OGR to load data into Oracle it creates
> such a column.  In GDAL 1.2.4 and later you can set the
> OCI_FID configuration option with the name of a column that
> should be used as the FID.  This can be set externally as an
> environment variables, or within a map file in a CONFIG line.
>
> However, I am not absolutely certain that fast feature
> fetching is possible on the results of a custom query in the
> DATA statement.  In fact, scanning the code I see that the
> case of user supplied SELECT statements does not include
> support for the OCI_FID environment variable.  This is a bug
> that can be corrected if you need, but even then you would
> need to include the target FID field in your select
> statement.  Something like.
>
>
> ... in the MAP section of the map file ...
>
> CONFIG "OCI_FID" "ID"
>
> ... in layer definition ...
>
> CONNECTIONTYPE ogr
> CONNECTION "oci: a/b at c"
> DATA "SELECT GEOM, LOCATIE_ID, ID FROM LOCATIE where
> LOCATIESOORT_ID = 10"
>
> Let me know if you would like to pursue this approach, and
> would like me to fix OGR's OCI driver so that it honours the
> OCI_FID config option.