Slow Database

[Seth Schrock]

I have designed a database that runs fine on my computer. However, when I put it on my client's computer, it runs painfully slow (2 second queries on my laptop, 10+ minutes or lockup on client PC). While running the query, the Disk Usage is at single digit percentage, about 3% CPU, 25% Memory and 0% network. It acts like the computer is throttling MS Access, but I don't know if that is even possible. The PC has 16GB of RAM and a 3.0GHz Quad Core processor. The data is stored in SQL Server on the same PC using a DSN-Less connection.

I'm really at a loss for why it runs so slow on the PC. Does anyone have anything that I should be looking at?

[NeoPa]

Which of the two PCs is the SQL Server on?

Is it possible it's trying to run on your SQL Server when running on the Client's PC?

[Seth Schrock]

Currently I have SQL Server running on both my laptop and the client PC. When I'm developing, then I'm running off of my laptop SQL server. When I test it, I copy the Access FE to the client PC and point to the client PC SQL Server. I can try connecting my laptop to their network and see how that runs.

I will say that if I run a query using SSMS on their PC, it runs just fine.

[NeoPa]

Try getting on your client's PC and opening one of the SQL linked tables. Does it run slowly? Is it somehow not properly set to the local SQL Server?

[Seth Schrock]

I can open the tables within Access just fine. I even created a view in SQL Server and linked that into Access and that opened fine as well. I will be testing the use of my SQL Server tomorrow.

[NeoPa]

You don't make it clear where you're running your tests Seth. I assume you read and understood clearly that it only makes sense to on the client's PC but I try to avoid assuming where possible as it rarely turns out well.

Was the data you were seeing exclusive to the client's PC? Not something that could have come from your own one in spite of thinking it's linked to the client's one?

All ideas that need to be clarified and tied down before progressing to other thoughts (Assuming we can come up with any).

Doh! Assumptions again!

[Seth Schrock]

I had tried opening the tables on the client PC last week, so I already knew that information. The data was exclusive to the network that the client PC was on. The data was the same as I had on my laptop, but there was no connection between the two. This evening I will try connecting my laptop to the network and see how the queries go on the client PC with my laptop being the SQL Server.

[Luk3r]

Is it possible that the client PC has some sort of Anti-Virus which is running real-time scans against the database files when they're being accessed?

[Seth Schrock]

I have now tried connecting to the SQL Server on my laptop with the FE on the client PC and that worked very well. I also tried turning off the Anti-virus, but unfortunately, that didn't change anything. I tried uninstalling and reinstalling SQL Server, but that didn't do anything. I ran out of time to try anything else, but at least I now know that the problem is somewhere between Access and SQL Server on the client PC.