What am I doing wrong? (horrible performance)

Question

I have a community site so it's database driven.  I cache as much as I can, but there's a lot things that need to be real-time.

I setup a host in North US and a database in North US.  It was just a test database without most of the data so instead of 16GB it was 55MB.  Trying to load pages was painfully slow (several seconds).  Even refreshing them.  So I I created a system check page where I just do a simple cache time test and a database time test.  For a simple select on clustered index (at least it was clustered when I transferred it using that free SQL Azure Migration tool from Codeplex) it bounces from between 1.5 seconds and .5 seconds.  I understand there's latency on the initial connection so I stored the LINQ context in the app cache (I read that some where so I gave it a try).  If I hit my own live database on my dedicated server from Windows Azure it's a lot faster, like under 150ms, but that's still too slow.  On my dedicated server itself hitting its own database it's under 15ms with 16GB of data.

Is there anything I can do? Is this abnormal?  BTW, my cache test was extremely fast.  Same speed as my dedicated server.  It's just the database that's very slow.

Answer 1

Hi Greg,

The first thing I would do is to update the statistics on your tables (http://msdn.microsoft.com/en-us/library/ms187348.aspx).

Also, make sure your indexes are present in the SQL Azure database. Try rebuilding them as well (http://msdn.microsoft.com/en-us/library/ee336278.aspx).

What is very likely is that the execution plan chosen by your local SQL Server database is very different than your SQL Azure database. Understanding why is the objective for performance tuning. It could be missing indexes, statistics not being correct (typical after loading a lot of data) or other conditions.

So if the above doesn't work for you I would suggest you try to extract the actual SQL statement being executed by Linq using profiler against your on premise database (profiler won't work against SQL Azure). Profiler will give you the SQL statement your are trying to execute; you can then use SQL Server Management Studio to execute that statement against your local server and SQL Azure so you can compare the execution plan on each. So if the above doesn't work, reply to this post and we will be happy to walk you through the steps.

 

 

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com

Answer 2

Thanks for the suggestions, I will re-setup my test (I had already removed it) and do those things.

Answer 3

Hi Greg,

Use Context.GetCommand(QueryObject).CommandText method to get the query from Linq to SQL and then run the query through Management Studio

Ex. context.GetCommand(query).CommandText

or Alternatively use Context.Log option as well to log all queries generated, Please read this article for more information 

http://damieng.com/blog/2008/07/30/linq-to-sql-log-to-debug-window-file-memory-or-multiple-writers

Hope this helps !!!

---

Arunraj Chandrasekaran, MCTS, Author: SQLXpertise.com
If you found this post useful, Please "Mark as Answer" or "Vote as Helpful"

Answer 4

Turning statistics on before the query and off after I can see that the query is indeed executed very fast.  It's the time it takes to send the request and get the response back that seems to be the problem.  I guess fewer database calls is what is needed.

Answer 5

Greg - How long did your query take to execute and to return the data? How many records are you returning?

Remember that *all* traffic is encrypted against SQL Azure, but not with SQL Server (by default). What this means is that you can have significantly more packets going through when making a request against SQL Azure than you would against SQL Server for large data sets. So the query may be increasingly slower with more data being returned. And of course, as you point out, the more roundtrips you issue, the slower it will be too, for the same reason.

Regarding the statistics, I meant updating your statistics; not turning on statistics before or after a query. Take a look at the link I sent you in my previous post related to the UPDATE STATISTICS command.

Since you are not providing any details (no SQL, no Linq, no performance metrics) there is very little we can do. Nevertheless, if you feel like digging deeper, take a look at this tool; it can provide you side-by-side comparison of SQL Server and SQL Azure performance metrics. The information returned will not provide any answers, but pointers that might be useful to you:  http://enzosqlbaseline.codeplex.com 

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com

Answer 6

I did update statistics and I rebuilt indexes as you said.  I didn't think it was necessary to provide SQL or LINQ because I determined that the database wasn't what was actually slow.  It seems to be slow returning the results and the number of roundtrips.

I ran tests using Enterprise Manager on my local machine, the Database Manager, and by using the site in a test page.  I used actual queries the site creates.  The database said it executed it fast, the site showed it took too long.

I didn't keep a detailed test log as I normally would.  My current server now has a bad block so I've been distracted with dealing with that.  I'll come back to this in the future with more detailed information about the tests and a better test web role.