Link to home
Start Free TrialLog in
Avatar of DataTrain
DataTrainFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access Database running slow

I have an Access database running in Access 2010 but in 2003 format (.mdb). It is split into code (front end) files (on each of 4 PCs) and a data (tables) file on one of the 4. Each PC has its code file linked to the data file as follows:
PC 1 data and code (new Win 7 PC)
PC 2 code (new Win 7 PC)
PC 3 code (older XP PC)
PC 4 code (older XP PC)
The machines are all in the same room connected via basic LAN.
When using the database, apart from PC 1, all three are very slow, especially PC 3 and PC 4. But PC2 also runs much slower than PC1.

I am wondering what to do! Options I am considering are:
1. Upgrading PC3 and PC4 and moving them to Windows 7.
2. Having a separate (5th) "server" PC to hold the data file.
3. Upgrading the database file to Access 2010 format (.accdb).

I would value any advice or opinion on what will make this work faster without spending unnecessary money. It is important as the database supports an organisation with over 3,000 members. Many thanks!
Avatar of Si Ball
Si Ball
Flag of United Kingdom of Great Britain and Northern Ireland image

i'd go with number 2 first, and also have the lan checked to see if it is performing suitably

Might be worth testing, opening a 60 page large PDF or worddoc from a share on PC one, and seeing if its slow on PC's2-4 too.. in which case you LAN might be the Slow point..

I upgraded a 6 year old pc to win 7 from XP about 2 years ago and it did make it run most things faster....

not sure how (3) would effect things, but its worth a try.
Suposing that front end files are the same for all the computers I'd copy PC1 front end file to PC2.

Also, running Access "Tools -> compact & repair database" on each computer front end file  could help to clean them from  corrupted data, memory traces, etc...

Upgrading to Win7 could be a good idea, I'm quite impressed with the efficiency increase of this version of windows.

But be wary of upgrading the code files to Access 2010 version, vba suffered some slighly modifications from 2003 to 2007 and further 2010 and you could find that, after saving them as 2010 version some methods stop working or shows unexpected errors.
I agree with Sudonium that you should put the data file on a separate server.  Having it on PC1 gives it preference to the other PCs.  As the other three PCs have to communicate with PC1 to get access to the data while PC1 has it as a local resource.

Something else you can try is running Windows update on your PCs to ensure that both Windows and Office  are fully up to date.  There could be a performance issue if the resource dlls are not current or fully compatible.

Have you done a compact and repair to insure that your database is running optimally?
Are the code front ends compiled?  If not you could also compile them for better performance.  However, it is essential that you make a copy of the un-compiled front end so that you can make changes in the future.
You really should have a seperate machine to act as a server, but really with only 4 users, the problem lies else where.

Unless there is an outright problem with the PC's communicating with PC1 or the LAN itself, you should have adaquate performance with only 4 users.

More then likely, it's the way the application was developed.

But a couple of questions:

1. What's the speed of your LAN.

2. PC3 and 4; how much memory do they have?  I'm assuming their older since their running XP.  Keep in mind that with Access and a JET database (MDB file), all the operations occur on the client not the server.  

  Use task manager to check the amount of free physical memory.

Beyond that, do indeed make sure the app is compliled if you used code (you do not need to convert to a MDE to have a compiled state).

Also make sure that:

1. name auto correct is turned off in options.
2. Subdatasheets is turned off for all tables (I have code to do this)
3. The MDB's are not being virus scanned at opening.

Finially, modify the app to open something in the backend on PC1 at startup.  This can be as simple as having a hidden form bound to a table.  Then close it at exit.

This prevents repeated closing/opening of the BE, which involves quite a bit of overhead.

Those are all fairly simple to do and should get you running fairly well unless the app has been developed poorly (ie. Dlookups() in queries).

Jim.
ASKER CERTIFIED SOLUTION
Avatar of Luke Chung
Luke Chung
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DataTrain

ASKER

Thank you for all these very helpful comments. When I have had opportunity to do further checks on-site (which may be a little while) I will respond further.
good luck.
I have been able to visit the client today and obtain this information:

COMPUTERS
PC1
OS:      Windows 7 Professional, SP1
CPU:      Intel Core i5, 3.33 GHz, 32-bit
RAM:      4 GB
HDD:      250 GB
Free:      168 GB
NA:      Intel 82578DC Gigabit Network Connection

PC2
OS:      Windows 7 Professional, SP1
CPU:      Intel Core i5, 3.33 GHz, 32-bit
RAM:      4 GB
HDD:      250 GB
Free:      211 GB
NA:      Intel 82578DC Gigabit Network Connection

PC3
OS:      Windows XP Home Edition, version 2002, SP3
CPU:      Intel atom 330, 1.60 GHz
RAM:      1 GB
HDD:      80 GB
Free:      60 GB
NA:      Realtek RTL8168C(P)/8111C(P) PCI-E Gigabit Ethernet NIC #2

PC4
OS:      Windows XP Home Edition, version 2002, SP3
CPU:      Celeron, 2.40 GHz
RAM:      768 MB
HDD:      37.2 GB
Free:      15.1 GB
NA:      Intel PRO/100 VE Network Connection

ROURTER
Make      Netgear
Type      5 Port 10/100 Mbps Switch
Model      P5605 v3
All devices connected by Ethernet cable.

Any thoughts in the light of this information?
Ideally, you want to put the data file on a server by itself.  As you have the data file on PC1 any use of PC1 uses resources that could be used to communicate with the other PCs.  PC1 has the advantage of having priority to the data file since it is a local resource. PC 3 and PC 4 are particularly slow because of the limited RAM available for processing the Front ends of the database.  You could probably boost their performance by adding more RAM.  However, this does not resolve the problem of having the data file on a PC (PC1) that is running other functions.
<<Any thoughts in the light of this information?>>

  PC3 & PC4 have inadaquate memory.   I think you'll find that if you start task manager (Ctrl/Alt/Del) and go to the performance tab, that available physical memory will be close to 0.

  As for PC2, it's more then adaquate.  My guess would be it's the app itself that needs some work.

  I would do all the simple stuff first as I mentioned earlier, then run the database performance analyzer and see if it suggests anything.  The area you should focus on first is the table indexes.

Jim.
OK, lots of different advice! So you don't think the Router is an issue? I am busy on other projects for a few days. I will consider this and comment ASAP. Thanks.
<<So you don't think the Router is an issue?>>

 Doubt it.  PC3 and 4 are definitely memory.  PC2 is most likely because the app is not written well.  But then again "slow" is a relative term.  Something that takes 2 seconds vs 1 someone may call slow and another not.

Jim.
I think more RAM would help PC3 and PC4. However the users complain it is slow when only PC1 and PC2 are using the database. I am therefore wondering if the design of the database is in fact the real issue. I first built it in 1999 and have made various changes and improvements along the way. The two main tables only have 300 and 3,000 records respectively BUT there is some quite complex VBA involved and queries including a UNION query. Maybe this is the issue?
<<I think more RAM would help PC3 and PC4.>>

 More ram would certainly help, although their probably old enough that a motherboard swap out will be required.  Even with that, your only going to get them up to the level of performance of PC2, and if that's not good enough either, then I'd focus on speeding up the app.

<<The two main tables only have 300 and 3,000 records respectively BUT there is some quite complex VBA involved and queries including a UNION query. Maybe this is the issue?>>

 Those are pretty small tables.  Hard to say what the issue is....could be anything.  For example, using any of the Domain functions (Dlookup(), DSum(), etc) in a query is a big no-no performance wise.

 I would start with the Database performance analyzer and see if it suggests anything and then look at that.  But don't take what it recommends and simply do it without first trying to understand why it's making the suggestion.  See if it makes sense for the way the app is used.

  Getting the indexing right will yield the most improvement for the least amount of work.

  Keep in mind that too many indexes can be just as bad as too few (slows down inserts, updates, and deletes).

   Also if you happen to have compound keys, an index will only be used if the search key is in the same order.  If it's not, the index will be igored.  For example, you have a time card table that has an index defined on the compound key employee ID and date.

  You do a findfirst by date in form because your trying to pull up all the employees for a given date.  That index will be ignored.   Very often your better off to have a seperate index on employee ID and one on date.

  Also, don't have indexes on fields that are not very unique.  i.e. indexing a yes/no field is typically not a good idea because it has only two possible values; yes and no.   It has what you would call "Low cardinaility".

  Beyond that and the things I mentioned in my first comment, what you could do for app optimization would fill a small book.

 Focus on the most intensive processes first; where do users spend most of their time and doing what?  And then maybe posting a sample DB with that shown or asking some questions on how it might be improved.

Jim.
After you have reviewed and optimized your indexes and code, I would compile the front end.  As stated earlier, please make sure you have a backup of your uncompiled front end.
<<After you have reviewed and optimized your indexes and code, I would compile the front end.  As stated earlier, please make sure you have a backup of your uncompiled front end.>>

 Just to make sure this is clear; compiling and converting to a MDE are not one in the same thing.

You can compile an app and leave it in a MDB format.  As long as you do nothing in the course of execution that would case it to become un-compiled (i.e. switch a form into design view or add a reference), your fine.

 Converting to a MDE format strips out all the source code leaving only compiled p-code left, so you want to make sure you have a backup before you do that, because you cannot make code changes to a MDE.

 And because the source code is stripped out, then the app must be in a compiled state when it's converted and once converted, it never can become un-compiled.  That's also why once you convert to a MDE, there are certain things you can no longer do.

  So don't simply convert to a MDE format for the sake of having a compiled app.  Conversion to a MDE does have impact beyond that and you need to test the app with it that way.

Jim.
Based on your comments, it appears there are problems with the performance independent of hardware. It's simply slow. Have you reviewed the list of performance suggestions in the link I provided?  That can help generally.

More importantly is to find specific places where performance is slow. For instance, in a multiuser database, you should always open a handle to the backend database when the application starts. That eliminates the creation and deletion of the LDB or LACCDB file each time a table is opened.

Here's a paper on the topic: Significantly Improve the Performance of Microsoft Access Databases with Linked Tables: http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html
<<For instance, in a multiuser database, you should always open a handle to the backend database when the application starts. >>

 Already mentioned it...

Jim.
Jim,

Sorry. Can't see where it's already mentioned.  

How about these items:

1. If a form is slow to load, change the recordsource from a SQL string to a saved query.

2. Making sure queries with GROUP BY clauses don't reference queries that also have GROUP BY clauses if the grouping is only needed once? (We added this to the new version of Total Access Analyzer).
I still have some testing to do but I believe this is the correct answer. Thanks.