[mapserver-users] MapServer Oracle performance

[Andy Colson]

Michael Shishcu wrote:
> Hi everyone.
> 
> I did some research and found a strange thing. Hope I'm wrong, but...
> It seems to me that when mapserver fetches data from the database, all 
> parameters for where clause of the query string (for example coordinates 
> for MDSYS.SDO_ORDINATE_ARRAY() procedure) are string literals and not 
> oracle's bind variables. This forces the DB to "hard parse" every query 
> from mapserver. But hard parse of the query is very CPU intensive 
> process, so data fetching is slower than it could be.
> Can anyone confirm this, and if so, is there a workaround of such a problem?
> 
> Regards, Michael
> 

Are you talking about preparing a query, like:

"select * from map where the_geom && ?"

and then executing the prepared query many-many times?

or are you talking about sending the arguments to a prepared query in 
"binary" vs string format?  (I know nothing about oracle, my terms are 
Postgres, but I assume close)

If you are talking preparing once, and firing many-many times, then yes 
it would be a speed up.  But just the format the params are sent in, I 
doubt would make a difference.  Parsing a string like "30,40,50,60" into 
an array [30, 40, 50, 60] should be very fast.  (and besides, somebody 
has to build the array, the time has to be spent somewhere, be it on the 
client or the server).

Do you have any timing's to show the difference between the methods? 
(I'm thinking like a perl script or something)  Or any benchmarks on the 
web someplace?

As for workaround, I would not think so.  You'd probably have to get 
your hands into the guts of mapserv and play around with the db 
interface stuff.

-Andy