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.
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.
dqmq
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.
ASKER
Can expand some more on memory leak?
Step 1:
Compact and Repair the ACCDB.
mx
Compact and Repair the ACCDB.
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
- 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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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.
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.
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).
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).
ASKER
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.
Today I'll be informed if the PC has been restored its original image.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
(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)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanx dqmq, excelent!
Whats DBMS lock?
Whats DBMS lock?
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.
ASKER
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.
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.
ASKER
anything else on this?
I am glad I could serve your technology needs in any way possible!