habidat
asked on
Weird multi-user Access performance problem
(I'm sorry this post is so long, but I wanted to be sure to mention the sorts of things that get asked in follow-up posts, so as not to waste anyone's time)
I have a perplexing performance problem in a multi-user Access application that has me stumped. I have done exhaustive research on this topic, and I believe all the usual "best practices" from the many previous posts on this topic (especially Tony's tips at granite.ab.ca) were already implemented and have been double-checked recently. I've been developing and supporting multi-user Access applications for many years, and I have never seen anything like this.
Here's the setup:
An unsecured Access 2000 MDE (split FE/BE), developed in Access 2000 and deployed as an Access 2000 MDE on each client PC; it is decompiled, compacted and repaired prior to each release. The app maintains a persistent connection the BE. Name autocorrect is off, record-level locking is off, default open mode is shared. In modules and classes, ADO is used extensively, DAO 3.6 only rarely. All ADO connections use the CurrentProject.Connection object. The data structure is highly normalized with approximately 65 tables all participating in RI. Subdatasheets are off in the BE. The application is well-indexed, and is not a heavy data entry application (probably less than 100 records a day) - mostly users browse data, make periodic edits and inserts, and run reports. All forms are bound, but none are linked directly to the full table - every form opens only after the user has requested a specific record or small set of records.
This app is currently installed at 13 different organizations, most of whom use XP Pro and Access 2000 or Access 2003, with the BE on a LAN shared drive. Most sites have between 5 and15 simultaneous users. All sites except ONE have perfectly acceptable performance running the app all day long, and have had for years.
The problem is that at only one client organization, the application launches and runs with acceptable performance in a multi-user setting for between 10 and 30 minutes, after which point the performance becomes "unacceptable" for at least one user, and eventually for all users, until they exit and re-launch the app. ("Fast" is seeing the primary forms opening in 1-3 seconds, and "slow" is between 6 and 10 seconds.)
The "problem" site has 4 users with new PCs with 1 gig RAM, all running Windows XP Pro SP2 with Office 2003 11.6566.8122 SP 2, msjet40.dll version 4.0.8618.0. The data file is on a shared drive on a DL 370 server with 2 gig of RAM. The data file is named in 8.3 format (QB891176) and is located just one level down the directory tree (e.g. R\AppName\AppData.mdb). All users have R/W/D permission on that folder. The IT staff at the client site has added the registry key for "shared file access delay" (QB150384). We are not seeing any data corruption, nor are we getting any locking conflicts. IT staff has said that no other major processes are running on this server.
To check for any hidden corruption in the data file, we created a new empty database, imported all the tables, and re-linked. The BE data file is approximately 280 meg and is regularly compacted and repaired. This client's data file is larger than any other organization using the app, but not by that much.
When multiple user are running the app and one has slow performance, the other users are not instantly affected; it may be another 10 or 15 minutes before any of the other users experience the slowdown. When users exit Access and restart, they have acceptable performance again, until the next slowdown occurs. We saw a user regain performance by just exiting the MDE and staying in Access, then re-opening the MDE, but we have not extensively tested this scenario. We have not asked users to stay in the “slow” mode indefinitely to see if they spontaneously regain performance.
This organization has a large physical plant with an extensive network. In order to rule out network infrastructure as a factor, the IT staff created a discrete LAN by moving the current server to the office where the users are located and hooking them up to the server directly with a switch, but we still saw the same performance drag after 10-15 minutes.
I am completely stumped - if there is a serious problem in how my application is designed or coded , I know I would be hearing complaints from other organizations, but we've checked, and the other 12 sites are seeing acceptable performance, so logically, it seems like it must be something site-specific, or a problem in my app that is only apparent here because of some site-specific setting or condition.
The fact that performance goes back up (temporarily) after users exit makes me suspect some sort of resource is being used up and then released, but how can I determine what it would be specific to this site? Access memory usage in Task Manager starts out at about 30,000k and is at only about 50,000k when the user experiences the slowdown, so it doesn't seem like it is specifically Access that is using up resources. I saw some posts in 2002 about a memory leak with Jet, but it looked like it had been fixed in recent SPs.
I believe that every ADO and DAO object that I open is closed and set to nothing, of course it is possible that somewhere in the many, many lines of code I've missed a few, but wouldn't I be seeing the same performance problem at other sites?
I don't think it is related the absolute size of the BE because when the app is fast, it's very fast. We also transferred a scrambled version to another client site and ran it there and performance for two users was fine (although we couldn't really take up their time to do extensive testing). I'm going to take it to a colleague's office and try testing on his server (I get very good performance using their data on my peer-to-peer setup).
We are not seeing any corruption, so I haven't asked the IT people to mess around with OpsLocks, but maybe I should try that next?
This client is running enterprise-wide Symantec real-time virus scan - could this be a factor? Does anyone know what sort of settings I should be looking at or directing the IT people to look at?
I have been collecting user-specific data for 2 days (my central error handler has been writing time in and out of every function) and I will be receiving those files Monday for analysis, but I'm not sure what I should be looking for.
A million thanks in advance for any advice.
- Ann
I have a perplexing performance problem in a multi-user Access application that has me stumped. I have done exhaustive research on this topic, and I believe all the usual "best practices" from the many previous posts on this topic (especially Tony's tips at granite.ab.ca) were already implemented and have been double-checked recently. I've been developing and supporting multi-user Access applications for many years, and I have never seen anything like this.
Here's the setup:
An unsecured Access 2000 MDE (split FE/BE), developed in Access 2000 and deployed as an Access 2000 MDE on each client PC; it is decompiled, compacted and repaired prior to each release. The app maintains a persistent connection the BE. Name autocorrect is off, record-level locking is off, default open mode is shared. In modules and classes, ADO is used extensively, DAO 3.6 only rarely. All ADO connections use the CurrentProject.Connection object. The data structure is highly normalized with approximately 65 tables all participating in RI. Subdatasheets are off in the BE. The application is well-indexed, and is not a heavy data entry application (probably less than 100 records a day) - mostly users browse data, make periodic edits and inserts, and run reports. All forms are bound, but none are linked directly to the full table - every form opens only after the user has requested a specific record or small set of records.
This app is currently installed at 13 different organizations, most of whom use XP Pro and Access 2000 or Access 2003, with the BE on a LAN shared drive. Most sites have between 5 and15 simultaneous users. All sites except ONE have perfectly acceptable performance running the app all day long, and have had for years.
The problem is that at only one client organization, the application launches and runs with acceptable performance in a multi-user setting for between 10 and 30 minutes, after which point the performance becomes "unacceptable" for at least one user, and eventually for all users, until they exit and re-launch the app. ("Fast" is seeing the primary forms opening in 1-3 seconds, and "slow" is between 6 and 10 seconds.)
The "problem" site has 4 users with new PCs with 1 gig RAM, all running Windows XP Pro SP2 with Office 2003 11.6566.8122 SP 2, msjet40.dll version 4.0.8618.0. The data file is on a shared drive on a DL 370 server with 2 gig of RAM. The data file is named in 8.3 format (QB891176) and is located just one level down the directory tree (e.g. R\AppName\AppData.mdb). All users have R/W/D permission on that folder. The IT staff at the client site has added the registry key for "shared file access delay" (QB150384). We are not seeing any data corruption, nor are we getting any locking conflicts. IT staff has said that no other major processes are running on this server.
To check for any hidden corruption in the data file, we created a new empty database, imported all the tables, and re-linked. The BE data file is approximately 280 meg and is regularly compacted and repaired. This client's data file is larger than any other organization using the app, but not by that much.
When multiple user are running the app and one has slow performance, the other users are not instantly affected; it may be another 10 or 15 minutes before any of the other users experience the slowdown. When users exit Access and restart, they have acceptable performance again, until the next slowdown occurs. We saw a user regain performance by just exiting the MDE and staying in Access, then re-opening the MDE, but we have not extensively tested this scenario. We have not asked users to stay in the “slow” mode indefinitely to see if they spontaneously regain performance.
This organization has a large physical plant with an extensive network. In order to rule out network infrastructure as a factor, the IT staff created a discrete LAN by moving the current server to the office where the users are located and hooking them up to the server directly with a switch, but we still saw the same performance drag after 10-15 minutes.
I am completely stumped - if there is a serious problem in how my application is designed or coded , I know I would be hearing complaints from other organizations, but we've checked, and the other 12 sites are seeing acceptable performance, so logically, it seems like it must be something site-specific, or a problem in my app that is only apparent here because of some site-specific setting or condition.
The fact that performance goes back up (temporarily) after users exit makes me suspect some sort of resource is being used up and then released, but how can I determine what it would be specific to this site? Access memory usage in Task Manager starts out at about 30,000k and is at only about 50,000k when the user experiences the slowdown, so it doesn't seem like it is specifically Access that is using up resources. I saw some posts in 2002 about a memory leak with Jet, but it looked like it had been fixed in recent SPs.
I believe that every ADO and DAO object that I open is closed and set to nothing, of course it is possible that somewhere in the many, many lines of code I've missed a few, but wouldn't I be seeing the same performance problem at other sites?
I don't think it is related the absolute size of the BE because when the app is fast, it's very fast. We also transferred a scrambled version to another client site and ran it there and performance for two users was fine (although we couldn't really take up their time to do extensive testing). I'm going to take it to a colleague's office and try testing on his server (I get very good performance using their data on my peer-to-peer setup).
We are not seeing any corruption, so I haven't asked the IT people to mess around with OpsLocks, but maybe I should try that next?
This client is running enterprise-wide Symantec real-time virus scan - could this be a factor? Does anyone know what sort of settings I should be looking at or directing the IT people to look at?
I have been collecting user-specific data for 2 days (my central error handler has been writing time in and out of every function) and I will be receiving those files Monday for analysis, but I'm not sure what I should be looking for.
A million thanks in advance for any advice.
- Ann
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The weekend's on...Tomorrow night is green beer, bagpipes and St Pat hats all around. Tonight...we sleep. )
J
J
Tonight YOU sleep ... gee ... that must be nice. Ok ... I must read this question ... .... ... ...
This may seem very weird at first but I suggest giving it a try.
I have had occasions where apps I have written work great everywhere except on a clients with exhaustively large networks. Though it defies all conventional wisdom I have found on occasion that placing the front ends in the same directory where the backend resides and installing a shortcut on my users machine to run front and back end from the server rather than running the front end from the workstation causes the application's performance problems to go away.
I have yet to figure out the reason why this works. I only know that this trick works occasionally and the common denominator is the size of the clients network - it's always on networks that are quite large.
That said it's an easy thing to try and I'd be interested to know if it works for you. If not I'll see if I can think of any other reason your app may run slow.
I have had occasions where apps I have written work great everywhere except on a clients with exhaustively large networks. Though it defies all conventional wisdom I have found on occasion that placing the front ends in the same directory where the backend resides and installing a shortcut on my users machine to run front and back end from the server rather than running the front end from the workstation causes the application's performance problems to go away.
I have yet to figure out the reason why this works. I only know that this trick works occasionally and the common denominator is the size of the clients network - it's always on networks that are quite large.
That said it's an easy thing to try and I'd be interested to know if it works for you. If not I'll see if I can think of any other reason your app may run slow.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks everyone for taking the time to read the long description (and for your kind comments)
Jeff - thanks for the Symantec link - I will pass it along to the client's IT folks. The word is that the antivirus system and settings are "nonnegotiable," but maybe we can get approval to tweak some settings to see if it affects the problem. I'll also ask some of our other clients what AV they are using - maybe I can find a commonality there to pursue.
Rick - I also have defied common wisdom and have a suite of 8 apps running at one client (a refinery with an enormous and very fast network), with the FEs all on the server with the BEs, there are nearly 100 registered users and frequently 20 or so users in the various apps at one time. This was a requirement from the client's IT people, and I really resisted because I was Andsure we'd see problems. Those apps are really snappy, and in the last few years we have had only one instance of corruption in one record in one table. It's crazy - it shouldn't work, but it does.
So I'll give this a try here at this site, but it seems like we ruled out the infrastructure as a factor by setting up a tiny LAN discionnected from the main network yesterday.
DBMX - thank for your suggestions - I had read the the MS link on long folder and file names, and we renamed our BE at this site from 11.3 to 8.3, with no discernable effect. The data file was already only one level down the tree in a folder with a short name. The IT people did turn off the sharing violation notification delay on the server and we saw an immediate improvement in initial app startup time, but again, no discernable impact on the eventual slowdown.
I haven't tried the advanced filename caching settings on the client, but since we no longer have a long file name, will that make a difference? No harm in trying it I guess.
The DisableFlushOnCleanup setting I must have missed the first time through. I am appending records in queries from a couple of dialogue entry forms (my home-made version of insert triggers), so I'll check this setting out too (but for what it's worth, I don't thnk the users have been performing these actions in the last few days). The caching and flushing caught my attention because that seems to be what's happening here - it's like some sort of bucket is filling up too fast.
And I'd be very interested in what you find out about your anomolous client performance too.
And now I'm off to download the Ultimate Troubleshooter!
Thanks everyone!
Jeff - thanks for the Symantec link - I will pass it along to the client's IT folks. The word is that the antivirus system and settings are "nonnegotiable," but maybe we can get approval to tweak some settings to see if it affects the problem. I'll also ask some of our other clients what AV they are using - maybe I can find a commonality there to pursue.
Rick - I also have defied common wisdom and have a suite of 8 apps running at one client (a refinery with an enormous and very fast network), with the FEs all on the server with the BEs, there are nearly 100 registered users and frequently 20 or so users in the various apps at one time. This was a requirement from the client's IT people, and I really resisted because I was Andsure we'd see problems. Those apps are really snappy, and in the last few years we have had only one instance of corruption in one record in one table. It's crazy - it shouldn't work, but it does.
So I'll give this a try here at this site, but it seems like we ruled out the infrastructure as a factor by setting up a tiny LAN discionnected from the main network yesterday.
DBMX - thank for your suggestions - I had read the the MS link on long folder and file names, and we renamed our BE at this site from 11.3 to 8.3, with no discernable effect. The data file was already only one level down the tree in a folder with a short name. The IT people did turn off the sharing violation notification delay on the server and we saw an immediate improvement in initial app startup time, but again, no discernable impact on the eventual slowdown.
I haven't tried the advanced filename caching settings on the client, but since we no longer have a long file name, will that make a difference? No harm in trying it I guess.
The DisableFlushOnCleanup setting I must have missed the first time through. I am appending records in queries from a couple of dialogue entry forms (my home-made version of insert triggers), so I'll check this setting out too (but for what it's worth, I don't thnk the users have been performing these actions in the last few days). The caching and flushing caught my attention because that seems to be what's happening here - it's like some sort of bucket is filling up too fast.
And I'd be very interested in what you find out about your anomolous client performance too.
And now I'm off to download the Ultimate Troubleshooter!
Thanks everyone!
" It's crazy - it shouldn't work, but it does."
Actually ... it's Not crazy, it should work and it does :-} Can 3 (or 4 - Jeff?) people here who know what they are talking about be wrong? :-} Don't worry ... the flack will come ... if anyone reads this post!
Even if the UT doesn't help you spot something in this case ... it WILL solve other issues in the future. And .. there is a button to email your processes to the to find out what 'unknowns' are ... and the update frequently. Very cool.
Yes ... I will keep you posted on my issue. One disadvantage I have .. if it comes to it ... is that I don't know C++ and can't 'look inside' to see why is going on. Fortunately ... a log file can be turned which jams out all the SQL.
ok .... post back as to your progress ... and best of luck. Thanks Jeff and Rick for helping too.
mx
Actually ... it's Not crazy, it should work and it does :-} Can 3 (or 4 - Jeff?) people here who know what they are talking about be wrong? :-} Don't worry ... the flack will come ... if anyone reads this post!
Even if the UT doesn't help you spot something in this case ... it WILL solve other issues in the future. And .. there is a button to email your processes to the to find out what 'unknowns' are ... and the update frequently. Very cool.
Yes ... I will keep you posted on my issue. One disadvantage I have .. if it comes to it ... is that I don't know C++ and can't 'look inside' to see why is going on. Fortunately ... a log file can be turned which jams out all the SQL.
ok .... post back as to your progress ... and best of luck. Thanks Jeff and Rick for helping too.
mx
Ann,
First, great detail on the question! But I do have questions of my own; What's acceptable vs unacceptable? You talk about it a lot, but are we talking seconds or a difference of minutes? Also is the performance slowdown across the board in the app? What type of app is this and is it large and complex or just a few tables and forms.
<<We are not seeing any corruption, so I haven't asked the IT people to mess around with OpsLocks, but maybe I should try that next?>>
Oplocks only help when the second user comes in after the first. After that, it doesn't matter.
As far as the FE/BE being on the same server, you might as well have not split the app. The whole point of splitting is so everyone has a seperate FE. You do make some things easier (like rolling out updates), but you are lossing some of the benefits.
I have two concerns; the heavy use of ADO and the fact that memory is growing. JET's cache is dynamic, but what you describe sounds like a memory leak.
<<The fact that performance goes back up (temporarily) after users exit makes me suspect some sort of resource is being used up and then released, but how can I determine what it would be specific to this site? Access memory usage in Task Manager starts out at about 30,000k and is at only about 50,000k when the user experiences the slowdown, so it doesn't seem like it is specifically Access that is using up resources. I saw some posts in 2002 about a memory leak with Jet, but it looked like it had been fixed in recent SPs.>>
Have one of your other clients check memory usage at the start and then 20 or 30 minutes latter. Do they see similar results?
JimD
First, great detail on the question! But I do have questions of my own; What's acceptable vs unacceptable? You talk about it a lot, but are we talking seconds or a difference of minutes? Also is the performance slowdown across the board in the app? What type of app is this and is it large and complex or just a few tables and forms.
<<We are not seeing any corruption, so I haven't asked the IT people to mess around with OpsLocks, but maybe I should try that next?>>
Oplocks only help when the second user comes in after the first. After that, it doesn't matter.
As far as the FE/BE being on the same server, you might as well have not split the app. The whole point of splitting is so everyone has a seperate FE. You do make some things easier (like rolling out updates), but you are lossing some of the benefits.
I have two concerns; the heavy use of ADO and the fact that memory is growing. JET's cache is dynamic, but what you describe sounds like a memory leak.
<<The fact that performance goes back up (temporarily) after users exit makes me suspect some sort of resource is being used up and then released, but how can I determine what it would be specific to this site? Access memory usage in Task Manager starts out at about 30,000k and is at only about 50,000k when the user experiences the slowdown, so it doesn't seem like it is specifically Access that is using up resources. I saw some posts in 2002 about a memory leak with Jet, but it looked like it had been fixed in recent SPs.>>
Have one of your other clients check memory usage at the start and then 20 or 30 minutes latter. Do they see similar results?
JimD
<<The problem is that at only one client organization, the application launches and runs with acceptable performance in a multi-user setting for between 10 and 30 minutes, after which point the performance becomes "unacceptable" for at least one user, and eventually for all users, until they exit and re-launch the app. ("Fast" is seeing the primary forms opening in 1-3 seconds, and "slow" is between 6 and 10 seconds.)>>
Sorry, missed this. Does the app do a large number of additions/deletions in the normal course of operations at this client compared to the others?
JimD
Sorry, missed this. Does the app do a large number of additions/deletions in the normal course of operations at this client compared to the others?
JimD
ASKER
JDettman - thanks for your comment - here are some responses:
The performance slowdown is apparently across the board, but most noticable to the users whern they open forms after selecting the record to work with (and this is how thay have been benchmarking back to us - e.g. "The traveler form is now taking 8 seconds to open instead of 2").
The app is used by 13 different organizations (so far) to manage back-office operations and marketing data analysis for large non-profit educational travel programs. The primary entities are travelers, tours, reservations and vendors. Each main entity is made up of a primary table and between 5 and 10 child tables, all related with RI. There are other minor entities with their related tables. I would say it is large and complex from the standpoint of functionality, UI-based analysis and summarization, and reporting, but it is not computationally complex (compared to engineering apps I have worked on), nor is there constant and extensive data entry. There are NOT a large number of additions and deletions at this client compared to the others; this client does have a lot of historical data, and a very large "traveler" base for marketing. However 2 or 3 other clients have many more concurrent users adding and modifying more records daily, with no performance issues.
The question of moving the FE to the server was a bit of a tangent from some earlier comments - I have thought about it and I'm not sure what I would learn from that excercise in this case, and it would muddy the water in some other ways that I don't hink would be helpful right now.
--> Update as of Monday:
I have added more logging capability to the app this client is using - I am logging each user in and out of the app centrally, and then on each client machine I am now tracking all the functions they go in and out of, with API ticks and the Now() function, and on every form_open event, I am logging the following memory stats: AvailablePageFile, AvailablePhysical , AvailableVirtual, MemoryLoad, TotalPageFile, TotalPhysical and TotalVirtual. I also added a hot-key for them to press when they experience the slowdown which grabs all the above-mentioned memory stats and writes it to a separate log. Then they can either keep working in slow mode or exit and restart. I think these logs will help - it's a little much to ask the users to be continually checking Task Manager - all they want to do it get their work done! I'll be collecting those logs sometime tomorrow.
Meanwhile my contact in IT is investigating whether he can configure the real-time virus scan to skip our local MDE and shared MDB files. I have high hopes for this.
I spent today going over virtually every line of code in my app to confirm that every DAO and ADO object that should be closed is being closed, and that all objects I set to something are set to nothing on exit. I use DAO rarely, and I did find a few DAO objects that weren't being closed, but they weren't in functions that are used very often. But the code is very tight now, and I'll test this version and release to them tomorrow or Wednesday, to see if it makes a difference.
Thanks everyone, for all your assistance. I'll post back when I have some data to look at.
The performance slowdown is apparently across the board, but most noticable to the users whern they open forms after selecting the record to work with (and this is how thay have been benchmarking back to us - e.g. "The traveler form is now taking 8 seconds to open instead of 2").
The app is used by 13 different organizations (so far) to manage back-office operations and marketing data analysis for large non-profit educational travel programs. The primary entities are travelers, tours, reservations and vendors. Each main entity is made up of a primary table and between 5 and 10 child tables, all related with RI. There are other minor entities with their related tables. I would say it is large and complex from the standpoint of functionality, UI-based analysis and summarization, and reporting, but it is not computationally complex (compared to engineering apps I have worked on), nor is there constant and extensive data entry. There are NOT a large number of additions and deletions at this client compared to the others; this client does have a lot of historical data, and a very large "traveler" base for marketing. However 2 or 3 other clients have many more concurrent users adding and modifying more records daily, with no performance issues.
The question of moving the FE to the server was a bit of a tangent from some earlier comments - I have thought about it and I'm not sure what I would learn from that excercise in this case, and it would muddy the water in some other ways that I don't hink would be helpful right now.
--> Update as of Monday:
I have added more logging capability to the app this client is using - I am logging each user in and out of the app centrally, and then on each client machine I am now tracking all the functions they go in and out of, with API ticks and the Now() function, and on every form_open event, I am logging the following memory stats: AvailablePageFile, AvailablePhysical , AvailableVirtual, MemoryLoad, TotalPageFile, TotalPhysical and TotalVirtual. I also added a hot-key for them to press when they experience the slowdown which grabs all the above-mentioned memory stats and writes it to a separate log. Then they can either keep working in slow mode or exit and restart. I think these logs will help - it's a little much to ask the users to be continually checking Task Manager - all they want to do it get their work done! I'll be collecting those logs sometime tomorrow.
Meanwhile my contact in IT is investigating whether he can configure the real-time virus scan to skip our local MDE and shared MDB files. I have high hopes for this.
I spent today going over virtually every line of code in my app to confirm that every DAO and ADO object that should be closed is being closed, and that all objects I set to something are set to nothing on exit. I use DAO rarely, and I did find a few DAO objects that weren't being closed, but they weren't in functions that are used very often. But the code is very tight now, and I'll test this version and release to them tomorrow or Wednesday, to see if it makes a difference.
Thanks everyone, for all your assistance. I'll post back when I have some data to look at.
Did you try the Ultimate TroubleShooter ?
Where are you located?
mx
Where are you located?
mx
ASKER
Hey mx -
Yes, thanks for the recommendation - I tried the Ultimate Troubleshooter on my PC, and it is pretty cool. I haven't tried it on the clients' PCs yet - I thnk I'll check the logs first and see what their memory load is.
I am located in San Francisco, and my "problem client" is in New York City.
Yes, thanks for the recommendation - I tried the Ultimate Troubleshooter on my PC, and it is pretty cool. I haven't tried it on the clients' PCs yet - I thnk I'll check the logs first and see what their memory load is.
I am located in San Francisco, and my "problem client" is in New York City.
Bet your PC is faster now :-}
I just used it for a friend over the weekend ... FOUR X faster bootup ... we timed it. Seriously. Talk about needless junk. Every frickin' program you install wants to start up some needless set of background tasks ... Roxio being one in particular. Insane.
I'm in LA and San Diego. Do you do consulting?
I have a couple more thoughts (long shots of course) ... but '24' is starting shortly and that has priority! Just enough time to do a short 20 min project for a client.
joe
I just used it for a friend over the weekend ... FOUR X faster bootup ... we timed it. Seriously. Talk about needless junk. Every frickin' program you install wants to start up some needless set of background tasks ... Roxio being one in particular. Insane.
I'm in LA and San Diego. Do you do consulting?
I have a couple more thoughts (long shots of course) ... but '24' is starting shortly and that has priority! Just enough time to do a short 20 min project for a client.
joe
Any news? Inquiring minds want to know :-)
mx
mx
ASKER
I'm still banging my head against the wall :(
I'll post Monday afternoon or eveing where we are with what has been tried - but here is a brief synopsis:
- turning off Symantec real-time virus scanning of MDB files on the server made no difference; we are waiting for the IT higher-ups to approve temporarily turning it off on the users local application folders.
-I've been logging all kids of stats, and now I have a ton of data to wade through - that's in progress.
- from what I can see so far from the logs, the slowdowns seem not to occur inside any of my procedures, but rather between them. I'm not sure what to make of this - either it is Access form and control processing that is bogging down, or somethng totally outside of Access. I can only log Access actions - I have no way of knowing or tracking what else is going on.
- when the users start my app, they are already running with a memory load of 50% or more. This seems high - my PCs all run with a load of about 10% when starting Access.
I'll follow-up with more detail tomorrow - thanks for your concern - I refuse to believe that this is an insoluble problem.
I'll post Monday afternoon or eveing where we are with what has been tried - but here is a brief synopsis:
- turning off Symantec real-time virus scanning of MDB files on the server made no difference; we are waiting for the IT higher-ups to approve temporarily turning it off on the users local application folders.
-I've been logging all kids of stats, and now I have a ton of data to wade through - that's in progress.
- from what I can see so far from the logs, the slowdowns seem not to occur inside any of my procedures, but rather between them. I'm not sure what to make of this - either it is Access form and control processing that is bogging down, or somethng totally outside of Access. I can only log Access actions - I have no way of knowing or tracking what else is going on.
- when the users start my app, they are already running with a memory load of 50% or more. This seems high - my PCs all run with a load of about 10% when starting Access.
I'll follow-up with more detail tomorrow - thanks for your concern - I refuse to believe that this is an insoluble problem.
I (we) will be waiting for tomorrow night ... almost like waiting for tomorrow night's episode of '24'.
Any luck with the Ultimate Troubleshooter ?
"turning off Symantec real-time virus scanning of MDB files"
I wonder what Symantec is actually looking for in an MDB?
Maybe this is solvable:
If your name is Ann (?) and that implies female (?) ... how come the icon for habidat is the male icon? Just curious?
mx
Any luck with the Ultimate Troubleshooter ?
"turning off Symantec real-time virus scanning of MDB files"
I wonder what Symantec is actually looking for in an MDB?
Maybe this is solvable:
If your name is Ann (?) and that implies female (?) ... how come the icon for habidat is the male icon? Just curious?
mx
ASKER
"Maybe this is solvable: If your name is Ann (?) and that implies female (?) ... how come the icon for habidat is the male icon? Just curious?"
Ha! Just too anxious to find a solution here - when I set up my profile I didn't even notice the Gender box. I am properly classified now.
Ha! Just too anxious to find a solution here - when I set up my profile I didn't even notice the Gender box. I am properly classified now.
LOL! A Boy Named Ann (not) ... ala Johnny Cash's a boy named Sue!
And now on to real issues :-)
joe.mx
And now on to real issues :-)
joe.mx
ASKER
Arrrgh! I am at my wit's end.
The IT folks turned off the local virus scanning for 15 minutes today and users reported the "same problem". I don't have the details of what "turning off" meant.
I am logging and graphing the memory load and page file use by tickcount and function call, and seeing very strange things, which I do not know how to interpret, much less how to present to you all.
I have made some minor modifications to the application to make some things faster, and the users will be using that version tomorrow (Wednesday). I noticed that most of the time lags were occuring between my function calls, not within them, so I tried to tune up the form behavior, so Access does less work. I honestly have no idea what the results will be, or what they will signify.
Anyone out there interested in some "live" brainstorming? We are ready to bring in some outside help to determine our next steps. My gut tells me we need a nitty-gritty Access/Jet/PC/Network conguration-type person. Anyone have any recommendations? I am in the SF Bay Area, the client is in NYC, and thanks to GoToMeeting, our savior can be anywhere...
I hop e to know more tomorrow afternoon after they have used the new "tuned" version. But I don't thnk it will make much difference, because the problem seems to be not that the app IS too slow, but that it BECOMES too slow, only for this particular client.
Well, time to go to sleep and dream of analyzing log files...
The IT folks turned off the local virus scanning for 15 minutes today and users reported the "same problem". I don't have the details of what "turning off" meant.
I am logging and graphing the memory load and page file use by tickcount and function call, and seeing very strange things, which I do not know how to interpret, much less how to present to you all.
I have made some minor modifications to the application to make some things faster, and the users will be using that version tomorrow (Wednesday). I noticed that most of the time lags were occuring between my function calls, not within them, so I tried to tune up the form behavior, so Access does less work. I honestly have no idea what the results will be, or what they will signify.
Anyone out there interested in some "live" brainstorming? We are ready to bring in some outside help to determine our next steps. My gut tells me we need a nitty-gritty Access/Jet/PC/Network conguration-type person. Anyone have any recommendations? I am in the SF Bay Area, the client is in NYC, and thanks to GoToMeeting, our savior can be anywhere...
I hop e to know more tomorrow afternoon after they have used the new "tuned" version. But I don't thnk it will make much difference, because the problem seems to be not that the app IS too slow, but that it BECOMES too slow, only for this particular client.
Well, time to go to sleep and dream of analyzing log files...
Hang tuff ... while I read the post ... no hair pulling now. I just watched 'House' ... and I ready to SOLVE a problem, dammit, lol ...
mx
mx
Ann, what exactly do you mean by:
"I noticed that most of the time lags were occuring between my function calls, not within them," ?
Also:
There has got to be some common element with the images (Office, et.al) that are on these systems at this client, that is causing these slowdowns ... over time. Like, at Edison where I'm at now (well, not RIGHT now) ... in the group I'm working with - ~ 80-100 people, pretty much everyone has the same hard drive 'image'. Again, just wondering if the UT program exposed anything ?
Well ... I'm outta here for the night myself ...
mx
"I noticed that most of the time lags were occuring between my function calls, not within them," ?
Also:
There has got to be some common element with the images (Office, et.al) that are on these systems at this client, that is causing these slowdowns ... over time. Like, at Edison where I'm at now (well, not RIGHT now) ... in the group I'm working with - ~ 80-100 people, pretty much everyone has the same hard drive 'image'. Again, just wondering if the UT program exposed anything ?
Well ... I'm outta here for the night myself ...
mx
ASKER
Hi Joe - here's what I meant:
I am logging the time in and out of every function called in my app, and I can determine how long each function takes to run. In most cases it is just a couple of tick counts. Sometimes I see long gaps between my function calls - of course some of this is expected - the user is reading data on the form, or deciding what to do next, or answering the phone. But sometimes is is within a longer procedure, so I know the lag is because Access or Windows is doing something.
e.g.:
1000 function1 In
1002 function2 In
1080 function2 Out
2300 function1 Out <--- what was happenng here before function2 ended?
Anyway, I have miles and miles of these logs that I am trying to analyze.
I am logging the time in and out of every function called in my app, and I can determine how long each function takes to run. In most cases it is just a couple of tick counts. Sometimes I see long gaps between my function calls - of course some of this is expected - the user is reading data on the form, or deciding what to do next, or answering the phone. But sometimes is is within a longer procedure, so I know the lag is because Access or Windows is doing something.
e.g.:
1000 function1 In
1002 function2 In
1080 function2 Out
2300 function1 Out <--- what was happenng here before function2 ended?
Anyway, I have miles and miles of these logs that I am trying to analyze.
ASKER
What I need is a hypothesis to test - but I don't know much, if anything, abut memory management, pages files and caching, etc.
What can cause multiple PCs sharing an Access BE database to become slower over time? (When other PCs using the same app in other locations o not experience the slowdown?)
Any ideas? We're ready to bring in anyone who feels like that have some ideas of how to identify the problem.
Here's what I'm planning today:
1. Use a recipe of actions and have multiple users here in my office simultaneously run through the steps (e.g. start at time marker 1: open form A, at time marker 2: find record, At time marker 3: do x,y,z, at time marker 4: go to form B, etc.) - log files will be generated.
2. Have the users in the problem site follow the same script
3. Ask some of our other nice clients at another site to do the same exercise.
Compare all the logs.
I'll report back.
What can cause multiple PCs sharing an Access BE database to become slower over time? (When other PCs using the same app in other locations o not experience the slowdown?)
Any ideas? We're ready to bring in anyone who feels like that have some ideas of how to identify the problem.
Here's what I'm planning today:
1. Use a recipe of actions and have multiple users here in my office simultaneously run through the steps (e.g. start at time marker 1: open form A, at time marker 2: find record, At time marker 3: do x,y,z, at time marker 4: go to form B, etc.) - log files will be generated.
2. Have the users in the problem site follow the same script
3. Ask some of our other nice clients at another site to do the same exercise.
Compare all the logs.
I'll report back.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes ... it reeks of a memory leak ... but ... on *every* user's system at that client ... man ... ??? I agree totally ... something environment specific on those machines.
Again ... I know I keep saying this ... but the Ultimate Troubleshoote has a 'Services' tab ... which shows - in much better detail than Task Manager ... the state of all services and their status, along with the Start Mode, Path ... and a description of what each service is and a recommendation as to whether it is 'necessary' to be running or not. Unfortunately, it does not display the real time % usage as does Task Manager. However, the two in conjunction may expose something.
mx
Again ... I know I keep saying this ... but the Ultimate Troubleshoote has a 'Services' tab ... which shows - in much better detail than Task Manager ... the state of all services and their status, along with the Start Mode, Path ... and a description of what each service is and a recommendation as to whether it is 'necessary' to be running or not. Unfortunately, it does not display the real time % usage as does Task Manager. However, the two in conjunction may expose something.
mx
ASKER
JimD -
Thanks for the idea of booting to safe mode - I hadn't thought of that. I think I'd have to have all 3 users do that though, because the slowdown does not occur unless more than one user is in the application. These users seem to have a pretty high memory load compared to my PCs.
I also learned that they generally do not reboot their PCs from day to day, so I'm asking them to reboot now and continue their work this afternoon to see if there is any difference.
Sometimes the problem makes me think it is PC-specific, but then the fact that it only happens when more than one user is in the app makes me think of the server. Maybe we have a couple of different problems going on (just to make it interesting!).
I did confirm that the application runs at perfectly acceptable speeds (as it does at all out other client sites) for one user alone indefintely, or for more than one user for 15 to 20 minutes, then gets very sluggish.
Thanks for the idea of booting to safe mode - I hadn't thought of that. I think I'd have to have all 3 users do that though, because the slowdown does not occur unless more than one user is in the application. These users seem to have a pretty high memory load compared to my PCs.
I also learned that they generally do not reboot their PCs from day to day, so I'm asking them to reboot now and continue their work this afternoon to see if there is any difference.
Sometimes the problem makes me think it is PC-specific, but then the fact that it only happens when more than one user is in the app makes me think of the server. Maybe we have a couple of different problems going on (just to make it interesting!).
I did confirm that the application runs at perfectly acceptable speeds (as it does at all out other client sites) for one user alone indefintely, or for more than one user for 15 to 20 minutes, then gets very sluggish.
ASKER
MX (and anyone else)
I have an Excel sheet listing the services, running modules and startup programs for one of the users (from MSInfo.exe) but it doesn't look like I can load attachments on this forum. I'd be happy to email it to anyone who thinks it would be useful.
I have an Excel sheet listing the services, running modules and startup programs for one of the users (from MSInfo.exe) but it doesn't look like I can load attachments on this forum. I'd be happy to email it to anyone who thinks it would be useful.
In fact, you can upload the file to:
http://www.ee-stuff.com/Expert/Upload/upload.php
And please do ...
mx
http://www.ee-stuff.com/Expert/Upload/upload.php
And please do ...
mx
ASKER
Any news Ann ?
mx
mx
ASKER
Well, the problem has been solved (hurray!), and I've been awaiting exact details so I can award points here, but here's the "advance press":
Last week someone in the IT department reinstalled the Symantec antivirus software on the desktop computers in question and after some delay (not sure if everyone rebooted right away, or perhaps other changes needed to be made elsewhere in the system?), the app started working "fast" and has stayed "fast" ever since, even with all four users in at once.
So, we are thrilled, naturally, that the users can now experience the application they way everyone else does, and the users are exetremely happy too.
I have asked my IT contact there for some more detailed information about what he did - exact Symantec product name and previous and current versions, any AV configuration changes or settings, did he install the "unadvertized" patch that JeffWilley suggested, etc. but the re-install definitely did the trick, and the local installation of the AV software was the culprit the entire time.
It is a little frustrating to have spent so much time troubleshooting and analyzing within Access and my app, when all along the problem was completely outside my area of influence, but I certainly learned a great deal about Jet performance and query and form opimization, and I have a lot of confidence in the performance and tuning of my app; there were just a few places where I could make changes that affected performance, and in fact none of these changes were noticable by these users - they were all sucked up by the AV drag. I also some nifty memory and ISAM stats logging code to use if I ever do need to track down performance problems with this or any other app, so that's good.
My main take-away lesson -- with a well-tuned app working as designed in other locations: check the AV stuff first! I just didn't feel like I could push so hard in a large organization that they reinstall their AV stuff, but I won't hesitate again if I see similar behavior. I could have saved myself (and the client's IT and user staff) a good deal of wasted time.
All's well that ends well - I will post the details back here as soon as I get them, and thanks to of you for your help - I suspect the points will go to JeffWilley, but I am waiting for final details from my client's IT staff.
And MX - thanks again for the Ultimate Troubleshooter recommendation!
Last week someone in the IT department reinstalled the Symantec antivirus software on the desktop computers in question and after some delay (not sure if everyone rebooted right away, or perhaps other changes needed to be made elsewhere in the system?), the app started working "fast" and has stayed "fast" ever since, even with all four users in at once.
So, we are thrilled, naturally, that the users can now experience the application they way everyone else does, and the users are exetremely happy too.
I have asked my IT contact there for some more detailed information about what he did - exact Symantec product name and previous and current versions, any AV configuration changes or settings, did he install the "unadvertized" patch that JeffWilley suggested, etc. but the re-install definitely did the trick, and the local installation of the AV software was the culprit the entire time.
It is a little frustrating to have spent so much time troubleshooting and analyzing within Access and my app, when all along the problem was completely outside my area of influence, but I certainly learned a great deal about Jet performance and query and form opimization, and I have a lot of confidence in the performance and tuning of my app; there were just a few places where I could make changes that affected performance, and in fact none of these changes were noticable by these users - they were all sucked up by the AV drag. I also some nifty memory and ISAM stats logging code to use if I ever do need to track down performance problems with this or any other app, so that's good.
My main take-away lesson -- with a well-tuned app working as designed in other locations: check the AV stuff first! I just didn't feel like I could push so hard in a large organization that they reinstall their AV stuff, but I won't hesitate again if I see similar behavior. I could have saved myself (and the client's IT and user staff) a good deal of wasted time.
All's well that ends well - I will post the details back here as soon as I get them, and thanks to of you for your help - I suspect the points will go to JeffWilley, but I am waiting for final details from my client's IT staff.
And MX - thanks again for the Ultimate Troubleshooter recommendation!
I would say a split is in order with everyone ... since all contributed reasonable assertions :-)
mx
mx
FYI, I just ran into something the other day; a conflict between Trend Micro and Sybot Search and Destroy. A feature in each product was actually causing a problem in the other. Made Trend suck a lot of CPU time, althought this was not obvious from task manager. After disabling the Immunize function in SB, Trend went back to being lean and mean.
Just shows you how complex things are getting under the hood.
Glad to hear that your at the end of a long road!
JimD
Just shows you how complex things are getting under the hood.
Glad to hear that your at the end of a long road!
JimD
ASKER
I'd be happy to split points! I've never posted a question here, so so I don't want to inadvertantly violate any etiquette - and I do appreciate everyone's diligence, courtesy, expertise, and willingness to help. What's the protocol in a case like this?
Well, my suggestion - for this very specific posting/situation/ etc ... would be to split the points more or less evenly, maybe a tad few extra to Jeff ... since he suggested the AV issue.
Jeff? JD? Rick? What do you think?
mx
Jeff? JD? Rick? What do you think?
mx
ASKER
OK - I'll split the points out more or less evenly, with more to Jeff.
And for the record, the version of Symantec AV was 9.0.3.1000 (according to the version for the "running modules" from msinfo on a user PC) before the reinstall and now it is 10.1.5.5000. There are other versions for other Symantec modules, but these are the main ones, as far as I can tell, and every AV component's version number went up.
My IT contact is still perplexed because he says that after uninstalling and reinstalling the AV, the users still experienced slowdowns and it wasn't until the next day that they saw the sustained fast performance. All I can think of is that there was:
(a) some sort of trickle-up effect of the AV reinstall in integration with other AV activities (e.g. mail server activites) that didn't take effect until some other service or module elsewhere was stopped and restarted; or
(b) what the users reported as a slowdown was in fact a task in the application that does take a little while - these users have a couple very heavy reports (many subreports, lots of child-data concatenation, etc.) that can take 30 seconds or so to format and display; or
(c) unbeknownst to my IT contact, someone else somewhere changed some other settings (network AV settings, or something else).
In any case, all is well there now, and between the slow performance and the fast (normal) performance, the only change anyone knows of was the desktop reinstall of the AV.
Phew!
A million thanks (and 500 points) for all of you!
(And tomorrow, I'm off for a well-deserved week's vacation in London! (Unfortunately not a "well-earned" vacation, since this entire performance effort was unpaid :( )
And for the record, the version of Symantec AV was 9.0.3.1000 (according to the version for the "running modules" from msinfo on a user PC) before the reinstall and now it is 10.1.5.5000. There are other versions for other Symantec modules, but these are the main ones, as far as I can tell, and every AV component's version number went up.
My IT contact is still perplexed because he says that after uninstalling and reinstalling the AV, the users still experienced slowdowns and it wasn't until the next day that they saw the sustained fast performance. All I can think of is that there was:
(a) some sort of trickle-up effect of the AV reinstall in integration with other AV activities (e.g. mail server activites) that didn't take effect until some other service or module elsewhere was stopped and restarted; or
(b) what the users reported as a slowdown was in fact a task in the application that does take a little while - these users have a couple very heavy reports (many subreports, lots of child-data concatenation, etc.) that can take 30 seconds or so to format and display; or
(c) unbeknownst to my IT contact, someone else somewhere changed some other settings (network AV settings, or something else).
In any case, all is well there now, and between the slow performance and the fast (normal) performance, the only change anyone knows of was the desktop reinstall of the AV.
Phew!
A million thanks (and 500 points) for all of you!
(And tomorrow, I'm off for a well-deserved week's vacation in London! (Unfortunately not a "well-earned" vacation, since this entire performance effort was unpaid :( )
<<(Unfortunately not a "well-earned" vacation, since this entire performance effort was unpaid :( )
Admin Edit Accept Multiple Solutions Accept as Solution >>
What? Your not sending them a bill? Although you *do* need to be carefull, if it was the app, you might be getting a bill for all the lost productivitey<g>.
At least you have "good will" banked up with them now and probably a lot more business in the future.
JimD
Admin Edit Accept Multiple Solutions Accept as Solution >>
What? Your not sending them a bill? Although you *do* need to be carefull, if it was the app, you might be getting a bill for all the lost productivitey<g>.
At least you have "good will" banked up with them now and probably a lot more business in the future.
JimD
Most excellent Ann.
This thread is 'logged and noted' and should be REQUIRED reading for anyone about to (or has) undertake a multi-user app.
Oh ... no more symantec for me .... www.secureresolutions.com ... for the last 1.5 years. Once you get the drift of how this works ... and the control YOU have via the web-based interface ... there is no going back to 'norton' or mcafee!
Sorry I can't join you in London :-(
mx
This thread is 'logged and noted' and should be REQUIRED reading for anyone about to (or has) undertake a multi-user app.
Oh ... no more symantec for me .... www.secureresolutions.com ... for the last 1.5 years. Once you get the drift of how this works ... and the control YOU have via the web-based interface ... there is no going back to 'norton' or mcafee!
Sorry I can't join you in London :-(
mx
ASKER
Well, we have asked to be reimbursed for some of our time, because from the get-go we said we didn't think it was our app, given the dozen happy sites out there using it with no performance issue. But it won't come near to the days and days of time I spent reading, researching, testing, etc. (not to mention the sleepless nights!)
But the clients are thrilled now, and useful experience and knowledge are invaluable, so it was worth it. On the plane tomorrow, I plan to write up a detailed performance trouble-shooting flowchart (Step 1 - check AV!), and, whenever we bring a new client on board, we are going to do performance testing on their network with a big scrambled sample dataset before going live, so we don't end up going through even an abbreviated version of this process again.
Oh, I've been using Grisoft's AVG for years and I love it!
I'll raise a pint to you all in London! Cheers!
But the clients are thrilled now, and useful experience and knowledge are invaluable, so it was worth it. On the plane tomorrow, I plan to write up a detailed performance trouble-shooting flowchart (Step 1 - check AV!), and, whenever we bring a new client on board, we are going to do performance testing on their network with a big scrambled sample dataset before going live, so we don't end up going through even an abbreviated version of this process again.
Oh, I've been using Grisoft's AVG for years and I love it!
I'll raise a pint to you all in London! Cheers!
mx