VB6 Database operations slow down when multiple users connect to the database

Question

I am currently using VB6 to connect to an MS Access database using DAO and Im, which are experiencing a very noticeable slowdown when a second user connects to the database.

Here are the steps to play:

• Open the database from computer A by entering the software

• Add entries to the database using software (takes about 4 seconds)

• The second user is registered in the software (computer B), that is: it opens the database, displays current transactions, but the user does nothing

• On computer A, repeat the add recording operation, now the operation takes about 6 seconds.

Additional Information...

• the operation continues after about 6 seconds even after computer B exits the software

• if you close and reopen the application from computer A, the operation will return to execution in just 0.4 seconds!

Any help would be greatly appreciated!

Thanks!

Answer 1

This is how MS Access works. Although it supports multiple users and supports the sharing of the database in the file’s shared access, several PCs can access it, but this is not very good. And if you do it simultaneously (multi-user and more network access to a file resource), I feel your pain.

The answer is to run the upgrade wizard and convert it to an instance of MS SQL Server. The MS SQL Server Express version is a good choice to replace Acess in this case. Please note that you can still store all your codes and reports, etc. In Access, only data needs to be moved.

---

To just understand the differences, in MS Access, when you read data from a database, all the data necessary to fulfill your request is read from a file by your program, server-side processing is not performed. If this data is online, you retrieve this data on your network. If there are multiple users, you have additional overhead for blocking. Each user program / process interacts effectively with the program / process of other users using file I / O (writing lock information to a network file or files). And if network I / O operations expire or other problems occur, then these files may be corrupted.

In SQL Server, the SQL Server engine manages data queries and returns only data. It also manages locks and can detect when a client disconnected or was scheduled to clean up, which reduces the problems caused by several users on the network.

Answer 2

We had a problem with our VB3 / Jet DB 2.5 application when we switched to using newer file servers.

The problem is the "opportunistic blocking": http://support.microsoft.com/kb/296264?wa=wsignin1.0

Albert probably describes the same thing; the server will allow one client exclusive access to the file, but when there is another chime, this exclusive access will be “trash” between them, which will lead to delays, since the client with oplock flushes the entire local cache to the server before another client can access file.

It could also be the reason that you get good performance with one client - if it uses oplock, it can cache all data locally.

It can also cause some unpleasant damage if one of your clients has a power failure or is disconnected from the network, because this reddening of the local cache on the server may be interrupted.

You could disable this (on the client — so you need to serve ALL clients) in Windows 2000 and XP as described in this article, but after Vista SP2 this seems impossible.

The points about using Access / JetDB as a multi-user database are essentially correct - this is not a good architectural choice, especially in light of the above. DAO is also an obsolete library, even in obsolete VB6. ADODB is the best choice for VB6 and should allow you a certain degree of database independence depending on how your application is written.

Answer 3

Since, as you pointed out, you get decent performance with one user on the system, then obviously your application by its nature does not pull too much data over the network, and we cannot blame the network speed here.

In fact, it happens that the Windows file sharing system switches from file sharing mode to multi-user file mode. This switch file mode causes a significant delay. And this also means that a 2nd or more user should try to figure out and set locks in the file.

To remove this noticeable delay, simply at the beginning of your application, open what we call a persistent connection. A persistent connection is simply what keeps the network connection open at all times, and therefore this significant delay in switching between two file modes for file sharing has been eliminated. Now you will find that the performance with two users should be the same as the one (provided that one user is idle and does not increase the load on the network). Therefore, when starting the application, open the back end table for the var and KEEP global variables so that the table always opens.