search

postgresql-interfaces / psqlodbc

Other
16 stars 15 forks source link

Slow Connection with ODBC Driver #32

Open FabianStoehr opened 4 months ago

FabianStoehr commented 4 months ago

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 commented 4 months ago

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

FabianStoehr commented 4 months ago

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 commented 4 months ago

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 commented 4 months ago

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 commented 2 months ago

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 commented 2 months ago

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 commented 2 months ago

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 GitHubhttps://github.com/postgresql-interfaces/psqlodbc/issues/32#issuecomment-2344405849, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BF5R7CUPSCB6VPRIJUBKJ3DZWCEXVAVCNFSM6AAAAABKW64PLCVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNBUGQYDKOBUHE. You are receiving this because you commented.Message ID: @.***>