Re: Slow query execution over high latency network

Hi Pierre,

Thanks a lot for your reply.

> Your attached file didn't come through.
Hmm, ok.
I uploaded the wireshark-log to: http://93.190.88.182/psql_large_query.bin

> - different execution plan between your app and ssh+psql, which can happen
> if the planning uses/doesn't use your specific parameters,

Its both times (ssh and remote psql) exactly the same query - I copied
the SQL generated by hibernate and executed it in psql. And although
it has many columns (~210) the result-set is only about 5 rows and am
sure not larger than a few kb.

> - 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

So are both psql and the jdbc driver dumb clients?
Or are there only buffers somewhere too small and therefor data is
sent in many smal batches.
I thought one query would more or less equal to one roundtrip, right?
Maybe I should ask on the pgsql-jdbc list.

> 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
> 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.

I use well tuned hibernate fetch profiles to ensure fewest possible roundtrips,
however I am not getting paid well enough to create an appserver tier ;)

Thanks, Clemens