EF生成的复杂sql改写

随风201333 2014-04-25 04:20:59

以下是Entity Framework 生成的，请帮忙修改成其他形式，比如join，现在生成的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]

...全文

510 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

随风201333 2014-04-28

打赏
举报

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

随风201333 2014-04-28

打赏
举报

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

LZD_jay 2014-04-27

打赏
举报

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

LongRui888 2014-04-27

打赏
举报

语句还真够长的啊

發糞塗牆 2014-04-27

打赏
举报

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

随风201333 2014-04-26

打赏
举报

upupupupup

lg314 2014-04-26

打赏
举报

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

随风201333 2014-04-25

打赏
举报

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

随风201333 2014-04-25

打赏
举报

这是plan，服务器上的

發糞塗牆 2014-04-25

打赏
举报

仅格式化，不看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]