为什么ToString()会如此显著地降低实体框架的性能?
本文关键字：实体 框架 性能 ToString 为什么 | 更新日期: 2023-09-27 18:16:35
我有这个简单的LINQ查询，执行时间为8395毫秒:
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");
:
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
执行时，生成以下SQL查询，执行时间为661 ms:
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
Entity Framework (version="6.1.0.133")一直在做什么?
对LINQ查询做了一点改动，即删除了select部分中的ToString()调用，使其执行时间为749毫秒:
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");
，生成如下SQL查询:
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
我可以看到，转换为字符串已经降低了SQL执行的性能，1.8倍。但EF的性能下降了11.2倍。为什么会这样呢?如果我真的需要在查询的select部分中使用ToString()，我该如何避免这种性能损失?
所以我在AdventureWorks的LINQPad上运行了以下两个表达式来测试一个理论:
from p in Persons
join row in BusinessEntityAddresses
on new {p.BusinessEntityID} equals new {row.BusinessEntityID}
select new { V = row.ModifiedDate }
和
from p in Persons
join row in BusinessEntityAddresses
on new {p.BusinessEntityID} equals new {row.BusinessEntityID}
select new { V = row.ModifiedDate.ToString() }
都有一些反射:
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
但是使用select new ... .ToString()的版本生成了几个额外的反射调用:
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
似乎在LINQ查询中调用。tostring会对每个条目造成额外的(重复的)反射开销。当你在之后调用它时，编译器知道如何内联反射以更有效地到达.ToString。
似乎底线是你应该避免在LINQ查询中调用方法，因为额外的反射开销(GetMethodFromHandle, MethodInfo)以你意想不到的方式被烤到你的查询中。
看起来延迟实际上是由双重转换(整数->字符串->整数)引入的。域对象上的属性是强类型的整数(我假设)。当使用.ToString执行Linq查询时，字段在SQL服务器上从整数转换为字符串，正如您在问题中注意到的那样，这会带来轻微的延迟。然而，数据实际上是以该字符串格式从SQL返回到EF的。但是，您的域对象属性是一个整数，因此EF必须将该字符串转换回整数以填充对象。EF必须解析该字符串，很可能使用Integer。在每一行执行TryParse，这会产生非常大的影响。
我相信，如果您将性能分析器附加到应用程序中，您将看到它将linq查询的大部分时间花在integer.TryParse.
更新
如果你想让它更快，只需使用(你可能需要为那个查询创建一个视图模型)
select new { V = row.NumericValue as string})
select new { V = row.NumericValue.ToString() })
当你调用Int32.ToString()它调用Number。from mscorlib.
然而，FormatInt32是在一个系统dll中定义的，它的源代码在这里
事情是这样的:
for (int i = 300000; i >= 0; i--)
{
ToString() //Calls Number.FormatInt32 from mscorlib which is defined to an external dll
{
FormatInt32() //From mscorlib: Call an external dll from the system
{
FormatInt32() //From ComNumber: Determine the type of convertion
NumberToString() // Execute the convertion
}
}
}
老回答
您从该查询中获得了多少个结果?
执行SQL查询，然后使用for循环将结果(Int)转换为String。查看此处的详细信息:将In转换为字符串的最快方法
对于每个获取到的Integer，它执行以下操作
for (int x = 0; x < NumberOfIterations; x++)
{
s[x] = i[x].ToString();
}
其他性能问题
另外，在运行时创建匿名类型而不是声明模型可能会增加开销。LINQ中的匿名类型查询或普通查询
还可以看到:c#匿名类型foreach循环，有更好的方法吗?