Thursday, January 8, 2015

Entity Framework DbContext vs ObjectContext vs LINQ2SQL vs ADO.NET vs Business Logic Toolkit Performance

With Entity Framework Microsoft recommends using DbContext instead of ObjectContext. So let's compare their performance.

These tests were performed on i5-4200H, DDR3-1600, Win 8.1 x64, SQL Server 2014, VS 2013, .NET 4.5, EF 6.1.2. Default Northwind database was used.

Tests include two different queries (simple, complex) and two lengths (10, 500 rows). Simple query:

SELECT TOP 10 O.OrderID, O.OrderDate, C.Country, C.CompanyName
FROM Orders O
JOIN Customers C ON O.CustomerID = C.CustomerID

Complex query:

SELECT TOP 10 OD.Quantity, OD.UnitPrice, OD.Discount, O.ShipCountry, S.Country
FROM Orders O
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
JOIN Products P ON OD.ProductID = P.ProductID
JOIN Categories Cat ON P.CategoryID = Cat.CategoryID
JOIN Suppliers S ON P.SupplierID = S.SupplierID
WHERE
    Cat.CategoryID IN (@categoryIds)
    AND S.SupplierID IN (@supplierIds)
ORDER BY OD.Discount DESC

There were 6 methods tested to work with database:

  1. DbContext CodeFirst (generated from DB)
  2. DbContext Designer (generated from DB)
  3. ObjectContext (generated from DB with EdmGen.exe)
  4. LINQ2SQL
  5. ADO.NET
  6. Business Logic Toolkit (raw SQL query)

Each method was tested with 1000 iterations (and 100 iterations to warm up).

Context Initialization

Since context initialization can't be measured directly, it was measured in the following way. Let say we executed a query:

using (var ctx = new MyContext())
{
    var list = ctx.Products.Where(r => r.Name.Length < 10).ToList();
}

then if we executed this query twice:

using (var ctx = new MyContext())
{
    var list = ctx.Products.Where(r => r.Name.Length < 10).ToList();
    var list2 = ctx.Products.Where(r => r.Name.Length < 10).ToList();
}

we got a system of linear equations:

q + ctx = x
2*q + ctx = y

and now it's easy to find context initialization time:

ctx = 2*x - y

Context initialization was measured using "Simple TOP 10" query.

Context initialization time for DbContext CodeFirst and Designer is nearly the same, while ObjectContext requires twice more time. ADO.NET and BLToolkit have nearly the same minimum time, thrice lower than DbContext. LINQ2SQL has twice lower time than DbContext.

But as you can see below context initialization time in absolute time doesn't make a big sense always.

Simple TOP 10 query

For the simple query with a few rows where request to database takes little time, EF query compilation takes almost all the time for DbContext and ObjectContext. LINQ2SQL takes twice more time than EF, because its mapping is slow (I'll tell why I think so below in "Complex TOP 10" test). BLToolkit takes slightly more time than ADO.NET. And I don't know why Precompiled ObjectContext takes less time than ADO.NET :) (but remember, this time is without context initialization). DbContext doesn't support precompiled queries at all.

With context initialization:

Simple TOP 500 query

Simple TOP 500 query takes more time to request data from database. This is the reason why DbContext and ObjectContext take only one half time more than ADO.NET, and third time more than BLToolkit and precompiled ObjectContext query.

With context initialization:

Complex TOP 10 query

Complex TOP 10 query has similar situation: EF query compilation time is comparable to query request to databse. This is why DbContext and ObjectContext takes only twice more time that ADO.NET and BLToolit.

As you remember, LINQ2SQL in "Simple TOP 10" test took more time than EF. And in this test it takes less time. We can suppose that query execution time() consists in the following steps:

  1. Context initialization - ctx()
  2. Query compilation - comp()
  3. Request to database - db()
  4. Mapping result - map()
Below is a little math :), where "1" is "Simple TOP 10" query, and "2" is this "Complex TOP 10" query.

time(L1) = ctx(L) + comp(L1) + db(1) + map(L1)
time(L2) = ctx(L) + comp(L2) + db(2) + map(L2)
time(EF1) = ctx(EF) + comp(EF1) + db(1) + map(EF1)
time(EF2) = ctx(EF) + comp(EF2) + db(2) + map(EF2)

ctx(L) = 0.09
ctx(EF) = 0.17
time(L1) = 0.87
time(EF1) = 0.55
time(L2) = 8.8
time(EF2) = 10.5

=>

0.87 = 0.09 + comp(L1) + db(1) + map(L1)
8.8 = 0.09 + comp(L2) + db(2) + map(L2)
0.55 = 0.17 + comp(EF1) + db(1) + map(EF1)
10.5 = 0.17 + comp(EF2) + db(2) + map(EF2)

=>

0.78 = comp(L1) + db(1) + map(L1)
7.9 = comp(L2) + db(2) + map(L2)
0.38 = comp(EF1) + db(1) + map(EF1)
10.33 = comp(EF2) + db(2) + map(EF2)

db(1) << (db2)
comp(L1) << comp(L2)
comp(EF1) << comp(EF2)
map(L1) ~= map(L2) = map(L)     // we can suggest this because both queries have 10 rows
map(EF1) ~= map(EF2) = map(EF)  // we can suggest this because both queries have 10 rows

=>

0.78 = comp(L1) + db(1) + map(L)        // (1)
7.9 = comp(L2) + db(2) + map(L)         // (2)
0.38 = comp(EF1) + db(1) + map(EF)      // (3)
10.33 = comp(EF2) + db(2) + map(EF)     // (4)

db(1) << (db2)
comp(L1) << comp(L2)
comp(EF1) << comp(EF2)

=> Let subtract (3) from (1), and (4) from (2)

0.4 = comp(L1) - comp(EF1) + map(L) - map(EF)         // (1)
-2.43 = comp(L2) - comp(EF2) + map(L) - map(EF)       // (2)

comp(L1) << comp(L2)
comp(EF1) << comp(EF2)

=> Let subtract (2) from (1)

2.83 = comp(L1) - comp(EF1) + map(L) - map(EF) - comp(L2) + comp(EF2) - map(L) + map(EF)

comp(L1) << comp(L2)
comp(EF1) << comp(EF2)

=>

2.83 = comp(L1) - comp(EF1) - comp(L2) + comp(EF2)

comp(L1) << comp(L2)
comp(EF1) << comp(EF2)

=>

comp(L2) - comp(L1) + 2.83 =  comp(EF2) - comp(EF1)    // (1)

comp(L1) << comp(L2)                                   // (2)
comp(EF1) << comp(EF2)                                 // (3)

=> Using comparisons (2) and (3)

comp(L2) + 2.83 ~=  comp(EF2)

So, we can say that LINQ2SQL takes lower time for compilation than EF. And if so, then EF takes lower time to map results. As I said above.

With context initialization:

Complex TOP 500 query

Complex TOP 500 query shows the same results as Complex TOP 10: time to request from database is comparable to compilation time, therefore DbContext and ObjectContext take only twice more time that ADO.NET and BLToolit.

With context initialization:

Conclusions

  • Context initialization for DbContext CodeFirst is slightly faster than for DbContext Designer (both generated from database).
  • Context initialization for ObjectContext is twice slower than for DbContext. But absolute time is not significant - 0.4 ms versus 0.2 ms.
  • LINQ2SQL can be faster than EF for complex queries, and also it can be precompiled for some queries.
  • EF has much faster mapping than LINQ2SQL.
  • ObjectContext is a bit slower than DbContext, but some queries can be precompiled (Parameters cannot be sequences).
  • BLToolkit doesn't provide compilation-time type checking, but it's fast nearly as ADO.NET and it has great mapping capabilities (this article is in Russian (main site is currently down) but you can understand a bit from code samples).

Raw results (XSLT).

View project source code at Bitbucket.

No comments:

Post a Comment