Re: Pg/ODBC driver connection discovery and speed

...some Shipping software.
Homegrown? Purchased? Contracted?
I doubt that type of schema discovery is needed for each Update.

I wonder if the "shipping software" is using a recordset update (DAO
.Edit/.Update or ADO recordset.Update) as opposed to pass-through style
SQL INSERTS, and UPDATE, which barring a poorly designed database and
index structure, should be lightning fast.

Brev Patterson wrote:

> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings