First, it works with an interpreted language, vb. Interpreted languages are always slower than compiled languages, as interpreters generally translate each line of code as they encounter it.
Second, MS access generates a lot of network traffic. I don't know why, but it does.
Microsoft publishes a nice page with performance tips. Some interesting knowledge base articles:
- http://support.microsoft.com/kb/209126
- http://support.microsoft.com/kb/128808
- http://www.microsoft.com/technet/archive/office/office97/maintain/off411ef.mspx?mfr=true
- http://support.microsoft.com/kb/889588
The latter is particularly interesting, it contains several tips (or tricks, depending on ones point of view)
Someone asked me to have a look at an application a fellow worker had developed and which didnt perform on the network of our customer.
A first analysis learnt that the data involved was not much: most tables had very little rows, 10-50 rows, while a few had about 400 rows and one had 700 rows. The whole ms access data file was less than 4 Mb.
Yes, the database had been split in a program mde and a database file. Good practice in many environments, as it allows developers to work on the programs while the users can continue working with it.
The application works in a network environment, where the customers are in The Hague while the servers are in Amsterdam. The customers worked within a powerfuse and citrix network environment.
The performance problem appeared throughout the application, but was most noticeble in the opening of two forms. One took 30 seconds to open, the other 60. In stand alone mode, the forms openend within a second.
The network posed no problem, a database operator openened the forms on the server while located in Amsetrdam and found the same results.
Using this kb article, I asked him to rename both the database .mdb file and the program .mde file to a 8.3 format name. The result was astonishing: times went down from 30 and 60 to 10 and 20 seconds respectively. Astonishing. I had hoped them to half, a factor of 3 was above my expectations.
In a test environment, the option "Turn off the sharing violation notification delay" yielded no benifit. The same results appeared when I tested "Disable automatic short file name generation".
What did result in a substantial improved perfoamnce was the option to turn of local settings fo windows. This halved the time required for opening one of the windows, but did nothing for the other window.
Another thing was the option
