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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!
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.
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
LVL 42
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.
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?
You mentioned the expected size is 112MB. Assuming this is the morning size, how big is it at the end of the day?
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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?
How many concurrent users are there?
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.
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.
- 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.
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
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.
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.
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
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).
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.
>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?
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?
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)
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.
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.
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.
>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
(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
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.
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Access 2007 Int… 207 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.