[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Optmize Microsoft Access 2007, slow at time

I have a DB in MS Access 2007, the size is about 112mb.  This DB is necessary for an application we have to run.  The application is developed in .NET. The PC is 2gb Ram, Vista.

The problem is that after a while, maybe 4 to 7 hours, the application get very sluggish and slow.  The application is opened in the morning, worked with the database (access) all day  and all ok, but after some hours, it just get super slow.  Sometime I exit from the application and comes back on, and it works it again.  

Questions:

1. What maintenance tasks is recommended for this database?

2. Beside the maintenance (if not the database), how can I determine if its Access, .NET or the PC

please advice.




0
rayluvs
Asked:
rayluvs
  • 11
  • 5
  • 5
  • +2
7 Solutions
 
dqmqCommented:
For Access, periodically compressing/reorging is a good idea.  However, your symptoms seem more like a memory leak of some sort.  I'd suggest starting up a task manager and monitoring the memory usage of your application.  If you see a gradual increase as the day progresses, then that's the culprit.  

 
0
 
rayluvsAuthor Commented:
Can expand some more on memory leak?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Step 1:

Compact and Repair the ACCDB.

mx
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CCastellanosCommented:
First and foremost please check if the size of your database has grown abnormally in a day's worthof work. This is a sign of many things not going well withthe file.
You mentioned the expected size is 112MB. Assuming this is the morning size, how big is it at the end of the day?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
It could be network traffic. Does performance get better later in the day, or early in the morning when traffic is slow?

How many concurrent users are there?



0
 
rayluvsAuthor Commented:
DatabaseMX,

We have been Compact and Repair the ACCDB.

CCastellanos,

I have checked the size of the DB and is around 112mb.  I have not verified if it has growned in the afternoon.  Nevertheless, if it grows ion the afternoon, is it possible to shrink back again in the morning to 112mb?

LSMConsulting,

The perfomance is better prior 5pm and after 5pm, it gets slower.  The user exits and start the applicatrion agaibn and all is ok

At any one point, there can be a max of 5 users.
0
 
CCastellanosCommented:
The answer is yes, it can go back to expected size, and I say expected as during the day I assume there is some data entry. You are the best person to know the growth pattern in your DB. But in any case, by experience slow DB performance can be due to many factors. Allow me to ask some questions that may allow narrow down the problem.
- Am I correct to assume this is a 2-tier implementation: Client/Server files.
- This is purely ACCESS and no MS SQL or other database backend flavor is involved.
- You clients are on the same network (no WAN involved).
- Can you share number of clients?
- Always check for backup or any other processor intensive applications running on the machine where the assumed backend database file is sitting. Do you know of any process that may be running at the same time every day.

All the previous are generic preliminary but necessary steps to be cleared.
0
 
rayluvsAuthor Commented:
Thanx! Here are the answer:

- Am I correct to assume this is a 2-tier implementation: Client/Server files.

   ANSWER:
   - the MS Access database is located on the server and the application is located on the
     5 workstation (there is no such server/client as SQL)
   
- This is purely ACCESS and no MS SQL or other database backend flavor is involved.
 
   ANSWER:
   - purely access 2007

- You clients are on the same network (no WAN involved).

   ANSWER:
   - same network (there may be one router or two that links them, but we can check)

- Can you share number of clients?

   ANSWER:
   - I don't quite understand this question, but there at the moment, 5 users access the database

- Always check for backup or any other processor intensive applications running on the
  machine where the assumed backend database file is sitting. Do you know of any
  process that may be running at the same time every day.

   ANSWER:
   - good question and there may be because this happens a little before 5m; we'll check
0
 
dqmqCommented:
Memory leak means the client consumes memory and does not give it back. Eventually, the client gets short on memory and performance suffers.  Symptoms of memory leak are that each client is affected independently and restarting the client clears the problem for awhile.  

Your problem description has some of those characterstics, however, the affinity with 5pm, does not quite fit.  Unless of course, the leak is in code that is predominantly run around that time every day.  

One test would be to start the application before the slowdown starts and then let it idle until other workstations are experiencing slowdown.  Then, ramp up the idle workstation to see if it is also effected (if so, the problem is not workstation-independent and memory leak is not suggested.  Also, check the memory usage from task manager before, during, and after the slowdown for clues.








 
0
 
rayluvsAuthor Commented:
we were present at the time of the "slowness" and couldn't find anything wrong.  We checked the other 4 PC working with access and all was ok.  We checked Lan, routers, we isoltaed the PC from the LAN and placed only the PC and the Server and still same problem.  

We also checked process running within the PC and there we did found something strange, randomly, when disabling processs the PC would reset.  Unfortunately we couldn't isolate which was causing the problem, but we we proceeded reinstalling all again.

Hence, we couldn;t detect why after 5pm this happens.

We'll reinstall and inform of status
0
 
rayluvsAuthor Commented:
NOTE: we did noticed that when it froze, it was when a lookup was being made on a field that is supposed to display a windows of option to choose.
0
 
CCastellanosCommented:
Interesting, then it might very well be a query with unexpected behavior (not to say defective). Do you know what processes are running within the application at or right before the time of "slowness". Perhaps, there an specific routine to happen at such a time, but one or more of queries involved are either too data intensive or not written properly. Memory leak as mentioned by dqnq would be something to consider, specially in any of these internal routines are opening objective in a loop and not closing them.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you run the DLookup outside of that form, do you still get the issues?

DLookups work fairly well, but if they are extremely complex (or call/use complex queries) you're often better off moving this to a more SQL-focused process (i.e. use Recordsets or SQL to do what is needed).
0
 
rayluvsAuthor Commented:
Understood, but the same application/DB run on the other PCs and works perfectly.  We did the exact same steps and at the very same time on the other PCs and ll is ok.   Is it possible the problem is in MS Access or .NET, but only prone to this specific PC?

Today I'll be informed if the PC has been restored its original image.
0
 
dqmqCommented:
>Is it possible the problem is in MS Access or .NET, but only prone to this specific PC?

Sure.  And restoring the image has a fair chance working.  But given your symptoms (only happens after 5pm, restarting the application clears the problem, isolated to one PC), it's more likely to be some kind of resource shortage or conflict.

During periods of slowness, what is the machine waiting for?
   Memory?
   CPU?
    I/O?
    Locked Resource?
0
 
rayluvsAuthor Commented:
When u say "what is the machine waiting for?", what do you mean?
(I don;t think is waiting for anything)

The PC image was restored and worked for a couple of days, but it happened again, but only once; its has been working perfect (and we didn't; do anything to that one problem it displayed, it just kept working ok)

This is real crazy...  as long a the user is happy, we're not touching it.  Unfortunately we can't identify the problem because it does happened, so we in observation.

Any last recommendations or observation?
(I think I should close the question)
0
 
CCastellanosCommented:
Hi Ramante:

Glad is stable by still troubled with it. I was reviewing the history of comments and wanted to add further notes:

-1- On... "We checked Lan, routers, we isoltaed the PC from the LAN and placed only the PC and the Server and still same problem"
(?) Did you change the switch port and even the cable of the computer?

-2- On... "we did noticed that when it froze, it was when a lookup was being made on a field that is supposed to display a windows of option to choose"
(R)Since you said the same operation works fine on all other 4 client computers and you mentioned it did happen onces more on the newly restored one, then it might very well be a Microsoft Office or .Net (as dqmq mentioned) or else update related to a field content error management (computer freezing) that most now be installed.
At the end just make sure all your computers are at the same level of updates in Microsoft Windows and Office.


Best regards.

0
 
rayluvsAuthor Commented:
Hi CCastellanos,

No, we didnt change the switch port or the cable of the computer.  We were about to, but the PC was already working and the user is in end-of-month, so we'll wait if happens again.

Thanx on the info on -2-, we'll followup on it.


I'll think I'll be closing the question if there is no more observations & recommendations.
0
 
dqmqCommented:
>When u say "what is the machine waiting for?", what do you mean?
(I don;t think is waiting for anything)

Computers are ALWAYS waiting on something. Otherwise, a task would complete instantly and then the computer is waiting for input. Even when CPU is running 100%, the system is waiting on electrons flowing, logic gates opening, clock cycles to tick off, and so forth (but for our purposes, suffice it to say: waiting on CPU).

When a workstation is running slow, my first diagnostic is almost always to switch on a performance monitor and see where the bottleneck is:
  CPU
  Paging
   I/O
If none of those are out-of-line, then it's some kind of external resorce like a DBMS lock or network request.

Moving forward, there's a fair chance that re-imaging eliminated the culprit.  But there's also a fair chance that the problem will re-appear when the stars line up the same way in the future.  I'd anticipate that by getting some performance benchmarks on key indicators during normal operation.  Start-->Run-->Perfmon.exe



 





 
0
 
rayluvsAuthor Commented:
Thanx dqmq, excelent!  

Whats DBMS lock?
0
 
dqmqCommented:
DBMS locks protect database resources.  For example one session requests a database update, the database issues a lock that protects the resource being updated.  If another session requests an update to the same resource, the lock will block the second update until the first one is finished.  The second session either waits for the lock to be released or gets control back with a resource busy error.
0
 
rayluvsAuthor Commented:
Thanx for the info.

I dont think that would be the problem.  When the PC locked, we went to another PC (3 more) and all could do the exact same thing.
0
 
rayluvsAuthor Commented:
anything else on this?
0
 
CCastellanosCommented:
I am glad I could serve your technology needs in any way possible!
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

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