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语句，尽量不用关联属性。。。

 

---

以上就是我的一个简单粗糙的优化，也许得出的结论并不正确，但相信启发还是有的。希望大牛们能拿出更全面的优化方案。

转载于:https://www.cnblogs.com/xxfss2/archive/2012/06/15/2550826.html