EF分类&分页 - 缓慢订购两次？ [英] EF sorting & paging - slow as ordered twice?
问题描述
public class Entity
{
public int Id {get;组; }
public string Field1 {get;组; }
public string Field2 {get;组; }
}
我想在网格中显示这些，分页为100,000许多显示在一个屏幕上（而不是很有效率）。网格应该允许排序和过滤 - 显然，所有这三个操作最好在服务器上完成，而EF应该翻译这些操作。
所以，让我们通过Field1排序500的第二页：
var items = context.Entities.OrderBy（e => e.Field1）.Skip（500）.Take（500）;
执行此查询时，需要 12秒！所以我挖了它，发现它被翻译如下：
SELECT TOP（500）[Extent1]。[Id] AS [ Id]，
[Extent1]。[Field1] AS [Field1]，
[Extent1]。[Field2] AS [Field2]
FROM（SELECT [Extent1]。[Id] AS [ Id]，
[Extent1]。[Field1] AS [Field1]，
[Extent1]。[Field2] AS [Field2]，
row_number（）OVER（ORDER BY [Extent1]）。 [Field1] AS [row_number]
FROM [dbo]。[Costs] AS [Extent1]）AS [Extent1]
WHERE [Extent1]。[row_number]> 500
ORDER BY [Extent1]。[Field1] ASC
当然这是两次？我不是SQL专家，而是通过Field1的子查询命令，并将此顺序分配给row_number。然后，我们将超过500个的TOP 500行数超过500，最多可以获得500行第2页。我们不需要再次通过Field1对结果进行排序。
如果我最终 ORDER BY [Extent1]。[Field1] ASC ，查询结果似乎相同，执行时间下降到大约150毫秒。
所以，显然150ms比12s更好 - 有什么我做错了吗？有没有办法解决这个问题？
更新
查询两者的计划是一样的。 Sort的工具提示唯一的区别是12S查询的4,604的实际行数和150ms查询的1,134。我会补充说，这是从固定的15个单词列表（为此测试）生成的数据 - 即Field1包含15个值中的1，因此基本上共有15组6,666行。
（点击放大图片）
这是由于在组合TOP和Gather Streams时，SQL Server中的错误/异常。 索引将修复它，以及禁用并行性（全局或为该用户或查询）。线索是汇集Streams溢出到tempdb，这是非常罕见的情况。 http://sqlblog.com /blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx 这是500级的东西。
注意，您不能遗漏最终的 ORDER BY ，因为这使得结果的顺序未定义。
I have a simple entity, and I have 100,000 of them in my SQL Sever 2012 database:
public class Entity
{
public int Id { get; set; }
public string Field1 { get; set; }
public string Field2 { get; set; }
}
I want to show these in a grid, paged as 100,000 is far too many to show on one screen (and not very efficient). The grid should allow sorting and filtering - obviously all 3 of these operations are best done on the server, and EF should translate these.
So, lets get the second page of 500 sorted by Field1:
var items = context.Entities.OrderBy(e => e.Field1).Skip(500).Take(500);
When this query is executed, it takes 12 seconds! So I dug into it and found it's translated as below:
SELECT TOP (500) [Extent1].[Id] AS [Id],
[Extent1].[Field1] AS [Field1],
[Extent1].[Field2] AS [Field2]
FROM (SELECT [Extent1].[Id] AS [Id],
[Extent1].[Field1] AS [Field1],
[Extent1].[Field2] AS [Field2],
row_number() OVER (ORDER BY [Extent1].[Field1] ASC) AS [row_number]
FROM [dbo].[Costs] AS [Extent1]) AS [Extent1]
WHERE [Extent1].[row_number] > 500
ORDER BY [Extent1].[Field1] ASC
Surely this is being sorted twice? I'm no SQL expert, but the sub-query orders by Field1 and assigns this order to row_number. Then we take the TOP 500 row_numbers over 500 to get up to 500 rows for page 2. We don't need to order the results by Field1 again.
If I take out the final ORDER BY [Extent1].[Field1] ASC, the query results seem to be the same and the execution time drops to a circa 150 milliseconds.
So, obviously 150ms is preferable to 12s - is there anything I'm doing wrong? Is there anything I can do to fix this?
Update
The query plan is the same for both. The only difference on the tooltip for the Sort is 'Actual Number of Rows' of 4,604 for the 12s query and 1,134 for the 150ms query. I would add that this is generated data from a fixed list of 15 words (for this test) - i.e. Field1 contains 1 of 15 values, so there are essentially 15 groups of 6,666 rows.
This is due to a bug/idiosyncrasy in SQL Server when TOP and Gather Streams are combined. An index will fix it, as will disabling parallelism (globally, or for this user, or for the query). The clue was that Gather Streams spilled to tempdb which is an exceedingly rare condition. http://sqlblog.com/blogs/paul_white/archive/2012/05/03/parallel-row-goals-gone-rogue.aspx This is 500 level stuff.
Note, that you can't leave out the final ORDER BY because this makes the order of the results undefined.
这篇关于EF分类&分页 - 缓慢订购两次？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
