加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

c# – 为什么ToString()会极大地降低Entity Framework的性能

发布时间：2020-12-15 18:25:10 所属栏目：百科 来源：网络整理
导读：我有这个简单的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 =
我有这个简单的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：

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

什么是实体框架(版本=“6.1.0.133”)一直在做什么？

对LINQ查询的一点改动,即在select部分中删除ToString()调用,使其执行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 = 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倍.为什么会这样？如果我真的需要在查询的选择部分中使用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

但是选择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)会以你不期望的方式进入查询.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同