Solved

Access Queries Running Extremely Slow Over Network

Posted on 2014-09-18
10
256 Views
Last Modified: 2015-05-23
My client has a small business. They are running Windows Server 2012 and 5 workstations running mostly Windows 7 Professional, although one laptop is running Vista Business.

They have a customized database running on Access 2002, which keeps track of all their customers and order histories. The Access file is stored on a shared network drive.

They are complaining that running certain queries can take up to 30 seconds to process. I've compacted the database file, but the queries are still running very slowly.

I'm not sure if this is an Access issue or a LAN issue, or perhaps a combination of both. Was looking to get some advice on how to go about troubleshooting this. Thanks in advance.
0
Comment
Question by:anuneznyc
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 325 total points
Comment Utility
There could be any number of issues.  Couple of questions:

1. Is the app setup in a FE/BE configuration?

2. What sizes are the tables involved?

3. Is this something that started recently or just been going downhill for a while?

4. Any recent changes in the environment?

5. Has indexing been checked?

6. Paste the SQL of one of the slow performing queries here.

Jim.
0
 

Author Comment

by:anuneznyc
Comment Utility
Thank you for the suggestions, Jim.

This has been an issue for a long time now. The one change the business owner is aware of is that they had a Sonos music player added to their network so they could stream music.

I will be onsite later today, so I can check the sizes of the tables.

How would I check on the indexing? And how can I view the SQL queries in Access?
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 325 total points
Comment Utility
<<This has been an issue for a long time now. The one change the business owner is aware of is that they had a Sonos music player added to their network so they could stream music. >>

 They could be having some network congestion.  Server might even be overworked.  Hard to say though without a lot more details.

<<I will be onsite later today, so I can check the sizes of the tables. >>

  Basically trying to get a feel if tables have records in the thousands, ten's of thousands, hundreds of thousands, or millions.  

   Since this has been on going for some time, it may be nothing more than the database has grown to the point where some data needs to be purged off to archive tables/DB's, or things done different.

<<How would I check on the indexing?>>

  With JET/ACE (a MDB/ACCDB BE for data storage), it mostly boils down to know the application, the queries it executes, and what indexes there are.  There is JET SHOWPLAN to tell you what's being used, but outside of that,knowing what to use is a whole discussion unto itself.

<< And how can I view the SQL queries in Access? >>

 With the queries that are running slow, open them in design view, then switch to SQL view.   The SQL statement for the query is then shown.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
Comment Utility
had a Sonos music player added to their network so they could stream music
My money is on this hogging all the bandwidth on the LAN.  Do they have a way of disabling it to get a base level of LAN traffic?
0
 

Author Comment

by:anuneznyc
Comment Utility
Thanks Pat. Yes, I suppose we could shut down the Sonos box to see if that improves the speed of the queries.

What is the best way to benchmark the performance of the LAN?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
I don't know.  I develop Access apps.  Hopefully you have a Network support person who should have the tools to check the LAN.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
um unless I didn't understand correctly, I thought the Sonos box was a recent addition where as the problem has been long standing, no?

Jim.
0
 

Author Comment

by:anuneznyc
Comment Utility
Hi Jim. Not exactly. The problem has been long-standing, but the Sonos box is not that recent. I only just picked up this client, but I believe they have had the Sonos for about a year or so. The business owner thinks he recalls that the Access DB did start running more slowly shortly after the Sonos was added. So there may indeed be a direct correlation b/t the addition of the Sonos and the slowness.
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 125 total points
Comment Utility
You can make simple test: copy large file (1Gb and more) from server to workstation. Measure time. Stop Sonos box, repeat copy. Compare time. If server is loaded with any tasks, repeat this process several times and calculate average time
0
 

Author Comment

by:anuneznyc
Comment Utility
Excellent idea, ALS315. Thank you!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Join & Write a Comment

Trying to figure out group policy inheritance and which settings apply where can be a chore.  Here's a very simple summary I've written which might help.  Keep in mind, this is just a high-level conceptual overview where I try to avoid getting bogge…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will walk an individual through the steps necessary to join and promote the first Windows Server 2012 domain controller into an Active Directory environment running on Windows Server 2008. Determine the location of the FSMO roles by lo…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now