[German]There are users who still access Access databases with older applications via ODBC drivers. Sometimes, when switching to newer hardware/Windows versions, access and especially performance problems occur – the database accesses are slow. A blog reader emailed me with such a case and the solution he used. The approach may be of interest to other users.
Advertising
A customer with old hardware/software
One of my blog readers is a software developer who encountered massive performance issues at a customer's Access database system. This is because this customer was still using old software from the 1990s. Here's what the blog reader told me.
We have (had) massive access problems to existing Access databases at a customer after computer updates. The customer has been using these databases (Access 2000 format *.mdb) for ages.
The used SW is still from the 90s and uses the 32Bit Access ODBC driver. We have offered several times to switch to SQL-Server and a newer database lib, but the customer could not make up his mind (or wants the all-embracing "eierlegende Wollmilchsau", which is then too expensive).
I think this description sounds familiar to every service provider, everyone has experienced something similar. The old software often can't be changed over so easily, but there is a hitch when switching to a new Windows version.
Hardware change causes an escalation
It's often the case that customers have to switch to new hardware at some point, at which point they're also usually due for a newer version of Windows. And that's where the problems start. The reader describes the customer's situation like this:
So he [the customer] has only ordered new industrial PCs and wants to run the old SW on them. Therefore only the hardware drivers were adapted and the old SW was installed.
Of course, the blog reader has tested this scenario before and he wrote: On various test machines, the software ran as before on the old PC under Windows 7 Professional. Database accesses ran in the blink of an eye under 2 seconds. But, unsurprisingly for me, problems did occur, which the reader describes like this:
On the new industrial PC running Windows 10, all database accesses are extremely slow. The network connections are ok, normal file transfers run with usual speed.
Only SQL accesses via ODBC to the Access files on the server are extremely slow. An access can take up to 5 minutes. The computer shows no significant network or CPU activity. It simply hangs.
The blog reader also told me that the problem PCs are running the latest Windows version, are fully patched, and are not running Office.
Advertising
Problem narrowed down
The reader was then able to reproduce the problem in his lab and examine it more deeply. His conclusion: It seems to be solely due to the computer (in his opinion, the CPU). The new CPUs are i7-8700 with 12 logical cores. He suspects that something is blocking (some race condition perhaps) because the CPU is too fast.
A workaround helps
At this point, the reader has developed a workaround, which he shares with the readership – ultimately it is about slowing down the faster processor for database accesses. In his particular case, the following measures provided the solution:
- Reduced the number of threads in the driver to 1 (!)
- Set the number of used processors in the program to 1 (this can also be done via Taskmanager under "Details", "Set membership", but then only for the current instance)
- Switched to the 2010 Access driver (maybe not absolutely necessary, but it seemed to help a bit)
After that, the program ran normally again. To the first point, to reduce the number of threads in the driver to 1, the reader sent me the following hint:
Restrict threads in the driver:
[open] ODBC manager and then [select] advanced options
[[then the number of threads can be predefined on the System-DSN tab (see screenshot)]
Adjust path to the DB best in the registry
At this point my thanks to the reader. Maybe it will help someone someday.
