• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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
0
Eileen Murphy
Asked:
Eileen Murphy
  • 5
  • 4
  • 2
  • +3
5 Solutions
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 MVPCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now