?
Solved

Access Queries Running Extremely Slow Over Network

Posted on 2014-09-18
10
Medium Priority
?
935 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 59

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 1300 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 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1300 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 41

Assisted Solution

by:PatHartman
PatHartman earned 200 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 41

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 59
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 41

Assisted Solution

by:als315
als315 earned 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Implementing simple internal controls in the Microsoft Access application.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

601 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