Solved

Access Database running slow

Posted on 2012-03-13
21
1,060 Views
Last Modified: 2012-04-18
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!
0
Comment
Question by:DataTrain
  • 6
  • 5
  • 4
  • +3
21 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37714333
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.
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 37714483
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.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37714526
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?
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37714542
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.
0
 
LVL 57
ID: 37714833
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.
0
 
LVL 10

Accepted Solution

by:
LukeChung-FMS earned 350 total points
ID: 37715384
Assuming the application runs properly on a "new" machine, it comes down to determining what's different among them. The OS will not make much of a performance difference. More critically would be the amount of RAM in the machine. Access 2010 uses more RAM than Access 2003, and Access is very RAM hungry. The more RAM it has, the faster the performance.

Additionally, there may be a difference in the network cards on the old machines. If you have a 10/100 hub, you want the connections running at 100.

One of the features of our Total Access Analyzer program is performance analysis and suggestions. You may want to check that out. A free preview of the 2010 version is good thru the end oft he week: http://www.fmsinc.com/MicrosoftAccess/BestPractices.html

Hope this helps.
0
 

Author Comment

by:DataTrain
ID: 37715648
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.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37724260
good luck.
0
 

Author Comment

by:DataTrain
ID: 37748805
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?
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37748993
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.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57
ID: 37749023
<<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.
0
 

Author Comment

by:DataTrain
ID: 37754076
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.
0
 
LVL 57
ID: 37754338
<<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.
0
 

Author Comment

by:DataTrain
ID: 37765512
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?
0
 
LVL 57
ID: 37765580
<<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.
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37765829
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.
0
 
LVL 57
ID: 37765906
<<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.
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 37766667
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
0
 
LVL 57
ID: 37767961
<<For instance, in a multiuser database, you should always open a handle to the backend database when the application starts. >>

 Already mentioned it...

Jim.
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 37768282
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).
0
 

Author Closing Comment

by:DataTrain
ID: 37861796
I still have some testing to do but I believe this is the correct answer. Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

#Citrix #Citrix Netscaler #HTTP Compression #Load Balance
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now