Take the 2-minute tour ×

I am upsizing an MS Access 2003 app to a SQL Server backend. On my dev machine, SQL Server is local, so the performance is quite good. I want to test the performance with a remote SQL Server so I can account for the effects of network latency when I am redesigning the app. I am expecting that some of the queries that seem fast now will run quite slowly once deployed to production.

How can I slow down (or simulate the speed of a remote) SQL Server without using a virtual machine, or relocating SQL to another computer? Is there some kind of proxy or Windows utility that would do this for me?

share|improve this question
Just write your app to be efficient in data retrieval, i.e., never retrieve more than the user needs or can work with at one time. This will be efficient in any environment, including with a Jet/ACE back end. There's nothing magic about it. The only circumstance in which you might encounter edge cases is if you're planning on running across the open Internet, with the relatively very low bandwidth there (compared to a LAN). In that case, you might do more, but I would advise against premature optimization -- make it efficient and then fix what isn't fast enough. –  David-W-Fenton Nov 13 '10 at 21:20
@David: I am porting a very large existing MS ACcess app. I need to be strategic in what changes I make, there is not the time or budget to modify every query and data source. –  RedFilter Nov 13 '10 at 23:25
I didn't even begin to suggest modifying everything. If it's a well-designed Access app, it will likely perform very well. If it's not, it will require a lot of work. The principle of retrieving only a limited number of records makes for a fast, efficient app no matter if the back end is Jet/ACE or a server database. –  David-W-Fenton Nov 14 '10 at 23:30
@David: I am not really sure what you are suggesting. You said "write the app to be efficient" - I am porting an existing app. You said "The principle of retrieving only a limited number of records makes for a fast, efficient app" - this is exactly what I intend to do, but need to determine where the bottlenecks are first. My question is not how to rewrite queries for better for performance, it is how to determine which queries are going to be slow once a network is in the picture. –  RedFilter Nov 14 '10 at 23:42
My experience with existing apps is that bottlenecks are almost impossible to predict. Every time I've upsized, I had a number of areas of each app where I thought there would be problems that needed to be handled by moving the logic serverside, and a number of other areas that I thought would perform just fine because of the original design. But about 50% of the time, the bottlenecks performed fine, and the unproblematic areas became bottlenecks. There is simply no way to know without upsizing and testing. Doing it with a server separate from the workstation should suffice to reveal these. –  David-W-Fenton Nov 15 '10 at 21:49

4 Answers 4

up vote 5 down vote accepted

I have not used it myself, but here's another SO question:

In one of the comments SQL Server has been mentioned explicitly.

share|improve this answer
Thanks for the link. –  Tony Toews Nov 14 '10 at 0:42

You may be operating under a misconception. MS-Access supports so-called "heterogeneous joins" (i.e. tables from a variety of back-ends may be included in the same query, e.g. combining data from Oracle and SQLServer and Access and an Excel spreadsheet). To support this feature, Access applies the WHERE clause filter at the client except in situations where there's a "pass-through" query against an intelligent back-end. In SQL Server, the filtering occurs in the engine running on the server, so SQL Server typically sends much smaller datasets to the client.

The answer to your question also depends on what you mean by "remote". If you pit Access and SQL Server against each other on the same network, SQL Server running on the server will consume only a small fraction of the bandwidth that Access does, if the Access MDB file resides on a file server. (Of course if the MDB resides on the local PC, no network bandwidth is consumed.) If you're comparing Access on a LAN versus SQL Server over broadband via the cloud, then you're comparing a nominal 100 mbit/sec pipe against DSL or cable bandwidth, i.e. against perhaps 20 mbit/sec nominal for high-speed cable, a fifth of the bandwidth at best, probably much less.

So you have to be more specific about what you're trying to compare.

Are you comparing Access clients on the local PC consuming an Access MDB residing on the file server against some other kind of client consuming data from a SQL Server residing on another server on the same network? Are you going to continue to use Access as the client? Will your queries be pass-through?

share|improve this answer
@Time: Your first paragraph is incorrect. The WHERE clause of my non-passthrough queries against SQL Server are being run on SQL Server in cases where there are not Access-specific keywords being used. Your second paragraph is incorrect as well: for queries run from Access against SQL Server, less bandwidth is consumed only in the case where the JOIN and WHERE clauses can be run on SQL Server because there are no Access=specific keywords, like 'IIF'. If there are Access-specific keywords, the entire dataset is pulled down to Access so it can run the keywords there. –  RedFilter Nov 14 '10 at 13:34
Whether the full dataset is pulled down depends on where the Access-specific functions are used. In the SELECT clause, it's not much of a problem, as all WHERE criteria and JOINs will be performed on the server, and then the functions processed in the SELECT only on the filtered resultset. But if you use Access-specific functions in your WHERE or JOIN clauses or ORDER BY, you may or may not pull down the whole table -- depends on whether Jet/ACE can figure figure a way to separate out certain limiting factors before the Access-specific functions. –  David-W-Fenton Nov 14 '10 at 23:33
@David: yes, this is an important point. –  RedFilter Nov 16 '10 at 15:19

There is a software application for Windows that does that (simulates a low bandwidth, latency and losses if necessary). It not free though. The trial version has a 30-sec emulation limit. Here is the home page of that product: http://softperfect.com/products/connectionemulator/

share|improve this answer

@RedFilter: You should indicate which version of Access you are using. This document from 2006 shows that the story of what Access brings down to the client across the wire is more complicated than whether the query contains "Access-specific keywords".


But Access may be getting more and more sophisticated about using server resources with each newer version.

I'll stand by my simple advice: if you want to minimize bandwidth consumption, while still using Access as the GUI, pass-through queries do best, because then it is you, not Access, who will control the amount of data that comes down the wire.

I still think your initial question/approach is misguided: if your Access MDB file was located on the LAN in the first place (was it?) you don't need to simulate the effects of network latency. You need to sniff the SQL statements Access generates, rather than introducing some arbitrary and constant "network latency" factor. To compare an Access GUI using an MDB located on a LAN server against an upsized Access GUI going against a SQL Server back-end, you need to assess what data Access brings down across the wire to the client from the back-end server. Even "upsized" Access can be a hog at the bandwidth trough unless you use pass-through queries. But a properly written client for a SQL-Server back-end will always be far more parsimonious with network bandwidth than Access going against an MDB located on a LAN server, ceteris paribus.

share|improve this answer
current access back-end MDB is on the LAN. I am currently sniffing queries. Version of Access is 2003. y nuderstanding is that pass-through queries are read-only, and in addition, some of my queries require input parameters. My opinion that the quickest way to determine what bottlenecks should be fixed is to do so empirically. –  RedFilter Nov 16 '10 at 15:18

Your Answer


