Sql server 2012 fetch vs old row_number performance. What am I missing? Why is row_number 17x faster?

Question

Update: Infact leave the below complicated query and please check this query. It says Fetch is 98% as compared to 2% in Row_Number?

Is Fetch yet another marketing keyword for sql server 2012?

-------------------------Original question--------------------

Let me clear that wherever I read, I find it stating that Fetch is very fast than old Row_Number function. However, I find it nearly the opposite and by a long way. My DB has nearly 0.2 million records. This is my query using 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

The above query takes 17 seconds to produce 50 records. This is the query plan:

This is the query plan XML in case it's not clear from images: https://www.dropbox.com/s/br5urj4xapazu9l/fetch.txt

Now this is the same query using old Row_Number (and using the same DB indexes and columns and Joins as 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

This query takes less than 1 second! This is the query plan for that:

So, what am I missing? Why is row_number faster than Fetch?

This is the query plan for rownum: https://www.dropbox.com/s/uin66esfb2ov8m7/rownum.txt

Answer 1

I think your problem is not OFFSET/FETCH vs ROW_NUMBER

in your original question the two queries are NOT the same,

the first query (OFFSET/FETCH) misses all filtering conditions on parameters, so it works on a larger base data and, with many JOINS the number of records could grows quickly

in second query (ROW_NUMBER) left joins are applyed after CTE execution and only for matching records (peta_rn>@12 AND peta_rn<=@13) reducing very much number of records to join.

the two queries are not comparable at all, I think that if you write the CTE version, using OFFSET/FETCH it will be faster than ROW_NUMBER version.

in fact talking about your EDIT/UPDATE, do not consider what EXECUTION PLAN tells you, just execute both and measure timings.. You will find that OFFSET/FETCH is faster anyway.

2018-10-04 EDIT/UPDATE
I did some more testing on differents scenarios and I found out that results can be different depending on indexes and table cardinality (aka COUNT(*))

If you are ordering by a column with a clustered index OFFSET/FETCH will be much faster than ROW_NUMBER. On little tables (less than 20000 rows) execution time is almost the same but with large tables OFFSET/FETCH will become soon much faster (200-300%).

If you are ordering by a column with a non clustered index, OFFSET/FETCH is never worst than ROW_NUMBER but the latter can perform well depending on parameters (table rows count, starting record and number of rows fetched).

If you are ordering by a column without any index OFFSET/FETCH is still a little faster than ROW_NUMBER but they perform almost the same.