Menu
I have this simple LINQ query, which executes for 8395 ms:
var _context = new SurveyContext();
_context.Database.Log = Console.WriteLine;
(from p in _context.Participants
join row in _context.ListAnswerSelections
on new {p.Id, QuestionId = 434} equals
new {Id = row.RelatedParticipantId, QuestionId = row.RelatedQuestionId}
select new { V = row.NumericValue.ToString() })
.ToList()
.Select(x => new {R = x.V})
.Count() //This is just to see one number instead of whole result
.Dump("Results");
Its IL:
IL_0000: nop
IL_0001: newobj Survey.Model.SurveyContext..ctor
IL_0006: stloc.0 // _context
IL_0007: ldloc.0 // _context
IL_0008: callvirt System.Data.Entity.DbContext.get_Database
IL_000D: ldnull
IL_000E: ldftn System.Console.WriteLine
IL_0014: newobj System.Action<System.String>..ctor
IL_0019: callvirt System.Data.Entity.Database.set_Log
IL_001E: nop
IL_001F: ldloc.0 // _context
IL_0020: callvirt Survey.Model.SurveyContext.get_Participants
IL_0025: ldloc.0 // _context
IL_0026: callvirt Survey.Model.SurveyContext.get_ListAnswerSelections
IL_002B: ldtoken Survey.Model.Participant
IL_0030: call System.Type.GetTypeFromHandle
IL_0035: ldstr "p"
IL_003A: call System.Linq.Expressions.Expression.Parameter
IL_003F: stloc.1 // CS$0$0000
IL_0040: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>..ctor
IL_0045: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>
IL_004A: call System.Reflection.MethodBase.GetMethodFromHandle
IL_004F: castclass System.Reflection.ConstructorInfo
IL_0054: ldc.i4.2
IL_0055: newarr System.Linq.Expressions.Expression
IL_005A: stloc.2 // CS$0$0001
IL_005B: ldloc.2 // CS$0$0001
IL_005C: ldc.i4.0
IL_005D: ldloc.1 // CS$0$0000
IL_005E: ldtoken Survey.Model.ModelBase.get_Id
IL_0063: call System.Reflection.MethodBase.GetMethodFromHandle
IL_0068: castclass System.Reflection.MethodInfo
IL_006D: call System.Linq.Expressions.Expression.Property
IL_0072: stelem.ref
IL_0073: ldloc.2 // CS$0$0001
IL_0074: ldc.i4.1
IL_0075: ldc.i4 B2 01 00 00
IL_007A: box System.Int32
IL_007F: ldtoken System.Int32
IL_0084: call System.Type.GetTypeFromHandle
IL_0089: call System.Linq.Expressions.Expression.Constant
IL_008E: stelem.ref
IL_008F: ldloc.2 // CS$0$0001
IL_0090: ldc.i4.2
IL_0091: newarr System.Reflection.MethodInfo
IL_0096: stloc.3 // CS$0$0002
IL_0097: ldloc.3 // CS$0$0002
IL_0098: ldc.i4.0
IL_0099: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_Id
IL_009E: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>
IL_00A3: call System.Reflection.MethodBase.GetMethodFromHandle
IL_00A8: castclass System.Reflection.MethodInfo
IL_00AD: stelem.ref
IL_00AE: ldloc.3 // CS$0$0002
IL_00AF: ldc.i4.1
IL_00B0: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_QuestionId
IL_00B5: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>
IL_00BA: call System.Reflection.MethodBase.GetMethodFromHandle
IL_00BF: castclass System.Reflection.MethodInfo
IL_00C4: stelem.ref
IL_00C5: ldloc.3 // CS$0$0002
IL_00C6: call System.Linq.Expressions.Expression.New
IL_00CB: ldc.i4.1
IL_00CC: newarr System.Linq.Expressions.ParameterExpression
IL_00D1: stloc.s 04 // CS$0$0003
IL_00D3: ldloc.s 04 // CS$0$0003
IL_00D5: ldc.i4.0
IL_00D6: ldloc.1 // CS$0$0000
IL_00D7: stelem.ref
IL_00D8: ldloc.s 04 // CS$0$0003
IL_00DA: call System.Linq.Expressions.Expression.Lambda
IL_00DF: ldtoken Survey.Model.ListAnswerSelection
IL_00E4: call System.Type.GetTypeFromHandle
IL_00E9: ldstr "row"
IL_00EE: call System.Linq.Expressions.Expression.Parameter
IL_00F3: stloc.1 // CS$0$0000
IL_00F4: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>..ctor
IL_00F9: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>
IL_00FE: call System.Reflection.MethodBase.GetMethodFromHandle
IL_0103: castclass System.Reflection.ConstructorInfo
IL_0108: ldc.i4.2
IL_0109: newarr System.Linq.Expressions.Expression
IL_010E: stloc.2 // CS$0$0001
IL_010F: ldloc.2 // CS$0$0001
IL_0110: ldc.i4.0
IL_0111: ldloc.1 // CS$0$0000
IL_0112: ldtoken Survey.Model.ListAnswerSelection.get_RelatedParticipantId
IL_0117: call System.Reflection.MethodBase.GetMethodFromHandle
IL_011C: castclass System.Reflection.MethodInfo
IL_0121: call System.Linq.Expressions.Expression.Property
IL_0126: stelem.ref
IL_0127: ldloc.2 // CS$0$0001
IL_0128: ldc.i4.1
IL_0129: ldloc.1 // CS$0$0000
IL_012A: ldtoken Survey.Model.ListAnswerSelection.get_RelatedQuestionId
IL_012F: call System.Reflection.MethodBase.GetMethodFromHandle
IL_0134: castclass System.Reflection.MethodInfo
IL_0139: call System.Linq.Expressions.Expression.Property
IL_013E: stelem.ref
IL_013F: ldloc.2 // CS$0$0001
IL_0140: ldc.i4.2
IL_0141: newarr System.Reflection.MethodInfo
IL_0146: stloc.3 // CS$0$0002
IL_0147: ldloc.3 // CS$0$0002
IL_0148: ldc.i4.0
IL_0149: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_Id
IL_014E: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>
IL_0153: call System.Reflection.MethodBase.GetMethodFromHandle
IL_0158: castclass System.Reflection.MethodInfo
IL_015D: stelem.ref
IL_015E: ldloc.3 // CS$0$0002
IL_015F: ldc.i4.1
IL_0160: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>.get_QuestionId
IL_0165: ldtoken <>f__AnonymousType0<System.Int32,System.Int32>
IL_016A: call System.Reflection.MethodBase.GetMethodFromHandle
IL_016F: castclass System.Reflection.MethodInfo
IL_0174: stelem.ref
IL_0175: ldloc.3 // CS$0$0002
IL_0176: call System.Linq.Expressions.Expression.New
IL_017B: ldc.i4.1
IL_017C: newarr System.Linq.Expressions.ParameterExpression
IL_0181: stloc.s 04 // CS$0$0003
IL_0183: ldloc.s 04 // CS$0$0003
IL_0185: ldc.i4.0
IL_0186: ldloc.1 // CS$0$0000
IL_0187: stelem.ref
IL_0188: ldloc.s 04 // CS$0$0003
IL_018A: call System.Linq.Expressions.Expression.Lambda
IL_018F: ldtoken Survey.Model.Participant
IL_0194: call System.Type.GetTypeFromHandle
IL_0199: ldstr "p"
IL_019E: call System.Linq.Expressions.Expression.Parameter
IL_01A3: stloc.1 // CS$0$0000
IL_01A4: ldtoken Survey.Model.ListAnswerSelection
IL_01A9: call System.Type.GetTypeFromHandle
IL_01AE: ldstr "row"
IL_01B3: call System.Linq.Expressions.Expression.Parameter
IL_01B8: stloc.s 05 // CS$0$0004
IL_01BA: ldtoken <>f__AnonymousType1<System.String>..ctor
IL_01BF: ldtoken <>f__AnonymousType1<System.String>
IL_01C4: call System.Reflection.MethodBase.GetMethodFromHandle
IL_01C9: castclass System.Reflection.ConstructorInfo
IL_01CE: ldc.i4.1
IL_01CF: newarr System.Linq.Expressions.Expression
IL_01D4: stloc.2 // CS$0$0001
IL_01D5: ldloc.2 // CS$0$0001
IL_01D6: ldc.i4.0
IL_01D7: ldloc.s 05 // CS$0$0004
IL_01D9: ldtoken Survey.Model.ListAnswerSelection.get_NumericValue
IL_01DE: call System.Reflection.MethodBase.GetMethodFromHandle
IL_01E3: castclass System.Reflection.MethodInfo
IL_01E8: call System.Linq.Expressions.Expression.Property
IL_01ED: ldtoken System.Int32.ToString
IL_01F2: call System.Reflection.MethodBase.GetMethodFromHandle
IL_01F7: castclass System.Reflection.MethodInfo
IL_01FC: ldc.i4.0
IL_01FD: newarr System.Linq.Expressions.Expression
IL_0202: call System.Linq.Expressions.Expression.Call
IL_0207: stelem.ref
IL_0208: ldloc.2 // CS$0$0001
IL_0209: ldc.i4.1
IL_020A: newarr System.Reflection.MethodInfo
IL_020F: stloc.3 // CS$0$0002
IL_0210: ldloc.3 // CS$0$0002
IL_0211: ldc.i4.0
IL_0212: ldtoken <>f__AnonymousType1<System.String>.get_V
IL_0217: ldtoken <>f__AnonymousType1<System.String>
IL_021C: call System.Reflection.MethodBase.GetMethodFromHandle
IL_0221: castclass System.Reflection.MethodInfo
IL_0226: stelem.ref
IL_0227: ldloc.3 // CS$0$0002
IL_0228: call System.Linq.Expressions.Expression.New
IL_022D: ldc.i4.2
IL_022E: newarr System.Linq.Expressions.ParameterExpression
IL_0233: stloc.s 04 // CS$0$0003
IL_0235: ldloc.s 04 // CS$0$0003
IL_0237: ldc.i4.0
IL_0238: ldloc.1 // CS$0$0000
IL_0239: stelem.ref
IL_023A: ldloc.s 04 // CS$0$0003
IL_023C: ldc.i4.1
IL_023D: ldloc.s 05 // CS$0$0004
IL_023F: stelem.ref
IL_0240: ldloc.s 04 // CS$0$0003
IL_0242: call System.Linq.Expressions.Expression.Lambda
IL_0247: call System.Linq.Queryable.Join
IL_024C: call System.Linq.Enumerable.ToList
IL_0251: ldsfld UserQuery.CS$<>9__CachedAnonymousMethodDelegate2
IL_0256: brtrue.s IL_026B
IL_0258: ldnull
IL_0259: ldftn UserQuery.<Main>b__1
IL_025F: newobj System.Func<<>f__AnonymousType1<System.String>,<>f__AnonymousType2<System.String>>..ctor
IL_0264: stsfld UserQuery.CS$<>9__CachedAnonymousMethodDelegate2
IL_0269: br.s IL_026B
IL_026B: ldsfld UserQuery.CS$<>9__CachedAnonymousMethodDelegate2
IL_0270: call System.Linq.Enumerable.Select
IL_0275: call System.Linq.Enumerable.Count
IL_027A: ldstr "Results"
IL_027F: call LINQPad.Extensions.Dump
IL_0284: pop
IL_0285: ret
<Main>b__1:
IL_0000: ldarg.0
IL_0001: callvirt <>f__AnonymousType1<System.String>.get_V
IL_0006: newobj <>f__AnonymousType2<System.String>..ctor
IL_000B: stloc.0 // CS$1$0000
IL_000C: br.s IL_000E
IL_000E: ldloc.0 // CS$1$0000
IL_000F: ret
<>f__AnonymousType1`1.get_V:
IL_0000: ldarg.0
IL_0001: ldfld 16 00 00 0A
IL_0006: stloc.0
IL_0007: br.s IL_0009
IL_0009: ldloc.0
IL_000A: ret
<>f__AnonymousType1`1.ToString:
IL_0000: newobj System.Text.StringBuilder..ctor
IL_0005: stloc.0
IL_0006: ldloc.0
IL_0007: ldstr "{ V = "
IL_000C: callvirt System.Text.StringBuilder.Append
IL_0011: pop
IL_0012: ldloc.0
IL_0013: ldarg.0
IL_0014: ldfld 16 00 00 0A
IL_0019: box 02 00 00 1B
IL_001E: callvirt System.Text.StringBuilder.Append
IL_0023: pop
IL_0024: ldloc.0
IL_0025: ldstr " }"
IL_002A: callvirt System.Text.StringBuilder.Append
IL_002F: pop
IL_0030: ldloc.0
IL_0031: callvirt System.Object.ToString
IL_0036: stloc.1
IL_0037: br.s IL_0039
IL_0039: ldloc.1
IL_003A: ret
<>f__AnonymousType1`1.Equals:
IL_0000: ldarg.1
IL_0001: isinst 06 00 00 1B
IL_0006: stloc.0
IL_0007: ldloc.0
IL_0008: brfalse.s IL_0022
IL_000A: call 10 00 00 0A
IL_000F: ldarg.0
IL_0010: ldfld 16 00 00 0A
IL_0015: ldloc.0
IL_0016: ldfld 16 00 00 0A
IL_001B: callvirt 11 00 00 0A
IL_0020: br.s IL_0023
IL_0022: ldc.i4.0
IL_0023: nop
IL_0024: stloc.1
IL_0025: br.s IL_0027
IL_0027: ldloc.1
IL_0028: ret
<>f__AnonymousType1`1.GetHashCode:
IL_0000: ldc.i4 8B 15 0F EC
IL_0005: stloc.0
IL_0006: ldc.i4 29 55 55 A5
IL_000B: ldloc.0
IL_000C: mul
IL_000D: call 10 00 00 0A
IL_0012: ldarg.0
IL_0013: ldfld 16 00 00 0A
IL_0018: callvirt 14 00 00 0A
IL_001D: add
IL_001E: stloc.0
IL_001F: ldloc.0
IL_0020: stloc.1
IL_0021: br.s IL_0023
IL_0023: ldloc.1
IL_0024: ret
<>f__AnonymousType1`1..ctor:
IL_0000: ldarg.0
IL_0001: call System.Object..ctor
IL_0006: ldarg.0
IL_0007: ldarg.1
IL_0008: stfld 16 00 00 0A
IL_000D: ret
<>f__AnonymousType2`1.get_R:
IL_0000: ldarg.0
IL_0001: ldfld 17 00 00 0A
IL_0006: stloc.0
IL_0007: br.s IL_0009
IL_0009: ldloc.0
IL_000A: ret
<>f__AnonymousType2`1.ToString:
IL_0000: newobj System.Text.StringBuilder..ctor
IL_0005: stloc.0
IL_0006: ldloc.0
IL_0007: ldstr "{ R = "
IL_000C: callvirt System.Text.StringBuilder.Append
IL_0011: pop
IL_0012: ldloc.0
IL_0013: ldarg.0
IL_0014: ldfld 17 00 00 0A
IL_0019: box 02 00 00 1B
IL_001E: callvirt System.Text.StringBuilder.Append
IL_0023: pop
IL_0024: ldloc.0
IL_0025: ldstr " }"
IL_002A: callvirt System.Text.StringBuilder.Append
IL_002F: pop
IL_0030: ldloc.0
IL_0031: callvirt System.Object.ToString
IL_0036: stloc.1
IL_0037: br.s IL_0039
IL_0039: ldloc.1
IL_003A: ret
<>f__AnonymousType2`1.Equals:
IL_0000: ldarg.1
IL_0001: isinst 07 00 00 1B
IL_0006: stloc.0
IL_0007: ldloc.0
IL_0008: brfalse.s IL_0022
IL_000A: call 10 00 00 0A
IL_000F: ldarg.0
IL_0010: ldfld 17 00 00 0A
IL_0015: ldloc.0
IL_0016: ldfld 17 00 00 0A
IL_001B: callvirt 11 00 00 0A
IL_0020: br.s IL_0023
IL_0022: ldc.i4.0
IL_0023: nop
IL_0024: stloc.1
IL_0025: br.s IL_0027
IL_0027: ldloc.1
IL_0028: ret
<>f__AnonymousType2`1.GetHashCode:
IL_0000: ldc.i4 21 74 32 1C
IL_0005: stloc.0
IL_0006: ldc.i4 29 55 55 A5
IL_000B: ldloc.0
IL_000C: mul
IL_000D: call 10 00 00 0A
IL_0012: ldarg.0
IL_0013: ldfld 17 00 00 0A
IL_0018: callvirt 14 00 00 0A
IL_001D: add
IL_001E: stloc.0
IL_001F: ldloc.0
IL_0020: stloc.1
IL_0021: br.s IL_0023
IL_0023: ldloc.1
IL_0024: ret
<>f__AnonymousType2`1..ctor:
IL_0000: ldarg.0
IL_0001: call System.Object..ctor
IL_0006: ldarg.0
IL_0007: ldarg.1
IL_0008: stfld 17 00 00 0A
IL_000D: ret
when it executes, it generates the following SQL query, which takes 661 ms to execute:
SELECT
[Extent1].[Id] AS [Id],
CAST( [Extent2].[NumericValue] AS nvarchar(max)) AS [C1]
FROM [dbo].[Participants] AS [Extent1]
INNER JOIN [dbo].[ListAnswerSelections] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[ListAnswer_RelatedParticipantId]) AND (434 = [Extent2].[ListAnswer_RelatedQuestionId])
-- Executing at 08-Oct-15 3:26:59 PM +03:00
-- Completed in 661 ms with result: SqlDataReader
What was Entity Framework (version="6.1.0.133") doing all that time?
Little change to the LINQ query, namely removing ToString() call in the select part, makes it execute for 749 ms:
var _context = new SurveyContext();
_context.Database.Log = Console.WriteLine;
(from p in _context.Participants
join row in _context.ListAnswerSelections
on new {p.Id, QuestionId = 434} equals
new {Id = row.RelatedParticipantId, QuestionId = row.RelatedQuestionId}
select new { V = row.NumericValue })
.ToList()
.Select(x => new { R = x.V.ToString() })
.Count()
.Dump("Results");
with the following generated SQL query:
SELECT
[Extent1].[Id] AS [Id],
[Extent2].[NumericValue] AS [NumericValue]
FROM [dbo].[Participants] AS [Extent1]
INNER JOIN [dbo].[ListAnswerSelections] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[ListAnswer_RelatedParticipantId]) AND (434 = [Extent2].[ListAnswer_RelatedQuestionId])
-- Executing at 08-Oct-15 3:33:33 PM +03:00
-- Completed in 367 ms with result: SqlDataReader
I can see that cast to string has degraded performance of SQL execution, 1.8 times. But the performance of EF degraded 11.2 times. Why is this the case? And if I really need to have ToString() in my select section of the query, how can I avoid this performance penalty?
519
Entity Framework has the following forms of caching built-in: Object caching – the ObjectStateManager built into an ObjectContext instance keeps track in memory of the objects that have been retrieved using that instance. This is also known as first-level cache.
EF5 is built into the core of . NET 4.5, whereas EF6 has been shifted out, and is open source. This means that you must add the new EF6 assemblies to all of the relevant projects in the solution, in particular the entry project. This means that you must remove assembly System.
AsEnumerable() would execute the query using LINQ-to-SQL, then use LINQ-to-Objects for the Count , Sum , and Average . Instead, when I do this in LINQPad, I see the same query is run three times. If I replace . AsEnumerable() with .
Using Contains in Entity Framework is actually very slow. It's true that it translates into an IN clause in SQL and that the SQL query itself is executed fast. But the problem and the performance bottleneck is in the translation from your LINQ query into SQL.
So I ran the following two expressions through LINQPad on AdventureWorks to test out a theory:
from p in Persons
join row in BusinessEntityAddresses
on new {p.BusinessEntityID} equals new {row.BusinessEntityID}
select new { V = row.ModifiedDate }
and
from p in Persons
join row in BusinessEntityAddresses
on new {p.BusinessEntityID} equals new {row.BusinessEntityID}
select new { V = row.ModifiedDate.ToString() }
Both had some reflection involved:
IL_017D: ldtoken <>f__AnonymousType1<System.String>.get_V
IL_0182: ldtoken <>f__AnonymousType1<System.String>
IL_0187: call System.Reflection.MethodBase.GetMethodFromHandle
IL_018C: castclass System.Reflection.MethodInfo
But the version with select new ... .ToString() generated several additional reflection calls:
IL_014E: call System.Reflection.FieldInfo.GetFieldFromHandle
IL_0153: call System.Linq.Expressions.Expression.Field
IL_0158: ldtoken System.Object.ToString
IL_015D: call System.Reflection.MethodBase.GetMethodFromHandle
IL_0162: castclass System.Reflection.MethodInfo
IL_0167: ldc.i4.0
IL_0168: newarr System.Linq.Expressions.Expression
IL_016D: call System.Linq.Expressions.Expression.Call
IL_0172: stelem.ref
IL_0173: ldloc.1 // CS$0$0001
IL_0174: ldc.i4.1
IL_0175: newarr System.Reflection.MethodInfo
IL_017A: stloc.2 // CS$0$0002
IL_017B: ldloc.2 // CS$0$0002
IL_017C: ldc.i4.0
It seems like calling .ToString in the LINQ Query is causing extra (duplicative) reflection overhead for each entry. When you call it afterwards, the compiler knows how to inline the reflection to get to .ToString more efficiently.
It seems like the bottom line is that you should avoid calling methods within LINQ Queries, as additional reflection overhead (GetMethodFromHandle, MethodInfo) gets baked into your query in ways that you wouldn't expect.
118
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
