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!
LVL 99
Lee W, MVPTechnology and Business Process AdvisorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aranaCommented:
check the cpu usage in task manager and performance monitor to find out what is taking such a big slice of cake
aranaCommented:
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 AdvisorAuthor 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%.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Lee W, MVPTechnology and Business Process AdvisorAuthor 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.
aranaCommented:
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 AdvisorAuthor 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 DeveloperCommented:
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 AdvisorAuthor 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 AdvisorAuthor Commented:
The same query that fills the Customer and location table, when run from the SQL SMS completes in 1 second.
lijunguoCommented:
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 AdvisorAuthor 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.
lijunguoCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Thank you - I looked a while ago and couldn't find an updated one - I'll give this one a shot.
lijunguoCommented:
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 AdvisorAuthor 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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.