<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2655.35">
<TITLE>RE: [UMN_MAPSERVER-USERS] Oracle Spatial Performance</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>It is also very important for performance reasons to increase the array size in maporaclespatial.c and recompile. We generally recommend setting array size to ~ 1000. We generally see an order of magnitude increase in speed with this change. </FONT></P>
<P><FONT SIZE=2>Mike Smith </FONT>
<BR><FONT SIZE=2>USACE-CRREL</FONT>
<BR><FONT SIZE=2>Remote Sensing GIS Center of Expertise</FONT>
<BR><FONT SIZE=2>Hanover, NH</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: Frank Warmerdam</FONT>
<BR><FONT SIZE=2>To: MAPSERVER-USERS@LISTS.UMN.EDU</FONT>
<BR><FONT SIZE=2>Sent: 9/25/2004 7:34 PM</FONT>
<BR><FONT SIZE=2>Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance</FONT>
</P>
<P><FONT SIZE=2>Vladimir Guzmán wrote:</FONT>
<BR><FONT SIZE=2>> Hello, List.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> I don't know if this is an off-topic question but, I really need some</FONT>
<BR><FONT SIZE=2>help.</FONT>
<BR><FONT SIZE=2>> I've worked a lot with mapserver, specially with phpmapscript, and</FONT>
<BR><FONT SIZE=2>with</FONT>
<BR><FONT SIZE=2>> shape files and postgis data.</FONT>
<BR><FONT SIZE=2>> Finally, about 6 months ago, we received a request from a customer in</FONT>
<BR><FONT SIZE=2>> order to develop a web application that would be able to connect with</FONT>
<BR><FONT SIZE=2>> oracle data.</FONT>
<BR><FONT SIZE=2>> Well, after face all kinds of issues with oracle spatial, everything</FONT>
<BR><FONT SIZE=2>is</FONT>
<BR><FONT SIZE=2>> working fine now. Except the speed.</FONT>
<BR><FONT SIZE=2>> Every map request to oracle spatial takes at least 20 seconds, with</FONT>
<BR><FONT SIZE=2>very</FONT>
<BR><FONT SIZE=2>> small geometries, and in some cases it takes 2 minutes or more.</FONT>
<BR><FONT SIZE=2>> I would like you to give me some clue, because I've worked with</FONT>
<BR><FONT SIZE=2>postgis</FONT>
<BR><FONT SIZE=2>> and shps and I've never had this kind of problems.</FONT>
<BR><FONT SIZE=2>> All the geometries are indexed.</FONT>
<BR><FONT SIZE=2>> The time responses are almost the same working with ORACLESPATIAL and</FONT>
<BR><FONT SIZE=2>> OGR connection.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> For example:</FONT>
<BR><FONT SIZE=2>> The command:</FONT>
<BR><FONT SIZE=2>> ogrinfo OCI:xxx/xxx@xxx -sql "SELECT ID, ORA_GEOMETRY, NOMBRE FROM</FONT>
<BR><FONT SIZE=2>> SIG_DEPTOS"</FONT>
<BR><FONT SIZE=2>> Takes 1 minute.</FONT>
<BR><FONT SIZE=2>> The same geometry in shp:</FONT>
<BR><FONT SIZE=2>> ogrinfo -al departamentos.shp</FONT>
<BR><FONT SIZE=2>> Takes less than one second.</FONT>
</P>
<P><FONT SIZE=2>Vladimir,</FONT>
</P>
<P><FONT SIZE=2>I can think of a couple of issues.</FONT>
</P>
<P><FONT SIZE=2>First, the connection overhead to Oracle tends to be pretty expensive.</FONT>
<BR><FONT SIZE=2>In my work it was several seconds to a local Oracle instance. When</FONT>
<BR><FONT SIZE=2>using</FONT>
<BR><FONT SIZE=2>MapServer in "cgi" mode where a new process is forked and needs to</FONT>
<BR><FONT SIZE=2>connect</FONT>
<BR><FONT SIZE=2>to Oracle for each map request the extra overhead can be quite</FONT>
<BR><FONT SIZE=2>devestating.</FONT>
<BR><FONT SIZE=2>The presume solution to this is to keep the process resident, for</FONT>
<BR><FONT SIZE=2>instance</FONT>
<BR><FONT SIZE=2>by using FastCGI and caching the oracle connection. This is currently</FONT>
<BR><FONT SIZE=2>not supported by MapServer, but I am working on FastCGI support again on</FONT>
<BR><FONT SIZE=2>behalf of Refractions and so a solution may become available in the</FONT>
<BR><FONT SIZE=2>coming</FONT>
<BR><FONT SIZE=2>months.</FONT>
</P>
<P><FONT SIZE=2>Second, by default OGR will collect schema information from all the</FONT>
<BR><FONT SIZE=2>tables (or at least spatial tables) in your database. This can be very</FONT>
<BR><FONT SIZE=2>expensive for a database with alot of tables. However, this issue is</FONT>
<BR><FONT SIZE=2>unique to OGR connections to Oracle, and should not occur with the more</FONT>
<BR><FONT SIZE=2>direct Oracle support in MapServer.</FONT>
</P>
<P><FONT SIZE=2>Best regards,</FONT>
<BR><FONT SIZE=2>--</FONT>
<BR><FONT SIZE=2>---------------------------------------+--------------------------------</FONT>
<BR><FONT SIZE=2>------</FONT>
<BR><FONT SIZE=2>I set the clouds in motion - turn up | Frank Warmerdam,</FONT>
<BR><FONT SIZE=2>warmerdam@pobox.com</FONT>
<BR><FONT SIZE=2>light and sound - activate the windows | <A HREF="http://pobox.com/~warmerdam" TARGET="_blank">http://pobox.com/~warmerdam</A></FONT>
<BR><FONT SIZE=2>and watch the world go round - Rush | Geospatial Programmer for Rent</FONT>
</P>
</BODY>
</HTML>