AccessXP, multiuser – Performance loss (Access XP)

Topic

New Reply

I have had an Access 97 based system running at a local firm for a number of years (recently on Windows XP based machines).
They have just upgraded to a new server with the latest Windows / Exchange server versions. The firm that did the installation has also modified users settings on the local PC’s.

I have converted the Frontend to an Access XP based version (MDE) and changed the backend to Access XP version.
Recordsets (apart from recordsetclone of forms) are as ADO recordsets.
A typical example would be
rs.open szSQL, currentproject.connection, adopenstatic, adlockoptimistic

The first user logs into the system – bit sluggish but acceptable.
The second user logs in and now has to wait twenty seconds before the startup form appears.

I have also put the MDB version onto a PC to attempt to track the problem and found that opening a table (with one record which has one field -admin password) from the database view takes in excess of 4 seconds. Single stepping code shows that each call to open a recordset or use a dlookup takes many seconds.

I have turned the subdatasheets option off on each table in the backend, to no effect.

I am out of ideas as to what is so wrong with the performance. (On a PC here with Win XP I can open multiple users concurrently and it takes fractions of a second to start – as the Access 97 version used to do).
What could be causing the problem? (IS it a known problem with the latest MS server?) What could I try to identify the true bottleneck?

Reply | Quote

Replies

Have you turned off Name AutoCorrect in the General tab of Tools | Options…? Although, that should not make a difference in an MDE database, I think…

Reply | Quote

No (or at least it is the default – must check).
Frontend or backend or both?

Reply | Quote

No (or at least it is the default – must check).
Frontend or backend or both?

Reply | Quote

Have you turned off Name AutoCorrect in the General tab of Tools | Options…? Although, that should not make a difference in an MDE database, I think…

Reply | Quote

Have you placed the front-end on the workstation, or are all workstations hitting a single front-end on the server?

Reply | Quote

Yes, FE on workstations, BE on server.

Reply | Quote

OK – that eliminates that possibility. We’ve seen something rather similar a couple of times, and fought our way through it. One possibility has to do with the lack of a persistent connection to the backend, another is an interaction with security. Check out the performance tips at http://www.granite.ab.ca/access/performancefaq.htm%5B/url%5D. The one with the attempt to delete the LDB file sounds like it could be your issue.

Reply | Quote

Network ‘should’ be OK. It worked fine for the Access 97 version on the old server. No hardware changes to switch/cables has been made, just new server/OP system, various patches installed and some changes (grrr..) on the local machines re user rights.

I’ll check the link out.

Reply | Quote

Network ‘should’ be OK. It worked fine for the Access 97 version on the old server. No hardware changes to switch/cables has been made, just new server/OP system, various patches installed and some changes (grrr..) on the local machines re user rights.

I’ll check the link out.

Reply | Quote

Thanks – i’ve worked throught the list – those that I can influence.
I’ve forced a connection to be permanent (bound form), checked things are turned off, replaced custom functions in queries (one) and changed dlookup/dmax to custom functions using recordsets.
Starting for the second and other users is now acceptable (not as fast as with Access 97 but only 3-4 seconds).
Using the interface doesn’t seem to be any faster than before the changes. It’s slower than Access 97 (ca 1 second where it took say 0.5 seconds) but it is usable.

Reply | Quote

Put some of the blame on unicode and some on the new VBE and the reworked query engine. Access 97 did NOT load all the modules immediately as 2000 and XP do and it handled optimization differently. Even in 97 multiple subforms loading when a form opened could slow the form down enormously, however. I make heavier use of saved queries in XP and make every effort to load subforms the first time they are needed, which speeds the opening of the form but delays the subform slightly.

Reply | Quote

There isn’t much more I can optimise in the FE wrt. forms. Basically the ‘normal’ user just gets one form (with 4 sub forms) and then a simple print menu when they click a button.

If you are interested it is example 1 on my home page. http://www.ainscow.ch

The admin gets a different set of options but that is really just for control purposes.

Reply | Quote

There isn’t much more I can optimise in the FE wrt. forms. Basically the ‘normal’ user just gets one form (with 4 sub forms) and then a simple print menu when they click a button.

If you are interested it is example 1 on my home page. http://www.ainscow.ch

The admin gets a different set of options but that is really just for control purposes.

Reply | Quote

Put some of the blame on unicode and some on the new VBE and the reworked query engine. Access 97 did NOT load all the modules immediately as 2000 and XP do and it handled optimization differently. Even in 97 multiple subforms loading when a form opened could slow the form down enormously, however. I make heavier use of saved queries in XP and make every effort to load subforms the first time they are needed, which speeds the opening of the form but delays the subform slightly.

Reply | Quote

Thanks – i’ve worked throught the list – those that I can influence.
I’ve forced a connection to be permanent (bound form), checked things are turned off, replaced custom functions in queries (one) and changed dlookup/dmax to custom functions using recordsets.
Starting for the second and other users is now acceptable (not as fast as with Access 97 but only 3-4 seconds).
Using the interface doesn’t seem to be any faster than before the changes. It’s slower than Access 97 (ca 1 second where it took say 0.5 seconds) but it is usable.

Reply | Quote

OK – that eliminates that possibility. We’ve seen something rather similar a couple of times, and fought our way through it. One possibility has to do with the lack of a persistent connection to the backend, another is an interaction with security. Check out the performance tips at http://www.granite.ab.ca/access/performancefaq.htm%5B/url%5D. The one with the attempt to delete the LDB file sounds like it could be your issue.

Reply | Quote

Andy,

Did you turn off subdatasheets on the tables, front and back end? They’re on by default and I’ve found that they can cause serious delays in just opening a linked table in the database window! They especially sap performance if you are also using combobox lookup fields in your tables. With all those queries running, the system just seems to step all over itself. This can be true even with the front and back ends local on the same machine.

Reply | Quote

I’d just done the BE. The FE has just a couple of ‘trivial’ tables, I’ll turn it off on those and give it a try.
I feel the DB is more or less in order as it works fine here (for 2 users) and performance (in real location) for the first user is OK, it’s users 2, 3… that it deteriorates catastrophically.

Reply | Quote

I’d just done the BE. The FE has just a couple of ‘trivial’ tables, I’ll turn it off on those and give it a try.
I feel the DB is more or less in order as it works fine here (for 2 users) and performance (in real location) for the first user is OK, it’s users 2, 3… that it deteriorates catastrophically.

Reply | Quote

Andy,

Did you turn off subdatasheets on the tables, front and back end? They’re on by default and I’ve found that they can cause serious delays in just opening a linked table in the database window! They especially sap performance if you are also using combobox lookup fields in your tables. With all those queries running, the system just seems to step all over itself. This can be true even with the front and back ends local on the same machine.

Reply | Quote

Yes, FE on workstations, BE on server.

Reply | Quote

Have you placed the front-end on the workstation, or are all workstations hitting a single front-end on the server?

Reply | Quote