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