1

I have an SQL Server Database on an Azure SQL Server and a .NET Website connecting to it from a seperate Virtual Machine, via IIS on the VM. Connecting to this database and running the website code on my local machine runs queries in about 10 seconds compared around a minute or longer for the same queries on the Website running on the Virtual Machine, which sometimes timeout.

I don't know if that's enough detail but just wondered if there was anything obvious that could be causing the difference in response times? Surely if the queries need to be optimised then it would be the same on my local machine?

The specs of the various elements are: - Virtual Machine: Standard A4 (8 Cores, 14 GB memory) - SQL Database: S3 Standard (100 DTUs) / Server version: V12 Both running in West Europe but in different resource groups

My local machine has an i7 processor with 16Gb RAM

David Makogon
  • 69,407
  • 21
  • 141
  • 189
t.c.
  • 53
  • 6
  • 1
    You've included zero information to help you answer this question (which probably can't be answered anyway): No info on VM specs running SQL Server (e.g. VM type, attached storage type); no info on Azure Web App size; no info on local machine. Just "it's faster locally." – David Makogon Apr 15 '16 at 14:01
  • You're right, sorry for the lack of any useful information. I've added the specs of the VM, SQL DB and my local machine for more detail. – t.c. Apr 15 '16 at 14:31
  • 1
    So... this question belongs on ServerFault, as it's not programming-related. That said (and I won't post this as an answer, since it's *not*): You chose an A-series VM (presumably for your web tier?), which is the lowest-end VM spec available, and comparing it to a non-virtual i7 machine (apples-to-oranges). It's likely that a D2v2 or D3v2 will yield better results, both costing less than the A4 you chose (again, an off-topic discussion here, and this question will likely be closed). – David Makogon Apr 15 '16 at 15:24
  • @DavidMakogon you forgot to add that the local machine most likely uses an SSD or a dedicated HDD running at 7200 rpm. That could cause a *huge* difference in speed – Panagiotis Kanavos Apr 15 '16 at 15:27
  • @DavidMakogon Thank for the information. You're right, it probably does belong on ServerFault. Didn't know that about the A series compared to the D series, good to know. – t.c. Apr 15 '16 at 16:33

1 Answers1

0

You say 'queries' take 10s vs 60s. Do you mean there are multiple smaller queries being run, and the total for all queries is 10vs60?

If so, you may have some network latency on your web server, so that it takes longer to send and recieve the data than it does to execute the query.

If not, it might be the client code you're using on your website. For example, Entity Framework can get really slow if joining over many complex tables -- it'a process called Materialization, and it's caused grief in the past. In the linked example, for instance, converting from VARCHAR to NVARCHAR through EF causes significant performance problems.

Community
  • 1
  • 1
Steve Cooper
  • 20,542
  • 15
  • 71
  • 88
  • Sorry that wasn't too clear. I'm running the same piece of code, which does a customer look-up, on my local machine and the Website on the VM. The time difference is for running that same function, so the same query. I'll check out the link though to see if it helps, thanks. – t.c. Apr 15 '16 at 14:56
  • How are you actually executing the command question is it, four example, entity framework, or raw ADO.NET – Steve Cooper Apr 15 '16 at 16:36
  • I'm connecting via ADO.NET – t.c. Apr 18 '16 at 07:33
  • Then I'm afraid I've drawn a blank! All I have left is: ramp the service plan on the website to a P4 for ten minutes and see if the problem still occurs. If it does, it's probably network. If it goes away, you are resource-bound on the we server side. – Steve Cooper Apr 18 '16 at 07:38
  • 1
    That's not a problem, thank you for your time thinking about this. That sounds like a good approach to try. We're also going to make sure everything is in the same Resource Group and look through the logs from the Query Performance Insight to improve performance. – t.c. Apr 18 '16 at 09:06