[Mapserver-users] RE: Slow Oracle connection

[COUTUJF at dfo-mpo.gc.ca]

Hello everyone,
			I did not see my reply back to Frank in today's
digest so I fear it has not been published. Since there are some very
interesting info in it (mainly the results of Frank's comments) , I'm taking
the chance of republishing it again. Sorry if we end up with the same
message twice...

thanks,


JF
Canadian Coast Guard
coutujf at dfo-mpo.gc.ca

------------------------------------------------------------------------

Dear Frank,
		adding the name of the table to the ovf file did the trick.
Plus, I don't get the funny table names with the accents anymore...

There are a lots of tables in this database, but the table I'm trying to
connect to only has about 4000 records.

The problem I have now, is that my ogrinfo requests are good and quite fast
(couple of seconds...) but my MapServer requests still take forever (and
actually never comes back..).

Here is the ogrinfo output:

INFO: Open of `sipa.ovf'
using driver `VRT' successful.
1: SIPAQBC (Point)

IF I take a look at the ODBC log file I seem to get stuck at the login: (and
again that is only using MapServer, everything is fine with ogrinfo) 


mapserv         284-be4	ENTER SQLAllocEnv 
		HENV *              013E4850

mapserv         284-be4	EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
		HENV *              0x013E4850 ( 0x00961738)

mapserv         284-be4	ENTER SQLAllocConnect 
		HENV                00961738
		HDBC *              013E4854

mapserv         284-be4	EXIT  SQLAllocConnect  with return code 0
(SQL_SUCCESS)
		HENV                00961738
		HDBC *              0x013E4854 ( 0x009617e0)

mapserv         284-be4	ENTER SQLSetConnectOption 
		HDBC                009617E0
		UWORD                      103 <SQL_LOGIN_TIMEOUT>
		SQLLEN                     5

mapserv         284-be4	EXIT  SQLSetConnectOption  with return code 0
(SQL_SUCCESS)
		HDBC                009617E0
		UWORD                      103 <SQL_LOGIN_TIMEOUT>
		SQLLEN                     5

mapserv         284-be4	ENTER SQLConnectW 
		HDBC                009617E0
		WCHAR *             0x00961910 [      -3] "SIPA\ 0"
		SWORD                       -3 
		WCHAR *             0x1F7C4AA0 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x1F7C4AA0 [      -3] "******\ 0"
		SWORD                       -3 


Here is my .map layer in case that's where the problem lies (but I doubt
it):

LAYER
      NAME SIPA
      TYPE POINT
      CONNECTIONTYPE OGR
      CONNECTION "sipa.ovf" 
      DATA "SIPAQBC" 
      STATUS ON
    
    PROJECTION
      "init=epsg:4326"
    END
    TOLERANCE 5
    CLASS
      SYMBOL mapinfo-sym-34
      SIZE 9
      COLOR 255 0 0
      
    END
   END

any ideas?

thanks,

JF
Canadian Coast Guard
coutujf at dfo-mpo.gc.ca




-----Message d'origine-----
De : Frank Warmerdam [mailto:warmerdam at pobox.com]
Envoyé : Thursday, July 08, 2004 10:12 AM
À : COUTUJF at dfo-mpo.gc.ca
Cc : mapserver-users at lists.gis.umn.edu
Objet : 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