better Paging techniques without using offset

Question

Hi,

I want to replace this paging technique with a high performance solution. Please see and suggest.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
ALTER PROCEDURE [dbo].[spGetAllList] 	  
	@Take int = 0,  
	@Skip int = 0	  
AS  
BEGIN  
  
Declare @fromRow int = @Take * (@Skip);  
Declare @toRow int = @Take * (@Skip +1);  
  
  
        Select cn.Id,cn.CustomerName, cn.AgrNumber, cn.ResNumber, c.Id CarId, c.Make, c.Model, c.Group, c.Tariff, cn.StartDate, cn.EndDate  
        From Contract cn   
        Left Join Cars c on cn.CarId = c.Id  
	Order By CAST(cn .AgrNumber as int) DESC	, CAST(cn .ResNumber as int) DESC  
	OFFSET @fromRow ROWS  
	FETCH NEXT @Take ROWS ONLY;  
  
END  
END  

I want to re-write this paging logic through offset which should be better from it and can achieve more performance.

Answer 1

Hi @mehmood tekfirst
Try this:

;WITH CTE AS  
(      
 Select cn.Id,cn.CustomerName, cn.AgrNumber, cn.ResNumber, c.Id CarId, c.Make, c.Model, c.Group, c.Tariff, cn.StartDate, cn.EndDate  
       ,ROW_NUMBER() OVER(ORDER BY CAST(cn .AgrNumber as int) DESC , CAST(cn .ResNumber as int) DESC) AS PageIndex  
 From Contract cn Left Join Cars c on cn.CarId = c.Id  
)  
SELECT *  
FROM CTE  
WHERE PageIndex > @fromRow AND PageIndex <= @toRow  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Your performance issue is not in the OFFSET/FETCH. To do paging it must run the entire query, and sort it to find the window you want to return. That part is the same regardless of your method. You need to tune your query.

What is the data type of AgrNumber and ResNumber? Why are you casting them to INT in the order by? Is there a proper index created?

Answer 3

I guess the problem is here:

   Order By CAST(cn .AgrNumber as int) DESC    , CAST(cn .ResNumber as int) DESC  

What are the actual data types of these columns? What indexes are there on the table?

I would guess that the execution plan results in a full sort of the data every time. If you would have a clustered index on these columns, and their data type would be int, so the CAST is not needed., the SP would only have to read @Take * (@skip + 1) rows.