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
Eileen MurphyIndependent Application DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Indexing is a big part of this - are you sure you've properly indexed the backend tables?

Possible candidates for indexing include:

Fields used in a JOIN or WHERE clause
Fields used in SORTING or ORDER BY
Fields where you might SEARCH

Also:

Don't index fields that have large amounts of repeating data. It would be useless to index a "Gender" field, for example

Don't "over index" - Access tends to add indexes to your tables, and can sometimes duplicate those indexes (especially with Autonumber fields). You only need to index a field one time.

Indexing can slow down inserts/updates, but with modern machines that's generally not a problem.

=========================

Do you have a large number of subforms? These can cause significant performance implications.

=========================

If you run the application in your office over a LAN, how is the performance?




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eileen MurphyIndependent Application DeveloperAuthor Commented:
Have tested it in my office on my LAN and wirelessly - smoking fast. I will go through all of my tables again to check the indexes --- I'm pretty sure I did that already, but it can't hurt to check again.
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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 ----
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
How large is the table that the continuous form is based on?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also: What's the server environment? Server 2008, 64-bit perhaps? If so, there are reported performance issues on that platform (just google for the keywords and you'll see quite a bit of them).
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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...
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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.
0
als315Commented:
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?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Has IT installed the Service Packs for Office 2007?

So that we're clear: The batch file simply copies a new FE to the RD Desktop of that user? It doesn't copy the FE down to that user local physical machine, and try to run that through RD? That would be the wrong way to manage this.

Is this a Hyper-V setup (i.e. virtual machine)?

Is this a standard Terminal Server setup, or are you using other technology to allow multiple remote logins?



0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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....>>

Access has issues with 64 bit and multi-core processors and also 2008 R2 (as LSM has noted).

See the following thread:

http://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/Remote_Desktop-Terminal_Services/Q_25091616.html

Note the comment at the bottom about MaxBuffers.  You may want to try adjusting MaxBuffers with the dbEngine SetOption method to see if that changes anything.

JimD.
0
agilitycnsCommented:
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.

0
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
agilitycns,

Do you have the Office 2007 SP2 update installed.
 
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Eileen MurphyIndependent Application DeveloperAuthor Commented:
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!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.