ODBC access very slow when form is open

[Kowalski]

I've got a weird issue that I desperately need help on.
I've got an Access 365 database with a number of tables linked to SQL Server (2016) via an ODBC connection.

In short the problem is that reading data from SQL is very very slow when I have a form open.

When I open the database in Access and run a lookup in the immediate window, it is nice and fast.
E.g.
?Dlookup("TaskID", "Tasks") //This runs in a few milliseconds.

Now I've created a standard form with 1 field on. The form does not read from table Tasks. In my case it reads from table Companies.
If I open the form in Form View and I run the DLookup as above in the Immediate Window it is slow. Takes about 8 seconds for every execution. Even having the form open in Design view makes the lookup slow.
If I close my form, then the DLookup is quick again. This is really hampering my work as I have other forms that does a lot of database access, and all access is slow.

I've also switched on the ODBC tracing and the sqlout.txt only get created after about 7 seconds, and then 1 second later I have the results. Why does it take so long?
What can it be?

 

[Uncle Gizmo]

I noticed your question has not yet received a reply, hence I am bumping it up the list, hoping that you will get some more views.

 

[The_Doc_Man]

I saw this but I have no expertise in SQL server.

The things I look for in performance problems are memory, virtual memory, and active competition for the CPU. I don't see this as a disk space issue. How much RAM do you have on your front-end system? How much virtual memory do you have allocated on your FE system? If you run Task Manager, click the Processes tab, then click the CPU header on the display, that will make the top CPU consumer be the top of the display. If TM is running with this display, and you then attempt this experiment, what is running in that approximately 7 second interval?

 

[Kowalski]

Not hardware resources. Interestingly, if I change from SQL2016 to 2019 the problem disappears.

 

[The_Doc_Man]

If you can figure out the vintage of your ODBC drivers and compare them to the vintage of your SQL server, you might find that the ODBC was newer than the SQL you had started with. Software version mismatches will do strange things.

 

[Kowalski]

Thanks. Have not considered that. Might well be.

 

[Minty]

I also suspect ODBC drivers, SQL server 2019 > 2016 shouldn't really make any difference though.

Where is the Server and how are you physically connected to it?

 

[Kowalski]

I also suspect ODBC drivers, SQL server 2019 > 2016 shouldn't really make any difference though.

Where is the Server and how are you physically connected to it?

Hi. The SQL Server is on my local machine as it's in the dev environment. Connecting to it via odbc linked tables.

 

[Minty]

You have both 2019 and 2016 sql servers on your dev machine? or when you installed 2019 the problem went away?

If the latter then I suspect the install will have updated your SQL ODBC Drivers.

 

[Kowalski]

You have both 2019 and 2016 sql servers on your dev machine? or when you installed 2019 the problem went away?

If the latter then I suspect the install will have updated your SQL ODBC Drivers.

I have both. I just moved the DB from 2016 to 2019.

 

[Minty]

Interesting. What version of the SQL ODBC driver are you using?

 

[Kowalski]

Interesting. What version of the SQL ODBC driver are you using?

2011.10.7462.06

 

[Minty]

Okay that is the SQL Server Native Client 11 .

I would consider upgrading to a later version - ODBC version 17, and see if that helps.
Also when you moved it from 2016 to 2019 where the table indexes etc updated or changed in anyway?

 

[Kowalski]

No index changes. Just a straight restore.

 

[lenincahuasqui]

all "D" (Dlookup, Dsum, etc) have problems with ODBC, is better make view in database and "D" use with this view

 

[sportsguy]

I have the same slow issue, though not related to a Dlookup with the same ODBC driver to SQL Server 2016 in the company data center.
THinking we need to update the server virtual machine to SQL 2019 in the data center

Thanks