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

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?
JoarAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
JoarAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< 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

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
Ultimate Tool Kit for Technology Solution Provider

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 now.

cquinnCommented:
Is the system split into a frontend and backend?  If not, performance will be terrible with multiple users
0
JoarAuthor Commented:
Yes, the data part is on the server and the program part runs from the client
0
JoarAuthor Commented:
<<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
JoarAuthor Commented:
Looks like the compact and Repair did it! Thanks!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<<<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
JoarAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
JoarAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
JoarAuthor Commented:
Thank again!
0
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
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.