Azure SQL / EF performance issues

6
votes

Recently I have run into some pretty weird performance issues with SQL Azure / Web Apps / Entity Framework.

It would appear that occasionally calls to the database (both read and write queries) would hang for anywhere between a few seconds and a few minutes (!). This happens even on a select query on a table with 4 columns containing 5 rows of data.

The issue appears to happen at random and is not reproducible. Upgrading DB to a higher performance tier seems not to have any effect. Both the web app and the sql azure database are in the same region.

DB performance graph is generally flat lining around 0.5% of resource utilization with an occasional spike to around 5% - so the issue certainly does not lie with the resource constraints.

I would have no clue how to start investigating the issue given the intermittent nature of it. I would greatly appreciate any feedback. Last week the issue

Could it have something to do with the way Entity Framework handles the DB connections specifically to the sql azure? Testing on the local SQL express has never caused anything similar.

2
entity-frameworkazure-sql-databaseazure-web-app-service
"It would appear that occasionally calls to the database (both read and write queries) would hang for anywhere between a few seconds and a few minutes (!)" You need to use some tool like sql nexus and check whats happening in database for over a period of time. – TheGameiswar
Am on the same boat, let me know the possible tricks/fix – Jaya
I have not yet figured out a solution or the cause. I will definitely share when I do figure it out – Nick Goloborodko
Ok one thing that I have tried and it looks like it has made a huge different in the performance is blogs.msdn.com/b/adonet/archive/2010/06/09/… – Nick Goloborodko
Infact - thi may just be the solution that I was looking for! – Nick Goloborodko

2 Answers

3
votes

After battling performance issues with Entity Framework, we finally switched over to Dapper and saw a huge increase in performance. They have some benchmarks on their GitHub page showing the speed difference.

https://github.com/StackExchange/dapper-dot-net

Also, I am unsure what version of EF you are using, but if it is EF Core, the performance of that is currently worse than that of previous versions. Another comparison of performance can be found here: https://www.exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/.

0
votes

Ok, so looks like I have found a solution for my performance issues - and it as as simple as enabling Multiple Active Result Sets (https://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.110).aspx) for the conenction.

There must have been some change in the EF framework from around 2 years ago - as the issue has only arisen after I have upgraded to 6.1.3. I'm not sure what the original version was - but it was which ever one was current 2 years ago.

I hope this helps someone else. It has caused me a lot of grief and cost a large potential project to fall through.