5

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

Improve this question
1
  • 2
    You can use Profiler to show you the commands that are being passed to the SQL Server. As for the relative speed, you could just test it yourself. Commented Oct 23, 2015 at 14:56

4 Answers 4

Reset to default
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.)

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

Thanks for the Pass-Through queries solution. Did not know that at the beginning. Spent too little time in Access side. However, @Dan Bracuk seems to have some different ideas. I will test that.
@kzhang12: well, you asked about SQL Server. Only in rare cases as described above have I seen performance problems in Access queries that would not have been there in direct SQL queries. Never when working directly with tables, or with "simple" queries (a couple of tables with INNER JOINs).
0

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.

Improve this answer

7 Comments

This sounds like the redbrick ODBC driver is ... not very good. But I agree to "try and see".
This is a good example. Thanks. But I do not quite understand "the output will be read only" part. After you use the pass-through query, you can still edit the underlying SQL.
It may be the driver or it may not be. I'll not attempt to analyse my observations, only present them.
About the read only part. I use access to edit records in the redbrick database. If I use a pass through query, I can't edit any records that are returned.
This is not a good answer to the question. Query translation and performance is closely related the ODBC-Driver (amongst other things). So you cannot compare Redbrick with SQL-Server.
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.