November 28, 2005 at 6:47 pm
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/diggingintoaccessperformance.asp
January 2, 2006 at 3:37 am
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));
to
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.
January 2, 2006 at 7:23 am
Andy,
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
January 2, 2006 at 8:43 am
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
January 2, 2006 at 9:57 am
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
sqlSentry
January 2, 2006 at 10:24 am
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.
January 2, 2006 at 12:04 pm
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.
January 2, 2006 at 12:46 pm
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.
January 3, 2006 at 5:53 am
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.
January 25, 2006 at 9:18 am
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.
Seth
November 19, 2008 at 7:22 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply