Performance with oraclespatial using ogr

[Frank Warmerdam]

Merlijn Simonse wrote:
> Hi all,
>
> I have some oracle layers in my map to which I connect using ogr. I use
> ogr because I also make selections on this layer. Everything performs very
> well until the Oracle table gets too many records. As soon as it has more
> than 10,000 records it can take up to a few minutes to make the map.
> Selecting a polygon from this layer takes even more time and selecting n
> polygons can take more than 5 minutes.
>
> My data parameter is looks like this.
>
>   CONNECTIONTYPE ogr
>   CONNECTION "oci: a/b at c"
>   DATA "SELECT GEOM, LOCATIE_ID FROM LOCATIE where LOCATIESOORT_ID = 10"
>
> Is there anyway I can improve performance changing this query or other
> parameters?

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.

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