Posted by Robert Bazinet  From/InfoQ

The ADO.NET Team recently discussed various performance aspects of the ADO.NET Entity Framework.  The ADO.NET Entity Framework entered its third beta back in December and since that time the team has provided developers with information about using the framework but now they are providing developers with the performance aspects.

The articles spends time looking at the performance of the ADO.NET Entity Framework by breaking down the stack and showing how to speed up a simple query and explain the performance characteristics of the framework.

It's important to point out when a layer of abstraction or something else like an EDM is used to transform the relational schema of a database, there is going to be a performance decrease.

The Query and Results

The article uses the NorthWind database for the model and creates a very simple query:

using (NorthwindEntities ne = new NorthwindEntities())
{
    foreach (Order o in ne.Orders)
    {
        int i = o.OrderID;
    }
}

The test was run for 10 iterations over a total of 848 rows for each query.  The results are interesting with the first run being 4241 ms and each subsequent run averaging around 13 ms.  A good part of the time is the creation of the ObjectContext and then executing any operation that accesses the database, some expensive operations occur.
Breaking down each operation by percentage gives us some insight:

• Loading Metadata (11%)
• Initializing Metadata (14%)
• Opening Connection (8%)
• View Generation (56%)
• Load Assembly (2%)
• Tracking (1%)
• Materialization (7%)
• Misc (1%)

By far the biggest percentage of time is View Generation at a whopping 56%.  When View Generation is the primary time cost, developers can use  the EDM generator (EdmGen.exe) command line tool with the view generation command parameter (/mode:ViewGeneration), the output is a code file (C# or VB.NET) that can be included in the project. Having the view pre-generated reduces the startup time down to 2933 ms, about a 28% decrease in the overall time for the iteration.  Generating the views and distributing with applications is a good solution to gain performance.  The downside is the views are no longer dynamic and need to be regenerated and kept synchronized when there are changes to the model.

Query Performance

It's pointed out that a major design element for performance is the query cache. Once a query is executed, parts of it are maintained in a global cache. The query and metadata caching results in the second run always executes faster than the first run.  For example, looking at this Entity SQL query:

using (PerformanceArticleContext ne = new PerformanceArticleContext())
{
    ObjectQuery<Orders> orders = ne.CreateQuery<Orders>("Select value o from Orders as o");
    foreach (Orders o in orders)
    {
        int i = o.OrderID;
    }
}

The first run of this query takes 179 ms, but the next run takes only 15 ms. The execution difference between the initial and subsequent ones is building the command tree that gets passed down to the provider for execution.

LINQ queries are similar to Entity SQL queries in the way it executes.  For example, the query below:

using (PerformanceArticleContext ne = new PerformanceArticleContext())
{
    var orders = from order in ne.Orders
                select order;

    foreach (Orders o in orders)
    {
        int i = o.OrderID;
    }
}

The execution of the LINQ query takes 202 ms initially and 18 ms on subsequent executions, still slower than Entity SQL. Taking a look at using compiled LINQ queries to improve performance further. The advantage of compiling a LINQ query is that the expression tree is built when the query is compiled and doesn’t need to be rebuilt on subsequent executions.  The code for the compiled LINQ query looks like this:

public static Func<PerformanceArticleContext, IQueryable<Orders>> compiledQuery = CompiledQuery.Compile((PerformanceArticleContext ne) => (from o in ne.Orders select o));

using (PerformanceArticleContext ne = new PerformanceArticleContext())
{
    foreach (Orders o in compiledQuery(ne))
    {
        int i = o.OrderID;
    }
}

Notice PerformanceArticleContext is a delegate.  The execution time for the compiled LINQ query is 305 ms on the first execution and 15 ms on subsequent ones.  The results are not amazing but it's interesting to note the 3ms decrease in execution time for the compiled LINQ query from the the regular one, not important for a few queries but has value when performing thousands of queries.

The ADO.NET team suggests being careful with the Track/NoTrack options in your queries:

In the previous examples, all the queries result in the creation of an object that gets added to the ObjectStateManager so that we can track updates. When it is not important to track updates or deletes to objects, then executing queries using the NoTracking merge option may be a better option. For example, NoTracking may be a good option in an ASP.NET web application that queries for a specific category name but doesn’t make updates to the returned data. In a case like this, there is a performance benefit to using NoTracking queries.

Based on the numbers, the NoTracking option provides a big reduction in the amount of time, where most of this gain comes when we stop tracking changes and managing relationships. For a NoTracking query, the compiled LINQ query outperforms the standard LINQ query both in first execution and in subsequent executions. Note that the second execution of the compiled LINQ query is equivalent to the second execution of the Entity SQL query.

The ADO.NET team also suggests keeping a few things in mind when creating queries:

When optimizing query performance in the Entity Framework, you should consider what works best for your particular programming scenario. Here are a few key takeaways:

• Initial creation of the ObjectContext includes the cost of loading and validating the metadata.
• Initial execution of any query includes the costs of building up a query cache to enable faster execution of subsequent queries.
• Compiled LINQ queries are faster than Non-compiled LINQ queries.
• Queries executed with a NoTracking merge option work well for streaming large data objects or when changes and relationships do not need to be tracked.

For more information on ADO.NET and Entity Framework information, please check out the ADO.NET Team Blog.

感谢原创者的辛勤劳动，希望对您有所帮助，转载请注明原出处。

您可能对 [ADO.Net] 的这些文章也感兴趣： 迈向Data 2.0——学习使用ADO.NET数据服务框架 ADO.NET Entity Framework：lazy loading(二) 在VisualC#中访问不同数据库 如何将一个超级链接列绑定多个字段 Using ADO.NET Data Service – Consuming 用C#和ADO.NET建立一个数据绑定网格 Enumerate SQL Server Instances in C#, Using ODBC 在C#中使用ADO.Net部件来访问Access数据库 ADO.NET Entity Framework(4)ObjectQuery ADO.NET Entity Framework 体验：持久化

挖掘ADO.NET Entity框架的性能

译/佚名

ADO.NET团队最近讨论了ADO.NET Entity框架的各种性能特征。ADO.NET Entity框架在12月已经进入它的第三个beta版本，自那时起开发团队就开始为开发人员提供了使用该框架的相关信息。而现在，则为开发人员提供了框架性能方面的信息。

本文鞭辟入里地介绍了ADO.NET Entity框架的性能，演示了如何提高简单查询速度的方法，并阐释了框架的性能特征。

需要重点指出的是，当一个抽象层或者类似EDM（译注：指Entity Data Model）的模块被用来转换数据库的关系样式时，会带来一定的性能损失。

查询与结果

本文使用了NorthWind数据库作为模型，并创建了一个简单查询：

(NorthwindEntities ne =  NorthwindEntities())
{
    (Order o  ne.Orders)
    {
        i = o.OrderID;
    }
}

测试时，我们的每个查询对整个848行数据进行了10次遍历。结果很有意思，第1次运行时耗费了4241毫秒，而接下来的每次运行则平均耗费13毫秒左右的时间。最耗时的一部分内容是ObjectContext的创建，而在执行任意一个访问数据库的操作时，都会有一些耗时的操作发生。

每次操作的百分比值可以给我们一些启示：

• 装载元数据（11%）
• 初始化元数据（14%）
• 打开连接（8%）
• 生成视图（56%）
• 装载程序集（2%）
• 跟踪（1%）
• 实例化（7%）
• 其它（1%）

耗时百分比值最大的是视图生成，它达到了惊人的56%。既然视图生成是造成性能损耗的罪魁祸首，那么开发人员最好是使用命令行工具EDM生成器（EdmGen.exe），运行时需要加上视图生成命令参数（/mode:ViewGeneration），它的输出内容为一个代码文件（C#或者VB.NET），可以包含在项目中。视图的预生成可以将启动时间降低到2933毫秒，而对于循环遍历操作，整个时间可以降低28%。生成视图并随着应用程序一起发布是提高性能的妙方，但其缺点则在于视图不再是动态的，一旦模型发生改变，就需要重新生成以保持同步。

查询性能

需要指出的是关于性能的主要设计要素是查询缓存。一旦执行了查询，它的一部分内容就被维持在全局缓存中。由于查询与元数据缓存的存在，使得第二次运行的执行速度总是比第一次运行快。例如，如下的Entity SQL查询：

(PerformanceArticleContext ne =  PerformanceArticleContext())
{
    ObjectQuery<Orders> orders = ne.CreateQuery<Orders>();
    (Orders o  orders)
    {
        i = o.OrderID;
    }
}

第一次运行该查询耗时179毫秒，但下一次运行则只耗费了15毫秒的时间。首次运行与后续运行在执行方面的区别在于它构建了能够为执行传递provider的命令树（command tree）。
LINQ查询在执行方式上与Entity SQL查询相似。例如，下面的查询：

(PerformanceArticleContext ne =  PerformanceArticleContext())
{
    var orders = from order  ne.Orders
                select order;
    (Orders o  orders)
    {
        i = o.OrderID;
    }
}

首次执行LINQ查询耗时202毫秒，而随后的执行耗时18毫秒，两者的差距还要低于Entity SQL。可以看到，使用编译了的LINQ查询对于性能的提高更为明显。编译LINQ查询的好处在于它构建了表达树（expression tree），当查询被编译时，后续的执行就不需要重建表达树了。编译的LINQ查询代码看起来像这样：

Func<PerformanceArticleContext, IQueryable<Orders>> compiledQuery = CompiledQuery.Compile((PerformanceArticleContext ne) => (from o  ne.Orders select o));
(PerformanceArticleContext ne =  PerformanceArticleContext())
{
    (Orders o  compiledQuery(ne))
    {
        i = o.OrderID;
    }
}

注意，PerformanceArticleContext是一个委托。对于编译了的LINQ查询而言，第一次执行耗时305毫秒，而随后的执行时间则为15毫秒。结果并不惊人，值得关注的是编译的LINQ查询比之常规方式的LINQ查询，执行时间少了3毫秒。或许对于几个查询而言，这算不上什么，但如果有数以千计的查询，这样的性能提升就倍显价值所在了。

ADO.NET团队建议开发人员在查询中应谨慎使用Track/NoTrack选项：

在之前的例子中，所有放在对象创建中的查询结果都被添加到ObjectStateManager中，因此我们能够跟踪它们的更新。如果没有必要跟踪对象的更新和删除，那么最好是使用NoTracking合并项。例如，在一个ASP.NET Web应用程序中，如果它要查询一个指定的分类名称，但却不需要对返回的数据进行更新，那么NoTracking就会是一个不错的选择。在这种情形下，使用NoTracking的查询会在性能方面得到改善。

基于前面的一组数字，NoTracking选项能够大幅度地降低执行的时间，而其中性能的提升主要源自于我们停止了对变更的跟踪以及对关系的管理。如果使用NoTracking查询，无论是第一次执行还是随后的执行，编译的LINQ查询都要优于标准的LINQ查询。注意，编译的LINQ查询的第二次执行与Entity SQL查询的第二次执行相等。

ADO.NET团队同时还提醒开发者在创建查询时，有一些内容必须铭记于心：

在Entity框架中优化查询性能时，应该针对特定的编程场景做出最佳选择。这里列举了几个关键项：

• ObjectContext的首次创建包含了装载和验证元数据的性能损耗。
• 任何一个查询的首次执行都包含了构建一个查询缓存的性能损耗，以利于提高后续查询的执行速度。
• 编译的LINQ查询比未编译的LINQ查询要快。
• 如果不需要跟踪数据的变更与数据的关系，或者对大数据对象进行流操作，那么通过NoTracking合并项执行查询，效果会更佳。

若要了解更多关于ADO.NET和Entity框架的信息，敬请访问ADO.NET的团队博客。