Seeing some slowness issues.

snorkel · Post by **snorkel** » Wed 28 Jan 2009 16:22

Hi,
Jus a FYI, some of my customers are seeing some drastic slow downs compared to libpq.dll based solutions(Zeos for example) when the connection is a little slower.

I have noticed a little bit of slowness, but it's hard to judge on a 100mb full duplex LAN connection. Some of it may be related to the actuall connection process being slower than libpq.dll based VCL solutions.

Any plans to optimize the speed a bit?

Thanks,

Snorkel

snorkel · Post by **snorkel** » Wed 28 Jan 2009 19:53

some more info:
I have discovered that the slowness is the most drastic when going over a SSH tunnel. I sent private email to support with more details.

I am not sure, but I suspect that the slowness is somehow related to the fact that pgDAC is not using the simple query option of the PGSQL wire protocol.

snorkel · Post by **snorkel** » Thu 29 Jan 2009 14:54

Any ideas on how I could improve the performance?
I tried setting to unidirectional, but it made no difference and I tried wrapping multiple queries in a transaction, but speed remains very slow, especially over SSH tunnels (even on very fast LANS)

Plash · Post by **Plash** » Fri 30 Jan 2009 13:15

We are working on this problem. Please see details in my e-mail answer.

snorkel · Post by **snorkel** » Fri 30 Jan 2009 15:36

Plash wrote:We are working on this problem. Please see details in my e-mail answer.

Hi,
thank you for the tip on the protocol version (I really would not have thought to change that

That alone made a huge difference all by itself. It does seem that the prepared statements of the non simple query are causing the slowness.

Thanks,

Snorkel

snorkel · Post by **snorkel** » Wed 04 Feb 2009 16:41

FYI,

I have resolved all my slowness issues by dropping the protocol version down to v2 (should be fine for now, seems to work against all version of PGSQL for my app needs)

I also have resolved the SSH slowness issues by switching my tunnel process to securebridge. It turned out the SSH issue was related to using putty plink.exe via createprocess and a pipe, for whatever reason having a pipe connected to the process causes a major slowdown in tunnel performance.
By using securebridge instead I dropped 10 to 15 seconds off query times over the tunnel. Using Plink.exe or putty.exe standalone also fixed the problem.

snorkel · Post by **snorkel** » Thu 12 Feb 2009 00:24

I found a draw back to not using protocol v3, if you use v2 you lose all the detailed error information like char position etc.

Hope you guys can get the speed issues resolved with v3 of the protocol.

I can live without the detailed error information for awhile.

Later,

Snorkel