c# - Entity Framework 为分页查询生成低效的 SQL

Question

我有一个针对一个实体的简单分页 linq 查询:

var data = (from t in ctx.ObjectContext.Widgets
           where t.CampaignId == campaignId && 
                 t.CalendarEventId == calendarEventId
                 (t.RecurringEventId IS NULL OR t.RecurringEventId = recurringEventId)
           select t);

data = data.OrderBy(t => t.Id);

if (page > 0)
{
    data = data.Skip(rows * (page - 1)).Take(rows);
}

var l = data.ToList(); 

我希望它生成类似于以下的 SQL:

select top 50 * from Widgets w where CampaignId = xxx AND CalendarEventId = yyy AND (RecurringEventId IS NULL OR RecurringEventId = zzz) order by w.Id

当我在 SSMS 中运行上述查询时，它很快返回(必须先重建我的索引)。

但是，生成的SQL是不同的。它包含一个嵌套查询，如下所示:

SELECT TOP (50) 
[Project1].[Id] AS [Id], 
[Project1].[CampaignId] AS [CampaignId]
<redacted>
FROM ( SELECT [Project1].[Id] AS [Id], 
[Project1].[CampaignId] AS [CampaignId], 
<redacted>, 
row_number() OVER (ORDER BY [Project1].[Id] ASC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[CampaignId] AS [CampaignId], 
        <redacted>
        FROM [dbo].[Widgets] AS [Extent1]
        WHERE ([Extent1].[CampaignId] = @p__linq__0) AND ([Extent1].[CalendarEventId] = @p__linq__1) AND ([Extent1].[RecurringEventId] = @p__linq__2 OR [Extent1].[RecurringEventId] IS NULL)
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[Id] ASC

Widgets表很大，内部查询返回100000条记录导致超时。

我能做些什么来改变这一代人吗？我做错了什么吗？

更新

我终于设法重构我的代码以相对快速地返回结果:

var data = (from t in ctx.ObjectContext.Widgets
           where t.CampaignId == campaignId && 
                 t.CalendarEventId == calendarEventId
                 (t.RecurringEventId IS NULL OR t.RecurringEventId = recurringEventId)
           select t)).AsEnumerable().Select((item, index) => new { Index = index, Item = item });

            data = data.OrderBy(t => t.Index);

            if (page > 0)
            {
                data = data.Where(t => t.Index >= (rows * (page - 1)));
            }

            data = data.Take(rows);

请注意，page > 0 逻辑仅用于防止使用无效参数；它没有优化。事实上 page > 1 虽然有效，但并未为第一页提供任何明显的优化；因为 Where 不是一个缓慢的操作。

Answer 1

在 SQL Server 2012 之前，生成的 SQL 代码是执行分页的最佳方式。是的，它很糟糕而且效率很低，但它是你能做的最好的，即使是手工编写你自己的 SQL 脚本。网上有大量关于此的数字墨水。只需用谷歌搜索即可。

在第一个页面中，这可以优化为不执行 Skip 而只是 Take 但在任何其他页面中你都 f***** 了。

一个解决方法可能是生成你自己的 row_number 持久化(自动识别可以工作)并且只做 where(widget.number > (page*rows) ).Take(rows)代码。如果您的 widget.number 中有一个好的索引，查询应该会非常快。 但是，这会破坏动态orderBy。

但是，我可以在您的代码中看到您总是通过 widget.id 进行订购；因此，如果动态 orderBy 不是必需的，这可能是一个有效的解决方法。

Will you take your own medicine?

你能问我吗

不，我不会。处理此问题的最佳方法是拥有一个持久性读取模型，您甚至可以为每个小部件 orderBy 字段创建一个表，并具有自己的 widget.number。问题是仅仅为了这个问题而用持久化读取模型来建模系统太疯狂了。拥有读取模型是系统整体设计的一部分，需要从系统设计和开发的一开始就考虑到这一点。