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:
| METHOD | AVERAGE TIME, ms |
| Stored Procedure | 110 |
| Dynamic SQL | 115 |
| Parameterized Dynamic SQL | 126 |
| LINQ to SQL | 4,699 |
| Entity Framework | 10,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:
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?
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.
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;
}
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.
Post a Comment