Solved

Access Queries Running Extremely Slow Over Network

Posted on 2014-09-18
10
411 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
ID: 40330599
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
ID: 40330638
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
ID: 40330739
<<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
Don't miss ATEN at NAB Show April 24-27!

Visit ATEN at NAB Show to learn how our "Seamlessly Entertaining" solutions deliver fast, precise video streaming without delays for the broadcasting and media environment. ATEN will showcase its 16x16 Modular Matrix Switch (VM1600) and KVM Over IP Solution (KE6900 series).

 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 50 total points
ID: 40330946
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
ID: 40330999
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
 
LVL 36

Expert Comment

by:PatHartman
ID: 40331020
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
ID: 40331081
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
ID: 40331139
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 40

Assisted Solution

by:als315
als315 earned 125 total points
ID: 40331147
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
ID: 40331225
Excellent idea, ALS315. Thank you!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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