Re: Slow query execution over high latency network

>
> When executing huge (10kb), hibernate-generated queries I noticed that
> when executed remotly over high-latency network (ping to server
> 200-400ms), the query takes a lot longer to complete.
>
> When the query is executed remotly (psql or jdbc) it takes 1800ms to
> execute, when I issue the query in an ssh terminal, I see the results
> almost immediatly.
> So although I should see the same latency over ssh , its way faster over
> ssh.
> The transmitted data is small (the wireshard-file has 22kb, attached),
> and even though the umts-network is high-latency its relativly high
> bandwith (~512kbit/s up, ~2mbit/s down).

Well, if your upload bandwidth is really 512 kbits, uncompressed
transmission of your query text should take about 0.2s, not too bad. SSH
normally uses compression, so it should be a lor faster.

Your attached file didn't come through.

Anyway, there are several options :

- different execution plan between your app and ssh+psql, which can happen
if the planning uses/doesn't use your specific parameters, or if some
wrong type bindings in your app force postgres not to use an index
(there's been a few messages on that lately, check the archives).

- dumb client versus smart client :

smart client : use the protocol which sends the query text + parameters +
prepare + execute in 1 TCP message, 1 ping, postgres works, 1 ping, get
reply
dumb client :
- send prepare
- wait for reply
- send execute
- wait for reply
- send "gimme result"
- wait for reply
- etc

> Any idea whats causing this? Maybe too small buffers somewhere?
> For me it poses problem, because I am working on a 2-Tier java
> application which should connect to postgres remotly - however with
> every more complex query taking 2s its almost unuseable over wireless
> networks (umts).

If you want to ensure the fastest response time you need to ensure than
one user action (click) needs one and only one roundtrip to the server
before all the results are displayed. If said action needs 2 SQL queries,
it ain't possible, unless (maybe) you use the asynchronous query protocol.
You can also stuff multiple queries in stored procedures (but Hibernate
won't be able to generate them obviously).

One solution could be to put the database handling stuff inside an
appserver, make your app communicate to it with a low-overhead RPC
protocol (ie, not raw uncompressed XML) that minimizes the number of
roudtrips, and compresses data thoroughly.