我的数据库中有一个Entity
视图,代码中还有一个代表该视图的Entity
类。为了获得某个页面的结果,我正在这样做:
var result = await dbContext.Entity
.OrderByDescending(e => e.CreatedOn)
.ThenByDescending(e => e.Id)
.Skip((currentPage - 1) * itemsPerPage)
.Take(itemsPerPage)
.ToListAsync();
对于50个项目(currentPage = 1
、itemsPerPage = 50
(的第一页,EF生成以下内容:
SELECT
[Extent1].[OrderId] AS [Id],
[Extent1].[Number] AS [CreatedOn],
...
FROM [dbo].[Entity] AS [Extent1]
ORDER BY row_number() OVER (ORDER BY [Extent1].[CreatedOn] DESC, [Extent1].[Id] DESC)
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
问题是查询执行的时间很长。这就是为什么我尝试不使用row_number((,它更快,我得到了相同的结果:
SELECT
[Extent1].[OrderId] AS [Id],
[Extent1].[Number] AS [CreatedOn],
...
FROM [dbo].[Entity] AS [Extent1]
ORDER BY [Extent1].[CreatedOn] DESC, [Extent1].[Id] DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
因此,我的问题是:
1(为什么这里通常使用row_number((
2(是否存在强制EF在此查询中不使用它的原因?
UPDATE:在视图使用的表上添加聚集索引有助于提高性能。谢谢
看看这是否更快:
var result = await dbContext.Entity
.OrderByDescending(e => e.CreatedOn)
.ThenByDescending(e => e.Id)
.Select((x,i) => new {index = i, item = x})
.GroupBy(x => x.index / itemsPerPage)
.Select(x => x.Select(y => y.item).ToList())
.ToListAsync();