Link to home
Start Free TrialLog in
Avatar of Lee W, MVP
Lee W, MVPFlag for United States of America

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!
Avatar of Arana (G.P.)
Arana (G.P.)

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.
Avatar of Lee W, MVP

ASKER

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%.
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.
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.
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.
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.
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.
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.
ASKER CERTIFIED SOLUTION
Avatar of lijunguo
lijunguo
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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!