better Paging techniques without using offset

Question

better Paging techniques without using offset

mehmood tekfirst 771

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.

Viorel 126.9K Reputation points

2022-08-04T14:03:24.87+00:00

Maybe removing the CAST and defining corresponding indices (and maybe adding OPTION (RECOMPILE)) will make your statement much better, keeping it clear.

Answer accepted by question author

LiHong-MSFT 10,061

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.

mehmood tekfirst 771 Reputation points

2022-08-05T06:38:43.527+00:00

Thank you LiHongMST.

This point was raised in my mind when I posted a question here.

" 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."

I wanted the way in which we could safe from the run of entire query.
LiHong-MSFT 10,061 Reputation points

2022-08-05T08:00:21.673+00:00

Hi @mehmood tekfirst
It seems you need Keyset Pagination for this. Refer to this similar thread: Is there any better option to apply pagination without applying OFFSET in SQL Server?

Best regards,
LiHong
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2022-08-05T09:40:03.017+00:00

Li, that will not help in this case. The root problem is that the sort columns have the wrong data type.

2 additional answers

Your answer

Viorel 126.9K Reputation points

2022-08-04T14:03:24.87+00:00

Maybe removing the CAST and defining corresponding indices (and maybe adding OPTION (RECOMPILE)) will make your statement much better, keeping it clear.
mehmood tekfirst 771 Reputation points

2022-08-05T06:38:43.527+00:00

Thank you LiHongMST.

This point was raised in my mind when I posted a question here.

" 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."

I wanted the way in which we could safe from the run of entire query.
LiHong-MSFT 10,061 Reputation points

2022-08-05T08:00:21.673+00:00

Hi @mehmood tekfirst
It seems you need Keyset Pagination for this. Refer to this similar thread: Is there any better option to apply pagination without applying OFFSET in SQL Server?

Best regards,
LiHong
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2022-08-05T09:40:03.017+00:00

Li, that will not help in this case. The root problem is that the sort columns have the wrong data type.

Answer 1

Tom Phillips 17,786

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?

mehmood tekfirst 771 Reputation points

2022-08-05T06:40:25.723+00:00

This point was raised in my mind when I posted a question here.

" 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."

I wanted the way in which we could safe from the run of entire query.

The type of AgrNumber and ResNumber is varchar.

No, I didn't create any index for these varchar columns.

Will it good to create an index on varchar columns ?

Answer 2

Erland Sommarskog 134.4K MVP Volunteer Moderator

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.

mehmood tekfirst 771 Reputation points

2022-08-05T06:41:09.087+00:00

This point was raised in my mind when I posted a question here.

" 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."

I wanted the way in which we could safe from the run of entire query.

The type of AgrNumber and ResNumber is varchar.

No, I didn't create any index for these varchar columns.

Will it good to create an index on varchar columns ?
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2022-08-05T09:39:00.04+00:00
Will it good to create an index on varchar columns ?

No, that will not help with the table in its current shape. Strings and numbers
are not the same things. Say that you have these values:

This is how the values will sort when they are varchar. varchar are strings, and when we sort strings we first look at the first character, and only when two values have the same first character, we look at the second one. If you create an index on these two varchar values, the values will be stored in the order above.

But in your query, you asked to have them sorted as numbers:

1
2
3
4
12
13
21
123
200
3000

That is a completely different order. So SQL Server needs to sort the entire table before it can return the full page.

Therefore to get the desired performance, you need to do two things:

Change the data type of these columns to int.

Once you have done this, you can add an index.
Tom Phillips 17,786 Reputation points

2022-08-05T16:35:18.273+00:00
In addition to Erland's answer.

You can create a computed persisted column in your table:

AgrNumber_Int AS TRY_CAST(AgrNumber as INT) PERSISTED, ResNumber_Int AS TRY_CAST(ResNumber as INT) PERSISTED

Then you can create an index on CarId, ArgNumber_Int, ResNumber_Int. And your sort by to use ArgNumber_Int, ResNumber_Int. That should help
Erland Sommarskog 134.4K Reputation points MVP Volunteer Moderator

2022-08-05T16:48:39.653+00:00

Yes, that is also a possible option, although I don't see why there would be a varchar column as well.

By the way, PERSISTED is not needed. You can create indexes on non-persisted computed columns as well.

Share via

better Paging techniques without using offset

2 additional answers

Your answer