CODEWITHSUNDEEP
sql-serverperformancems-accessodbc
Edgar
Edgar

Reputation: 2767

Access SQL-Server slow when opening multiple queries

I am upsizing an Access 2010 database to MS SQL-Server and I have some strange problem that only occurs if I open more than one query. I discovered the problem because I had a form with three sub forms which opened very slowly. Then I removed one sub form after another or just opened the queries for testing. The following description is the end of my testing.

To describe the problem lets concentrate on three queries which I call qA, qB and qC. Each query is based on each one table on the SQL-Server with just a few records. The tables are linked tables which are connected to the SQL server with this connection string (Later I found out this connection string was wrong and this was the problem. I keep it here so that this question and answer still makes sense.):

ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=SERVER01;Trusted_Connection=Yes;DATABASE=AATest001;

Currently Access and SQL-Server both run on a single high performance PC.

If I open only qA or only qB or only qC each query opens instantly. But if I open qA and then qB and then qC then the whole procedure becomes slow. The first query starts fast, the next (most of the time) also still fast. But the third query needs about 10 seconds or more to open.

It does not matter if I open them in the order A, B, C or C, B, A or any other order. It is always the last query which opens very slow.

It does not even matter if I wait 10 seconds or more between opening the queries. If two queries are open already then the third will be slow. Even if I waited a few minutes between opening the 2nd and 3rd query the 3rd query will open very slow.

If I open two queries and then I close one of them and open again another query all works fast.

I appreciate any answer but also possible tips how I can further analyze the problem. I tried SQL Server Profiler (which I know only very little) but nothing was obvious.

Upvotes: 1

Views: 1353

Answers (1)

Edgar
Edgar

Reputation: 2767

I found an answer but I still think this is crazy.

I rechecked all my settings and I found that the connection string in the linked tables did not use “SQL Server Native Client 11.0” as I thought it would but it used “DRIVER=SQL Server”.

After I changed all tables to “SQL Server Native Client 11.0” there was no performance problem anymore. But what does this tell us about the standard “DRIVER=SQL Server”? Shouldn’t that also work without problems?

I like to add one more important information:

When I linked the tables from Access to SQL I used a DSN file. I though Access would use that file for the connection. But this is only partly true because when I changed the DSN file (I enabled logging) later this did not change anything for my linked tables.

It seems that when Access links the tables it reads the connection string in the DSN file and then writes that connection string into the Connect property of each table. And after that is done Access ignores the DSN file. The DSN file can even be deleted and Access won’t miss it.

I hope this will help other users with similar problems.

Upvotes: 2

Related Questions