Why does performance degrade as multiple users use same query?

We have a network of 11 workstations that can be potentially logged into our Access2003 application at the same time.  

One form in that application includes a very complex query.  When one person is logged into the application, the response time for this form is fairly quick (20 secs). However, even with 2 additional people  logged in, the performance for this form degrades dramatically (1-2 mins+)
.  There is a coded safeguard in the calling form preventing multiple people from using this form for the same calling parameter.

Any help on this would be appreciated.

Thanks,

Judith
JudithARyanTech SupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Jeffrey CoachmanMIS LiasonCommented:
First, is the database "split"?
http://www.techrepublic.com/blog/10-things/10-plus-reasons-to-split-an-access-database/
If not, ...then it needs to be.

Other thoughts...
If the query is truly complex, then it may involve multiple tables and complex calculations, so this may never be "Fast" for multiple users...

There is a coded safeguard in the calling form preventing multiple people from using this form for the same calling parameter.
Not sure what this means, ...can you explain?
JudithARyanTech SupportAuthor Commented:
Yes, the app is frontend and backend.

Yes, the query uses 4 tables and 7 queries, which probably also uses tables.

When the form is called, a contact parameter is passed.  This contact is Primary Key in the contact table.  Before the form is called, a flag is set in the process table indicating that this contact is being used.  If the process table shows this contact is already being used, a warning message is issued and the form is not called.  If the form is called, the busy flag in the process table is deleted when the form is closed.

Is there a way to determine how to layer queries when they are complex?

Judith
Dale FyeOwner, Developing Solutions LLCCommented:
Any time you have multiple people querying the same tables at the same time, you are going to get some delay.  One way that I use to address this is to use local temporary tables for some of the work.  I have an article on using temp tables;  the point is that if you can pull the limited info you need from each table into a local table, then you are no longer in conflict with the other users.  Take a look at my article and the sample subroutine I've written which will allow you to either create those local table in the front end (or will put them in a separate temp database on your local machine and will then link those tables into your front end).

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
JudithARyanTech SupportAuthor Commented:
Thanks again!
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.