Skip site navigation (1) Skip section navigation (2)

Pg/ODBC driver connection discovery and speed

From: Brev Patterson <bpatterson(at)backcountry(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Pg/ODBC driver connection discovery and speed
Date: 2004-07-30 17:59:35
Message-ID: 6.1.2.0.0.20040730114621.02401ad0@mail.141.com (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-odbc by date

Next:From: Antonio PenninoDate: 2004-07-30 18:29:25
Subject: Re: problem with CVS version
Previous:From: Dave PageDate: 2004-07-30 13:03:17
Subject: Re: problem with CVS version

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group