troubleshooting Question

EXTREMELY SLOW Access/SQL on same Machine

Avatar of Lee W, MVP
Lee W, MVPFlag for United States of America asked on
Microsoft SQL ServerMicrosoft AccessWindows Networking
15 Comments1 Solution1504 ViewsLast Modified:
I have an Access Database with SQL back end I maintain for a client... I took a copy of the client's data and set it up on my SQL 2008 R2 server on a separate Windows Server 2008 R2 VM server with other databases.

When I open the access file on "WORKSTATION-A" (with the remote server), it takes about 3 seconds or less to open the database front end, and another 2 seconds from clicking the button that brings up my heavily loaded data form that pulls up customer data and depending on customer, specific location information from another table and populates a tab control with at least 12 tabs.  In 5 seconds, I'm ready to go, no problem.

When I open the same access file on a local computer "WORKSTATION-B" with a local install of SQL Express 2014 and no other databases, it takes 8 seconds to reach the form where I can click that button to bring up the customer screen which then takes 73 seconds to open fully.  The first part takes 2-3x as long and the second part takes 36x as long.  BUT I'M LOCAL!

*IF* I alter the ODBC connection on "B" to access the server instead of local SQL instance, things are much faster (but still slower); Access opens in about 3 seconds (same as station "A") but then the form takes almost 12 seconds to fully load - still, 6x faster than when using the local copy of the data!

BOTH computers are 8-core AMD FX series CPUs with over 3GHz.  "A" has 32 GB of RAM and runs Windows 8.1 Enterprise, "B" has 16 GB running Windows 10 Pro 1511. I seriously doubt the RAM difference is the problem; with the database open, Windows Task Manager reports 26.1 GB available - and that's with other apps open.  And I there was a slowness like this (not sure if it was as bad but I suspect so) when the Win10 system was on Windows 8.1 Pro.

If I point the data source on B to the server (which is only accessible over a slower VPN link), I can test the ODBC connection successfully in under 1 second.
If I point the data source on B to the local install, I can test the ODBC connection successfully but it takes about 5 seconds - AT LEAST 5x slower than the VPN connection to the LOCAL server that shouldn't really be even using a network!  (I know it hits the network stack though).

BOTH systems have Hyper-V installed (which means networking passes through Hyper-V)

SO..... HOW CAN I GET THE LOCAL INSTANCE TO RESPOND LIKE THE REMOTE INSTANCE - FAST!
ASKER CERTIFIED SOLUTION
lijunguo

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 15 Comments.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 15 Comments.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004