与 ID 上的 .SingleOrDefault 相比，DbSet.Find 方法慢得可笑 [英] 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 多毫秒才能从只有 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 毫秒内找到竞争对手.

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

我使用 fluent API 为 CompetitionSubscriptions 和 OpponentMeetings 定义了多对多关系.

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

Competitor 类的 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)

这是怎么回事??

推荐答案

Find 在内部调用 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).

如果您想对很多实体使用 Find - 例如在循环中 - 您可以像这样禁用自动更改检测(不能在 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;
}

现在，Find 不会在每次调用时都调用 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/

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