Access 2007 Performance Issues
Hello experts. I have a 7-user application which slows down significantly when more than one user logs in. It is a FE/BE set-up. All users log into a single server via Remote Desktop. The BE and FE are on the server. A batch file copies the FE with the user's network login appended to the filename -- so all users are working in their own FE. I have done the following: turned Name Autocorrect off, I set sub-datasheets to None in all the BE tables, I have insured that there is no looping code anywhere, etc.
Their IT group is evaluating the server's hardware performance right now -- checking logs, etc.
The application has one continuous-forms form which is tabbed -- where the tabs filters the view based on the tab - and at any given time a view only has up to 50 records or so -- the underlying recordsource contains 2 linked / related tables with approximately 1000 records.
When 1 user is logged in, this form flies. As each subsequent user logs in, performance degrades significantly. No more than 3 users are in this form at a time ---
I am at a loss as to what to check next. My client has assumed it's the application, but I think it may be environmental.
I intend to go in there Monday and move the FE and BE to a local LAN to test the application's performance on that platform.
If it's a design flaw, I can live with that. I have not been billing for my time over the past couple of weeks because if it is the application, then I need to fix it -- and eat the cost. If it winds up being environmental, then I'll need to recoup some of my time in terms of billing.
I'm looking for some advice and direction so I can be prepared if the LAN scenario doesn't improve things.
Thanks for any suggestions.
Best, Eileen
Their IT group is evaluating the server's hardware performance right now -- checking logs, etc.
The application has one continuous-forms form which is tabbed -- where the tabs filters the view based on the tab - and at any given time a view only has up to 50 records or so -- the underlying recordsource contains 2 linked / related tables with approximately 1000 records.
When 1 user is logged in, this form flies. As each subsequent user logs in, performance degrades significantly. No more than 3 users are in this form at a time ---
I am at a loss as to what to check next. My client has assumed it's the application, but I think it may be environmental.
I intend to go in there Monday and move the FE and BE to a local LAN to test the application's performance on that platform.
If it's a design flaw, I can live with that. I have not been billing for my time over the past couple of weeks because if it is the application, then I need to fix it -- and eat the cost. If it winds up being environmental, then I'll need to recoup some of my time in terms of billing.
I'm looking for some advice and direction so I can be prepared if the LAN scenario doesn't improve things.
Thanks for any suggestions.
Best, Eileen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Clean indexes - no duplicates... all the fields used in common searches indexed....
The Main Table is linked by its autonumber to it's child's related number field. One-to-many. Such a simple structure.
I've developed far more sophisticated apps with many more users and have not experienced this.
It seems that the continuous forms form is the culprit --- but it shouldn't be. I'd use a temporary table if the data wasn't being updated continuously by 2 or 3 people all day long ----
The Main Table is linked by its autonumber to it's child's related number field. One-to-many. Such a simple structure.
I've developed far more sophisticated apps with many more users and have not experienced this.
It seems that the continuous forms form is the culprit --- but it shouldn't be. I'd use a temporary table if the data wasn't being updated continuously by 2 or 3 people all day long ----
How large is the table that the continuous form is based on?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It is 2008.64-bit --- I sent the links to their IT guys -- who sort of blew me off -- minimized them because the server has so much memory....
255 records - a query filtering only active records. The actual table has 20,000 records in it...
255 records - a query filtering only active records. The actual table has 20,000 records in it...
ASKER
I had individual queries based on the user's tab selection, but when I posted this question last week was told it was better to not change the recordsources on the fly -- rather just filter the form on the fly ----
Neither way seemed to make much of a difference.
Neither way seemed to make much of a difference.
How are tables from BE to FE linked? If it is an RDP envoronment, you should use local path, not network.
Access 2010 have 64 bit version. Can you try to use 64-bit Access 2010 runtime?
Access 2010 have 64 bit version. Can you try to use 64-bit Access 2010 runtime?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I am a member of the IT company that is handling this issue.
To answer any environmental issues. It is Windows Server 2008 Service Pack 2, X64 running on a dedicated HP server. It has 18GB of memory, dual multi core processors and the disk RAID is optimized by running a RAID 10 on 15K RPM SAS disks. We have opened a case with Microsoft to review the event logs of the server and monitor the performance of the server. It appears to us and to Microsoft that the issue is related to high disk usage and high cache usage. We have enabled Microsoft's Dynamic Cache and that made almost no difference. We have opened a support case with HP to investigate any possible issues with the RAID controller / Hard disk drives, and other than a firmware update they have not seen anything wrong. The interesting portion is that when the Access users see a slow down, everybody in the environment sees it (as if it is impacting the entire Terminal server) However, we can have up to 20 users on the server and if nobody is using Access, it appears that the servers performance is exactly what you would expect.
To answer any environmental issues. It is Windows Server 2008 Service Pack 2, X64 running on a dedicated HP server. It has 18GB of memory, dual multi core processors and the disk RAID is optimized by running a RAID 10 on 15K RPM SAS disks. We have opened a case with Microsoft to review the event logs of the server and monitor the performance of the server. It appears to us and to Microsoft that the issue is related to high disk usage and high cache usage. We have enabled Microsoft's Dynamic Cache and that made almost no difference. We have opened a support case with HP to investigate any possible issues with the RAID controller / Hard disk drives, and other than a firmware update they have not seen anything wrong. The interesting portion is that when the Access users see a slow down, everybody in the environment sees it (as if it is impacting the entire Terminal server) However, we can have up to 20 users on the server and if nobody is using Access, it appears that the servers performance is exactly what you would expect.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<However, we can have up to 20 users on the server and if nobody is using Access, it appears that the servers performance is exactly what you would expect.>>
Sounds like something then with JET's page cache and I would not be surprised that it deals with the issues that were in the comment of the thread I posted.
I would point out that comment to them and see if they can get a dump out of JET to check if it is indeed the same problem or not.
JimD.
Sounds like something then with JET's page cache and I would not be surprised that it deals with the issues that were in the comment of the thread I posted.
I would point out that comment to them and see if they can get a dump out of JET to check if it is indeed the same problem or not.
JimD.
ASKER
Still working on it - we moved to another server and am migrating the back-end to SQL Server... don't know what else to do.
Thanks all!!!
Thanks all!!!
ASKER