Entity framewrok (linq to entity)查询优化的一点摸索
对于linq to entity 生成的复杂sql语句相信已经困扰大家很久了，本人也是。从接触实体框架到现在，一直都是边学边用，用啥学啥，没有系统的学习过。同时所接触项目也对性能方面没什么要求，所以本人虽然对于EF生成的又臭又长的SQL相当不爽，但也没花时间去优化过。
今天难得有空，便尝试着做了小小的优化，略有收获，分享出来，希望能对大家也有帮助。环境是EF4.0+SQL2008.
先说说我的思路
首先我们知道，从我们写的LINQ或LAMBDA语句到生成的SQL 是有一个翻译的过程的，这个翻译是程序进行的，它必定按照已定的某种规则去翻译。同样的一个结果的查询语句，我们选择不同的写法就会出现不同的SQL语句。所以同一个查询语句尝试不同的写法，对比生成的SQL语句，也许能让我们摸出EF的翻译规则。
优化过程
我在项目中找了一段并不十分复杂也不算十分简单的查询语句，它涉及了5张表,4次join 。先来看看最初版本的代码和生成的SQL吧
1 var baseResult = from p in Database.CreditAuditDS 2 join p2 in Database.RoleDS on p.ToCreditFlow.RoleId equals p2.ROLESID 3 join p3 in Database.VEnterpriseDS on p.ToCreditDeclare.EnterpriseCode equals p3.Code 4 where p2.ROLESID == roleId && p.ToCreditFlow.Level == level && p.ToCreditDeclare.AuditState == p.ToCreditFlow.Id 5 select new ViewCreditAudit 6 { 7 Id = p.Id, 8 DeclareId = p.ToCreditDeclare.Id, 9 DeclareCode = p.ToCreditDeclare.DeclareCode, 10 Content = p.ToCreditDeclare.Content, 11 CreatedAt = p.CreatedAt, 12 EnterpriseName = p3.Name, 13 Result = p.Result, 14 Title = p.ToCreditDeclare.Title 15 };
1 exec sp_executesql N'SELECT 2 [Extent1].[Id] AS [Id], 3 [Extent1].[DeclareId] AS [DeclareId], 4 [Extent10].[DeclareCode] AS [DeclareCode], 5 [Extent10].[Content] AS [Content], 6 [Extent1].[CreatedAt] AS [CreatedAt], 7 [Extent5].[Name] AS [Name], 8 [Extent1].[Result] AS [Result], 9 [Extent10].[Title] AS [Title] 10 FROM [dbo].[T_Credit_Audit] AS [Extent1] 11 INNER JOIN [dbo].[M_ROLES] AS [Extent2] ON EXISTS (SELECT 12 1 AS [C1] 13 FROM ( SELECT 1 AS X ) AS [SingleRowTable1] 14 LEFT OUTER JOIN (SELECT 15 [Extent3].[Id] AS [Id], 16 [Extent3].[RoleId] AS [RoleId] 17 FROM [dbo].[T_Credit_Flow] AS [Extent3] 18 WHERE [Extent1].[FlowId] = [Extent3].[Id] ) AS [Project1] ON 1 = 1 19 LEFT OUTER JOIN (SELECT 20 [Extent4].[Id] AS [Id] 21 FROM [dbo].[T_Credit_Flow] AS [Extent4] 22 WHERE [Extent1].[FlowId] = [Extent4].[Id] ) AS [Project2] ON 1 = 1 23 WHERE [Project1].[RoleId] = [Extent2].[ROLESID] 24 ) 25 INNER JOIN (SELECT 26 [V_Enterprise].[Id] AS [Id], 27 [V_Enterprise].[Code] AS [Code], 28 [V_Enterprise].[AreaCode] AS [AreaCode], 29 [V_Enterprise].[Name] AS [Name], 30 [V_Enterprise].[Type] AS [Type] 31 FROM [dbo].[V_Enterprise] AS [V_Enterprise]) AS [Extent5] ON EXISTS (SELECT 32 1 AS [C1] 33 FROM ( SELECT 1 AS X ) AS [SingleRowTable2] 34 LEFT OUTER JOIN (SELECT 35 [Extent6].[Id] AS [Id], 36 [Extent6].[EnterpriseCode] AS [EnterpriseCode] 37 FROM [dbo].[T_Credit_Declare] AS [Extent6] 38 WHERE [Extent1].[DeclareId] = [Extent6].[Id] ) AS [Project4] ON 1 = 1 39 LEFT OUTER JOIN (SELECT 40 [Extent7].[Id] AS [Id], 41 [Extent7].[EnterpriseCode] AS [EnterpriseCode] 42 FROM [dbo].[T_Credit_Declare] AS [Extent7] 43 WHERE [Extent1].[DeclareId] = [Extent7].[Id] ) AS [Project5] ON 1 = 1 44 WHERE ([Project4].[EnterpriseCode] = [Extent5].[Code]) OR (([Project5].[EnterpriseCode] IS NULL) AND ([Extent5].[Code] IS NULL)) 45 ) 46 LEFT OUTER JOIN [dbo].[T_Credit_Flow] AS [Extent8] ON [Extent1].[FlowId] = [Extent8].[Id] 47 INNER JOIN [dbo].[T_Credit_Declare] AS [Extent9] ON ([Extent1].[FlowId] = [Extent9].[AuditState]) AND ([Extent1].[DeclareId] = [Extent9].[Id]) 48 LEFT OUTER JOIN [dbo].[T_Credit_Declare] AS [Extent10] ON [Extent1].[DeclareId] = [Extent10].[Id] 49 WHERE ([Extent2].[ROLESID] = @p__linq__0) AND ([Extent8].[Level] = @p__linq__1)',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=58,@p__linq__1=2
很长吧，这样的SQL语句我已经在SQL SERVER PROFILER中见过无数次了。刚才说过，这个查询只是对5个表已经连接查询，理想状态，也就是我们自己手写代码，4个inner join 就可以了。但是我们看看EF为我们生成的代码吧，复杂的对于我这个SQL菜鸟都看不大懂了，反正不管是inner join 还是left outer join ，JOIN这个关键词出现了一共9次。
接下来看看这个查询语句的第二个版本和生成的SQL吧
1 var baseResult = from p in Database.CreditDeclareDS 2 from p2 in p.ToCreditAudit 3 join p3 in Database.RoleDS on p2.ToCreditFlow.RoleId equals p3.ROLESID 4 join p4 in Database.VEnterpriseDS on p.EnterpriseCode equals p4.Code 5 where p3.ROLESID == roleId && p2.ToCreditFlow.Level == level && p.AuditState == p2.ToCreditFlow.Id 6 select new ViewCreditAudit 7 { 8 Id = p2.Id, 9 DeclareId = p.Id, 10 DeclareCode = p.DeclareCode, 11 Content = p.Content, 12 CreatedAt = p2.CreatedAt, 13 EnterpriseName = p4.Name, 14 Result = p2.Result, 15 Title = p.Title 16 };
1 exec sp_executesql N'SELECT 2 [Extent1].[Id] AS [Id], 3 [Extent2].[Id] AS [Id1], 4 [Extent1].[DeclareCode] AS [DeclareCode], 5 [Extent1].[Content] AS [Content], 6 [Extent2].[CreatedAt] AS [CreatedAt], 7 [Extent6].[Name] AS [Name], 8 [Extent2].[Result] AS [Result], 9 [Extent1].[Title] AS [Title] 10 FROM [dbo].[T_Credit_Declare] AS [Extent1] 11 INNER JOIN [dbo].[T_Credit_Audit] AS [Extent2] ON ([Extent1].[Id] = [Extent2].[DeclareId]) AND ([Extent1].[AuditState] = [Extent2].[FlowId]) 12 INNER JOIN [dbo].[M_ROLES] AS [Extent3] ON EXISTS (SELECT 13 1 AS [C1] 14 FROM ( SELECT 1 AS X ) AS [SingleRowTable1] 15 LEFT OUTER JOIN (SELECT 16 [Extent4].[Id] AS [Id], 17 [Extent4].[RoleId] AS [RoleId] 18 FROM [dbo].[T_Credit_Flow] AS [Extent4] 19 WHERE [Extent2].[FlowId] = [Extent4].[Id] ) AS [Project1] ON 1 = 1 20 LEFT OUTER JOIN (SELECT 21 [Extent5].[Id] AS [Id] 22 FROM [dbo].[T_Credit_Flow] AS [Extent5] 23 WHERE [Extent2].[FlowId] = [Extent5].[Id] ) AS [Project2] ON 1 = 1 24 WHERE [Project1].[RoleId] = [Extent3].[ROLESID] 25 ) 26 INNER JOIN (SELECT 27 [V_Enterprise].[Id] AS [Id], 28 [V_Enterprise].[Code] AS [Code], 29 [V_Enterprise].[AreaCode] AS [AreaCode], 30 [V_Enterprise].[Name] AS [Name], 31 [V_Enterprise].[Type] AS [Type] 32 FROM [dbo].[V_Enterprise] AS [V_Enterprise]) AS [Extent6] ON [Extent1].[EnterpriseCode] = [Extent6].[Code] 33 INNER JOIN [dbo].[T_Credit_Flow] AS [Extent7] ON [Extent2].[FlowId] = [Extent7].[Id] 34 WHERE ([Extent3].[ROLESID] = @p__linq__0) AND ([Extent7].[Level] = @p__linq__1)',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=58,@p__linq__1=2
当我看到这次生成的SQL时，我心中不由一喜：貌似看到曙光了。。。
首先生成的SQL语句缩短了很多字符，JOIN这个关键字这次只出现了6次。经过反复的对比，我发现了一些门道，于是按着这个门道，我再次修改。以下是最后的代码和生成的SQL。
1 var baseResult = from p in Database.CreditDeclareDS 2 from p2 in p.ToCreditAudit 3 join p5 in Database .CreditFlowDS on p2.FlowId equals p5 .Id 4 join p3 in Database.RoleDS on p5.RoleId equals p3.ROLESID 5 join p4 in Database.VEnterpriseDS on p.EnterpriseCode equals p4.Code 6 where p3.ROLESID == roleId && p5.Level == level && p.AuditState == p5.Id 7 select new ViewCreditAudit 8 { 9 Id = p2.Id, 10 DeclareId = p.Id, 11 DeclareCode = p.DeclareCode, 12 Content = p.Content, 13 CreatedAt = p2.CreatedAt, 14 EnterpriseName = p4.Name, 15 Result = p2.Result, 16 Title = p.Title 17 };
1 exec sp_executesql N'SELECT 2 [Extent1].[Id] AS [Id], 3 [Extent2].[Id] AS [Id1], 4 [Extent1].[DeclareCode] AS [DeclareCode], 5 [Extent1].[Content] AS [Content], 6 [Extent2].[CreatedAt] AS [CreatedAt], 7 [Extent5].[Name] AS [Name], 8 [Extent2].[Result] AS [Result], 9 [Extent1].[Title] AS [Title] 10 FROM [dbo].[T_Credit_Declare] AS [Extent1] 11 INNER JOIN [dbo].[T_Credit_Audit] AS [Extent2] ON [Extent1].[Id] = [Extent2].[DeclareId] 12 INNER JOIN [dbo].[T_Credit_Flow] AS [Extent3] ON ([Extent2].[FlowId] = [Extent3].[Id]) AND ([Extent1].[AuditState] = [Extent3].[Id]) 13 INNER JOIN [dbo].[M_ROLES] AS [Extent4] ON [Extent3].[RoleId] = [Extent4].[ROLESID] 14 INNER JOIN (SELECT 15 [V_Enterprise].[Id] AS [Id], 16 [V_Enterprise].[Code] AS [Code], 17 [V_Enterprise].[AreaCode] AS [AreaCode], 18 [V_Enterprise].[Name] AS [Name], 19 [V_Enterprise].[Type] AS [Type] 20 FROM [dbo].[V_Enterprise] AS [V_Enterprise]) AS [Extent5] ON [Extent1].[EnterpriseCode] = [Extent5].[Code] 21 WHERE ([Extent4].[ROLESID] = @p__linq__0) AND ([Extent3].[Level] = @p__linq__1)',N'@p__linq__0 int,@p__linq__1 int',@p__linq__0=58,@p__linq__1=2
这才是我想要生成的SQL，4次inner join ，除了视图V_Enterprise这里有点瑕疵，基本符合要求了。
说说我的分析
与其说是分析，不如说是观察结果。
在我的第一个代码版本中，我只用了两次join，但是却在from 和where 中用了两个关联属性。
在我的第二个代码版本中，我用了两次join 和额外的from ，相当于三次join 用了一个关联属性。
在我的第三个代码版本中，我相当于用了四次join，但没有再使用关联属性。
那么，我们可以简单的得出这样的一个结果：尽量使用join语句，尽量不用关联属性。。。
以上就是我的一个简单粗糙的优化，也许得出的结论并不正确，但相信启发还是有的。希望大牛们能拿出更全面的优化方案。
