Solved

Access Queries Running Extremely Slow Over Network

Posted on 2014-09-18
10
353 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 35

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 35

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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
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.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 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