ADO.NET Entity Framework Performance Comparison

There have been a few questions from the last performance blog post about how the Entity Framework compares against other object relational mapping frameworks. The simplest way to compare the performance of Entity Framework with various competing products is to use query performance against SqlClient as a benchmark. This way, anyone can run the same query benchmarks against SqlClient with your current ORM product.

Here are the last two performance blog posts:

· Exploring Performance Part 1

· Exploring Performance Part 2

Here’s the configuration that I used to run my tests:

· Visual Studio 2008.

· SQL Express (installed with Visual Studio).

· ADO.NET Entity Framework Beta 3.

· Entity Framework Tools December 2007 CTP.

· A C# console application built under the release mode configuration.

· I’m using is Northwind as my database.

· I’m running on my laptop, which is a dual core 2GHz processor with 3GB of RAM.

To create a benchmark for an ORM, we must read the data rows returned by the SqlDataReader into objects. It is not enough to simply iterate over the reader I used the same model as my previous performance blog post. Here’s the full EDM, but I really only used the Orders EntitySet.

 

To demonstrate performance in a slightly differently way, I added some paging queries to the scenarios from the previous posts. In the other performance blog posts, I compared tracking versus no tracking queries, and in these examples I am only showing the NoTracking cases. Objects are tracked when queried, added, or attached to the cached references inside a class owned by the ObjectContext called the ObjectStateManager. This tracking behavior is specified using the MergeOption. When updates to properties of the tracked objects occur, the properties are marked as modified and the original values are kept for performing updates back to the database. This enables users to simply write code against the objects themselves and call SaveChanges. The Entity Framework takes care of all the “heaving lift” of updating the database.

Query

For SqlClient queries, I created a class for Orders and get/set methods for properties. Here’s an example of the OrderID property. This is as simple as I can make it.

public int OrderID

{

    get

    {

        return _OrderID;

    }

    set

    {

        _OrderID = value;

    }

}

 

Here’s the SqlClient query I preformed. In the query, I specified each column individually instead using “*”. I wanted the comparison to include setting property values because that’s one of the benefits of using the Entity Framework.

SqlConnection con = new SqlConnection(connectionString);

con.Open();

SqlCommand cmd = con.CreateCommand();

string cmdText = "select CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAddress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, ShipVia from Orders order by OrderID";

cmd.CommandText = cmdText;

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

    Order o = new Order();

    if (!dr.IsDBNull(0)) o.CustomerID = dr.GetString(0);

    if (!dr.IsDBNull(1)) o.EmployeeID = dr.GetInt32(1);

    if (!dr.IsDBNull(2)) o.Freight = dr.GetDecimal(2);

    if (!dr.IsDBNull(3)) o.OrderDate = dr.GetDateTime(3);

    if (!dr.IsDBNull(4)) o.OrderID = dr.GetInt32(4);

    if (!dr.IsDBNull(5)) o.RequiredDate = dr.GetDateTime(5);

    if (!dr.IsDBNull(6)) o.ShipAddress = dr.GetString(6);

    if (!dr.IsDBNull(7)) o.ShipCity = dr.GetString(7);

    if (!dr.IsDBNull(8)) o.ShipCountry = dr.GetString(8);

    if (!dr.IsDBNull(9)) o.ShipName = dr.GetString(9);

    if (!dr.IsDBNull(10)) o.ShippedDate = dr.GetDateTime(10);

    if (!dr.IsDBNull(11)) o.ShipPostalCode = dr.GetString(11);

    if (!dr.IsDBNull(12)) o.ShipRegion = dr.GetString(12);

    if (!dr.IsDBNull(13)) o.ShipVia = dr.GetInt32(13);

    int i = o.OrderID;

}

con.Close();

 

When I ran this code 100 times, the average execution time was 4 milliseconds. I threw out the first run because of the one-time costs associated with connecting to the database and generating an execution plan. The first run time was 451 milliseconds.

Now moving over the Entity Framework, I performed the same query. I’m using compiled LINQ queries. Here’s the query that I performed.

public static Func<NorthwindEntities, IQueryable<Order>>

       compileFullQuery = CompiledQuery.Compile(

                                   (NorthwindEntities ne) =>

                                               (from o in ne.Orders

                                                select o)

                                   );

 

using (NorthwindEntities ne = new NorthwindEntities())

{

    foreach (Order or in compileFullQuery(ne))

    {

        int i = or.OrderID;

    }

}

 

When I ran this compiled LINQ query 100 times, the average execution time was 7.5 milliseconds. Again, I threw out the first execution at 1782 milliseconds.

Using an Entity SQL query instead of a LINQ query, I got slightly better times. Here’s the code for the query.

using (NorthwindEntities ne = new NorthwindEntities())

{

    ObjectQuery<Order> ordersQuery

              = ne.CreateQuery<Order>("SELECT VALUE o FROM Orders as o");

    foreach (Order or in ordersQuery.Execute(MergeOption.NoTracking))

    {

        int i = or.OrderID;

        count++;

    }

}

The average time after 100 runs, minus the first run, was 6 milliseconds. This time, the first execution was only 389 milliseconds. Here’s a chart that summarizes the time.

These are Beta 3 times and we are trying to make execution faster all the time. Now that we have a baseline comparison, I moved to a more common query in Web scenarios that use paging. Most folks are not going to query all the rows in a database to bind to some control, but a more realistic scenario would be to just query for a few entities at a time until all the entities are returned.

Paging Query

Paging is used to retrieve the data in chunks, such as many of us are used to when browsing for products to buy or search through the results of a Web search. For example, when I go to www.Live.com and search for “ADO.NET Entity Framework,” I may get a few million results, but I don’t want to see all those results in one web page. Instead, I get back chucks of 20 results on a page. Paging also enables me to skip to a particular page and get the result from a range of 20. In SqlClient, I can do this using the example below, where I just loop through this code for each page count and get 20 items at a time until all the 848 rows are consumed. That’s 43 queries to get all the data in more manageable pages.

SqlConnection con = new SqlConnection(connectionString);

con.Open();

SqlCommand cmd = con.CreateCommand();

string cmdText = "select TOP 20 CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAddress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, ShipVia from Orders where OrderID > @lastID order by OrderID";

cmd.CommandText = cmdText;

SqlParameter parameter = new SqlParameter("lastID", _nLastID);

cmd.Parameters.Add(parameter);

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

    Order o = new Order();

    if (!dr.IsDBNull(0)) o.CustomerID = dr.GetString(0);

    if (!dr.IsDBNull(1)) o.EmployeeID = dr.GetInt32(1);

    if (!dr.IsDBNull(2)) o.Freight = dr.GetDecimal(2);

    if (!dr.IsDBNull(3)) o.OrderDate = dr.GetDateTime(3);

    if (!dr.IsDBNull(4)) o.OrderID = dr.GetInt32(4);

    if (!dr.IsDBNull(5)) o.RequiredDate = dr.GetDateTime(5);

    if (!dr.IsDBNull(6)) o.ShipAddress = dr.GetString(6);

    if (!dr.IsDBNull(7)) o.ShipCity = dr.GetString(7);

    if (!dr.IsDBNull(8)) o.ShipCountry = dr.GetString(8);

    if (!dr.IsDBNull(9)) o.ShipName = dr.GetString(9);

    if (!dr.IsDBNull(10)) o.ShippedDate = dr.GetDateTime(10);

    if (!dr.IsDBNull(11)) o.ShipPostalCode = dr.GetString(11);

    if (!dr.IsDBNull(12)) o.ShipRegion = dr.GetString(12);

    if (!dr.IsDBNull(13)) o.ShipVia = dr.GetInt32(13);

    // just do one response to make sure we have a get

    _nLastID = o.OrderID;

    count++;

}

con.Close();

 

 

When I ran this loop that consumed all the data in pages of 20 rows, the time averaged 18 milliseconds on 100 tries. Each iteration made 43 paging queries to consume all 848 rows. The first run was slower at 453 milliseconds and as before, I didn’t use that number in my average calculations. This means that every paging query took less than 1 millisecond; in fact when just doing the math, the time is rounded to 0.419 milliseconds.

Now let’s see how LINQ to Entities and Entity SQL compare to SqlClient.

Here’s the paging query for LINQ to Entities, again using compiled queries. (If somehow you managed not to notice how much simpler and cleaner the LINQ code is over that SqlClient then take a moment now to enjoy this much easier way to query and materialize data.)

public static Func<NorthwindEntities, int, IQueryable<Order>>

        compiledQuery = CompiledQuery.Compile(

                                    (NorthwindEntities ne, int lastCount) =>

                                        (from o in ne.Orders

                                         orderby o.OrderID

                                         select o).Skip(lastCount).Take(20)

                                    );

 

using (NorthwindEntities ne = new NorthwindEntities())

{

    ne.Orders.MergeOption = MergeOption.NoTracking;

    foreach (Order o in compiledQuery(ne, _dbTotalRead))

    {

        int i = o.OrderID;

    }

}

 

The time for a full read over 100 iterations averaged 87 milliseconds, ignoring the first run. This equates to an average of 2 milliseconds per query. Below is the paging query expressed in Entity SQL.

using (NorthwindEntities context = new NorthwindEntities())

{

    ObjectQuery<Order> orders

        = context.CreateQuery<Order>("select value o from Orders as o order

                                     by o.OrderID skip @skip limit 20",

                                     new ObjectParameter("skip",

                                                         _dbTotalRead));

    orders.MergeOption = MergeOption.NoTracking;

    foreach (Order o in orders)

    {

        int i = o.OrderID;

    }

}

 

I ran this test 100 times; the average time was 64 milliseconds ignoring the first run. This averages about 1.5 per query, about 3 times that of SqlClient and better than LINQ to Entities.

With any framework, we need to consider the benefits and risks, and as you continue to use the Entity Framework and EDM, you’ll find it has quite a bit of benefits.

 Brian Dawson
Program Manager, ADO.NET