asked on
EXTREMELY SLOW Access/SQL on same Machine
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!
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!
Microsoft SQL ServerMicrosoft AccessWindows Networking
Last Comment
Lee W, MVP