次の方法で共有

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

Comments

  • Anonymous
    March 27, 2008
    I'm sorry, but I don't think paging scenario is valid. You measuring totally different queries. If I not mistaken in case of EF there will be an inner query with row_number in it. Maybe you should use it in direct case too?

  • Anonymous
    March 27, 2008
    L'ADO .Net Team vient de poster deux nouveaux posts : le premier concerne l'utilisation des procédures

  • Anonymous
    March 27, 2008
    indeed, the paging query is totally different in the plain sql case. Also the queries used are the most simplest ones you can possibly imagine. What people will want to know is how the EF holds up in eager loading scenario's and nested queries like: var q = from c in ctx.Customers           where ..           select new { c.CustomerId, c.CompanyName, c.Orders }; is that as slow as linq to sql's? For these kind of queries people use an o/r mapper framework. For flat lists of data, one could write a wrapping layer with hardcoded sql queries in a few days anyway.

  • Anonymous
    March 27, 2008
    > Also the queries used are the most simplest ones you can possibly imagine. I think point of this post is to benchmark "plumbing" in EF, not quality of generated code. But that's really interesting area too, of course.

  • Anonymous
    March 28, 2008
    Parece que los chicos de ADO.NET se han puesto las pilas y han querido desmostrar con una comparación

  • Anonymous
    March 29, 2008
    The comment has been removed

  • Anonymous
    March 29, 2008
    Web:markItUp!-LightweightTextEditorCSSMessageBoxcollectionAbsolutePositioninginside...

  • Anonymous
    March 29, 2008
    Continuing his series on Entity Framework Performance, Brian Dawson compares performance of SQL Client

  • Anonymous
    April 03, 2008
    Thanks for the comments. I did do the typical paging query for t-sql, see below. I replaced the literal numbers with parameters, but the bad news is this query is slower than my orginal TOP with the index by 2x.  So either way this data is still pretty relavent, and I"m glad you mentioned the change. SELECT CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAddress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, ShipVia FROM (      SELECT CustomerID, EmployeeID, Freight, OrderDate, OrderID, RequiredDate, ShipAddress, ShipCity, ShipCountry, ShipName, ShippedDate, ShipPostalCode, ShipRegion, ShipVia, ROW_NUMBER() OVER(Order BY od.OrderID) as RowNumber      FROM dbo.Orders od ) Orders WHERE RowNumber > 10 AND RowNumber < 30 order by OrderID -Brian

  • Anonymous
    April 03, 2008
    During these initial performance posts, I wanted to stay simple with the queries. I'll continue to take feedback and my next post in a couple of weeks, I put down more challenging query numbers. Thanks, Brian

  • Anonymous
    April 14, 2008
    What about stored procedures?? Do you think it's valid to compare this result to SPs??

  • Anonymous
    April 15, 2008
    Hi! While the performance for beta 3 looks to be quite satisfactory (again considering all the other factors in ones call stack), I would just mention the performance of LINQ to SQL. The performance optimization by the LINQ to SQL team have been outstanding and that OR/M is incredible (only a few percentages slower than using a low level SqlDataReader). While the ADO.NET EF is an interesting choice for an OR/M, architechts looking for typed objects and the best performance should go with LINQ to SQL. Keep up the good work.

  • Anonymous
    May 13, 2008
    Entity Framework FAQ Version 0.4 – 5/13/2008 New in this version of the FAQ… · Major Update for VS 2008

  • Anonymous
    May 19, 2008
    The EF is obvious very very slow. I think MS has a big job ahead to improve it.

  • Anonymous
    June 11, 2008
    The Entity Framework enables developers to reason about and write queries in terms of the EDM model rather than the logical schema of tables, joins, foreign keys, and so on. Many enterprise systems have multiple applications/databases with varying degrees

  • Anonymous
    June 20, 2008
    The comment has been removed

  • Anonymous
    July 01, 2008
    Concurrently with the finalization of the initial LINQ release bits, community previews of complementary

  • Anonymous
    August 09, 2008
    Part of the Entity Framework FAQ . 18. Multi-threading 18.1. What is the recommendation for running a

  • Anonymous
    August 15, 2008
    Entity Framework（EF）存取Entity的三种方式。 LINQ to Entities 直接通过LINQ存取，可完全将程序与数据库分离，由LINQ在内部自动使用Object Se

  • Anonymous
    September 21, 2008
    &lt;p&gt;&amp;lt;p&gt;&amp;amp;lt;p&gt;&amp;amp;amp;#160;Esta semana fiquei curioso com rela&#231;&#227;o ao desempenho de aplica&#231;&#245;es que utlizam como camada de acesso a dados o&amp;amp;amp; ...

  • Anonymous
    November 04, 2008
    Here's an idea/thought for Microsoft: If there is an impedance between the ado.net layer and SQL Server that occurs when text sql is passed from the webserver/business object server to the database server, why not extend SQL Server's query compilation subroutines to the webserver/BO server? In that way, CPU involved with sql text query parsing and sql server command parsing, and query plan optimization can be offloaded to farmed webservers and BO servers, sparing the critical (& often singular) DB server some CPU cycles.  In addition, T-SQL syntax errors can be caught before passing off the query to the database, again sparing the performance hit if such syntax errors would need to be caught at the db server. The Entity Framework, Linq to SQL and other such data layers that engage in SQL text generation, would be an ideal place for further optimizations and tight coupling (with a driver?) with a database server. Of course, growing the database upwards into the framework would not be a "pure" approach towards database independance.  However, if such tighter coupling could be implemented with a (thick?) "driver" approach, then db independance could be maintained, while hopefully providing huge scalability gains. Of course, elegant & efficient implementation would be harder than describing this simple idea.  Or maybe such a project is already underway, or these data layers are already doing this?

  • Anonymous
    November 21, 2008
    The comment has been removed

  • Anonymous
    January 11, 2009
    If we’ll skip exact details, we can say, that internal behavior of whole modeling and mapping is based

  • Anonymous
    March 10, 2009
    Compiled Resources http://www.renaissance.co.il/downloads/Entity%20Framework%20Essential%20Resources.pdf

  • Anonymous
    April 19, 2009
    The comment has been removed

  • Anonymous
    June 01, 2009
    Uhm... If I might raise my hand to ask what might be an obvious question:  What happened to table adapters? How might table adapters compare in this test?  And how come it's not even being discussed.  I feel as if I missed an important decision in a meeting, and thereafter, everybody else is keeping it a secret.

  • Anonymous
    June 08, 2009
    i require same sort of development..but the backend database is oracle. how can i work with that.?

  • Anonymous
    June 21, 2009
    A lot of people are confused about whether or not they should use LINQ to SQL , because the word on the

  • Anonymous
    July 19, 2009
    Release DATE of new version of ADO.NET Entity Framework?

  • Anonymous
    July 22, 2009
    I thought of using entity framework in my new project and I came across this article. http://efvote.wufoo.com/forms/ado-net-entity-framework-vote-of-no-confidence/ Are you going to address these issues in future releases?

  • Anonymous
    October 16, 2009
    Hi Is it right that we only use Entity Framework, because of ORM(Object-Relation Mapping) not for Ling or ... ? We only use SQL Entity queries and Conceptual Model feature of Entity Framework.

  • Anonymous
    January 06, 2010
    Yes, Jay has a good point: What happened to table adapters? Why not consider them at all? I think they do a pretty good job.

  • Anonymous
    January 16, 2013
    What about EF using Sproc? How's the performance for that compared with EF compiled linq, sql client or entity sql. I think it bypasses some steps. Thanks

  • Anonymous
    January 17, 2013
    Hi Dan, When using stored procedures (or raw T-SQL for that matter) with EF you will overpass the cost of compiling the ESQL query or the LINQ query, but you will still have the cost of materializing the results, and the cost of entity tracking if it is enabled. For more details I suggest you take a look at the performance considerations for EF5 whitepaper we released a few months ago: msdn.microsoft.com/.../hh949853

  • Anonymous
    March 22, 2014
    avoid using EF if you care about performance over rapid development.