Please help.. One of our clients is having a problem that's been stumping us for the last two months. I apologize for the many, many variables in this problem, but, well.. that's the problem. I'm not exactly sure where to even start looking for the solution. I'd welcome any suggestions for where to start looking for the cause of the problem, and the clients are clamoring for a permanent fix. We're in kind of a bind, particularly since neither us or the clients have any kind of diagnostic software, so we need to try to derive the cause and the fix from what we can observe.
The client has an Access 2000 front-end (one on each workstation desktop), connected to an SQL Server 7.0 back-end, that's physically located at the other end of the office.
The problem, in short, is that the front-end database has a bound form in it, that lags every 20 records. That is, when scrolling through records, it scrolls fairly quickly (usually about one second to scroll) for the first 20 records, then between record 20 and 21 (and then between 40 and 41, etc.) it lags for several seconds. This, however, is when only one workstation is in the database - it happens much more frequently, and much more randomly, when there's more than one workstation using the database.
Here are all the things that we've been able to discover, that may or may not have something to do with the cause of this problem.
- The workstations are all running either Windows 98 or Windows XP. The Windows XP machines are new, and the clients claim the problem started when we moved some of the machines to XP. They also claim the problem went away when they unplugged their XP machines and went back to Windows 98. They recently went back to their XP machines, after we were able to help the problem somewhat by optimizing the form.
- When we created the exact same form and subform from scratch, the problem dropped in frequency. That is, when we redid the form, the same lag occurred, but in every 40 records in our controlled test, rather than in every 20 records.
- The form (which is bound to a query based on a table called tblCountForm, linked to tblStore) has two subforms.
- One subform is a datasheet form (also based on tblCountForm) lists all other records in tblCountForm for the current store.
- The other subform is a single form that shows information for the currently selected shopper (which is not necessarily the same as the shopper ID in tblCountForm, hence we cannot just have shopper info included in the query).
Removing the former subform appears to have no effect on the problem. Removing the latter subform, however, seems to make the problem go away.
- The tblCountForm table has approx. 65000 records and 58 fields.. ugh.. I did not design this database! :-( Some of the fields are poorly named (There's one, for example, called "Update" All the ID fields are named "<something> #"
. Changing the field names at this point is probably out of the question.
- The tblStores table has approx. 11000 records and 27 fields.
- The tblShoppers table (which is the basis for the subform that appears to be causing the problem) has approx. 40000 records and 96 (!!) fields. However, the subform is based on a query that is based on that one table, and the query only returns about 15 of those fields.
- The main form does have some code in the OnCurrent event, but removing the code doesn't appear to make a difference. The subform with the problem does not have any OnCurrent code.
- The problem does not appear to be related in any way to workstation memory. That is, the problem is exactly the same regardless of how many applications are running.
- The problem does not appear to be related to the data. That is, it occures every 20 records, regardless of which record you are on, and which one you are going to. There often isn't much of a discrepency between the ID numbers in either the main form or the problem subform.
- The problem seems to lessen when we scroll through the records more slowly. That is, if we scroll through the records quickly, the lag at record 20 is pronounced (sometimes as long as 40 seconds). When we wait a couple seconds between scrolling, the length of the lag seems to drop, although there is still lag.
- During less controlled (but more real) experiments, where more than one workstation is accessing the database at a time, the lag seems to be entirely random, instead of every 20 or 21 records.
- The database on the SQL Server is 1.2 GB large, according to Enterprise Manager. It is 25 months old. When we looked at the files on the hard drive, the LDF file was 10.9 MB and the MDF file was 1.15 GB.
- The SQL Server is a Dual Pentium 900 machine, with 256 MB RAM.
- When we looked at the performance indicators in the SQL Server Task Manager (in a controlled experiment using one of the Win XP workstations), the following occurred:
- When the workstation opened the form, the CPU usage spiked, but only to about 40%, from nothing.
- Each time the workstation scrolled to a new record, the CPU usage spiked, very very slightly.
- ..until, that is, the 20th record, when the lag occurred. When the lag started, the CPU usage dropped to almost nothing. Then, several seconds later, it spiked back up to about 45%, at which point the lag at the workstation dropped.
- The memory usage on the server remained constant throughout.
- The database also (in a most likely unrelated occurrance), has fairly frequent problems of their ODBC connections getting disconnected, usually when somebody else on the network is synchronizing the SQL server with another one in Georgia. Not sure if this is to be expected or not.
- The entire network has an RT45 Ethernet structure.
All this (particularly the CPU usage on the server thing) sounds to me like a networking problem caused by one or more of the new XP machines. But then why would the problem go away when we got rid of a particular subform? And why, in controlled experiments, does the problem occur on every 20th record (or every 40th with the new form), but occur on completely random records when more than one workstation is working? Are we even on the right track?
Very frustrating.. any ideas, suggestions, welcome. TIA! Katie
The client has an Access 2000 front-end (one on each workstation desktop), connected to an SQL Server 7.0 back-end, that's physically located at the other end of the office.
The problem, in short, is that the front-end database has a bound form in it, that lags every 20 records. That is, when scrolling through records, it scrolls fairly quickly (usually about one second to scroll) for the first 20 records, then between record 20 and 21 (and then between 40 and 41, etc.) it lags for several seconds. This, however, is when only one workstation is in the database - it happens much more frequently, and much more randomly, when there's more than one workstation using the database.
Here are all the things that we've been able to discover, that may or may not have something to do with the cause of this problem.
- The workstations are all running either Windows 98 or Windows XP. The Windows XP machines are new, and the clients claim the problem started when we moved some of the machines to XP. They also claim the problem went away when they unplugged their XP machines and went back to Windows 98. They recently went back to their XP machines, after we were able to help the problem somewhat by optimizing the form.
- When we created the exact same form and subform from scratch, the problem dropped in frequency. That is, when we redid the form, the same lag occurred, but in every 40 records in our controlled test, rather than in every 20 records.
- The form (which is bound to a query based on a table called tblCountForm, linked to tblStore) has two subforms.
- One subform is a datasheet form (also based on tblCountForm) lists all other records in tblCountForm for the current store.
- The other subform is a single form that shows information for the currently selected shopper (which is not necessarily the same as the shopper ID in tblCountForm, hence we cannot just have shopper info included in the query).
Removing the former subform appears to have no effect on the problem. Removing the latter subform, however, seems to make the problem go away.
- The tblCountForm table has approx. 65000 records and 58 fields.. ugh.. I did not design this database! :-( Some of the fields are poorly named (There's one, for example, called "Update" All the ID fields are named "<something> #"
. Changing the field names at this point is probably out of the question.- The tblStores table has approx. 11000 records and 27 fields.
- The tblShoppers table (which is the basis for the subform that appears to be causing the problem) has approx. 40000 records and 96 (!!) fields. However, the subform is based on a query that is based on that one table, and the query only returns about 15 of those fields.
- The main form does have some code in the OnCurrent event, but removing the code doesn't appear to make a difference. The subform with the problem does not have any OnCurrent code.
- The problem does not appear to be related in any way to workstation memory. That is, the problem is exactly the same regardless of how many applications are running.
- The problem does not appear to be related to the data. That is, it occures every 20 records, regardless of which record you are on, and which one you are going to. There often isn't much of a discrepency between the ID numbers in either the main form or the problem subform.
- The problem seems to lessen when we scroll through the records more slowly. That is, if we scroll through the records quickly, the lag at record 20 is pronounced (sometimes as long as 40 seconds). When we wait a couple seconds between scrolling, the length of the lag seems to drop, although there is still lag.
- During less controlled (but more real) experiments, where more than one workstation is accessing the database at a time, the lag seems to be entirely random, instead of every 20 or 21 records.
- The database on the SQL Server is 1.2 GB large, according to Enterprise Manager. It is 25 months old. When we looked at the files on the hard drive, the LDF file was 10.9 MB and the MDF file was 1.15 GB.
- The SQL Server is a Dual Pentium 900 machine, with 256 MB RAM.
- When we looked at the performance indicators in the SQL Server Task Manager (in a controlled experiment using one of the Win XP workstations), the following occurred:
- When the workstation opened the form, the CPU usage spiked, but only to about 40%, from nothing.
- Each time the workstation scrolled to a new record, the CPU usage spiked, very very slightly.
- ..until, that is, the 20th record, when the lag occurred. When the lag started, the CPU usage dropped to almost nothing. Then, several seconds later, it spiked back up to about 45%, at which point the lag at the workstation dropped.
- The memory usage on the server remained constant throughout.
- The database also (in a most likely unrelated occurrance), has fairly frequent problems of their ODBC connections getting disconnected, usually when somebody else on the network is synchronizing the SQL server with another one in Georgia. Not sure if this is to be expected or not.
- The entire network has an RT45 Ethernet structure.
All this (particularly the CPU usage on the server thing) sounds to me like a networking problem caused by one or more of the new XP machines. But then why would the problem go away when we got rid of a particular subform? And why, in controlled experiments, does the problem occur on every 20th record (or every 40th with the new form), but occur on completely random records when more than one workstation is working? Are we even on the right track?
Very frustrating.. any ideas, suggestions, welcome. TIA! Katie
![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
![[pc]](/data/assets/smilies/pc.gif "[pc] [pc]")
![[americanflag]](/data/assets/smilies/americanflag.gif "[americanflag] [americanflag]")
