Unanswered: MS Access runs slowly for client PC's after a update or insert
We are using MS Access as the backend to our application which has been written in delphi and have run into a problem that we have not been able to solve. Hoping someone has run into this before or any suggestions are much appreciated.
MS Access runs slowly for client PC's after a update or insert.
- I am using ADO to connect to the Access database, which is using the OLEDB for ODBC Provider.
- The application I have sends queries (both select and update) direct to the database (ie client datasets are used).
- When only select queries are sent to the DB the response time is fine.
- When an update or insert query is sent to the DB the response time of the PC it is run on is fine.
- When an update or insert query is sent to the DB the response time of any other client PCs running the application take about 5 to 6 times longer to run queries than before the update\insert query was done. This is the issue that I am having.
- Any client PC's that display this slower response time, can have their response time returned to normal by closing down the application and restarting it.
- No more than 3 PC's connected at one time to the DB.
- Maximum database size of 150MB.
- Problem occurs on various network setups, including domain and workgroup.
- Problem only surfaces for users at times well after any application updates have been applied (ie several weeks after, and then once the problem starts it continues).
- It does not occur for all user sites.
I have tried and thoroughly tested the following to no avail...
- Applied all the latest microsoft updates
- Closing and re-opening the ADO connection after updates\inserts
- Changed the ADO provider to Jet 4
- Saving the DB in Access 2000 or 2002 format
- Set the Default record locking to 'No Locks' and 'All records' and 'Edited record'
- Used 'Open databases using record-level locking' selected and unselected
- Many application techniques (using delphi) to work around the issue. Many of which have indeed improved general response times, but have not resolved this particular issue.
The only thing I have tried that has resolved the issue is... - Upsizing the database to SQL Server (Unfortunately this option is not a viable one for us at this stage, so I need to find a resolution to it while still using the Access DB).
Note: I moved this thread here to the Access forum, as it should not be in the other database server forum...
You say that you can't upgrade to SQL Server - what about MSDE? It's free, and can be installed on any of the PCs currently could host an MDB file. It is truly SQL Server lite, and any code that works with it will migrate seamlessly when you upgrade to the full-blown server later. Plus, it's a lot more reliable and is NOT prone to data file corruption. Finally, since its a true server product, you have a more options in the way you retrieve data, and in the way you write data (you can use stored procedures and triggers, for instance.)
With the Access database, how are you indexing the tables where you retrieve data? Since Access is a file-based database, all processing takes place at the ado client. Each field used in the WHERE clause or ORDER BY clause must be indexed, else the entire table gets copied from the data file to the client. In a local instance of the data file, the data transmission for the entire table takes place over the PC's data bus, and is fast enough to mask this issue. When the data file is remote, however, the transmission time adds up, and it is exascerbated by network bandwidth throttling (plus, in an already bandwidth limited situation, it will worsen the problem for other apps/users)
If the tables are indexed properly, only the index is copied in its entirety from the file to the ADO client, and then the individual records are retrieved.
Last edited by loquin; 03-06-07 at 15:54.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert