CODEWITHSUNDEEP
sqlsql-serverms-access
kzhang12
kzhang12

Reputation: 211

Access linked to SQL Server

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

Upvotes: 5

Views: 109

Answers (4)

C. White
C. White

Reputation: 800

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.)

Upvotes: 1

Raikol Amaro
Raikol Amaro

Reputation: 449

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

It depends the type of query you're executing. I've found that usually for SELECT queries the execution time ends up being pretty much the same, but for UPDATE and DELETE queries for some reason seem to be really slow. That said, I think everyone who's worked with Access for a little while will agree that it sometimes behaves a bit unexpectedly. So you will have to try it and compare the final results.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20794

I do something similar, but with redbrick, not sql server. These are my observations:

Using a pass through query there is little if any difference in performance. However, the output will be read only which may or may not be what you want.

Using the access query designer, on a single table, with a composite primary key, it might be lightning fast or horrendously slow. If the query is not going to return any rows, you instantly get an empty result set. However, if the query is going to return data, it's different story.

This example uses a table called clinic_fact with a primary key of registration_number and clinic_position. It has about 6.5 million records. If you query the database directly, filtering on a single registration_number, you get instant results. In access, it takes over 10 seconds to get the results. During that time I get to see what sql is being generated. It looks like this.

select yourfields
from "clinic_fact"  
where "registration_number" = 'something' and "clinic_position" = 2 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1 
or "registration_number" = 'something' and "clinic_position" = 1;

This query returns 2 rows. The clinic positions are 1 and 2.

Stuff like this may or may not happen with SQL Server. I suggest that you try it and see.

Upvotes: 0

Andre
Andre

Reputation: 27634

Will the speed the same as querying data in the SQL Server environment?

In general: yes. In my experience, for the vast majority of queries, the execution time on the server will be more or less identical in both cases. Of course there is a little network overhead to display the data in Access, so the total execution time may be a tad higher.

There are exceptions, though. I have had some complex queries, typically with one or more left joins, that perform abysmally when executed in Access, but without any problems when executed on SQL Server.

For these cases (you'll notice them!), use Pass-Through queries in Access. They are executed directly on the Server as written, without being interpreted by the ODBC driver.

Upvotes: 0

Related Questions