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:

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.

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.

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