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

[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


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



On 6/1/11 4:45 AM, "hschiebold at codematix.de" <hschiebold at codematix.de>
wrote:

> Dear list,
> 
> We figured out a performance Problem of newer mapserver Versions with
> oracle spatial datasources. As we found out mapserver 6 is about 2
> times slower in drawing same oracle spatial layer as mapserver 5.4. It
> seems that changed msOracleSpatialLayerNextShape is responsible for
> that but we're not sure. This is also the case in version 5.6.4.
> We think that the oracle problem is related to following changed lines in
> maporaclespatial.c (rev9302, about line 2108)
> 
> success = TRY( hand, OCIStmtFetch2( sthand->stmthp, hand->errhp,
> (ub4)ARRAY_SIZE, (ub2)OCI_FETCH_NEXT, (sb4)0, (ub4)OCI_DEFAULT ) ) &&
> TRY( hand, OCIAttrGet( (dvoid *)sthand->stmthp, (ub4)OCI_HTYPE_STMT,
> (dvoid*)&sthand->rows_fetched, (ub4 *)0, (ub4)OCI_ATTR_ROWS_FETCHED,
> hand->errhp ) )&& TRY( hand, OCIAttrGet( (dvoid *)sthand->stmthp,
> (ub4)OCI_HTYPE_STMT, (dvoid*)&sthand->rows_count, (ub4 *)0,
> (ub4)OCI_ATTR_ROW_COUNT, hand->errhp ) );
> 
> There is a significant difference to the code of 5.4.0 where the same
> oracle spatial data layer is 2 times faster. We tested this case with
> two different mapfiles and oracle datasources a few times. The first
> test was a layer with about 15000 points. The difference from 5.4.0
> binary to 5.6.4 binary was from about 0.6 seconds to 1 second in
> drawing this layer (taken from the log). The second test I made with
> other point data from oracle (about 200000 points from a view / the
> sql statement there is slow - about 10 seconds). There the difference
> was about 14 seconds to 31 !!! seconds in drawing the layer. Both
> tests were made with same mapfile and datasource - only changed
> mapserver cgi binary on the same machine.
> 
> Executing the SQL statements (taken from the log) using an SQL client
> results in no difference between these versions. So the problem seems
> to be in what is done on or after fetching the data in mapserver.
> 
> Can somebody explain this behaviour? In our current project we have 54
> layers with oracle spatial data and that issue could be one reason for
> a very bad performance at this time.
> 
> Thanks in advance
> 
> Holger
> _______________________________________________
> mapserver-dev mailing list
> mapserver-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-dev