DbSet.Find method ridiculously slow compared to .SingleOrDefault on ID

Question

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

Dim competitor=context.Competitors.Find(id)

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

When I change the code to:

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

Then the competitor is found in just 3 ms.

The Competitor class:

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

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

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)

What is going on here??

Theatrical

Find method first checks internal context's storage to return existing instance without roundtrip to database. How many records do you have loaded in your context? – Infusion

@LadislavMrnka I do not know how many records are loaded. Explicitly very few when I call this code. How do I check this? And there are only 60 Competitor records! – Theatrical

Answer 1

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).

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;
}

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:

(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/

Skutchan

Thanks! Turning off DetectChanges made the Find method a hundred times faster than the SingleOrDefualt operator (0,03 ms) all in all the Find method became 10.000 times faster... – Theatrical

This page still gets found quite a lot and +1 to the answer. However, beware that as of now (currently EF6), AutoDetectChangesEnabled now makes no noticable difference to performance in Find(), AND Find() is just as performant as Where().SingleOrDefault(), if not slightly more so. – Prone

@JonBellamy: Interesting! Did you find that by your own measurements or in another reference/documentation (or both)? What you say would - in my understanding - mean that either Find doesn't call DetectChanges anymore or the performance of DetectChanges has been seriously improved in EF 6 which would have a big impact on the overall performance of EF. – Skutchan

@Slauma: I performed countless of my own measurements. Interestingly, my sole reason for doing them was because I preferred Find for its simplicity, but didn't want to sacrifice performance. I hadn't worked with EF for a while, and had seen numerous performance boastings so spent some time testing EF6. I ran 1,000 tests on each of "Find", "Find with AutoDetect false" and "Where". Each test generated its own context. Find performed in circa 800ms, Find false in 850ms and Where in 890ms. They've definately done something awesome. – Prone

@Slauma, I should clarify that each of the 1,000 tests on all three methods generated it's own context to perform a single query, not one context per test of 1,000! :-) – Prone

@JonBellamy: Ah, OK, then the result is perhaps not so surprising because DetectChanges becomes slow if a context contains "many" objects. With only a single object in the context or even an empty context the overhead is probably negligible. – Skutchan

@Slauma, I'll have to experiment, but you have a valid point. Thanks for responding! – Prone

I just downloaded the source code for EF 6.0.2 and DbSet<T>.Find method is still calling DetectChanges and taking much longer (+60%) to execute than FirstOrDefault in my case. Here you can find the source code: #21455578 – Ramonitaramos

In re: to the comments that this has been fixed: NO! I'm running EF 6.1 and Find() is extremely slow for me where using SingleOrDefault(x => x.Id == "foo") is very expedient. – Valenta