Pg/ODBC driver connection discovery and speed - Mailing list pgsql-odbc

From Brev Patterson
Subject Pg/ODBC driver connection discovery and speed
Date
Msg-id 6.1.2.0.0.20040730114621.02401ad0@mail.141.com
Whole thread Raw
Responses Re: Pg/ODBC driver connection discovery and speed
List pgsql-odbc
Hello list,

I've searched through FAQs, docs, and the mailing list about this problem,
but haven't found anything, so I hope this isn't a repeat.

I've got a windows machine running some Shipping software, connected to a
postgresql 7.4 database via the Postgres/ODBC driver, behind a secure
stunnel connection.

On SELECT's from this windows machine, everything is great:  The driver
connects, discovers the info it needs, and then that connection stays open
as we do SELECT after SELECT, and it works great.

After the SELECTs, we do UPDATEs back to the database, sending back
Tracking # and weight Information.  Instead of connecting, discovering, and
then UPDATING on that connection over and over, each UPDATE connection is
separate.  So,
for every UPDATE, there is a new connection, a new discovery, etc.

Now, our database is quite large.  The ODBC driver runs queries like this
(this is what I mean by "discovery"):

Jul 30 11:35:05 tenzing postgres[1515]: [253-1] LOG:  statement: select
u.nspname, c.relname, a.attname, a.atttypid, t.typname, a.attnum, a.attlen,
a.at ttypmod, a.attnotnull,
Jul 30 11:35:05 tenzing postgres[1515]: [253-2]  c.relhasrules, c.relkind
from pg_catalog.pg_namespace u, pg_catalog.pg_class c,
pg_catalog.pg_attribute a, pg_catalog.pg_type t
Jul 30 11:35:05 tenzing postgres[1515]: [253-3]  where u.oid =
c.relnamespace and (not a.attisdropped) and c.oid= a.attrelid and
a.atttypid = t.oid and (a.attnum > 0) and
Jul 30 11:35:05 tenzing postgres[1515]: [253-4]  c.relname like
'category_subcat' and u.nspname like 'bcs' order by u.nspname, c.relname,
attnum
Jul 30 11:35:05 tenzing postgres[1515]: [254-1] LOG:  duration: 2.503 ms

on every object/table/etc in our whole database.  This ends up taking 5 to
7 seconds.

Our warehouse workers have to sit there for many seconds after a package
has shipped, waiting for each UPDATE connection to do this discovery,
before the actual UPDATE is done.  (The actual UPDATE query itself is very
fast).

During Christmas, we're going to have so many packages going through, that
this delay will kill us.

I'm wondering if any of the possible solutions are available:

1) Can I force the first UPDATE connection to stay open, meaning only a
single discovery set, and then fast UPDATEs right after each other
somehow?  Would this be part of the driver, or part of our shipping software?

2) Can I force the driver to cut down the amount of discovery it does?  Or
cache the discovery?  (If I could have it discover only certain pertinent
tables, instead of every table and object in our entire database, it would
be fast enough to do this on every UPDATE query connection).  I tried
creating and connecting a specific database user for this, giving it
permissions to only the necessary tables, but those discovery queries still
ran on everything in the database.

3) Are those discovery queries necessary?  Or can we turn them off somehow?

The only option that seems remotely helpful for this would be the option of
using Cursors, but we select and update single rows at a time, not many.

thanks for any help or advice,

Brev Patterson
Backcountry.com


pgsql-odbc by date:

Previous
From: "Dave Page"
Date:
Subject: Re: problem with CVS version
Next
From: "Antonio Pennino"
Date:
Subject: Re: problem with CVS version