Re: Slow query execution over WAN network

Clemens Eisserer wrote:
> Hi,
>
> I experience really slow query execution over remote networks,
> especially for a query which returns a lot of columns (~255) caused by
> joins.
>
> Executing the same query over different networks gives the following times:
> Local:      20ms
> DSL:      400ms
> UMTS: 1500ms
>
> The query returns 12 rows, and 255 column - most of them with only
> very little data. A wireshark-snapshot of a request is only ~30kb
> large, so I guess there isn't a lot of data transported.
>
> I've uploaded a netbeans-profiler screenshot as well as a wireshark profile to:
> http://93.190.88.182/jdbc_traffic.wireshark
> http://93.190.88.182/jdbc_profile.png
>
> Any idea whats causing the high latency? Are there multiple
> round-trips going on here?
> I already tried adjusting fetch size, however didn't change anything.

I only see the query taking 400ms or so in the wireshark capture you
attached, so I assume this the DSL case?

There's only one round trip happening at the application level (client
sends Parse/Bind/Describe/Execute/Sync in one go; server sends
ParseComplete/BindComplete/RowDescription/DataRows/CommandComplete/ReadyForQuery
in response) so there's not much else the driver can do here, you're at
the mercy of how fast TCP can push that data around.

What are the sustained data rates and latency you see for your DSL and
UMTS connections? You have about 10kB to send to the server; and about
23kB to receive from the server. I suspect much of the query time is
explained just by your connection speed. 33kB is not a "lot of data" for
a LAN, but you'd be surprised how long it takes to push even small
amounts of data across a WAN, especially something asymmetric like DSL/UMTS.

In the DSL case it takes around ~230ms just to get the query to the
server (this is about right for an upstream data rate of ~400-500Kbit);
then the query itself takes ~80ms to execute (seems high, but it's hard
to break that latency down into query execution vs. network latency);
then ~105ms to get the results back to the client, which is a data rate
of around 2.2Mbit. TCP is probably still in slow-start too, which won't
be helping.

I would guess that the UMTS case is so much worse because of a
combination of higher base latency + slower data rates.

You might want to look at a wireshark capture on the server side, too -
with only one side, it's hard to tell the difference between inherent
latency in the connection, time the server spends processing things, and
latency due to pushing data around. Also you can miss dropped packets /
retransmissions unless you look at both sides.

Oliver