Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Digging Into Access Performance


Digging Into Access Performance

Author
Message
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8202 Visits: 2711
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/diggingintoaccessperformance.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Inge Buchanan
Inge Buchanan
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 2

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.


Jim Dettman
Jim Dettman
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9

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
Bob Monahon
Bob Monahon
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 9

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.




Regards,

Bob Monahon
Greg Gonzalez
Greg Gonzalez
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 207

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


tombynum
tombynum
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 8

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
Inge Buchanan
Valued Member
Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)Valued Member (74 reputation)

Group: General Forum Members
Points: 74 Visits: 2

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
Richard Moldwin
SSC-Enthusiastic
SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)SSC-Enthusiastic (144 reputation)

Group: General Forum Members
Points: 144 Visits: 15

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
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8202 Visits: 2711
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.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
seth byce
seth byce
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search