?
Solved

MS Access 2003: speed dropdown in a multiuser environment when 2 or more clients start a search simultainously

Posted on 2008-11-20
13
Medium Priority
?
211 Views
Last Modified: 2013-11-28
I have an (old) ms access app, that runs i a 2003 version mulituser environment (up to 10 clients). The app is developed/maintained in a 2007 environment. Now I experience the following: when 2 or more clients start a search at the same time, both of them will thereafter have very slow searches (was not so before). Theese searches normally results in some hundred records. If the client logs out and in again, the search is fast again as long as nobody else are doing a search. My records are growing bigger and bigger... The last change was (among others) to insert 2 new fields of type memo in the main table. Theese two fields contain not much text so far.  Before there where 2 Memo fields in the table (now 4). I have a feeling that some size limits are the problem, but I do not know. And what can I do? Has anybody experienced this?
0
Comment
Question by:Joar
13 Comments
 
LVL 85
ID: 23002797
Have your endusers applied all the patches/hotfixes to their 2007 installs? Several of them deal directly with performance-related issues. Allen Browne has a list of them on his site:

http://allenbrowne.com/Access2007.html
0
 

Author Comment

by:Joar
ID: 23002858
My end-users are using Access 2003. It is just I as the developer that are using 2007. It is a .mdb app from the older versions
0
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 23003339
<< Before there where 2 Memo fields in the table (now 4).>>
  It sounds like their might be some fragmentation of the memo fields.  Have you run compact & repair recently on the backend DB?  It also suggests the the JET cache is not being very effective, filling up with a lot of junk data in order to find the records.  Logging out and then in would flush the cache.  The first search would be fast then and remaining searches slow.
 I'd also review your indexing on the table(s) involved in the search.
JimD.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Expert Comment

by:cquinn
ID: 23003547
Is the system split into a frontend and backend?  If not, performance will be terrible with multiple users
0
 

Author Comment

by:Joar
ID: 23004871
Yes, the data part is on the server and the program part runs from the client
0
 

Author Comment

by:Joar
ID: 23010924
<<I'd also review your indexing on the table(s) involved in the search.>>
Not sure what you are thinking about with the indexes? Remember: The search is fast as long as no other clients starts a search. Thereafter the search will be slowly untill logout/login, even if the other clients do no more searches...
About the cache, what may have happended now, since the speed was no problem some weeks ago (the app is 10 years old..). May I do some cleaning action to the cache from my access program?
0
 

Author Closing Comment

by:Joar
ID: 31518607
Looks like the compact and Repair did it! Thanks!
0
 
LVL 58
ID: 23013122
<<<<I'd also review your indexing on the table(s) involved in the search.>>
Not sure what you are thinking about with the indexes? Remember: The search is fast as long as no other clients starts a search. Thereafter the search will be slowly untill logout/login, even if the other clients do no more searches...
>>
While not the root problem, inefficent indexing might compound it.
<<About the cache, what may have happended now, since the speed was no problem some weeks ago (the app is 10 years old..). May I do some cleaning action to the cache from my access program?>>
  The cache is maintained by JET and while there are some things you can do to control it, you really should not have to.  Most cache problems are a reflection of something going on else where.
  What you should be doing is performing a compact and repair on a regular basis (at least once a week for any production DB and more often if it is very active).
JimD.
0
 

Author Comment

by:Joar
ID: 23013262
I will take the weekly compact and repair activity into consideration. I would be happy if I could automate it... lets say the first startup of the app on mondays automatically starts a compact and repair. Possible?
0
 
LVL 58
ID: 23013429
<<lets say the first startup of the app on mondays automatically starts a compact and repair. Possible?>>
Yes and no because a compact and repair cannot be done on the currently opened database.  There are a couple of things you can do:
1.  If the app is split (Front end and a Back end),  then it is possible to open the front end and do a C&R on the backend as long as you don't have any connections to it.
2. You can start Access with the /compact switch, so it is possible to setup a job in task scheduler and run a C&R in off-hours.
3. You can set the option "Compact on close" in Access.  When the last user exits, the DB is automatically compacted and repaired.  Note that this is for the currently open database; if you have a split app, it is the front end that is C&R'd, not the backend.
4. You can setup a "Compact DB" app where from the current db, you call it with the name of the current db, which closes, then the "Compact DB" runs and compacts your DB, and then re-opens the compacted DB when it's done.  This technique was used often before the Compact and Repair on close feature was added.
5. You can write a custom launcher program which does this before the app opens.  Many developers write these to do things like version checks or licensing checkes.  Once everything is OK, then they launch the Access app.  It would be easy to stick a compact and close on a front end or back end from such a utility.
  Out of all of the above, #2 is the simplest to implement.
JimD.
0
 

Author Comment

by:Joar
ID: 23013583
Thans a lot!
I in fact I have a startup app (#5) checking the version and if necceary gets the new version. I think I will try to implement it here. I guess that if some other clients already have opened the backend db, I wil not be able to compact? Should I take a copy first, if the compact fails in some way?
0
 
LVL 58
ID: 23013854
<<I guess that if some other clients already have opened the backend db, I wil not be able to compact?>>
  That is correct.  You'll get an error.
<< Should I take a copy first, if the compact fails in some way?>>
  The process does that be default.  It compacts to a copy, then overwrites the original file if the operation is sucessfull. However that still does not mean you should have a backup in hand.
  Compact and repair runs based on what it sees in the DB.  If you have corruption creep in, it is possible to end up with a mess after a compact and repair.  That's why I suggested #2 (beyond it being the simplest); you can schedule it right after your nightly backup.
JimD.
0
 

Author Comment

by:Joar
ID: 23014005
Thank again!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

807 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