EXTREMELY SLOW Access/SQL on same Machine

Lee W, MVP
Lee W, MVP used Ask the Experts™
on
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!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
check the cpu usage in task manager and performance monitor to find out what is taking such a big slice of cake
also try disabling Named Pipes and TCP/IP so that local SQL Server uses Shared Memory
if your local pc is connected tu the remote server or vpn while you are trying the local DB there may be a conflict of name resolutions.

check the autoclose property of your database, maybe in the server there are many users and db remains open , but locally it closes it having to reload everything each time.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
Nothing.  It's a lot of wait time.  30 seconds into the load, it shows 90% idle and nothing related to SQL or Access (or antivirus) using more than 1%.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
check the autoclose property of your database, maybe in the server there are many users and db remains open , but locally it closes it having to reload everything each time.
Definitely not - I'm the only user of this database on my development systems.
exactly on your server you are not the only one I suppose, so it remains open if it doesnt have autoclose, but when you are the only user so it closes it as soon as you stop using it.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
The server has other databases which are VERY lightly used - perhaps 1000 transactions per day. TOTAL.  Among ALL the databases.

For this database I am LITERALLY the only one who ever has it open.
Olaf DoschkeSoftware Developer

Commented:
What queries run? Do the queries crunch on much data while only serving a few rows in the end? Then doing that server side can be much faster than doing that local, as a server may have better resources for that. CPU load is not hte only factor, als hdd load of the SQL process.

If you Sum up 2GB of data to 100 rows in a group by - even when it's a simple single table group by - that can take the long time, while a server with that data cached or on ssd can do the same aggregation much faster. The network load and time for that does not matter in that case.

So overall a server side load of complexer query becomes a client load when running with local SQL and that overall can run much slower.

Bye, Olaf.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
The server is a custom built system that actually uses the same kind of 8 Core AMD FX CPU with 6 running VMs on mirrored drives. If anything, the server load is GREATER than the workstation and it's performance is FAR better... not just a little.

Further the local system has 16 GB of RAM and the server has only been allocated 3 GB.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
The same query that fills the Customer and location table, when run from the SQL SMS completes in 1 second.
The official SQL Server 2014 Express edition memory limit is 1GB per instance, also the other SQL server is SQL 2008. On workstation, please download and update the ODBC driver to match SQL server 2014 express, then make changes on ACCESS to use the new ODBC driver. You may use an old ODBC driver for SQL 2008.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
The official SQL Server 2014 Express edition memory limit is 1GB per instance
True - but with multiple databases and only 3GB of RAM on the server and the SQL Server on "B" using 500 MB of RAM, it doesn't seem very likely this is the problem.  Especially considering the queries in SQL SMS run quickly.

also the other SQL server is SQL 2008.
Yes... it's performance for BOTH systems is fine!

On workstation, please download and update the ODBC driver to match SQL server 2014 express, then make changes on ACCESS to use the new ODBC driver. You may use an old ODBC driver for SQL 2008.
Please provide a link and clarify WHICH workstation you think I should install the updated driver on.
New ODBC driver download link:  https://www.microsoft.com/en-au/download/details.aspx?id=36434
Just need to install it on the slow workstation, then you need to build a new data source on the slow workstation to test it.
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
Thank you - I looked a while ago and couldn't find an updated one - I'll give this one a shot.
You're welcome! From the supported system, it did not say Windows 10 and Server 2012 R2, but it worked on those OS.  

If that one is not working for you, you may try this preview one.
https://www.microsoft.com/en-us/download/details.aspx?id=50420
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013

Author

Commented:
Your link was not for US so I wanted to make sure I wasn't going to have any language issues and I googled it.  For the reference of others https://www.microsoft.com/en-us/download/details.aspx?id=36434 was what I came up with (oddly just changing the au to us didn't work for me).

Anyway, When I updated the drive it's now performing MUCH better.  I didn't time it but the local version is responding within a second or two of the one connecting to a server... that's fine.  And I DID have to recreate the DSN (the original DSNs were using "SQL Server" and the new one that works faster is using "SQL Server Native Client 11.0"

As I mentioned, I had looked a while ago but not found anything updated... this is less than a month old and works - if I had just googled first, I might never have asked the question... oh well.  Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial