SQL Azure - very slow compared to localhost database

Question

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?

Answer 1

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

Answer 2

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.