Azure SQL database performance

I have an ASP.NET MVC website running on Azure with a backend Azure SQL Server database. The entity framework is in use. Done a bit of work figuring out the performance of the Azure database in this scenario.

Components:

Local setup
Intel i7 - 4770 3.4GHz, 8Gb, windows 7 64bit
SQL Server 2012
Visual Studio 2013, debug mode website running on IIS express

Azure Setup:
(Testing/usage recorded are between Jan and March 2015 on North Europe)
Azure Website (various sizes)
Azure SQL Database V11 (Various sizes)

Network:
Standard broadband connection (about 18Mb down, 750K up, shared with other users on company network)

Test 1:

Azure performance using SQL Management Studio
Running a query that pulls together several tables for 120K items. The resultant data size is 38Mb.
These are the timings for running a query in SQL Management studio and displaying all the results.
Note: The Azure timings will include d/l over a broadband connection.

On a local SQL server it took <2secs to process and display
On a S0 10DTU Azure SQL Database it took between 45s and 1m20s
On a S1 20DTU Azure SQL Database it took around 16s

The DTU has a significant impact if large data sets are being pulled.

Test 2:

These are the timings for an operation on the website that pulled a large set of data with entity framework then performed some heavy processing of that data. Doesn't include sending data to client browser. The data in the database is a combination of several tables and around 40Mb of data.
The times are approximate and the 'SQL' timings are done using the SQL Management studio locally rather than the website.

Local website and Local database

  • Total = 11sec
  • SQL = 2sec
  • .ToList (inc SQL) = 10.5s (maybe 8secs of entity processing time)
  • Processing = 0.5s

Azure website and Azure database (S1 20 DTU, Basic Website)

  • Total = 33sec
  • SQL = 19sec (SQL Management studio include d/l time for data. Having only the ID field <2secs)
  • .ToList = 32s (Maybe 13s of entity time)
  • Processing = 0.8s

Changing Database to S2 50DTU

  • Total = 32sec
  • SQL = 20sec

Changing Azure Website to Medium 2core 3.5Gb and Azure DB to S2 50DTU made no noticeable difference.
Changing Azure Website to Medium 2core 3.5Gb and Azure DB to S1 20DTU Gave 27secs the following day.

Changing Azure Website to Large and Azure DB to S1 20DTU Gave 25secs.
Changing Azure Website to Small and Azure DB to S1 20DTU Gave 29secs.

Test 3:

Another case where the operation was taking 8s in 20DTU and 2.6s in 100DTU. Again this was large data pulling.

Conclusion

From all my testing and running the website I came to the following
1) The processing ability of website and database seem to be pretty good performance and not that far off that when running locally to panic. (Excluding data transfer)

2) For my data processing needs upping the website from Basic made not that big a difference and not worth the extra cost.

3) The amount of data transferred looks like the primary cause of the bottlenecks. The amount of data passed between the Azure website and the Azure database is very important. The database DTU has a big impact on this. (NOTE: It is the data transfer between the azure systems not the website to client data transfer)
It appears almost like having some sort of throttle that will slow the data transfer between the systems and drag out the time. Having higher DTU will reduce this.

Improving speed

Taking the conclusion that the data transferred between Azure DB and Website then trying to limit that data is important.
I took the following steps which all made a drastic reductions in times. Going from minutes to a few seconds.

1) Making sure any entity calls to get data are limited to what you need for your task.
For example my database may contain 50 fields but I have a display that just needs a name, description. Just pull out that few fields with something like the following lambda .Select() which will create a SQL that only returns the 4 fields and not the whole lot.

_uow.EQTemplates.Where(x => x.EQid == eqId).Select(x => new TemplateViewModel {  
    EQid = x.EQid,
    Id = x.Id,
    Name = x.Template,
    Description = x.Description,
});
//the EQTemplate is my entity DbSet

2) When using the Telerik Kendo grid control then make sure in the controller anything passed to the ToDataSourceResult is an IQueryable. This will ensure that when it resolves the list, the call to the SQL Database will have all the grid filtering applied and thus limiting the amount of data pulled to the website. (This is assuming you have done anything that has triggered the entity query off like a .ToList() before the ToDataSourceResult)
This applies to any similar Telerik UI

3) If there is processing between the website page viewed data and the database then big pulls of data for running calculations may still be necessary regardless of any user grid filtering.
In this scenario I made the following changes that brought
- local processing times from 10 secs to 1second.
- Azure processing times from 1 minute to 4 seconds.

A vast improvement.
a) When extracting the data for processing only pull out the fields that are necessary. (like point 1 above)
b) If the data has foreign key fields where the link was just to get the text for an ID then this was excluded and only the id returned. A hash table in the processing could pull in the required text instead.
Something like

Dictionary<int, string> abbrhash = uow.TagAbbreviations.Where(x => x.ProjectId == projectId).ToDictionary(x=>x.Id,x=>x.Name);  

Will produce the a dictionary with the id to text look up that can be used in the processing.
Originally I had 100K rows of data with a foreign key text of around 30 characters so instead it just returns the integer id and saves Mb of data transfer. The performance hit of using the hashtable during processing was very small and far outweighed by the reduction in overall time.

4) Use the SQL Server profiler or similar tool to check the SQL being run. Optimise as required.

With all the above I happily run a quite heavy data based website on a Basic Azure Website with a S1 20 DTU Database.

DTU Monitor

The Azure monitor was showing 25% DTU usage which looked like it had plenty of headroom.
I added one more .Include to pull in a Text for an ID and bang it maxed the DTU at 100% and the query went up by a minute.

So cant go by the monitor!

V12 database

I had done the above testing on a V11 database. Since then the V12 is available. I moved across and at the S1 20DTU level I am running at I have seen no performance change either way.

Bulk Inserts and Updates

The DTU level of the database has a big impact also when using entity for doing bulk inserts and updates of data. One insert operation was taken over 35 minutes. Upping the DTU dropped that down to a few minutes.
Problem is that the Entity Framework is not designed for doing bulk stuff so see blog entry -insertlink- on alternatives that are quick and don't need a high DTU.

References

Azure SQL Database Performance Guidance
http://msdn.microsoft.com/en-us/library/azure/dn369873.aspx


comments powered by Disqus