Access SQL Server

Question

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

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

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

The index works (or helps). If I contact this [TEST] using Access and write a request in Access:

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

Will the speed be the same as querying data in a SQL Server environment? Is there a way to check it, such as an execution plan or SQL Server Profiler on SQL Server?

thanks

Answer 1

As long as you make all your connections and where the clauses on the indexed fields are, it should be relatively similar to the server. Problems arise when you use the Access query engine to retrieve data from related tables, and you use non-indexed fields.

In these cases, Access likes to do things like query everything from a table, and execute its filters on the client side. This hits the server hardest, hits the network harder and makes the machine punch a lot more data to display a set of results.

Missing requests are by far the best choice to avoid such problems. A few warnings for using end-to-end queries with the DAO:

• You cannot assign parameters to end-to-end DAO queries, so when you need flexible criteria, you will need to use dynamic SQL.
• can do complex things in end-to-end DAO queries (for example, variable declarations, run multiple queries sequentially, cursors (if necessary), etc.) , but in order to do something more complex than a single statement, you need SET NOCOUNT ON , otherwise Access will throttle when the first statement returns its status.

(If I work in code, I usually prefer to use ADO for end-to-end queries, mainly because it allows me to use parameters, and I'm super paranoid about dynamic SQL (especially if there is string input). The disadvantage is that ADO does not handles JET as well as DAO environments).

Answer 2

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

In general: yes. In my experience, for the vast majority of requests, the execution time on the server will be more or less the same in both cases. Of course, there is a small network invoice for displaying data in Access, so the total execution time may be slightly higher.

There are exceptions. I had some complex queries, usually with one or more left connections, which are executed with horror when executed in Access, but without problems when executed on SQL Server.

In these cases (you'll notice them!) Use Pass-through queries in Access. They run directly on the server, as written, without being interpreted by the ODBC driver.

Answer 3

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

Using pass through query is slightly different from performance. However, the output will be read-only, which may or may not be what you want.

Using the access request constructor in a separate table with a composite primary key, it can be lightning fast or terribly slow. If the query does not return any rows, you will immediately receive an empty result set. However, if the request returns data, it will be a different story.

This example uses a table called clinic_fact with the primary key registration_number and clinic_position. It has about 6.5 million entries. If you query the database directly by filtering a single registration number, you get instant results. Access requires more than 10 seconds to get results. During this time, I see which sql is being created. 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. Clinic positions are 1 and 2.

Such things may or may not happen with SQL Server. I suggest you try and see.

Answer 4

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

It depends on the type of query being executed. I found that usually for SELECT queries, the execution time ends almost the same, but for UPDATE and DELETE queries for some reason it seems very slow. However, I think that everyone who has worked with Access for some time will agree that this sometimes behaves a little unexpectedly. Therefore, you will have to try and compare the final results.