[ psqlodbc-Bugs-1000714 ] Driver gets progressively slower Multi-threading retrieval

Bugs item #1000714, was opened at 2006-08-24 19:40
You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10007 14&group_id=1000125

Category: None
Group: None
>Status: Closed
Resolution: None
Priority: 3
Submitted By: Farid Zidan (faridz)
>Assigned to: Hiroshi Inoue (hinoue)
Summary: Driver gets progressively slower Multi-threading retrieval

Initial Comment:
4 or more threads active on the same connection doing sequential data retrieval over data of 300 tables. Some tables have 200k rows. Driver gets progressively slower retrieving large table data until and it appears to go into an infinite loop using 100% cpu and not doing any useful work.

Not able to isolate this issue. But you can reproduce it using CompareData application www.zidsoft.com

Required: one or two databases with over 300 tables with at least 6-8 tables with 200k or more rows.

Add a DBMS comparison for the two datasources in CompareData. Open a Compare Data tab to compare all the data of the two datasources tables and set the max thread count to 6 or 8 threads and then start the comparison. The first few tables data are compared quickly but as the application progesses through the tables and encounters tables with large number of rows, the driver slows down more and more.

If you set the max thread count to 1, then the driver appears to works fine.

This issue does not happen with other DBMS drivers and appear specific to the PostgreSQL ODBC driver. I am using the latest version of the driver psqlodbc35w.dll dated 8/24/06 2:33pm ( an 8.02 bug fix by Hiroshi Inoue ).

Please let me know if you need any help reproducing this issue.

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

Comment By: Farid Zidan (faridz)
Date: 2006-12-01 14:01

Message:
This appears to be a server issue and not a driver issue.
Used the same PostgreSQL driver but with EnterpriseDB server
without exhibiting this issue.

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-28 21:35

Message:
Correction. After trying again I found out that the
Updatable Cursors option setting does not matter.

Tested UseDeclareFetch option by setting it on ( Updatable
Cursors unchecked ) and found out that the driver locks up
when I tested using 8 threads.

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

Comment By: Hiroshi Inoue (hinoue)
Date: 2006-08-25 23:16

Message:
Hi Farid,

> This issue does not happen if Updatable
> Cursors option is unchecked in Page 2 of
> the driver ODBC datasource advanced options
> tag.

Does CompareData open cursors in READ_ONLY
mode ?
What is the setting of the UseDeclareFetch
option for the Datasource ?

regards,
Hiroshi Inoue

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

Comment By: Farid Zidan (faridz)
Date: 2006-08-25 10:09

Message:
This issue does not happen if Updatable Cursors option is
unchecked in Page 2 of the driver ODBC datasource advanced
options tag. Driver not releasing locks on statement handle
after the statement handle is feed? or not efficiently
removing/searching open statement handles?

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

You can respond by visiting:
http://pgfoundry.org/tracker/?func=detail&atid=538&aid=10007 14&group_id=1000125

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly