The DbSet.Find method is ridiculously slow compared to .SingleOrDefault for ID

Question

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

Dim competitor=context.Competitors.Find(id) 

When I look at this, the Find method takes 300 + ms to retrieve the member from the table for a total of 60 records.

When I change the code to:

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

Then the competitor is in just 3 ms.

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 have defined many-to-many relationships for CompetitionSubscriptions and OpponentMeetings using the free API.

The Competitor class ID property is Long, which is translated using the First code into the Identity column with the primary key in datatable (SQL Server Compact 4.0)

What's going on here?

Answer 1

Find calls DetectChanges internally, SingleOrDefault (or any query at all) does not. DetectChanges is an expensive operation, which is why the reason Find is slower (but can become faster if the object is already loaded into the context, because Find will not start the query, but simply returns the loaded object).

If you want to use Find for a large number of objects - for example, in a loop - you can turn off automatic detection of changes like this (you cannot write it to VB, so 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 will not call DetectChanges with every call, and it should be as fast as SingleOrDefault (and faster if the object is already bound to the context).

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

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

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