postgresql-interfaces/psqlodbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
10+ messages / 4 participants
[nested] [flat]
* [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-07-11 13:14 "FabianStoehr (@FabianStoehr)" <[email protected]>
0 siblings, 0 replies; 10+ messages in thread
From: FabianStoehr (@FabianStoehr) @ 2024-07-11 13:14 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-07-11 22:10 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2024-07-11 22:10 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
Hard to say. How many rows are there, and how wide are the rows ?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-07-14 11:54 ` "FabianStoehr (@FabianStoehr)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: FabianStoehr (@FabianStoehr) @ 2024-07-14 11:54 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-07-14 15:26 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2024-07-14 15:26 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-07-17 07:39 ` "FabianStoehr (@FabianStoehr)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: FabianStoehr (@FabianStoehr) @ 2024-07-17 07:39 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-09-10 12:46 ` "khogan02 (@khogan02)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: khogan02 (@khogan02) @ 2024-09-10 12:46 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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,
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-09-11 18:37 ` "davecramer (@davecramer)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: davecramer (@davecramer) @ 2024-09-11 18:37 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
> 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 ?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2024-09-12 14:50 ` "khogan02 (@khogan02)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: khogan02 (@khogan02) @ 2024-09-12 14:50 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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<https://github.com/postgresql-interfaces/psqlodbc/issues/32#issuecomment-2344405849;, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BF5R7CUPSCB6VPRIJUBKJ3DZWCEXVAVCNFSM6AAAAABKW64PLC...;.
You are receiving this because you commented.Message ID: ***@***.***>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2025-01-29 16:15 ` "kseyoss (@kseyoss)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: kseyoss (@kseyoss) @ 2025-01-29 16:15 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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?
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver
@ 2025-01-29 16:26 ` "khogan02 (@khogan02)" <[email protected]>
8 siblings, 0 replies; 10+ messages in thread
From: khogan02 (@khogan02) @ 2025-01-29 16:26 UTC (permalink / raw)
To: postgresql-interfaces/psqlodbc <[email protected]>
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2025-01-29 16:26 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-07-11 13:14 [postgresql-interfaces/psqlodbc] issue #32: Slow Connection with ODBC Driver "FabianStoehr (@FabianStoehr)" <[email protected]>
2024-07-11 22:10 ` "davecramer (@davecramer)" <[email protected]>
2024-07-14 11:54 ` "FabianStoehr (@FabianStoehr)" <[email protected]>
2024-07-14 15:26 ` "davecramer (@davecramer)" <[email protected]>
2024-07-17 07:39 ` "FabianStoehr (@FabianStoehr)" <[email protected]>
2024-09-10 12:46 ` "khogan02 (@khogan02)" <[email protected]>
2024-09-11 18:37 ` "davecramer (@davecramer)" <[email protected]>
2024-09-12 14:50 ` "khogan02 (@khogan02)" <[email protected]>
2025-01-29 16:15 ` "kseyoss (@kseyoss)" <[email protected]>
2025-01-29 16:26 ` "khogan02 (@khogan02)" <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox