MS Access slow when two identical Queries are opened simultaneously (1 Viewer)

[ions]

Hello MS Access Expert,

Suppose I have a table Y with 30K records and I have two queries in MS Access which are identical (SQL = Select x from Y where x = "a").

When I open the first query the results appear instantly. While keeping the first query open, I subsequently open a second identical query and the same results now take 15 seconds to open. Why is MS access behaving this way when the two queries are identical?

I also noticed that if the first query is opened and then table Y is opened subsequently it is also very slow, hence, this is not isolated to queries.

I would like to first understand why MS Access behaves like this and then I can produce a workaround.

Thank you.

 

[CJ_London]

Is Y a linked table?

what happens if you select a different field in each query?

 

[Shimon]

Is this a DB that you created?
If not, maybe this is relevant:
within Access, Forms, Reports and Queries have a RecordLocks property that controls how the underlying data is locked.

Setting this to "No Locks" (0) should mean that optimistic locking is used, and two or more users can edit the record.

Another possibility is disk IO, but you would have problems with other files, too.

I now realise that this is posted in the SQL server section, so you can rule out disk IO.
Sincerely,
Shimon

 

[ions]

Is Y a linked table?

what happens if you select a different field in each query?

Sorry, the table is an ODBC link to an SQL server table.

If query one is open then the entire table also opens slowly, so, the field criteria may not be important. I’ll make a video tomorrow of the behaviour and attach it here.

 

[Galaxiom]

When I open the first query the results appear instantly.

Are you sure the first query is not still running when you try to open the second query? Although results appear quickly, this does not mean the query has completed returning all the results.

 

[tvanstiphout]

Which ODBC driver are you using? You should be on v18, and on the latest version thereof.
Is SQL Server fully patched with the latest CU? Are you using a recent version of SQL Server?

While the slow query runs, execute

sp_who

in SSMS, and check the blk column for non-zero value.
What you are seeing is not normal.

 

[The_Doc_Man]

Galaxiom's question is important. Was the first query a "pass-thru" type of query? If so, code "releases" in the front-end once the back-end acknowledges that the query has started. If that is the case, the second query might be delayed because of internal locks that prevent two sessions from interfering with each other. The 2nd query might have to delay for tables being locked. I don't know enough about SQL Server locking internals, but I believe it has the ability to do table locks.

 

[ions]

Thank you for everyone's responses. I figured out the cause. I was using the Windows Native SQL Server driver not ODBC Driver 17 for SQL Server. When I switched to ODBC Driver 17 for SQL Server everything executes quickly.

Based on the below forum discussion (see the last post) I opted to use the Windows Native SQL Server driver, however, I will be using the latest Driver moving forward.

Which ODBC driver should I use?

Thank you

 

[Minty]

As per that discussion, I would install the latest one available - Version 18 at the time of writing.

edit : From here

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server

Download the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.

learn.microsoft.com

 

[GPGeorge]

As per that discussion, I would install the latest one available - Version 18 at the time of writing.

edit : From here

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server

Download the Microsoft ODBC Driver for SQL Server to develop native-code applications that connect to SQL Server and Azure SQL Database.

learn.microsoft.com

IMO, the only reasons not to install and use the latest ODBC driver (18 at this point in time) are:

• You work in an environment in which someone must install the driver on a multitude of local computers for each Access user and the IT support available to you to implement that is limited or lacking and you can't physically go to each computer to do that as needed.
• You are not interested in providing the best possible performance for your application.