Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Digging Into Access Performance

By Andy Warren,

One of the IT teams with my current employer has used Access as a data manipulation platform for SQL Server data for years. It's got quite a few things built in that are very handy (very easy to use heterogeneous data, VBA IDE, and it's all saved in one MDB file), but performance has never been it's strong suit in our environment. In truth it's not always about performance, but sometimes performance does matter. To clarify, I'm speaking about performance when using linked tables to connect to SQL rather than performance when using data contained solely within the MDB. I'm not here to convince you that Access is bad, because I don't think it is, but to show you that there are times when you may need to counsel a different approach depending on the size/time of the operations being performed.

I started by linking in two tables from Northwind on my local machine, Employees and EmployeeTerritories. I then built a simple update statement using the Query Builder (shown below) and then took at look at the generated SQL.

UPDATE dbo_Employees INNER JOIN dbo_EmployeeTerritories ON dbo_Employees.EmployeeID = dbo_EmployeeTerritories.EmployeeID SET dbo_Employees.LastName = "Leverling2"
WHERE (((dbo_EmployeeTerritories.EmployeeID)=3));

Now I don't expect you to know Northwind by heart, but looking at I would think that it should be a fairly efficient query. Before digging into Access, I decided to benchmark it by converting the query to TSQL and profiling it. This is the revised query:

UPDATE Employees SET Employees.LastName = 'Leverling2' from Employees INNER JOIN EmployeeTerritories ON Employees.EmployeeID = EmployeeTerritories.EmployeeID 
WHERE (((EmployeeTerritories.EmployeeID)=3));

Profiler shows the query takes 8 reads to execute, and the query plan looks reasonably straight forward.

Now let's look at what Access does. To duplicate for testing, include the SQL:Batch Completed, RPC:Starting and RPC:Completed events in Profiler. Running the query from Access generates 131 reads. Where do the extra reads come from? Let's take a look at what it sent to the server:

First, it sent this:

SELECT "dbo"."Employees"."EmployeeID","dbo"."EmployeeTerritories"."EmployeeID","dbo"."EmployeeTerritories"."TerritoryID" FROM "dbo"."Employees","dbo"."EmployeeTerritories" WHERE (("dbo"."Employees"."EmployeeID" = "dbo"."EmployeeTerritories"."EmployeeID" ) AND ("dbo"."EmployeeTerritories"."EmployeeID" = 3 ) )

This is bringing back the four rows the query generates. It's four rows because of the join to employeeterritories.

Then it runs another select (for reasons that are unclear to me) to get the employeeid and lastname from employees.

exec sp_prepexec @P1 output, N'@P1 int', N'SELECT "EmployeeID","LastName"  FROM "dbo"."Employees"  WHERE "EmployeeID" = @P1', 3

Then it runs four (!) updates when we know that there is really only a single row in employees to be updated. I suspect that within Access it is looping through the initial result set and generating one update statement per row. Note that sp_prepexec is undocumented in BOL, quick research indicates that it's similar to sp_executesql.

exec sp_prepexec @P1 output, N'@P1 nvarchar(20),@P2 int', N'UPDATE "dbo"."Employees" SET "LastName"=@P1 WHERE "EmployeeID" = @P2', N'Leverling2', 3
exec sp_prepexec @P1 output, N'@P1 nvarchar(20),@P2 int', N'UPDATE "dbo"."Employees" SET "LastName"=@P1 WHERE "EmployeeID" = @P2', N'Leverling2', 3
exec sp_prepexec @P1 output, N'@P1 nvarchar(20),@P2 int', N'UPDATE "dbo"."Employees" SET "LastName"=@P1 WHERE "EmployeeID" = @P2', N'Leverling2', 3
exec sp_prepexec @P1 output, N'@P1 nvarchar(20),@P2 int', N'UPDATE "dbo"."Employees" SET "LastName"=@P1 WHERE "EmployeeID" = @P2', N'Leverling2', 3

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. The downside to it is that the syntax must be TSQL, not Access SQL, so it's not quite as easy as executing it a different way but it is doable if needed.

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.

I'm going to follow up this article with at least one more to dig into insert and delete performance. For now, recommendations for getting max update performance from Access against linked SQL tables are as follows:

  • If only a few rows involved per update is probably not worth tuning
  • If more than few rows or done frequently, consider moving to pass through queries, using code to execute the sql directly by opening up a connection yourself, or using code to execute a stored procedure
  • Keep standard tuning guidelines in mind -  tune the things that are creating a bottleneck first, then decide whether to spend additional time optimizing non critical areas
Total article views: 8074 | Views in the last 30 days: 1
 
Related Articles
FORUM

sql query need Help about Absent Employee

Query about absent employee

FORUM

update access to sql server

update and query access

FORUM

Employee job mapping

Soln required for employee and job mapping query

FORUM

Help on Update/Query

Update/Query

FORUM

Update in Access front-end of SQL back-end

When is a SQL Server query updatable in an Access front-end view?

Tags
access    
programming    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones