[Mapserver-users] Slow Oracle connection

Thu Jul 8 08:11:06 PDT 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_01C464FD.8EF7DD6E
Content-Type: text/plain

Frank,

In the future to avoid this on large tables in oracle, you could get the
extents from the sdo_geom_metadata_table and get the row_count from the
data_dictionary. That would be a lot faster. 

Mike Smith
GIS Specialist/Physical Scientist/Oracle Developer
Remote Sensing/GIS Center of Expertise
Army Engineer Research & Development Center 
Hanover, NH
(603) 646-4765
michael.smith at erdc.usace.army.mil

-----Original Message-----
From: Frank Warmerdam [mailto:warmerdam at pobox.com] 
Sent: Thursday, July 08, 2004 10:12 AM
To: COUTUJF at dfo-mpo.gc.ca
Cc: mapserver-users at lists.gis.umn.edu
Subject: Re: [Mapserver-users] Slow Oracle connection

COUTUJF at dfo-mpo.gc.ca wrote:
> Hello,
> 	I'm trying to connect to an Oracle (not spatial) through an ovf file

> and ODBC. I tested the DSN with Access and everything is ok and runs fast.
> When I'm testing my connection with "ogrinfo sipa.ovf", the connection 
> does occur but it's so slow. It can take up to 15 minutes to get the info
back!!!
> 
> Using the same connection with MapServer is not better. I probably hit 
> a timeout before anything can be drawn.
> 
> The table scheme is called SIPAQBC and the name of the table is 
> MARINFO so here is my ovf file:
> 
> <OGRVRTDataSource>
> <OGRVRTLayer name="SIPAQBC">
> <SrcDataSource>ODBC:SIPAQBC/SIPAQBC at SIPA</SrcDataSource>
> <SrcLayer>MARINFO</SrcLayer>
> <GeometryType>wkbPoint</GeometryType>
> <LayerSRS>WGS84</LayerSRS>
> <GeometryField encoding="PointFromColumns" x="LONGITUDE" 
> y="LATITUDE"/> </OGRVRTLayer> </OGRVRTDataSource>
> 
> The funny thing is that the output of the "ogrinfo sipa.ovf" command  
> always begins with:
> 
> ERROR 4: Update access not supported for VRT datasources.

JF,

You can use the -ro (read only) flag with ogrinfo to avoid first trying to
open the source in update mode.

> ERROR 1: No column definitions found for table 'Activit?', layer not
usable.
> ERROR 1: No column definitions found for table 'Activit? CPA', layer 
> not

I'm not excactly sure what is happening here.  Do you have any table(s) with
accents in the table names?  It almostly looks as if the table name had an
accent converted into a "?" during reading and this made it impossible to
query for the table field list.

As to your general problem, I am not certain why but I wouldn't be surprised
to find that alot of time is being spent scanning the layer list or doing
the spatial extents/feature count required by ogrinfo.  The first two things
that ogrinfo reports for a layer are the feature count and geographic
extents.  It can require two complete passes through the table to collect
this information.  Are there alot of records in your table?

If the problem is that there are alot of tables in your database, then you
can just list the tables you want to access in your connection string.

eg.
<SrcDataSource>ODBC:SIPAQBC/SIPAQBC at SIPA,MARINFO</SrcDataSource>

Note that I don't think MapServer normally queries for feature count or
geographic extents so that slowdown in ogrinfo won't occur in MapServer.

Best regards,
-- 
---------------------------------------+--------------------------------
---------------------------------------+------
I set the clouds in motion - turn up   | Frank Warmerdam,
warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent

_______________________________________________
Mapserver-users mailing list
Mapserver-users at lists.gis.umn.edu
http://lists.gis.umn.edu/mailman/listinfo/mapserver-users

------_=_NextPart_001_01C464FD.8EF7DD6E
Content-Type: text/html
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=3DUS-ASCII">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2655.35">
<TITLE>RE: [Mapserver-users] Slow Oracle connection</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Frank,</FONT>
</P>

In the future to avoid this on large tables in =
oracle, you could get the extents from the sdo_geom_metadata_table and =
get the row_count from the data_dictionary. That would be a lot faster. =

<P><FONT SIZE=3D2>Mike Smith</FONT>
<BR><FONT SIZE=3D2>GIS Specialist/Physical Scientist/Oracle =
Developer</FONT>
<BR><FONT SIZE=3D2>Remote Sensing/GIS Center of Expertise</FONT>
<BR><FONT SIZE=3D2>Army Engineer Research & Development Center =
</FONT>
<BR><FONT SIZE=3D2>Hanover, NH</FONT>
<BR><FONT SIZE=3D2>(603) 646-4765</FONT>
<BR><FONT SIZE=3D2>michael.smith at erdc.usace.army.mil</FONT>
<BR><FONT SIZE=3D2> </FONT>
</P>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Frank Warmerdam [<A =
HREF=3D"mailto:warmerdam at pobox.com">mailto:warmerdam at pobox.com</A>] =
</FONT>
<BR><FONT SIZE=3D2>Sent: Thursday, July 08, 2004 10:12 AM</FONT>
<BR><FONT SIZE=3D2>To: COUTUJF at dfo-mpo.gc.ca</FONT>
<BR><FONT SIZE=3D2>Cc: mapserver-users at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2>Subject: Re: [Mapserver-users] Slow Oracle =
connection</FONT>
</P>

COUTUJF at dfo-mpo.gc.ca wrote:
 > Hello,
 >       I'm trying to =
connect to an Oracle (not spatial) through an ovf file 
 > and ODBC. I tested the DSN with Access and =
everything is ok and runs fast.
 > When I'm testing my connection with =
"ogrinfo sipa.ovf", the connection 
 > does occur but it's so slow. It can take up to =
15 minutes to get the info back!!!
 > 
 > Using the same connection with MapServer is not =
better. I probably hit 
 > a timeout before anything can be drawn.
 > 
 > The table scheme is called SIPAQBC and the name =
of the table is 
 > MARINFO so here is my ovf file:
 > 
 > <OGRVRTDataSource>
 > <OGRVRTLayer =
name=3D"SIPAQBC">
 > =
<SrcDataSource>ODBC:SIPAQBC/SIPAQBC at SIPA</SrcDataSource></FO=
NT>
 > <SrcLayer>MARINFO</SrcLayer>
 > =
<GeometryType>wkbPoint</GeometryType>
 > <LayerSRS>WGS84</LayerSRS>
 > <GeometryField =
encoding=3D"PointFromColumns" x=3D"LONGITUDE" =

 > y=3D"LATITUDE"/> =
</OGRVRTLayer> </OGRVRTDataSource>
 > 
 > The funny thing is that the output of the =
"ogrinfo sipa.ovf" command  
 > always begins with:
 > 
 > ERROR 4: Update access not supported for VRT =
datasources.

<P><FONT SIZE=3D2>JF,</FONT>
</P>

<P><FONT SIZE=3D2>You can use the -ro (read only) flag with ogrinfo to =
avoid first trying to open the source in update mode.</FONT>
</P>

> ERROR 1: No column definitions found for table =
'Activit?', layer not usable.
 > ERROR 1: No column definitions found for table =
'Activit? CPA', layer 
 > not

<P><FONT SIZE=3D2>I'm not excactly sure what is happening here.  =
Do you have any table(s) with accents in the table names?  It =
almostly looks as if the table name had an accent converted into a =
"?" during reading and this made it impossible to query for =
the table field list.</FONT></P>

<P><FONT SIZE=3D2>As to your general problem, I am not certain why but =
I wouldn't be surprised to find that alot of time is being spent =
scanning the layer list or doing the spatial extents/feature count =
required by ogrinfo.  The first two things that ogrinfo reports =
for a layer are the feature count and geographic extents.  It can =
require two complete passes through the table to collect this =
information.  Are there alot of records in your table?</FONT></P>

<P><FONT SIZE=3D2>If the problem is that there are alot of tables in =
your database, then you can just list the tables you want to access in =
your connection string.</FONT></P>

<P><FONT SIZE=3D2>eg.</FONT>
<BR><FONT =
SIZE=3D2><SrcDataSource>ODBC:SIPAQBC/SIPAQBC at SIPA,MARINFO</SrcD=
ataSource></FONT>
</P>

<P><FONT SIZE=3D2>Note that I don't think MapServer normally queries =
for feature count or geographic extents so that slowdown in ogrinfo =
won't occur in MapServer.</FONT></P>

<P><FONT SIZE=3D2>Best regards,</FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------+-----------------------=
---------</FONT>
<BR><FONT =
SIZE=3D2>---------------------------------------+------</FONT>
<BR><FONT SIZE=3D2>I set the clouds in motion - turn up   | =
Frank Warmerdam, warmerdam at pobox.com</FONT>
<BR><FONT SIZE=3D2>light and sound - activate the windows | <A =
HREF=3D"http://pobox.com/~warmerdam" =
TARGET=3D"_blank">http://pobox.com/~warmerdam</A></FONT>
<BR><FONT SIZE=3D2>and watch the world go round - =
Rush    | Geospatial Programmer for Rent</FONT>
</P>

<P><FONT =
SIZE=3D2>_______________________________________________</FONT>
<BR><FONT SIZE=3D2>Mapserver-users mailing list</FONT>
<BR><FONT SIZE=3D2>Mapserver-users at lists.gis.umn.edu</FONT>
<BR><FONT SIZE=3D2><A =
HREF=3D"http://lists.gis.umn.edu/mailman/listinfo/mapserver-users" =
TARGET=3D"_blank">http://lists.gis.umn.edu/mailman/listinfo/mapserver-us=
ers</A></FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C464FD.8EF7DD6E--