Thread: TCP_NO_DELAY & TcpAckFrequency (Re: Slow query through ODBC)
Hi list This is a follow-up to http://archives.postgresql.org/pgsql-odbc/2006-02/msg00067.php Let me sum up the problem : I run a query in pgadmin into a file (to be sure that all data is transfered). It runs very fast (in about 10sec), and data is transfered from the server at a very fast rate (~1MB per second). I run the same query via psqlodbc in a VBA application. It takes ages to run and data is transfered at a very slow pace (~80kB per second). The query returns the WKT representation of a geometry, so it returns a very long varchar field. Last time we discussed this problem, we thought the problem was with TCP_NO_DELAY. We hadn't found out why the TCP/IP stack behaved differently in pgadmin and in psqlodbc. I am still experiencing this behaviour, even though I installed postgresql on a brand new server, and my workstation was also just reinstalled. Since the problem is still there, I'd like to know if newer versions of psqlodbc could have changed things. I am still using the latest official releases (8.01.0200), but maybe there are beta release I could try ? Any help would be greatly appreciated ! Thanks a lot -- Arnaud
> I am still experiencing this behaviour, even though I installed > postgresql on a brand new server, and my workstation was also just > reinstalled. > Since the problem is still there, I'd like to know if newer > versions of psqlodbc could have changed things. > I am still using the latest official releases (8.01.0200), but > maybe there are beta release I could try ? There is new version 08.02.0002 on pgfoundry.org. And in bug entry BiDiMode* you can find the URL for the latest .dll files located at Hiroshi pages. The 08.02 version is based on 07.03 - not 08.01 so it could helps you. Regards, Luf
pgsql-odbc-owner@postgresql.org wrote: >> I am still experiencing this behaviour, even though I installed >> postgresql on a brand new server, and my workstation was also just >> reinstalled. Since the problem is still there, I'd like to know if >> newer versions of psqlodbc could have changed things. >> I am still using the latest official releases (8.01.0200), but maybe >> there are beta release I could try ? > > There is new version 08.02.0002 on pgfoundry.org. And in bug > entry BiDiMode* you can find the URL for the latest .dll > files located at Hiroshi pages. > The 08.02 version is based on 07.03 - not 08.01 so it could helps you. The link for Hiroshi's website is the following: http://www.geocities.jp/inocchichichi/psqlodbc/index.html --------------- Philippe Lang Attik System
Attachment
pgsql-odbc-owner@postgresql.org wrote: > Hi list > > This is a follow-up to > http://archives.postgresql.org/pgsql-odbc/2006-02/msg00067.php > > Let me sum up the problem : > I run a query in pgadmin into a file (to be sure that all > data is transfered). It runs very fast (in about 10sec), and > data is transfered from the server at a very fast rate (~1MB per > second). I run the same query via psqlodbc in a VBA application. It > takes ages to run and data is transfered at a very slow pace (~80kB > per second). Arnaud, What client exactly do you use for querying the server in VBA? Are you maybe using MS Access? If yes, what version of Accessare you using? 2003? And are you using an Access 2000 compatible MDB maybe? Otherwise, what client data access package are you using from VBA? DAO? ADO? --------------- Philippe Lang Attik System
Attachment
Philippe Lang a écrit : > What client exactly do you use for querying the server in VBA? Are you maybe using MS Access? If yes, what version of Accessare you using? 2003? And are you using an Access 2000 compatible MDB maybe? > Otherwise, what client data access package are you using from VBA? DAO? ADO? Philippe, the client is an Autocad2000 VBA macro. I am using ADO, with a DSN-less connection (basic connection string, nothing but server address, port, username and password). Do you have a clue ? Regards -- Arnaud
Arnaud Lesauvage wrote: > Philippe Lang a écrit : >> What client exactly do you use for querying the server in > VBA? Are you maybe using MS Access? If yes, what version of > Access are you using? 2003? And are you using an Access 2000 > compatible MDB maybe? >> Otherwise, what client data access package are you using from VBA? >> DAO? ADO? > > > Philippe, the client is an Autocad2000 VBA macro. > I am using ADO, with a DSN-less connection (basic connection > string, nothing but server address, port, username and password). > > Do you have a clue ? > > Regards Hi, I suggest you try another client, like for example MS Access 2003 with a 2003 MDB, for example, just to make sure the problemscomes from the driver. What you experience might be a problem that come from Autocad... Can you make a test with DAO as well? Quite old, but might be faster as well... --------------- Philippe Lang Attik System
Attachment
Philippe Lang a écrit : > I suggest you try another client, like for example MS Access 2003 with a 2003 MDB, for example, just to make sure the problemscomes from the driver. What you experience might be a problem that come from Autocad... > > Can you make a test with DAO as well? Quite old, but might be faster as well... I can try from within Access2000, but I don't have Access2003. I'll try DAO as well. Thanks for your suggestions ! -- Arnaud
Arnaud Lesauvage wrote: > Philippe Lang a écrit : >> I suggest you try another client, like for example MS Access 2003 with a 2003 MDB, for example, just to make sure theproblems comes from the driver. What you experience might be a problem that come from Autocad... >> >> Can you make a test with DAO as well? Quite old, but might be faster as well... > > I can try from within Access2000, but I don't have Access2003. > I'll try DAO as well. I found the problem ! I just used UseDeclareFetch=1 in the connection string, and now my queries are blindingly fast !!! I am quite amazed, I am pretty sure I tried this before... I even thin I removed this setting when trying to find the cause of the problemm !... Thanks a lot to all the people who helped me ! Regards -- Arnaud