2

I decided I wanted to try out Microsoft SQL Azure, as many people have talked very highly about it. It should be fast, flexible, cheap and many other things.

I got it up and running, migrated my data to Azure and hooked up the connectionstring. I tried to run some queries on the database, and was shocked about how slow even simple queries were. A "SELECT *" from a table with 700 rows took 7 seconds. My page also seems extremely slow, compared to when I used a localhost managent studio or a database on a shared hosting.

Now, when I setup my server, I couldn't pick a physical position. However, I live in Denmark, and I can see the server is the "South Central US". This might be the issue.

I don't use any stored procedures (so I guess no parameter sniffing).. I can also see my indexes is transfered succesfully.

Any ideas on what to do? Any performance things I am missing?

Lars Holdgaard
  • 9,496
  • 26
  • 102
  • 182
  • 5
    You're pulling data over an Internet connection, versus accessing the data locally. In addition, Azure is a shared resource. What did you expect to happen? – Robert Harvey Oct 30 '12 at 22:31
  • 1
    You may want to re-create your database in one of the European data centers http://matthew.sorvaag.net/2011/06/windows-azure-data-centre-locations/ – Adam Wenger Oct 30 '12 at 22:31
  • Select * from Table might be a simple query, but your problem might be how much data it's dragging back. I'd try and avoid that one on localhost, never mind as far away as this. Chop it down down to select SomeColumn FRom SomeTable Where SomeKey = Value. Run it a number of times, to make sure everything has woken up. Then start widening the selet and pulling mor rows back. THere's no way you are not optimising for this set up, need a good gauge of the charateristics – Tony Hopkinson Oct 30 '12 at 22:53
  • I expected the speed to be at least acceptable. 5-10 Seconds to load simple queries is unaccepted.. It must be a setup problem - Azure is used by a lot of companies, and if this was a normal problem, no one would use it. I recreated the DB in North Europe.. I will continue troubleshooting – Lars Holdgaard Oct 31 '12 at 12:57
  • I would also add in a profiler to your website, see if you have any N+1 problems. http://code.google.com/p/mvc-mini-profiler/ – Arran Oct 31 '12 at 15:37
  • Also Azure database does not cluster index. You need to change the indexing to a supported format. – Piotr Kula Apr 16 '13 at 08:45
  • 1
    Azure's performance is terrible. I literally cloned a database and loaded it locally. On azure, the query took over 500 seconds to complete. Locally, it only took 10 seconds. It's processing hundreds of thousands of rows, but only returning 4 counts, so it's not an internet connection issue.... the query itself is running way too long on Azure. – Triynko Sep 17 '15 at 04:19
  • Possible duplicate of [Why is running a query on SQL Azure so much slower?](https://stackoverflow.com/questions/31086778/why-is-running-a-query-on-sql-azure-so-much-slower) – Michael Freidgeim Feb 17 '18 at 20:36

2 Answers2

3

I ran into this very issue the last few days. Change your database tier from basic to standard and you will see a HUGE increase in performance. I am working on a query intensive dashboard at the moment, it took a 20 sec response time down to 2 sec response.

JReam
  • 898
  • 2
  • 13
  • 28
3

I've used Azure now for the last many years, and my original question is pretty much solved.

My main take-aways after dealing with Azure databases for a while:

  • It's extremely important that your application and database is placed in the same region. If not, then you will have a slow application. Recently I had an API and app running on two different regions - it took ~1 second for every response.. After moving it to same, it was instant
  • If your application has a high load, it's often a good idea to upgrade. This happens earlier than you might expect
  • Pick the nearest region - it really matters
Lars Holdgaard
  • 9,496
  • 26
  • 102
  • 182