<HTML>
<HEAD>
<TITLE>Re: [mapserver-dev] Performance problem with oracle spatial in mapserver 6</TITLE>
</HEAD>
<BODY>
<FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'>All,<BR>
<BR>
I’ve traced the performance hit in Oracle Spatial to the use of a scrollable cursor in the main execute statement (trunk Line 2210).<BR>
<BR>
Changing from <BR>
<BR>
</SPAN></FONT><FONT SIZE="2"><FONT FACE="Consolas, Courier New, Courier"><SPAN STYLE='font-size:10pt'>OCIStmtExecute( hand->svchp, sthand->stmthp, hand->errhp, (ub4)ARRAY_SIZE, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, (ub4) OCI_STMT_SCROLLABLE_READONLY ) )<BR>
</SPAN></FONT></FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'> <BR>
To <BR>
<BR>
</SPAN></FONT><FONT SIZE="2"><FONT FACE="Consolas, Courier New, Courier"><SPAN STYLE='font-size:10pt'>OCIStmtExecute( hand->svchp, sthand->stmthp, hand->errhp, (ub4)ARRAY_SIZE, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, (ub4)OCI_DEFAULT ) )<BR>
</SPAN></FONT></FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'> <BR>
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.<BR>
<BR>
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?<BR>
<BR>
Mike<BR>
<BR>
<BR>
-- <BR>
Michael Smith<BR>
Remote Sensing/GIS Center<BR>
US Army Corps of Engineers<BR>
<BR>
<BR>
On 6/1/11 9:32 AM, "Holger Schiebold" <<a href="hschiebold@codematix.de">hschiebold@codematix.de</a>> wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'>Hi Mike,<BR>
<BR>
both datasources are point data in one view / table with srid and the<BR>
performance problem has nothing to do with transforming or the oracle<BR>
itself (i checked every statement). I tested it again - the only thing i<BR>
change is mapserver binary from 5.4.0 to 5.6.4 and i can see in the<BR>
logfile and in my wms client the performance difference. Both views /<BR>
tables have more then 30 columns which at the end are used in the<BR>
getFeatureInfo. But the performance problem i have already with a normal<BR>
getMap request to the mapserver binary.<BR>
<BR>
regards<BR>
<BR>
Holger<BR>
<BR>
<BR>
<BR>
Am 01.06.2011 12:55, schrieb Smith, Michael ERDC-CRREL-NH:<BR>
> Holger,<BR>
><BR>
> I've done some performance testing on MapServer (a number of versions,<BR>
> including 6) using Oracle Spatial (its our main data source) and haven't run<BR>
> into this issue. I'll do some checks today with some large datasets and see<BR>
> if I can replicate the issue.<BR>
><BR>
> Are these 2d, 3d or 4d datasets? Points, lines or polygons? Do these layers<BR>
> have defined SRIDs? Same input/output projection?<BR>
><BR>
> Mike<BR>
><BR>
><BR>
<BR>
<BR>
--<BR>
Holger Schiebold<BR>
<BR>
<BR>
____________________<BR>
codematix GmbH<BR>
Felsbachstrasse 5/7<BR>
D-07745 Jena<BR>
Tel. +49 (3641) 3038-34<BR>
www.codematix.de<BR>
<BR>
<BR>
</SPAN></FONT></BLOCKQUOTE>
</BODY>
</HTML>