EF生成的复杂sql改写

Question

plan差跟用什么工具生成的SQL 语句关系不是很大，跟你表的索引，where条件的关系比较密切不过看你那个SQL真是头疼，不用SQL格式化工具，真是整理不出来

Answer 1

仅格式化，不看plan无法精确定位SELECT  [GroupBy3].[A1] AS [C1]
FROM    ( SELECT    COUNT([Limit1].[C1]) AS [A1]
          FROM      ( SELECT TOP ( 10 )
                                [Project3].[C1] AS [C1]
                      FROM      ( SELECT    1 AS [C1] ,
                                            [Project2].[C1] AS [C2]
                                  FROM      ( SELECT    ( SELECT
                                                              COUNT(1) AS [A1]
                                                          FROM
                                                              [dbo].[Position]
                                                              AS [Extent3]
                                                          WHERE
                                                              [Project1].[id] = [Extent3].[portfolio_id]
                                                        ) AS [C1]
                                              FROM      ( SELECT
                                                              [Extent1].[id] AS [id] ,
                                                              ( SELECT
                                                              COUNT(1) AS [A1]
                                                              FROM
                                                              [dbo].[Position]
                                                              AS [Extent2]
                                                              WHERE
                                                              [Extent1].[id] = [Extent2].[portfolio_id]
                                                              ) AS [C1]
                                                          FROM
                                                              [dbo].[Portfolio]
                                                              AS [Extent1]
                                                        ) AS [Project1]
                                              WHERE     [Project1].[C1] < 30
                                            ) AS [Project2]
                                ) AS [Project3]
                      ORDER BY  [Project3].[C2] DESC
                    ) AS [Limit1]
        ) AS [GroupBy3]  

Answer 2

这是plan， 服务器上的

Answer 3

两个表的 id 都建立了聚集index， [portfolio_id] 有非聚集index建立。

Answer 4

就是重复读取了Position 这个表两次，逻辑上一次应该就够了，如果能读一次的话减小32%的开销。应该是你写的ef部分的表达式有问题，可以贴这个看一看

Answer 5

两个16%的图标，看看是什么列导致scan。另外多处hash，是你的数据返回本身就很大？还是missing index了。建了索引没用上，那跟没建没什么区别，有时候反而更差

Answer 6

SQL优化从方面着手，首先，要看一下生成这段SQL的EF查询语句是否已是最优的写法；其次，根据生成的SQL执行plan检查是否缺少索引或者命中的索引是否最优（从Plan中查看所有索引使用Index Seek来查询就是最优）

Answer 7

》》是你的数据返回本身就很大？
只有1行返回。
看来不是那么容易修改成其他的sql了？ 

Answer 8

1个77万行，另一个表 130万行， 加了 merge join 的提示，速度也差不多。 估计是用count ，必然有index scan 了吧