[Mapserver-users] Slow Oracle connection

[Frank Warmerdam]

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