CODEWITHSUNDEEP
entity-frameworkazure-sql-databaseazure-web-app-service
Nick Goloborodko
Nick Goloborodko

Reputation: 3053

Azure SQL / EF performance issues

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.

Upvotes: 6

Views: 1105

Answers (2)

Nick Goloborodko
Nick Goloborodko

Reputation: 3053

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.

Upvotes: 0

Justin Patten
Justin Patten

Reputation: 1069

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/.

Upvotes: 3

Related Questions