
If a business application is fast at head office but very slow from a branch or over VPN, the cause is usually not bandwidth but “chatty” data access: hundreds of small queries are sent for a single screen, and each one pays the line’s round-trip time (RTT). The most common source is the N+1 query problem caused by lazy loading in ORMs. The fix is to measure the query count, reduce round trips with eager loading or projection, or move to an architecture that runs the application close to the database.
Symptoms: “The program freezes at the branch”
- The same screen opens in 1–2 seconds at head office but takes minutes for a user at a branch or working from home over VPN.
- Increasing the branch line’s bandwidth brings no noticeable improvement.
- The server’s CPU and disk usage are low; individual queries in SQL Server finish within milliseconds.
- The time to open the screen grows in direct proportion to the number of records in the list.
- The application may show timeout messages such as “Timeout expired” or “The wait operation timed out”.
- The application log shows hundreds of consecutive “Executed DbCommand (1ms)” lines.
Root cause: query count × network latency
In a desktop application that connects directly from the client computer to the database (two-tier), every query requires a round trip over the network. On the local network at head office this usually takes less than a millisecond; on a branch line or VPN it can rise to tens of milliseconds. No matter how fast the query runs on the server, the application waits for the response before sending the next query, so the total time grows roughly to “number of queries × RTT”.
Example calculation (for illustration only, not a measured value): if 500 queries are sent while a screen opens, on a local network with 0.5 ms RTT the waiting time alone is 500 × 0.5 ms = 0.25 seconds. On a branch line with 30 ms RTT, the same screen waits 500 × 30 ms = 15 seconds. Doubling the bandwidth does not shorten these 15 seconds, because little data is transferred but there are many waits.
This is why the query count is decisive. Microsoft’s EF Core documentation explains the typical cause: with lazy loading enabled, a list is fetched first, and then, as a loop accesses each record’s related data, a separate query is sent per record. The documentation calls this the “N+1 problem” and states that it “can cause very significant performance issues”. In EF Core, proxy-based lazy loading is enabled with the Microsoft.EntityFrameworkCore.Proxies package and UseLazyLoadingProxies(), and applies to every navigation property declared virtual; a single configuration line can therefore make the whole application chatty. The same pattern appears in other ORMs and in hand-written queries inside loops.
File-based applications that open data files over a network share (SMB) slow down over a WAN for the same reason: every read is a separate network request. Here too, the solution is to move the processing to where the data lives.
Step-by-step diagnosis and fix
- Measure latency: from a computer at the branch, ping the database server and test the connection to the SQL Server port; compare with a computer at head office.
- Measure the query count: while opening the slow screen, capture completed queries from that client with an Extended Events session in SQL Server. If you see hundreds of similar queries for a single screen (differing only in their parameter), the problem is N+1.
- Confirm on the application side: if you use EF Core, briefly enable command logging with LogTo; each command is written as an “Executed DbCommand” line with its duration. Microsoft notes that the log reveals unexpected round trips as multiple commands where only one is expected.
- Rule out the server: if individual query durations are short and server resources are idle, the problem is not the server but the number of round trips. For large result sets, also check the ASYNC_NETWORK_IO wait in sys.dm_os_wait_stats; Microsoft explains it as the client not consuming data fast enough or as network delays.
- Fix the code: if you know the related data will be needed, use eager loading with Include, or project only the required fields with Select. Microsoft recommends avoiding lazy loading because it makes it extremely easy to trigger N+1 inadvertently.
- If many one-to-many relationships are loaded in one query, watch out for cartesian explosion; use split queries if necessary. According to the documentation, a split query performs a round trip per query, but still far fewer than N+1.
- Consider an architectural fix: if the code cannot be changed, run the application close to the database; for example, connect branch users to a remote desktop (RDP/RemoteApp) server at head office, or have the client talk to a web API or web application running on the server instead of the database.
REM Measuring round-trip time from a branch computer
ping -n 20 erp-server
# PowerShell: SQL Server port reachability and RTT
Test-NetConnection -ComputerName erp-server -Port 1433-- Counting queries from a specific client (Extended Events)
CREATE EVENT SESSION [BranchQueryCount] ON SERVER
ADD EVENT sqlserver.rpc_completed (
ACTION (sqlserver.client_hostname, sqlserver.client_app_name)
WHERE (sqlserver.client_hostname = N'BRANCH-PC01')),
ADD EVENT sqlserver.sql_batch_completed (
ACTION (sqlserver.client_hostname, sqlserver.client_app_name)
WHERE (sqlserver.client_hostname = N'BRANCH-PC01'))
ADD TARGET package0.event_file (SET filename = N'C:\temp\BranchQueryCount.xel');
GO
ALTER EVENT SESSION [BranchQueryCount] ON SERVER STATE = START;
-- have the user open the slow screen, then:
ALTER EVENT SESSION [BranchQueryCount] ON SERVER STATE = STOP;// EF Core: enabling command logging temporarily
optionsBuilder.UseSqlServer(connection).LogTo(Console.WriteLine, LogLevel.Information);
// N+1: the customer of each order is loaded by a separate query (lazy loading)
var orders = await ctx.Orders.ToListAsync();
foreach (var o in orders) Console.WriteLine(o.Customer.Name);
// Single round trip: eager loading or projection
var list = await ctx.Orders.Include(o => o.Customer).ToListAsync();
var summary = await ctx.Orders.Select(o => new { o.No, o.Customer.Name }).ToListAsync();Do not leave command logging or the Extended Events session running permanently in production. Microsoft notes that permanent command logging in production can slow the application down and fill up the disk; after measuring, stop the session and remove it with DROP EVENT SESSION.
Permanent prevention
- Run performance tests not on the local network but over a connection with latency similar to branch conditions; set an upper limit for “queries per screen”.
- Keep lazy loading disabled by default; load related data explicitly (Include, projection or explicit loading).
- Tag important queries with EF Core’s TagWith feature so you can immediately see in the log which screen they belong to.
- Cache rarely changing reference data (cities, units, categories) on the application side.
- If the number of branches is growing, plan a move from the two-tier architecture to an API or web front end running on the server.
When should you call in an expert?
If the slowness is disrupting sales, shipping or till operations at the branches, if you have no access to the source code, or if the network, server and software teams keep pointing at each other, an independent, measurement-based review saves time. In situations like these, which we encounter in the field at businesses in Denizli, Workas measures query counts and latency as part of its custom software development service and presents code-fix or architectural options together with their costs.
Frequently Asked Questions
Will a faster branch line solve the problem?
Usually not. Because the problem is the number of round trips rather than the amount of data transferred, more bandwidth does not noticeably reduce waiting time; you need to lower latency or reduce the number of queries.
Should lazy loading be banned completely?
Microsoft recommends avoiding lazy loading because it makes it extremely easy to trigger N+1 inadvertently. When needed, explicit loading makes it clear in the code where a round trip happens.
What can we do without access to the source code?
Running the application where the database is located is the most practical route: branch users connect to a remote desktop or RemoteApp server at head office; since only the screen image is transferred, queries run at local network speed.
If the ASYNC_NETWORK_IO wait is high, is the network definitely the problem?
No. According to Microsoft, this wait can also come from the client consuming data slowly, for example when a very large result set is filtered on the client. Check the query count and result size first.


