Sql Server 2012 fetch 与旧 row_number 性能。我缺少什么？为什么 row_number 快 17 倍？

Question

更新:事实上，请留下以下复杂的查询，请检查此查询。它说 Fetch 为 98%，而 Row_Number 中为 2%？

Fetch 是 SQL Server 2012 的另一个营销关键字吗？

------------------------原问题--------------------

让我澄清一下，无论我读到什么，我都发现 Fetch 比旧的 Row_Number 函数非常快。然而，我发现情况几乎相反，而且相差甚远。我的数据库有近 20 万条记录。这是我使用 Fetch 的查询:

exec sp_executesql N'set arithabort off;set transaction isolation level read uncommitted;
                                    Select cte.DocumentID, cte.IsReEfiled, cte.IGroupID, cte.ITypeID, cte.RecordingDateTime, cte.CreatedByAccountID, cte.JurisdictionID, 
                        cte.LastStatusChangedDateTime as LastStatusChangedDateTime
                        ,  cte.IDate, cte.InstrumentID, cte.DocumentStatusID,ig.Abbreviation as IGroupAbbreviation, u.Username, j.JDAbbreviation, inf.DocumentName,
                       it.Abbreviation, cte.DocumentDate, ds.Abbreviation as DocumentStatusAbbreviation,  ds.Name as DocumentStatusName,
                        ( SELECT CAST(CASE WHEN cte.DocumentID = (
                                SELECT TOP 1 doc.DocumentID
                                FROM  Documents doc
                                WHERE doc.JurisdictionID = cte.JurisdictionID
                                        AND doc.DocumentStatusID = cte.DocumentStatusID
                                ORDER BY LastStatusChangedDateTime) 
                            THEN 1
                            ELSE 0
                        END AS BIT)
                        ) AS CanChangeStatus ,

                        Upper((Select Top 1 Stuff( (Select ''='' + dbo.GetDocumentNameFromParamsWithPartyType(Business, FirstName, MiddleName, LastName, t.Abbreviation, NameTypeID, pt.Abbreviation, IsGrantor, IsGrantee)  From DocumentNames dn
                                Left Join Titles t
                                    on dn.TitleID = t.TitleID               
                                Left Join PartyTypes pt
                                    On pt.PartyTypeID = dn.PartyTypeID
                                        Where DocumentID = cte.DocumentID
                                            For XML PATH('''')),1,1,''''))) as FlatDocumentName 

                        FROM Documents cte Left Join DocumentStatuses ds On                     
                        cte.DocumentStatusID = ds.DocumentStatusID 
                        Inner Join Users u on cte.UserID = u.UserID
                        Inner Join IGroupes ig On ig.IGroupID = cte.IGroupID
                        Inner Join ITypes it On ig.IGroupID = it.IGroupID
                        Left Join InstrumentFiles inf On cte.DocumentID = inf.DocumentID 
                    Left Join Jurisdictions j on j.JurisdictionID = cte.JurisdictionID Where 1=1
                    Order by cte.LastStatusChangedDateTime OFFSET 110700 Rows FETCH Next 50 Rows ONLY',N'@0 int,@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int',
                    @0=4,@1=1,@2=5,@3=9,@4=4,@5=1,@6=1,@7=5,@8=9,@9=4,@10=1,@11=1

上述查询需要 17 秒才能生成 50 条记录。这是查询计划:

这是查询计划 XML，以防图像不清楚: https://www.dropbox.com/s/br5urj4xapazu9l/fetch.txt

现在，这是使用旧 Row_Number 的相同查询(并使用与 Fetch 相同的数据库索引和列以及联接):

exec sp_executesql N'set arithabort off;set transaction isolation level read uncommitted;With cte as (Select peta_rn = ROW_NUMBER() OVER (ORDER BY  d.LastStatusChangedDateTime  asc )  
                                                , d.DocumentID
                                                , u.Username
                                                , it.Abbreviation AS ITypeAbbreviation
                                                , ig.Abbreviation AS IGroupAbbreviation
                                                , d.IsReEfiled 
                                                , d.IGroupID 
                                                , d.ITypeID 
                                                , d.RecordingDateTime 
                                                , d.CreatedByAccountID 
                                                , d.JurisdictionID
                                                , d.LastStatusChangedDateTime AS LastStatusChangedDateTime 
                                                , d.IDate 
                                                , d.InstrumentID 
                                                , d.DocumentStatusID
                                                , d.DocumentDate
                                From Documents d
                                Inner Join Users u on d.UserID = u.UserID Inner Join IGroupes ig on ig.IGroupID = d.IGroupID
                                Inner Join ITypes it on it.ITypeID = d.ITypeID Where 1=1  ANd d.IGroupID = @0   And (d.JurisdictionID = @1 Or DocumentStatusID = @2 Or DocumentStatusID = @3
                                    Or DocumentStatusID = @4 Or DocumentStatusID = @5)   And d.DocumentStatusID <> 3 And  d.DocumentStatusID <> 8 And  d.DocumentStatusID <> 7 AND
                                        ((CreatedByJurisdictionID = @6 Or DocumentStatusID = @7 Or DocumentStatusID = @8
                                        Or DocumentStatusID = @9 Or DocumentStatusID = @10
                                    Or CreatedByAccountID IN (Select AccountID From AccountsJurisdictions Where JurisdictionID = @11)))) Select cte.DocumentID, cte.IsReEfiled, cte.IGroupID, cte.ITypeID, cte.RecordingDateTime, cte.CreatedByAccountID, cte.JurisdictionID, 
                        cte.LastStatusChangedDateTime as LastStatusChangedDateTime
                        ,  cte.IDate, cte.InstrumentID, cte.DocumentStatusID,cte.IGroupAbbreviation, cte.Username, j.JDAbbreviation, inf.DocumentName,
                       cte.ITypeAbbreviation, cte.DocumentDate, ds.Abbreviation as DocumentStatusAbbreviation,  ds.Name as DocumentStatusName,
                        ( SELECT CAST(CASE WHEN cte.DocumentID = (
                                SELECT TOP 1 doc.DocumentID
                                FROM  Documents doc
                                WHERE doc.JurisdictionID = cte.JurisdictionID
                                        AND doc.DocumentStatusID = cte.DocumentStatusID
                                ORDER BY LastStatusChangedDateTime) 
                            THEN 1
                            ELSE 0
                        END AS BIT)
                        ) AS CanChangeStatus ,

                        Upper((Select Top 1 Stuff( (Select ''='' + dbo.GetDocumentNameFromParamsWithPartyType(Business, FirstName, MiddleName, LastName, t.Abbreviation, NameTypeID, pt.Abbreviation, IsGrantor, IsGrantee)  From DocumentNames dn
                                Left Join Titles t
                                    on dn.TitleID = t.TitleID               
                                Left Join PartyTypes pt
                                    On pt.PartyTypeID = dn.PartyTypeID
                                        Where DocumentID = cte.DocumentID
                                            For XML PATH('''')),1,1,''''))) as FlatDocumentName 

                        FROM cte Left Join DocumentStatuses ds On
                        cte.DocumentStatusID = ds.DocumentStatusID Left Join InstrumentFiles inf On cte.DocumentID = inf.DocumentID 
                    Left Join Jurisdictions j on j.JurisdictionID = cte.JurisdictionID Where 1=1 And peta_rn>@12 AND peta_rn<=@13 Order by peta_rn',N'@0 int,@1 int,@2 int,@3 int,@4 int,@5 int,@6 int,@7 int,@8 int,@9 int,@10 int,@11 int,@12 int,@13 int',@0=4,@1=1,@2=5,@3=9,@4=4,@5=1,@6=1,@7=5,@8=9,@9=4,@10=1,@11=1,@12=110700,@13=110750

此查询耗时不到 1 秒!这是其查询计划:

那么，我错过了什么？为什么 row_number 比 Fetch 快？

这是 rownum 的查询计划: https://www.dropbox.com/s/uin66esfb2ov8m7/rownum.txt

Answer 1

我认为您的问题不在于 OFFSET/FETCH 与 ROW_NUMBER

在您原来的问题中，两个查询不相同，

第一个查询(OFFSET/FETCH)错过了参数的所有过滤条件，因此它适用于更大的基础数据，并且，通过许多 JOINS，记录数量可能会快速增长

在第二个查询 (ROW_NUMBER) 中，左联接在 CTE 执行后应用，并且仅适用于匹配记录 (peta_rn>@12 AND peta_rn<=@13)，从而减少了要联接的记录数量。

这两个查询根本没有可比性，我认为如果你编写CTE版本，使用OFFSET/FETCH它会比ROW_NUMBER版本更快。

事实上，谈论您的编辑/更新时，不要考虑执行计划告诉您的内容，只需执行两者并测量时间。您会发现 OFFSET/FETCH 无论如何都更快。

2018-10-04 编辑/更新
我对不同的场景进行了更多测试，发现结果可能会有所不同，具体取决于索引和表基数(又名 COUNT(*))

如果您按具有聚集索引的列进行排序，OFFSET/FETCH 将比 ROW_NUMBER 快得多。在小表(少于 20000 行)上，执行时间几乎相同，但在大表上 OFFSET/FETCH 很快就会变得更快 (200-300%)。

如果您按具有非聚集索引的列进行排序，OFFSET/FETCH 绝不会比 ROW_NUMBER 最差，但后者可以根据参数(表行)执行良好的操作计数、起始记录和获取的行数)。

如果您按没有任何索引的列进行排序，OFFSET/FETCH 仍然比 ROW_NUMBER 快一点，但它们的性能几乎相同。