ROW_NUMBER排序操作员 [英] ROW_NUMBER SORT OPERATOR

问题描述

我创建了下表进行测试：

 CREATE Table tblAuthors 
（
 Id int identity ，
作者名称nvarchar（50），
国家nvarchar（50）
）
 
声明@Id int 
设置@Id = 1 
 
而@Id< = 12000 
开始
插入tblAuthors值（'作者 - '+ CAST（@Id as nvarchar（10）），
'国家 - '+ CAST（@Id as nvarchar（10））+'name'）
 
 Set @Id = @Id + 1 
 End 
 CREATE CLUSTERED INDEX [ix_Author_PK] on tblAuthors（Id ）; 
 CREATE NONCLUSTERED INDEX [ix_Author] ON tblAuthors（Id，Country DESC）INCLUDE（Author_name）; 

如果我运行此查询：

 SELECT TOP 10 Id，Author_name，
 ROW_NUMBER（）OVER（[Id] ORDER BY [country] DESC的分区）作为
来自tblAuthors 

"row_number（）"函数不会在执行计划中产生任何排序运算符，因为结果已经在索引"ix_Author"中排序。一切都很棒。

现在我想过滤一些id：

 CREATE TABLE #tmp（Id INT ）
 
 INSERT INTO #tmp VALUES（1），（3），（5）
 
 SELECT a.Id，a.Author_name，
 ROW_NUMBER（）OVER （a。[Id] ORDER BY a。[country] DESC）AS 
 FROM tblAuthors a 
 INNER JOIN #tmp t ON a.Id = t.Id; 

现在您在执行计划中有排序运算符，成本为62％。如果我只是从已经排序的结果集中排除了一些id，为什么会有sort运算符 - 子结果也应该排序？我没有在这里看到排序运算符的重点。

如何通过过滤较大的已排序结果集来获取排序结果集？

我可以在过滤之前移动rownumber函数，但更糟糕的是，因为你必须在所有记录上执行它而不是仅过滤一个（并且CTE也会在后面生成临时表，这也是一些性能损失）：

; WITH cte AS（
 SELECT a.Id，a.Author_name，
 ROW_NUMBER（）OVER（PARTITION BY a。[Id] ] ORDER BY a。[country] DESC）AS 
 FROM tblAuthors a 
）
 SELECT c。* FROM cte c 
 INNER JOIN #tmp t ON c.Id = t .Id; 

那么，任何想法如何省略过滤结果集的排序？

解决方案

Hi
simonxy ，

 

在SQL Server执行计划中，Sort-Merge被拆分为两个运算符，Sort和Merge-Join，因为排序操作可能不是必需的，例如，如果数据已排序
已经。

 

所以在你的执行计划中，SORT
是由JOIN引起的 而不是
ROW_NUMBER 。在以下脚本中， 
我会建议你创建 CLUSTERED INDEX 对于你的表' tblAuthors '和 
' ＃tmp '请尝试我的脚本。
我比较了
的执行计划 这三个 script 并发现我的费用最少 而SORT将为
消失 。

  

  -  drop table tblAuthors d rop table #tmp 
 CREATE表tblAuthors 
（
 Id int identity，
 Author_name nvarchar（50），
 country nvarchar（50）
）
 
声明@Id int 
设置@Id = 1 
而@Id< = 12000 
开始
插入tblAuthors值（'作者 - '+ CAST （@Id as nvarchar（10）），
'Regine  - '+ CAST（@Id as nvarchar（10））+'name'）
 
 Set @Id = @Id + 1 
结束
 go 
 创建集群索引[ix_Author] ON tblAuthors（Id，Country DESC） 
 go 
 CREATE TABLE #tmp（Id INT）
 INSERT INTO #tmp VALUES（1），（3），（5）
 go 
  CREATE CLUSTERED INDEX [ix_tmp] ON #tmp（Id） 
 go 
  ------你的第一个脚本<成本  11％ >  
 SELECT a.Id，a.Author_name，
 ROW_NUMBER （）OVER（分段a。[Id] ORDER BY a。[country] DESC）AS 
 FROM tblAuthors a 
 INNER JOIN #tmp t ON a.Id = t.Id; 
  ------你的第二个脚本<成本  85％ >  
; WITH cte AS（
 SELECT a.Id， a.Author_name，
 ROW_NUMBER（）OVER（由a。[Id] ORDER BY a。[country] DESC）AS nn 
 FROM tblAuthors a 
）
 SELECT c。 * FROM cte c 
 INNER JOIN #tmp t ON c.Id = t.Id; 
 
  ------我的脚本<成本  4％ >  
 SELECT a.Id，a.Author_name，
 ROW_NUMBER （）OVER（分区一个。[Id] ORDER BY a。[country] DESC）AS 
 FROM tblAuthors a 
 where exists（select * from #tmp b where a.Id = b.id ）
 
 

最好的问候，

Rachel

I have created the following table for test:

CREATE Table tblAuthors
(
   Id int identity,
   Author_name nvarchar(50),
   country nvarchar(50)
)

Declare @Id int
Set @Id = 1

While @Id <= 12000
Begin 
   Insert Into tblAuthors values ('Author - ' + CAST(@Id as nvarchar(10)),
              'Country - ' + CAST(@Id as nvarchar(10)) + ' name')
   
   Set @Id = @Id + 1
End
CREATE CLUSTERED INDEX [ix_Author_PK] ON tblAuthors(Id);
CREATE NONCLUSTERED INDEX [ix_Author] ON tblAuthors(Id, Country DESC) INCLUDE (Author_name);

If I run this query:

SELECT TOP 10 Id, Author_name,
ROW_NUMBER() OVER(PARTITION BY [Id] ORDER BY [country] DESC) AS rn
FROM tblAuthors

the "row_number()" function will not produce any sort operator in execution plan, since results are already sorted inside index "ix_Author". Everything great.

Now I would like to filter some id:

CREATE TABLE #tmp (Id INT)

INSERT INTO #tmp VALUES(1),(3),(5)

SELECT a.Id, a.Author_name,
ROW_NUMBER() OVER(PARTITION BY a.[Id] ORDER BY a.[country] DESC) AS rn
FROM tblAuthors a
INNER JOIN #tmp t ON a.Id=t.Id;

Now you have sort operator inside execution plan with cost 62%. Why there is sort operator if I just exclude some id from already sorted result set - the sub-result should be also sorted? I don't see the point of sort operator here.

How can I get sorted result set from filtering the larger already sorted result set?

I can move rownumber function before filter, but that is even worse, since you have to execute it on all records instead on only filtered one(and CTE also produce temp table in behind, which is also some performance hit):

;WITH cte AS(
	SELECT a.Id, a.Author_name,
	ROW_NUMBER() OVER(PARTITION BY a.[Id] ORDER BY a.[country] DESC) AS rn
	FROM tblAuthors a
)
SELECT c.* FROM cte c
INNER JOIN #tmp t ON c.Id=t.Id;

So, any idea how to omit sort on filtered result set?

解决方案

Hi simonxy,

SQL Server has three algorithms to choose from when it needs to join two tables. The Nested-Loops-Join, the Hash-Join and the Sort-Merge-Join. Which one it selects it bases on cost estimates. In this case it figured, that based on the information it had available a Sort-Merge-Join was the right choice.

 

In SQL Server execution plans a Sort-Merge is split into two operators, the Sort and the Merge-Join, because the sort operation might not be necessary, for example if the data is sorted already.

 

So in your execution plan , SORT is caused by JOIN instead of ROW_NUMBER. In following script,  I will advise you to create CLUSTERED INDEX for your tables 'tblAuthors' and  '#tmp' and please try my script. I compared the execution plan of these three script and found that my cost was the least and SORT will disappear.

  

--drop table tblAuthors  drop table #tmp
CREATE Table tblAuthors
(
   Id int identity,
   Author_name nvarchar(50),
   country nvarchar(50)
)

Declare @Id int
Set @Id = 1
While @Id <= 12000
Begin 
   Insert Into tblAuthors values ('Author - ' + CAST(@Id as nvarchar(10)),
              'Country - ' + CAST(@Id as nvarchar(10)) + ' name')
   
   Set @Id = @Id + 1
End
go
CREATE CLUSTERED INDEX [ix_Author] ON tblAuthors(Id, Country DESC) 
go
CREATE TABLE #tmp (Id INT)
INSERT INTO #tmp VALUES(1),(3),(5)
go
CREATE CLUSTERED INDEX [ix_tmp] ON #tmp(Id) 
go
------your first script < cost 11%  >
SELECT a.Id, a.Author_name,
ROW_NUMBER() OVER(PARTITION BY a.[Id] ORDER BY a.[country] DESC) AS rn
FROM tblAuthors a
INNER JOIN #tmp t ON a.Id=t.Id;
------your second script < cost 85%  >
;WITH cte AS(
	SELECT a.Id, a.Author_name,
	ROW_NUMBER() OVER(PARTITION BY a.[Id] ORDER BY a.[country] DESC) AS rn
	FROM tblAuthors a
)
SELECT c.* FROM cte c
INNER JOIN #tmp t ON c.Id=t.Id;

------my  script < cost 4%  >
SELECT a.Id, a.Author_name,
ROW_NUMBER() OVER(PARTITION BY a.[Id] ORDER BY a.[country] DESC) AS rn
FROM tblAuthors a
where exists(select * from #tmp b where a.Id=b.id )

Best Regards,

Rachel

这篇关于ROW_NUMBER排序操作员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！