Subject: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver

From: "FabianStoehr (@FabianStoehr)" <[email-masked]>
Date: Thu, 11 Jul 2024 13:14:18 +0000

Hi there,

I'm using psqlodbc driver to connect to my remote postgresql server via ms access.
The connection is established even with SSL encryption and everything works except that the connection seems to be super slow.
I did all kinds of queries including adding rows to an existing table, updating singe values or using a big update query.
It really seems to depend of the number of objects that are stored i.e. the number of lines that need to be updated and not the number of attributes or fields.
It can't be the internet connection since importing the same amount of data with a csv file via pgadmin only takes 2 seconds while the query via ms access took 40 mins.
Can anybody help me? I tried to activate row versioning but that doesn't really help.
I think the problem already starts there: when I just open the linked table in ms access (basically select * from ...) it doesn't load all rows but only the top something and when i start scrolling down more rows appear. grouping queries work fine tho and give me the correct count results.

I really hope someone know the answer to this.
It would be great if I could just update tables in my remote postgresql database from my ms access database on my client pc.

Many thanks in advance!

Fabian

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Thu, 11 Jul 2024 22:10:45 +0000

Hard to say. How many rows are there, and how wide are the rows ?

---

From: "FabianStoehr (@FabianStoehr)" <[email-masked]>
Date: Sun, 14 Jul 2024 11:54:49 +0000

Hi Dave, 

It depends. In my example with 40 minutes it was roughly 16000 rows with around 30 fields.
But also with smaller queries only containing 2000 rows and 3 columns to update, it takes about 5 minutes and that shouldn't be the case right? Or is that normal with odbc connections?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 14 Jul 2024 15:26:40 +0000

Well there's not enough information here for me to tell what is "normal" could be microsoft access could be different connections. 40 minutes is not acceptable whatever the case

---

From: "FabianStoehr (@FabianStoehr)" <[email-masked]>
Date: Wed, 17 Jul 2024 07:39:24 +0000

What information do you need?
We use the latest ms access version with standard settings (I couldn't find settings in ms access that might affect this anyways).
Also postgres runs on standard settings (except the modification for SSL encryption and a different port mapping).
So from my perspective everyone should get the same issue when downloading ms access and postgresql now and uses odbc to connect them (remotely). I use the latest psqlodbc version 16 00 0005.
I don't use declare/fetch and I increased the batch size to 50000. This didn't seem to change anything.
I tried this from two different computers in total different networks. The problem remains.
What options do you think I can set to make it work faster?

---

From: "khogan02 (@khogan02)" <[email-masked]>
Date: Tue, 10 Sep 2024 12:46:21 +0000

I am also seeing very slow response with newer drivers.  I support an older application that was using psqlodbc driver v 0903 until the update to postgres v12. Swapping in any newer driver results in SIGNIFICANT decrease in response. Same application, same database, same machine...only the driver changes. Any suggestion? Example: a series of queries runs in < 4min with the 0903 driver, but takes about 90m with the new ones,

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 11 Sep 2024 18:37:25 +0000

> I am also seeing very slow response with newer drivers. I support an older application that was using psqlodbc driver v 0903 until the update to postgres v12. Swapping in any newer driver results i…

Any way you can provide me with a way to replicate this ?

---

From: "khogan02 (@khogan02)" <[email-masked]>
Date: Thu, 12 Sep 2024 14:50:43 +0000

Thanks so much for reaching out.  I do not have a small test case I can share but think I may have finally figured out a solution.  I will reach back out once testing is complete.

From: Dave Cramer ***@***.***>
Sent: Wednesday, September 11, 2024 2:38 PM
To: postgresql-interfaces/psqlodbc ***@***.***>
Cc: Hogan, Kristin (PERATON) ***@***.***>; Comment ***@***.***>
Subject: [EXTERNAL] Re: [postgresql-interfaces/psqlodbc] Slow Connection with ODBC Driver (Issue #32)

—
Reply to this email directly, view it on GitHub<https://github.com/postgresql-interfaces/psqlodbc/issues/32#issuecomment-2344405849>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BF5R7CUPSCB6VPRIJUBKJ3DZWCEXVAVCNFSM6AAAAABKW64PLCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNBUGQYDKOBUHE>.
You are receiving this because you commented.Message ID: ***@***.***>

---

From: "kseyoss (@kseyoss)" <[email-masked]>
Date: Wed, 29 Jan 2025 16:15:35 +0000

Think we might also be seeing this. Access connected to Google Cloud Postgres.

Running a search from Access takes 15 minutes (on a small table with a couple of thousand entries). Tried lots of changes in the driver settings, none seem to make any difference. 

Swapping to a different ODBC driver improves response time significantly.  

Khogan; Any chance you could share your solution?

---

From: "khogan02 (@khogan02)" <[email-masked]>
Date: Wed, 29 Jan 2025 16:26:35 +0000

Hi kseyoss - you've got a different environment than I did but my "solution" was the psqlODBC_x64 driver version **12.02.0000** with **IgnoreTimeout=1** at the end of my Connection String. Response time is excellent and no timeouts for long-running queries.