Friday, November 13, 2009

Why I Hate Entity Framework

A few months ago I took over development of an insurance-related system. It was a Windows Forms application created for a small workgroup. Original design utilized ADO.NET Entity Framework and it was my first serious encounter with the technology. Immediately, I sensed that something wasn't right: application was too slow. There were only a dozen or so tables and hardly any data in them, yet forms took whole seconds to load. Database updates were even more problematic - not only they took long time, but sometimes they failed for no apparent reason.

Initially, I tried to write it all off as part of a learning curve. I recreated entity model and ran a SQL Profiler in an effort to better understand the technology. Profiler results were simply shocking: instead of executing a half-dozen or so SELECT statements in response to user opening a record, Entity Framework generated hundreds of them (trace file was over 1Mb in size).

So, I remembered the little performance test I did for LINQ to SQL about two years ago and decided to expand it to include Entity Framework. New method looks similar to the one used to test LINQ to SQL:
private TimeSpan RunEntityTest()
{
var swatch = Stopwatch.StartNew();
NorthwindEntities db = new NorthwindEntities(
ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString);

for (int orderId = 10248; orderId < 11078; orderId++)
{
var query = from o in db.Orders
where o.OrderID == orderId
select new
{
o.OrderID,
o.OrderDate,
o.Customers.CustomerID,
o.Customers.CompanyName,
ProdCount = o.Order_Details.Count
};

foreach (var item in query)
{
string s = item.CompanyName;
}
}
return swatch.Elapsed;
}
By the way, that original blog post has been criticized by an anonymous guest, who pointed out that my test routines for stored procedures, dynamic SQL and parameterized SQL were not equivalent to LINQ test, because I never read any values from SqlDataReader object after opening it. So, I changed the code in all three methods as follows:
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
string s = Convert.ToString(dr["CompanyName"]);
}
dr.Close();
I'm glad to say that this change didn't make any difference to test results: LINQ to SQL is still 40 times slower than stored procedures or dynamic SQL. But Entity Framework results were much worse: 2.2 times slower than LINQ to SQL. Here is the full table:

METHODAVERAGE TIME, ms
Stored Procedure110
Dynamic SQL115
Parameterized Dynamic SQL126
LINQ to SQL4,699
Entity Framework10,547

So, in the end, I rewrote the data layer of the application using LINQ to SQL.
Updated test harness code is available here: http://members.cox.net/rmamedov/blog/EntityFrameworkTestHarness.zip

4 comments:

genya said...

Interesting Article. We did re-write everything related to entity framework as well. Question to the author: stored procedure implementation was a level of magnitude more efficient then LINQ to SQL if you believe the matrix. Why improvement was re-written with LINQ?

RM said...

This was essentially a pragmatic decision. Rewriting data layer with stored procedures would have taken me more time than with LINQ to SQL. On the other hand, the application itself was designed for small workgroup, and LINQ performance was acceptable.

Rajin Kumar said...

Your query have a seriuos problem. Before you pass any query result a loop, should force the EF to execute the query otherwise the query execution takes place inside the loop. To for EF to execute query you could try a "ToList() or ToArray()" on the "query" object the code may be rewrite as



private TimeSpan RunEntityTest()
{
var swatch = Stopwatch.StartNew();
NorthwindEntities db = new NorthwindEntities(ConfigurationManager.ConnectionStrings["NorthwindEntities"].ConnectionString);
for (int orderId = 10248; orderId < 11078; orderId++)
{
var query = from o in db.Orders
where o.OrderID == orderId
select new { o.OrderID, o.OrderDate, o.Customers.CustomerID, o.Customers.CompanyName, ProdCount = o.Order_Details.Count };
var result = query.ToList();
foreach (var item in result)
{
string s = item.CompanyName;
}
}
return swatch.Elapsed;
}

Anonymous said...

Wholeheartedly Agree. I have found the Entity Framework a solution in search of a problem. I keep hearing people tout it as so much faster than having to write your own DAL and stored procedures, but they all seem to happily ignore the framework's rigidity and SLOWNESS, which compared to rolling your own become simply swapping one set of problems with another. Is any time gained? I haven't found any, nor any other benefits.