Why a Chatty SQL App Crawls Over a VPN and What to Batch

A form that renders in under a second on a LAN takes five seconds over a VPN, while a file copy on the same link runs at full speed. The difference is not bandwidth. The form waits for a sequence of answers and pays the full round trip at every step, while the file copy keeps bytes in flight continuously. Count those waits before you touch the access line.

The symptom is a screen, not a query

Every query on that screen can be fast and the screen can still be slow, because the total is the sum of waits rather than the sum of execution times. A statement that runs in two milliseconds still costs a whole round trip when the client must receive its result before sending the next one. On a path with forty milliseconds of latency, a hundred dependent calls add up to about four seconds before any rendering work happens. The arithmetic is a model of the wait, not a benchmark of your link.

That is why a speed test looks innocent. Bulk transfer measures throughput, a chatty form measures latency, and a VPN can be excellent at one while remaining ordinary at the other. Diagnosing the screen therefore means measuring the screen rather than the link.

Record the end-to-end time of one user action before changing anything. That number is the only baseline that can tell you whether a later fix worked.

Count sequential waits in one user action

Instrument the client instead of guessing. In psql, \timing on prints the elapsed time of each statement, and EXPLAIN (ANALYZE, BUFFERS) shows how much of that time the server actually spent on the work.

The gap between those two numbers is your network and client overhead. If a statement executes in two milliseconds and the client records sixty, the remaining fifty-eight are travel and local processing, and no index will remove them. Repeat that comparison for every statement the screen issues and add up the gaps rather than the execution times.

Write the result down as a count of sequential waits. That is the quantity the next two sections are able to reduce, and it is a number you can argue about with evidence.

Batch the independent calls, keep the dependent ones

Two statements that do not read each other results can travel together and be collected once, which turns two waits into one. Two statements where the second needs the value from the first cannot, because the dependency is real and batching it would change what the program does. Sort the calls into those two piles before rewriting anything, and the size of each pile tells you what is possible.

Set-based work removes waits more thoroughly than batching does. One query returning the rows that a loop was fetching one at a time eliminates the loop as well as the round trips. Look for the loop first, because it is usually the larger win and the easier change to review.

A batch still has to preserve error handling and transaction boundaries. Concatenating unrelated statements into a single string is not a safe optimization, and a batch that hides a failed statement behind a successful submission is worse than the waits it saved.

Reuse connections before you buy bandwidth

A new connection costs name resolution, a TCP handshake, TLS negotiation and authentication, and on a high-latency path each of those is another round trip before the first query runs. Connection pools exist to pay that cost once per process rather than once per call.

In ADO.NET, closing or disposing a pooled connection returns it for reuse instead of opening a new physical one, so the pattern that looks like a leak is often the correct usage. Verify the pool is genuinely in use rather than assuming it: log physical connection creation, or watch the session count on the server while the screen runs. The SQL performance guidance makes the same point about reducing round trips and batching frequent ad hoc calls, with service-specific examples you should map onto your own deployment.

Pooling removes setup cost and leaves serial query waits untouched. It is a prerequisite for a clean measurement rather than a fix on its own.

Weigh moving execution closer to the data

Running the application near the database replaces many remote database exchanges with a single remote display session. For a chatty workload that can be a large change, and it is an operational change as much as a performance one: session management, access control, software deployment and user experience all move with the application.

Measure the actual interface and the data it transfers before assuming either direction is faster. A remote desktop session moves pixels, and a screen with dense graphics can perform worse over the same path than the queries it replaced, especially when the client redraws continuously.

Then repeat the original action and compare three numbers: sequential waits, result volume and end-to-end completion time. An improvement that changes the measured user task while preserving data correctness and access policy is the one worth keeping.