{"id":25714,"date":"2022-07-21T00:12:00","date_gmt":"2022-07-20T22:12:00","guid":{"rendered":"http:\/\/159.69.82.204\/win\/?p=25714"},"modified":"2022-07-20T23:18:07","modified_gmt":"2022-07-20T21:18:07","slug":"solution-for-access-performance-issues-with-an-access-database-using-an-odbc-driver","status":"publish","type":"post","link":"https:\/\/borncity.com\/win\/2022\/07\/21\/solution-for-access-performance-issues-with-an-access-database-using-an-odbc-driver\/","title":{"rendered":"Solution for access\/performance issues with an Access database using an ODBC driver"},"content":{"rendered":"

[German<\/a>]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.<\/p>\n

<\/p>\n

A customer with old hardware\/software<\/h2>\n

\"\"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. <\/p>\n

\n

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.<\/p>\n

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).<\/p>\n<\/blockquote>\n

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.<\/p>\n

Hardware change causes an escalation<\/h2>\n

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: <\/p>\n

\n

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.<\/p>\n<\/blockquote>\n

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. <\/em>But, unsurprisingly for me, problems did occur, which the reader describes like this: <\/p>\n

\n

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. <\/p>\n

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.<\/p>\n<\/blockquote>\n

The blog reader also told me that the problem PCs are running the latest Windows version, are fully patched, and are not running Office. <\/p>\n

Problem narrowed down<\/h2>\n

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. <\/p>\n

A workaround helps<\/h2>\n

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: <\/p>\n