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??
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/
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With