[mapserver-dev] Performance problem with oracle spatial in mapserver 6

[Smith, Michael ERDC-CRREL-NH]

All,

I¹ve traced the performance hit in Oracle Spatial to the use of a scrollable
cursor in the main execute statement (trunk Line 2210).

Changing from 

  OCIStmtExecute( hand->svchp, sthand->stmthp, hand->errhp, (ub4)ARRAY_SIZE,
(ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, (ub4)
OCI_STMT_SCROLLABLE_READONLY ) )
          
To 

  OCIStmtExecute( hand->svchp, sthand->stmthp, hand->errhp, (ub4)ARRAY_SIZE,
(ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, (ub4)OCI_DEFAULT ) )
          
On a point layer with 78K points decreased time from 13.033s to 8.509s,
which is almost exactly the times I measured from rev 9295 and previous.

The next obvious question is, are there specific use cases where the
scrollable cursor needs to be used? For straight cgi drawing (wms and
mode=map), it works fine but I haven¹t investigated the various query modes,
mapscript, etc. Was there a specific reason that the cursor type was changed
to SCROLLABLE?

Mike


-- 
Michael Smith
Remote Sensing/GIS Center
US Army Corps of Engineers


On 6/1/11 9:32 AM, "Holger Schiebold" <hschiebold at codematix.de> wrote:

> Hi Mike,
> 
> both datasources are point data in one view / table with srid and the
> performance problem has nothing to do with transforming or the oracle
> itself (i checked every statement). I tested it again - the only thing i
> change is mapserver binary from 5.4.0 to 5.6.4 and i can see in the
> logfile and in my wms client the performance difference. Both views /
> tables have more then 30 columns which at the end are used in the
> getFeatureInfo. But the performance problem i have already with a normal
> getMap request to the mapserver binary.
> 
> regards
> 
> Holger
> 
> 
> 
> Am 01.06.2011 12:55, schrieb Smith, Michael ERDC-CRREL-NH:
>> > Holger,
>> >
>> > I've done some performance testing on MapServer (a number of versions,
>> > including 6) using Oracle Spatial (its our main data source) and haven't >>
run
>> > into this issue.  I'll do some checks today with some large datasets and >>
see
>> > if I can replicate the issue.
>> >
>> > Are these 2d, 3d or 4d datasets? Points, lines or polygons? Do these layers
>> > have defined SRIDs? Same input/output projection?
>> >
>> > Mike
>> >
>> >
> 
> 
> --
> Holger Schiebold
> 
> 
> ____________________
> codematix GmbH
> Felsbachstrasse 5/7
> D-07745 Jena
> Tel. +49 (3641) 3038-34
> www.codematix.de
> 
> 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-dev/attachments/20110601/9eba8285/attachment.html