[mapserver-users] Grave performance issue with MapServer and GDAL/OCI driver

[Smith, Michael ERDC-CRREL-NH]

You can also try using the Mapserver direct Oracle connection rather than
going through OGR.

LAYER
  NAME "aree_stradali"
  STATUS DEFAULT
  TYPE POLYGON
  # Dati da connessione Oracle
  CONNECTIONTYPE ORACLESPATIAL
  CONNECTION "username/secret at localhost/sitfi02t"
  DATA "SPATIAL_COLUMN FROM REPLICA_TOPONOMASTICA.AGGR_AREESTRADA USING
UNIQUE OBJECTID"
  # Dati da shapefile
  #DATA "shape/toponomastica/aree_strada.shp"
  PROJECTION
    "init=epsg:3003"
  END
  DUMP TRUE
  METADATA
    "wfs_title"     "aree_stradali"
    "gml_featureid" "objectid"
  END
  CLASS
    STYLE
      COLOR 255 0 0
      WIDTH 1
    END
  END
END


On 6/29/09 10:51 AM, "Frank Warmerdam" <warmerdam at pobox.com> wrote:

> Niccolo Rigacci wrote:
>> I experienced a severe performance problem with MapServer
>> configured as WFS server, taking data from Oracle via GDAL/OCI
>> driver.
>> 
>> All the features take about 9.6 Mb of xml data (via WFS) or 8.4
>> Mb of text data (via ogrinfo).
>> 
>> Retrieving all the features via WFS from a shapefile takes less
>> than one second, retrieving the same WFS from Oracle takes about
>> 470 seconds. Retrieving data via the ogrinfo utility takes about
>> 3 seconds:
>> 
>> Tests are performed from the same Linux box, taking data in this
>> way:
>> 
>>   1 s: wget <- http <- Remote_host <- MapServer <- GDAL <- shapefile
>> 470 s: wget <- http <- Remote_host <- MapServer <- GDAL/OCI <- localhost <-
>> Oracle
>>   3 s: ogrinfo <- GDAL/OCI <- Remote_host <- Oracle
>> 
>> It seems that wget (int the Oracle case) starts at about 400
>> Kb/sec transfer rate, then it drops down to 13 Kb/sec a few
>> seconds later.
>> 
>> Does somebody have any idea where is the bottleneck? How can I
>> debug such slodown?
> 
> Niccolo,
> 
> This is almost certainly due to the two-pass query approach in mapserver.
> On the first pass the id's of all features matching the query are collected
> and then the features are fetched, one by one, by id.  In the case of the
> oci driver in OGR a fetch by id issues an sql select statement.  So you are
> basically getting one sql select statement per features which is very
> inefficient.
> 
> What you need if you want to do large amounts of feature query - such as for
> WFS - is the new one pass query mechanism that Steve is working on.  I'm not
> exactly clear on it's status.
> 
> Note that this two pass query anti-optimization does not affect feature
> drawing which is accomplished in one pass.  It also does not significantly
> affect formats like shape where fetching by id's has no noticable overhead.
> 
> Best regards,