Split Database impacting on Performance


I have a split database with multiple front ends and one back end. These are all stored on network drives.

When just using it for data entry, all works as it should. However, when running SQL queries, the performance slows right down and what takes 5 to 10 seconds when a single user is using it can take over 40 minutes when we have multiple users.

Can anyone explain why this could happen? I've compressed the FEs and the BE too.


Sarith GadaData AnalystAsked:
Who is Participating?

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

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.

Dale FyeOwner, Developing Solutions LLCCommented:
First off, the FE should be on each users desktop, not stored on the network.  There are a number of ways to do this, the easiest is to create a batch file that copies the latest FE from the network to the users desktop whenever the user clicks on a desktop shortcut, and then launches the application.  I use a custom application launcher for each of my clients which displays only those applications that each user should have access to.  It then only copies the file from the server if the user doesn't have the latest version or if the user indicates they want to refresh the local copy.

As to the difference between an 10 second query and a 40 minute query, seems a little extreme.  Could there be other network or server issues when multiple users are logged in?  

-  First thing I would do is put the FE on the users desktops and test again.
-  Next thing I would do is make sure that the tables being queried are properly indexed, this can make a large difference in query performance

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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, are you working with a LAN or WAN? A LAN is a local network where all components are physically wired to each other, through routers/switches etc housed onsite. A WAN is one that goes beyond that, and uses VPN or some other technology to connect remote offices. Access generally works fine on a LAN, but not a WAN. Access also does not work well on wireless connections.
Also, If you haven't compacted the BE recently, do so.  Then compact the FE so that Access picks up the new statistics generated when the BE was compacted.  This is one of the reasons that I always distribute with a batch file.  Every time the user opens the app they get a fresh copy of the FE.  This ensures that the FE always compiles each query the first time it runs so that the query engine always generates a new execution plan.  The best method for retrieving data may change over time as the table grows and indexes are added.
SolarWinds® Network Configuration Manager (NCM)

SolarWinds® Network Configuration Manager brings structure and peace of mind to configuration management. Bulk config deployment, automatic backups, change detection, vulnerability assessments, and config change templates reduce the time needed for repetitive tasks.

Sarith GadaData AnalystAuthor Commented:
Hi all,

Thank you for your feedback. I will try and use a desktop based front end and see if that makes a difference.

I am working on a LAN, but we do have the ability to work through a wireless connection, although majority of users prefer docking and using a wired connection.

Will try and use this today and send feedback as soon as I can.

Thanks again,

Dale FyeOwner, Developing Solutions LLCCommented:
I would strongly encourage you NOT to use Access over a wireless connection.  It is a recipe for data and front-end corruption.  It is a bit more stable over wifi if you use SQL Server as the BE, but again, I cannot reiterate strongly enough that use of Access over wifi is a recipe for FE and BE corruption.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
but we do have the ability to work through a wireless connection
As Dale said, don't do this. It might work fine for a few days, or a few weeks, but you'll end up with a corrupt database. Access was never intended to be run in this manner, and does not have the recovery features needed for the constant link/relink processes that occur with all wireless connections.
Sarith GadaData AnalystAuthor Commented:
Thanks all for your support. Having spoken to the network guys in the office, we'll try and host on the SQL server and see how it fares from there.

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

From novice to tech pro — start learning today.