Slow Connection with ODBC Driver

[FabianStoehr]

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

[davecramer]

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

[FabianStoehr]

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?

[davecramer]

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

[FabianStoehr]

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?

[khogan02]

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,

[davecramer]

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,

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

[khogan02]

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) 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, Any way you can provide me with a way to replicate this ? — Reply to this email directly, view it on GitHub<#32 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BF5R7CUPSCB6VPRIJUBKJ3DZWCEXVAVCNFSM6AAAAABKW64PLCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNBUGQYDKOBUHE>. You are receiving this because you commented.Message ID: ***@***.***>

[kseyoss]

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?

[khogan02]

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.