Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Digging Into Access Performance Expand / Collapse
Author
Message
Posted Monday, November 28, 2005 6:47 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 1:53 PM
Points: 6,705, Visits: 1,684
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
Post #240188
Posted Monday, January 02, 2006 3:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 24, 2008 11:24 PM
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.

 

Post #247654
Posted Monday, January 02, 2006 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 10, 2012 5:37 PM
Points: 3, Visits: 6

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
Post #247664
Posted Monday, January 02, 2006 8:43 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 04, 2012 9:22 PM
Points: 102, 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
Post #247687
Posted Monday, January 02, 2006 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 5:04 AM
Points: 8, Visits: 188

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

Post #247701
Posted Monday, January 02, 2006 10:24 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, December 23, 2008 11:15 AM
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!
Post #247704
Posted Monday, January 02, 2006 12:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 24, 2008 11:24 PM
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.

Post #247714
Posted Monday, January 02, 2006 12:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 28, 2013 1:27 PM
Points: 116, 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. 

 

Post #247718
Posted Tuesday, January 03, 2006 5:53 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: 2 days ago @ 1:53 PM
Points: 6,705, Visits: 1,684
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
Post #247793
Posted Wednesday, January 25, 2006 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, December 30, 2006 6:14 PM
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

Post #253441
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse