Subject: RE: Slow Oracle connection - msg#00245

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@xxxxxxxxxxxxx

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

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@xxxxxxxxxxxxx




-----Message d'origine-----
De : Frank Warmerdam [mailto:warmerdam@xxxxxxxxx]
Envoyé : Thursday, July 08, 2004 10:12 AM
À : COUTUJF@xxxxxxxxxxxxx
Cc : mapserver-users@xxxxxxxxxxxxxxxxx
Objet : Re: [Mapserver-users] Slow Oracle connection


COUTUJF@xxxxxxxxxxxxx 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@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@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@xxxxxxxxx
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush | Geospatial Programmer for Rent