[Mapserver-dev] RE: [UMN_MAPSERVER-USERS] Oracle Spatial Performance

Sun Sep 26 14:38:55 EDT 2004

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C4A3F7.F6FEF972
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

I don't see one, and we have recommended this change before I believe.=20

Mike Smith

-----Original Message-----
From: Paul Ramsey
To: Smith, Michael ERDC-CRREL-NH
Cc: mapserver-dev at lists.gis.umn.edu
Sent: 9/26/2004 10:45 AM
Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance

Any reason not to simply apply this change to the standard source?

On Sunday, September 26, 2004, at 05:47 AM, Smith, Michael =20
ERDC-CRREL-NH wrote:

> It is also very important for performance reasons to increase the =20
> 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.
>
> Mike Smith
> USACE-CRREL
> Remote Sensing GIS Center of Expertise
> Hanover, NH
>
> -----Original Message-----
> From: Frank Warmerdam
> To: MAPSERVER-USERS at LISTS.UMN.EDU
> Sent: 9/25/2004 7:34 PM
> Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial Performance
>
> Vladimir Guzm=E1n wrote:
> > Hello, List.
> >
> > I don't know if this is an off-topic question but, I really need
some
> help.
> > I've worked a lot with mapserver, specially with phpmapscript, and
> with
> > shape files and postgis data.
> > Finally, about 6 months ago, we received a request from a customer
in
> > order to develop a web application that would be able to connect
with
> > oracle data.
> > Well, after face all kinds of issues with oracle spatial, =
everything
> is
> > working fine now.=A0 Except the speed.
> > Every map request to oracle spatial takes at least 20 seconds, with
> very
> > small geometries, and in some cases it takes 2 minutes or more.
> > I would like you to give me some clue, because I've worked with
> postgis
> > and shps and I've never had this kind of problems.
> > All the geometries are indexed.
> > The time responses are almost the same working with ORACLESPATIAL
and
> > OGR connection.
> >
> > For example:
> > The command:
> > ogrinfo OCI:xxx/xxx at xxx -sql "SELECT ID, ORA_GEOMETRY, NOMBRE FROM
> > SIG_DEPTOS"
> > Takes 1 minute.
> > The same geometry in shp:
> > ogrinfo -al departamentos.shp
> > Takes less than one second.
>
> Vladimir,
>
> I can think of a couple of issues.
>
> First, the connection overhead to Oracle tends to be pretty =
expensive.
> In my work it was several seconds to a local Oracle instance.=A0 When
> using
> MapServer in "cgi" mode where a new process is forked and needs to
> connect
> to Oracle for each map request the extra overhead can be quite
> devestating.
> The presume solution to this is to keep the process resident, for
> instance
> by using FastCGI and caching the oracle connection.=A0 This is =
currently
> not supported by MapServer, but I am working on FastCGI support again

> on
> behalf of Refractions and so a solution may become available in the
> coming
> months.
>
> Second, by default OGR will collect schema information from all the
> tables (or at least spatial tables) in your database. This can be =
very
> expensive for a database with alot of tables.=A0 However, this issue =
is
> unique to OGR connections to Oracle, and should not occur with the
more
> direct Oracle support in MapServer.
>
> Best regards,
> --
> ---------------------------------------=20
> +--------------------------------
> ------
> I set the clouds in motion - turn up=A0=A0 | Frank Warmerdam,
> warmerdam at pobox.com
> light and sound - activate the windows | http://pobox.com/~warmerdam
> and watch the world go round - Rush=A0=A0=A0 | Geospatial Programmer =
for
Rent
>
>
      Paul Ramsey
      Refractions Research
      Email: pramsey at refractions.net
      Phone: (250) 885-0632

------_=_NextPart_001_01C4A3F7.F6FEF972
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2655.35">
<TITLE>RE: [UMN_MAPSERVER-USERS] Oracle Spatial Performance</TITLE>
</HEAD>
<BODY>

I don't see one, and we have recommended this change =
before I believe.

<P><FONT SIZE=3D2>Mike Smith</FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Paul Ramsey</FONT>
<BR><FONT SIZE=3D2>To: Smith, Michael ERDC-CRREL-NH</FONT>
<BR><FONT SIZE=3D2>Cc: mapserver-dev at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2>Sent: 9/26/2004 10:45 AM</FONT>
<BR><FONT SIZE=3D2>Subject: Re: [UMN_MAPSERVER-USERS] Oracle Spatial =
Performance</FONT>
</P>

<P><FONT SIZE=3D2>Any reason not to simply apply this change to the =
standard source?</FONT>
</P>

<P><FONT SIZE=3D2>On Sunday, September 26, 2004, at 05:47 AM, Smith, =
Michael&nbsp; </FONT>
<BR><FONT SIZE=3D2>ERDC-CRREL-NH wrote:</FONT>
</P>

&gt; It is also very important for performance =
reasons to increase the&nbsp; 
 &gt; array size in maporaclespatial.c and recompile. =
We generally recommend

&gt; setting array size to ~ 1000. We generally see =
an order of magnitude&nbsp; 
 &gt; increase in speed with this change.
 &gt;
 &gt; Mike Smith
 &gt; USACE-CRREL
 &gt; Remote Sensing GIS Center of Expertise
 &gt; Hanover, NH
 &gt;
 &gt; -----Original Message-----
 &gt; From: Frank Warmerdam
 &gt; To: MAPSERVER-USERS at LISTS.UMN.EDU
 &gt; Sent: 9/25/2004 7:34 PM
 &gt; Subject: Re: [UMN_MAPSERVER-USERS] Oracle =
Spatial Performance
 &gt;
 &gt; Vladimir Guzm=E1n wrote:
 &gt; &gt; Hello, List.
 &gt; &gt;
 &gt; &gt; I don't know if this is an off-topic =
question but, I really need
 some
 &gt; help.
 &gt; &gt; I've worked a lot with mapserver, =
specially with phpmapscript, and
 &gt; with
 &gt; &gt; shape files and postgis data.
 &gt; &gt; Finally, about 6 months ago, we received a =
request from a customer
 in
 &gt; &gt; order to develop a web application that =
would be able to connect
 with
 &gt; &gt; oracle data.
 &gt; &gt; Well, after face all kinds of issues with =
oracle spatial, everything
 &gt; is
 &gt; &gt; working fine now.=A0 Except the =
speed.
 &gt; &gt; Every map request to oracle spatial takes =
at least 20 seconds, with
 &gt; very
 &gt; &gt; small geometries, and in some cases it =
takes 2 minutes or more.
 &gt; &gt; I would like you to give me some clue, =
because I've worked with
 &gt; postgis
 &gt; &gt; and shps and I've never had this kind of =
problems.
 &gt; &gt; All the geometries are indexed.
 &gt; &gt; The time responses are almost the same =
working with ORACLESPATIAL
 and
 &gt; &gt; OGR connection.
 &gt; &gt;
 &gt; &gt; For example:
 &gt; &gt; The command:
 &gt; &gt; ogrinfo OCI:xxx/xxx at xxx -sql &quot;SELECT =
ID, ORA_GEOMETRY, NOMBRE FROM
 &gt; &gt; SIG_DEPTOS&quot;
 &gt; &gt; Takes 1 minute.
 &gt; &gt; The same geometry in shp:
 &gt; &gt; ogrinfo -al departamentos.shp
 &gt; &gt; Takes less than one second.
 &gt;
 &gt; Vladimir,
 &gt;
 &gt; I can think of a couple of issues.
 &gt;
 &gt; First, the connection overhead to Oracle tends =
to be pretty expensive.
 &gt; In my work it was several seconds to a local =
Oracle instance.=A0 When
 &gt; using
 &gt; MapServer in &quot;cgi&quot; mode where a new =
process is forked and needs to
 &gt; connect
 &gt; to Oracle for each map request the extra =
overhead can be quite
 &gt; devestating.
 &gt; The presume solution to this is to keep the =
process resident, for
 &gt; instance
 &gt; by using FastCGI and caching the oracle =
connection.=A0 This is currently
 &gt; not supported by MapServer, but I am working on =
FastCGI support again

&gt; on
 &gt; behalf of Refractions and so a solution may =
become available in the
 &gt; coming
 &gt; months.
 &gt;
 &gt; Second, by default OGR will collect schema =
information from all the
 &gt; tables (or at least spatial tables) in your =
database. This can be very
 &gt; expensive for a database with alot of =
tables.=A0 However, this issue is
 &gt; unique to OGR connections to Oracle, and should =
not occur with the
 more
 &gt; direct Oracle support in MapServer.
 &gt;
 &gt; Best regards,
 &gt; --
 &gt; --------------------------------------- 
 &gt; +--------------------------------
 &gt; ------
 &gt; I set the clouds in motion - turn up=A0=A0 | =
Frank Warmerdam,
 &gt; warmerdam at pobox.com
 &gt; light and sound - activate the windows | <A =
HREF=3D"http://pobox.com/~warmerdam" =
TARGET=3D"_blank">http://pobox.com/~warmerdam</A>
 &gt; and watch the world go round - Rush=A0=A0=A0 | =
Geospatial Programmer for
 Rent
 &gt;
 &gt;
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Paul Ramsey
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Refractions =
Research
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Email: =
pramsey at refractions.net
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Phone: (250) =
885-0632

</BODY>
</HTML>
------_=_NextPart_001_01C4A3F7.F6FEF972--