Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Queries Running Extremely Slow Over Network

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

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 58

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
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
LVL 40

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 40

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 58
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 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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 …

773 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