WFS-SERVER performance on oraclespatial layers

Sun Jun 12 22:09:54 PDT 2005

Ludovic,

see also the following bug report for more info:

http://mapserver.gis.umn.edu/bugs/show_bug.cgi?id=1292

I would recommend adding yourself to the cc.

A short background information: since Mapserver was originally designed for shapefiles, the query mechanism is designed in a way that it fetches features based on id, so that's why for every feature a query is performed. It is really not efficient for database sources. There have been quite a few discussions about this in the past especially on the dev mailing list.

Best regards,
Bart

Bart van den Eijnden
Syncera IT Solutions
Postbus 270
2600 AG  DELFT

tel.nr.: 015-7512436
email: BEN at Syncera-ITSolutions.nl
>>> ludovic gnemmi <l_gnemmi at YAHOO.FR> 06/10/05 18:30 PM >>>
Hi,
Performance is very bad on my oraclespatial layer with
wfs getfeature request...i see (with --enable-debug
mode) on my apache error log that my initial query
produce new queries on each shape object one by one.
is it correct? I maybe miss something...

Here is my query:
DATA "GEOM FROM (SELECT * FROM TABLE WHERE
FIELD1='FILTER1') USING UNIQUE ID"

and my log:
[Fri Jun 10 08:36:42 2005].831748
msOracleSpatialLayerOpen called with: GEOM FROM
(SELECT * FROM  TABLE WHERE FIELD1='FILTER1') using
unique Id
[Fri Jun 10 08:36:43 2005].72897
msOracleSpatialLayerOpen. Shared connection not
available. Creating one.
[Fri Jun 10 08:36:43 2005].73019 msConnPoolRegister(my
connection string,0x8195588)
[Fri Jun 10 08:36:43 2005].115515
msOracleSpatialLayerFreeItemInfo was called.
[Fri Jun 10 08:36:43 2005].115712
msOracleSpatialLayerInitItemInfo was called.
[Fri Jun 10 08:36:43 2005].115763
msOracleSpatialLayerWhichShapes was called.
[Fri Jun 10 08:36:43 2005].115996
msOracleSpatialLayerWhichShapes. Using this Sql to
retrieve the data: SELECT ALL_MY_FIELDS, GEOM FROM
(SELECT * FROM  TABLE WHERE FIELD1='FILTER1') WHERE
SDO_FILTER( GEOM, MDSYS.SDO_GEOMETRY(2003, NULL,
NULL,MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),MDSYS.SDO_ORDINATE_ARRAY(868470,83215,965015,165840)
),'querytype=window') = 'TRUE'
[Fri Jun 10 08:36:58 2005].620540
msOracleSpatialLayerFreeItemInfo was called.
[Fri Jun 10 08:36:58 2005].620689
msOracleSpatialLayerClose was called. Layer
connection: my connection string
[Fri Jun 10 08:36:58 2005].620709
msOracleSpatialLayerClose. Cleaning layerinfo
handlers.
[Fri Jun 10 08:36:58 2005].620910
msOracleSpatialLayerClose. Cleaning Oracle handlers.
[Fri Jun 10 08:36:58 2005].620931
msConnPoolRelease(layer,my connection
string,0x8195588)
[Fri Jun 10 08:36:58 2005].620959 msConnPoolClose(my
connection string,0x8195588)
[Fri Jun 10 08:36:58 2005].630369
msOracleSpatialLayerOpen called with: GEOM FROM
(SELECT * FROM  TABLE WHERE FIELD='FILTER1') using
unique TABLE_id
[Fri Jun 10 08:36:58 2005].673861
msOracleSpatialLayerOpen. Shared connection not
available. Creating one.
[Fri Jun 10 08:36:58 2005].673966
msConnPoolRegister(my_layer,my connection
string,0x8192f30)
[Fri Jun 10 08:36:58 2005].679970
msOracleSpatialLayerFreeItemInfo was called.
[Fri Jun 10 08:36:58 2005].680102
msOracleSpatialLayerGetItems was called.
[Fri Jun 10 08:36:58 2005].685755
msOracleSpatialLayerInitItemInfo was called.
[Fri Jun 10 08:36:58 2005].685933
msOracleSpatialLayerGetShape was called. Using the
record = 82314.
[Fri Jun 10 08:36:58 2005].685984
msOracleSpatialLayerGetShape. Sql: SELECT
ALL_MY_FIELDS, GEOM FROM (SELECT * FROM  TABLE WHERE
FIELD1='FILTER1') WHERE Id = 82314
[Fri Jun 10 08:36:58 2005].707379
msOracleSpatialLayerGetShape was called. Using the
record = 82315.
[Fri Jun 10 08:36:58 2005].707544
msOracleSpatialLayerGetShape. Sql: SELECT
ALL_MY_FIELDS, GEOM FROM (SELECT * FROM  TABLE WHERE
FIELD1='FILTER1') WHERE Id = 82315
[Fri Jun 10 08:36:58 2005].712321
msOracleSpatialLayerGetShape was called. Using the
record = 82316.
[Fri Jun 10 08:36:58 2005].712481
msOracleSpatialLayerGetShape. Sql: SELECT
ALL_MY_FIELDS, GEOM FROM (SELECT * FROM  TABLE WHERE
FIELD1='FILTER1') WHERE Id = 82316
[Fri Jun 10 08:36:58 2005].716865
msOracleSpatialLayerGetShape was called. Using the
record = 82317.
[Fri Jun 10 08:36:58 2005].717172
msOracleSpatialLayerGetShape. Sql: SELECT
ALL_MY_FIELDS, GEOM FROM (SELECT * FROM  TABLE WHERE
FIELD1='FILTER1') WHERE Id = 82317
[Fri Jun 10 08:36:58 2005].722447
msOracleSpatialLayerGetShape was called. Using the
record = 82318.
....

Thanks for your help

Ludovic

___________________________________________________________________________
Appel audio GRATUIT partout dans le monde avec le nouveau Yahoo! Messenger
Téléchargez cette version sur http://fr.messenger.yahoo.com