Digging Into Access Performance

  • Andy Warren

    SSC Guru

    Points: 119684

  • Inge Buchanan

    SSC Veteran

    Points: 204

    Hi Andy,

    How about changing the query from

    UPDATE dbo_Employees INNER JOIN dbo_EmployeeTerritories ON dbo_Employees.EmployeeID = dbo_EmployeeTerritories.EmployeeID SET dbo_Employees.LastName = "Leverling2"

    WHERE (((dbo_EmployeeTerritories.EmployeeID)=3));


    UPDATE DISTINCTROW dbo_Employees INNER JOIN dbo_EmployeeTerritories ON dbo_Employees.EmployeeID = dbo_EmployeeTerritories.EmployeeID SET dbo_Employees.LastName = 'Leverling'

    WHERE (((dbo_Employees.EmployeeID)=3));

    This will only update the Employees table once so hopefully the overall performance would be much improved.

    Also if performance is an issue then maybe an Access Project could be used. The only drawback is that a stored proc would need to be created in the Northwind database unless some VBA code was used.


  • Jim Dettman

    SSC Rookie

    Points: 29


    It's not clear to me from your article exactly what the environment was that you used for testing.  It sounds like you did this all on a local machine, which is why this statement:

    "Access also supports the notion of a 'pass through' query that will executed on the server rather than in Access. I ran the same SQL query that I used for the baseline and got comparable results using this technique. "

      Gave you comparable results, which it should *not* have.  A true Pass-Through query (executed strictly by SQL Server) is significantly faster then going through JET.

      Using local linked ODBC tables, JET first parses your SQL and breaks it down into portions that the server can understand and those that it can't.  It executes one query for each server component (this is where your four additional queries comes from), gets the resulting data back, then processes the finial result locally.

      If you would have done the test on a fairly busy network, you should have noticed a difference even with only four records.  On a large update, there would be a very significant difference.

      Given that, someone reading your article could very easily be mislead into thinking that SQL Pass-through is not worth the effort.  This statement:

    “I did not verify actual network traffic for this test, but I suspect each statement is a round trip. If so, it's even more expensive than it looks. For updates of a few, or even a few hundred records on a decent network I doubt any of this will matter. Start trying to update 100k records and you will see a very long running transaction.”

     Is totally false with a true pass-through query.  The execution time would be similar to what you would expect executing the query on the server directly.

      The processing logic you noted is only being done because it is being done through an ODBC linked table with a backend server.  The logic used would be totally different if the tables were in a local MDB (try it and see what SHOWPLAN gives you) or with a pass-through query.  Again, I think it’s very misleading given the article title “Digging into Access Performance”.

     Access is a product with a wide range of choices when dealing with data sources.  It is critical when talking about performance that you note the exact conditions and understand why something is happening before your write about it.

    Jim Dettman

  • Bob Monahon

    Ten Centuries

    Points: 1282

    Hmm.  The Join to Employee Territories is a red herring. You're only updating the Employee table:

    UPDATE dbo_Employees

    SET dbo_Employees.LastName = "Leverling2"

    WHERE EmployeeID=3;

    If you were trying to profile a joined update, this would be a fairer test:

    "Discontinue all products supplied from Australia"

    UPDATE dbo_Products INNER JOIN dbo_Suppliers ON dbo_Products.SupplierID = dbo_Suppliers.SupplierID SET dbo_Products.Discontinued = True

    WHERE (((dbo_Suppliers.Country)="Australia"))

    The performance hit in your query is due to the fact that Access wants to bring all the data local, and issue its own updates on the underlying table; your "red herring" query tells Access that 4 rows are involved, so it generates 4 update calls. (I'm thinking the 2nd query Access generates is to get a lock on the record(s) to be updated.)

    I agree with Jim Dettman, a PTQ (Pass-Through Query) in my experience runs entirely on the Server at Server speed with none of the extra generated queries and updates that you found using ODBC/Linked tables.

    Frankly, I would be interested to know under what conditions Access tries to perform every update itself (like your example showed), and when (if ever) it will send a single update query to SQL Server to perform a multi-record update.


    Bob Monahon

  • Greg Gonzalez

    SSC Journeyman

    Points: 81

    When joining linked tables, even tables with the same data source, Access will not structure and optimize the query exactly the way SQL Server would.  The net result is usually that Access will try to pull a lot of extra data over locally and operate on it there instead of letting the server do the work.

    A good general rule of thumb is to avoid joining linked tables in an Access query whenever possible, and use pass-through queries instead... that way you know for sure the server will be doing the work, and network and client overhead will be kept to a minimum.

    For more details on all of this note the last 2 major bullets in this KB article:  http://support.microsoft.com/?kbid=286222

    Also note the mention of how to utilize a "remote index join", which can come in very handy for certain situations.

    Greg Gonzalez


  • tombynum

    Mr or Mrs. 500

    Points: 567

    If you're just trying to compare what native Access will do with SQL data, then you're basically correct.  Native Access with linked SQL tables will suffer greatly at the hands of the Jet engine.

    The only saving grace is the use of pass-through queries which will execute natively in SQL server.

    I have done this personally with a "utility" pass-through query.  You can use VBA code to prepare a sql statement and then re-write the text of the utility query...and then execute.  It kind of works like a "black box" conduit back to sql server.

    But for those interested in developing a UI with Access...you can't beat the use of the Access Data Project (ADP) introduced in Access 2000.  The new versions are even better.

    I've used ADP's with great success to take advantage of the speed of UI development with Access forms and reports while leaving SQL server alone to handle data.

    As long as all data manipulation is left to SQL stored procs, it's an absolute win+win for power, flexibility and speed to production.

    The ~BEST~ solution is always the simplest one!

  • Inge Buchanan

    SSC Veteran

    Points: 204

    Please note the access query is incorrect!

    It should be

    UPDATE DISTINCTROW dbo_Employees INNER JOIN dbo_EmployeeTerritories ON dbo_Employees.EmployeeID = dbo_EmployeeTerritories.EmployeeID SET dbo_Employees.LastName = 'Leverling'

    WHERE (((dbo_Employees.EmployeeID)=3));

    This will only select records with EmployeeID = 3, with matching records in the EmployeeTerritories table and only process the same employee row once.

    It is not correct to compare Access performance without writing the correct Access SQL.

  • Richard Moldwin


    Points: 1812

    And yet another problem with this article:  If you are doing serious SQL Server and looking for performance, you should be using an ADP, not an MDB.  In ADPs, T-SQL is the native language. 


  • Andy Warren

    SSC Guru

    Points: 119684

    I will go back and review all of your comments so far, hopefully in the next few days, and see what I find. As far as ADP's, tuning the queries, etc - the problem with all of those is that the user (me?) has to know to do them and know how to do them. What I tested is plain vanilla, out of the box data access using a linked server.

  • seth byce


    Points: 21

    I'm a little disappointed you didn't discuss pass-through queries as well.  Users can use the more robust T-SQL and get results much faster.  Jet is inefficient comparatively.  The only drawback is that one must have mastery of syntax as there is no syntax checker.


  • suzman

    SSC Rookie

    Points: 29

    Lots of really useful points from my predecessors here. I'd just like to add a link to an extremely helpul document on this topic written by Andy Baron of Microsoft entitled

    "Optimizing Microsoft Office Access Applications Linked to SQL Server"

    written in October 2006.

    In November 2008 I located this document at:

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply