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

[hschiebold at codematix.de]

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