DbSet.Find方法相对于.SingleOrDefault on ID而言比较慢 [英] DbSet.Find method ridiculously slow compared to .SingleOrDefault on ID

问题描述

我有以下代码（数据库是SQL Server Compact 4.0）：

I have the following code (Database is SQL Server Compact 4.0):

Dim competitor=context.Competitors.Find(id)

当我配置文件时，Find方法需要300 + ms从一张表中检索竞争对手只有60条记录。

When I profile this the Find method takes 300+ms to retrieve the competitor from a table of just 60 records.

当我将代码更改为：

Dim competitor=context.Competitors.SingleOrDefault(function(c) c.ID=id)

然后竞争对手仅在3 ms内。

Then the competitor is found in just 3 ms.

竞争对手类：

Public Class Competitor
    Implements IEquatable(Of Competitor)

    Public Sub New()
        CompetitionSubscriptions = New List(Of CompetitionSubscription)
        OpponentMeetings = New List(Of Meeting)
        GUID = GUID.NewGuid
    End Sub

    Public Sub New(name As String)
        Me.New()
        Me.Name = name
    End Sub

    'ID'
    Public Property ID As Long
    Public Property GUID As Guid

    'NATIVE PROPERTIES'
    Public Property Name As String

    'NAVIGATION PROPERTIES'
    Public Overridable Property CompetitionSubscriptions As ICollection(Of CompetitionSubscription)
    Public Overridable Property OpponentMeetings As ICollection(Of Meeting)
End Class

我定义了 CompetitionSubscriptions 和 OpponentMeetings 使用流利的许多关系API。

I defined the many to many relations for CompetitionSubscriptions and OpponentMeetings using the fluent API.

竞争对手类的ID属性是Long，由Code First转换为Identity列，数据表中的主键（SQL Server Compact 4.0）

The ID property of the Competitor class is a Long which is translated by Code First to an Identity column with a primary key in the datatable (SQL Server Compact 4.0)

这里发生了什么？

推荐答案

在内部查找调用 DetectChanges SingleOrDefault （或通常是任何查询）不。 DetectChanges 是一个昂贵的操作，所以这就是为什么 Find 更慢（但是如果实体是已经加载到上下文中，因为 Find 不会运行查询，而只返回加载的实体）。

Find calls DetectChanges internally, SingleOrDefault (or generally any query) doesn't. DetectChanges is an expensive operation, so that's the reason why Find is slower (but it might become faster if the entity is already loaded into the context because Find would not run a query but just return the loaded entity).

如果你想要使用为很多实体找到 - 例如在一个循环中 - 你可以禁用自动更改检测（不能写在VB中，所以一个C＃示例）：

If you want to use Find for a lot of entities - in a loop for example - you can disable automatic change detection like so (can't write it in VB, so a C# example):

try
{
    context.Configuration.AutoDetectChangesEnabled = false;
    foreach (var id in someIdCollection)
    {
        var competitor = context.Competitors.Find(id);
        // ...
    }
}
finally
{
    context.Configuration.AutoDetectChangesEnabled = true;
}

现在，查找每次调用都不会调用 DetectChanges ，它应该与 SingleOrDefault 一样快（如果实体已经附加到上下文）。

Now, Find won't call DetectChanges with every call and it should be as fast as SingleOrDefault (and faster if the entity is already attached to the context).

自动变更检测是一个复杂而有些神秘的主题。在这个四部分系列中可以找到很详细的讨论：

Automatic change detection is a complex and somewhat mysterious subject. A great detailed discussion can be found in this four-part series:

（链接到第1部分，第2,3和4部分的链接是在文章）

(Link to part 1, the links to parts 2, 3 and 4 are at the beginning of that article)

http://blog.oneunicorn.com/2012/03/10/secrets-of-detectchanges-part-1-what-does-detectchanges-do/

这篇关于DbSet.Find方法相对于.SingleOrDefault on ID而言比较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！