Re: Pg/ODBC driver connection discovery and speed
От | Greg Campbell |
---|---|
Тема | Re: Pg/ODBC driver connection discovery and speed |
Дата | |
Msg-id | 410A991F.2070502@us.michelin.com обсуждение исходный текст |
Ответ на | Pg/ODBC driver connection discovery and speed (Brev Patterson <bpatterson@backcountry.com>) |
Список | pgsql-odbc |
...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
Вложения
В списке pgsql-odbc по дате отправления: