Access linked to SQL Server

Question

If I use Access to link to the SQL Server database, will the speed be the same?

For example, I have a table [TEST] in the SQL Server database, with an index on column [ID]. If I write the SQL query in the SQL Server environment:

SELECT .... 
FROM TEST 
WHERE ID = ...;

The index is working (or helping). If I link to this [TEST] using Access, and write the query in Access:

SELECT .... 
FROM TEST 
WHERE ID = ...;

Will the speed the same as querying data in the SQL Server environment? Is there some way to test it, like the execution plan, or SQL Server Profiler in the SQL Server?

Thanks

Answer 1

As long as you are doing all of your joins and where clauses on indexed fields, it should be relatively similar server-side. Problems start to arise when you are using the Access query engine to pull data from linked tables and you are using non-indexed fields.

In these cases, Access likes to do things like request everything from the table, and perform its filters client-side. This hits the server harder, hits the network harder, and forces your machine to chug through a lot more data to display the result set.

Pass-through queries are definitely your best bet to avoid those sorts of problems. A few warnings for using pass-through queries with DAO:

• You can't assign parameters to DAO pass-through queries, so when you need flexible criteria, you'll need to use dynamic SQL.
• You can do complex stuff in DAO pass-through queries (e.g. variable declarations, run multiple queries in sequence, cursors (if you must), etc.) but in order to do anything more complicated than a single statement, you'll need to us SET NOCOUNT ON or else Access will choke when the first statement returns its status.

(If I'm working in code, I generally prefer to use ADO for pass-through queries, mostly because it lets me use parameters, and I'm super paranoid about dynamic SQL (especially if there's string input involved). The downside is that ADO doesn't handle JET environments as well as DAO does.)