database - Entity Framework - 查询远程数据库时性能不佳

Question

我为我们的新项目评估了几种 .NET 数据库访问技术，并发现在使用 Entity Framework 查询远程数据库时性能不佳。 Entity Framework 比 LinqToSql 或 SqlClient 慢 10 倍。也许你可以帮我解释或解决这个问题？

测试参数:

数据库:

• SQL Server 2008 企业版
• 一张表有 1000 条记录

• 表结构:

  [dbo].[Master](
         [Id] [int] IDENTITY(1,1) NOT NULL,
         [Value_Bit] [bit] NOT NULL,
         [Value_Float] [float] NOT NULL,
         [Value_DateTime] [datetime2](7) NOT NULL,
         [Value_Uniqueidentifier] [uniqueidentifier] NOT NULL,
         [Value_NVarchar100] [nvarchar](100) NOT NULL,
         [Value_NVarchar1000] [nvarchar](1000) NOT NULL,
         [InsertDate] [datetime] NOT NULL,
         [UpdateDate] [datetime] NOT NULL,
         [Version] [timestamp] NOT NULL)
  

基准应用:

• .NET Framework 4 和 4.5
• 作为 WinForms 应用程序托管

数据库访问技术:

• Entity Framework 5.0 (RC) 和 4.3.1
• LinqToSQL
• SQLClient

计算机(客户端/服务器):

• 三台硬件相似的不同电脑
• A 和 B 在同一子网中(例如 192.168.1.1 和 192.168.1.2)
• C 与 A 和 B 位于不同的子网中(例如 192.168.2.1)

我在作为客户端或服务器的不同计算机上使用每种数据库访问技术直接执行了 sql 查询select * from Master。平均时间是 1000 次迭代的结果。

测试场景 1:

• 客户:A

• 服务器:A

• Entity Framework :平均时间:17 毫秒

• LinqToSQL:平均时间:20 毫秒
• SqlClient:平均时间:15 毫秒

测试场景 2:

• 客户:A

• 服务器:B

• Entity Framework :平均时间:144 毫秒

• LinqToSQL:平均时间:141 毫秒
• SqlClient:平均时间:140 毫秒

测试场景 3:

• 客户:A

• 服务器:C

• Entity Framework :平均时间:2145 毫秒

• LinqToSQL:平均时间:151 毫秒
• SqlClient:平均时间:156 毫秒

测试场景 4:

• 客户:B

• 服务器:C

• Entity Framework :平均时间:2060 毫秒

• LinqToSQL:平均时间:141 毫秒
• SqlClient:平均时间:178 毫秒

为什么 Entity Framework 在测试场景 3 和 4 中比 LinqToSQL 或 SqlClient 慢 10 倍？

我使用 Entity Framework 4.3.1、5 (RC) 以及 .NET Framework 4 和 4.5 对其进行了测试，每次都是相同的结果。我禁用延迟加载和跟踪，使用编译查询和预生成 View ，但没有区别。

我使用 SQL Profiler 调查执行的 SQL 查询，发现 Entity Framework 的查询在 SQL Server 中已经花费了两秒钟(测试场景 3)。如果我从计算机 A 上的 Management Studio 执行查询，只需要 100 毫秒。

我使用 dotTrace (http://www.jetbrains.com) 分析了我的基准应用程序，发现大部分执行时间都被 ToList 方法消耗了。如果我深入调用堆栈，我会看到方法 System.Data.SqlClient.SqlDataReader.GetString(Int32) 和最后的 SNINativeMethodWrapper.SNIReadSyncOverAsync(SafeHandle, IntPtr&, Int32) 一直在消耗时间。 LinqToSql 也使用 SqlClient 并且具有几乎相同的调用堆栈，但执行时间快 10 倍。

我不知道幕后发生了什么。也许它与计算机名称解析有关，但我可以通过 IP 地址及其计算机名称 ping 计算机 C。有没有人可以对此进行解释或建议如何加快执行速度？

提前致谢

马蒂亚斯

Answer 1

如果没有看到您的代码，很难给出任何有根据的猜测，但是您可以大致了解一下 EF 的一些典型内容。

• 通常你可以通过使用 Compiled Queries 来显着加快速度。 .

• 在查看代码时，EF 查询的延迟执行可能是一个不明显的陷阱。

一个常见的错误是执行返回 IEnumerable 或 IQueryable 集合的查询，然后在循环中使用它:

// Execution will be deferred:
IEnumerable<person> peopleList = objectContext.People.Where(item => item.ID > 100);
foreach (person somePerson in peopleList)
{
  // do something here
}

此代码将对数据库进行多次往返，这可能会导致严重的性能问题。由于延迟执行和延迟加载，这将导致人员列表中每个项目 的代码再次查询数据库。根据通过网络传输的数据量，仅此一项就可能对性能造成严重损害。

您可以通过简单地对集合调用 ToList() 方法来减少这种开销。这将在一次往返中获取所有结果对象:

// Execution will be deferred:
List<person> peopleList = objectContext.People.Where(item => item.ID > 100)
                                              .ToList(); // Fetch objects NOW!

MSDN 提供了一篇带有一些建议的文章，Performance Considerations (Entity Framework) :

Strategies for Improving Performance You can improve the overall performance of queries in the Entity Framework by using the following strategies.

Pre-generate views

Generating views based on an entity model is a significant cost the first time that an application executes a query. Use the EdmGen.exe utility to pre-generate views as a Visual Basic or C# code file that can be added to the project during design. You could also use the Text Template Transformation Toolkit to generate pre-compiled views. Pre-generated views are validated at runtime to ensure that they are consistent with the current version of the specified entity model. For more information, see How to: Pre-Generate Views to Improve Query Performance (Entity Framework) and Isolating Performance with Precompiled/Pre-generated Views in the Entity Framework 4. When working with very large models, the following consideration applies: The .NET metadata format limits the number of user string characters in a given binary to 16,777,215 (0xFFFFFF). If you are generating views for a very large model and the view file reaches this size limit, you will get the "No logical space left to create more user strings." compile error. This size limitation applies to all managed binaries. For more information see the blog that demonstrates how to avoid the error when working with large and complex models.

Consider using the NoTracking merge option for queries

There is a cost required to track returned objects in the object context. Detecting changes to objects and ensuring that multiple requests for the same logical entity return the same object instance requires that objects be attached to an ObjectContext instance. If you do not plan to make updates or deletes to objects and do not require identity management , consider using the NoTracking merge options when you execute queries.

Return the correct amount of data

In some scenarios, specifying a query path using the Include method is much faster because it requires fewer round trips to the database. However, in other scenarios, additional round trips to the database to load related objects may be faster because the simpler queries with fewer joins result in less redundancy of data. Because of this, we recommend that you test the performance of various ways to retrieve related objects. For more information, see Shaping Query Results (Entity Framework). To avoid returning too much data in a single query, consider paging the results of the query into more manageable groups. For more information, see How to: Page Through Query Results (Entity Framework).

Limit the scope of the ObjectContext

In most cases, you should create an ObjectContext instance within a using statement (Using…End Using in Visual Basic). This can increase performance by ensuring that the resources associated with the object context are disposed automatically when the code exits the statement block. However, when controls are bound to objects managed by the object context, the ObjectContext instance should be maintained as long as the binding is needed and disposed of manually. For more information, see Managing Resources in Object Services (Entity Framework).

Consider opening the database connection manually

When your application executes a series of object queries or frequently calls SaveChanges to persist create, update, and delete operations to the data source, the Entity Framework must continuously open and close the connection to the data source. In these situations, consider manually opening the connection at the start of these operations and either closing or disposing of the connection when the operations are complete. For more information, see Managing Connections and Transactions in the Entity Framework.