Pagination with grouping

Question

I have a table with over 800K records. This table needs to be "grouped by" a certain column. An example would be:

However, with pagination, things get complicated. I want to show 50 rows for every group.

This means that if I have the following data:

Id	Color	Name
1	Red	Paper #1
2	Red	Paper #2
3	Red	Paper #3
4	Green	Paper #4
5	Green	Paper #5
6	Green	Paper #6
7	Green	Paper #7

And I wanted to get 2 items for every group per page (instead of 50, for the sake of this example), on page 1 I'd have:

Id	Color	Name
1	Red	Paper #1
2	Red	Paper #2
4	Green	Paper #4
5	Green	Paper #5

And at page 2 I'd have:

Id	Color	Name
3	Red	Paper #3
6	Green	Paper #6
7	Green	Paper #7

My current SQL query (SQL Server) for pagination is the following:

SELECT * from [Order] ORDER BY [From] ASC OFFSET (@PageNumber - 1) * @RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY

Not a complete answer, but start with SELECT *,ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) AS Row FROM Order WHERE Row BETWEEN (@PageNumber -1 )*@RowsOfPage+1 AND @PageNumber*@RowsOfPage ORDER BY Color,Row — Robert McKee
– Robert McKee, Commented Oct 19, 2021 at 3:25
This table needs to be "grouped by" a certain column What is that certain column for the sample data that you posted ? — Squirrel
– Squirrel, Commented Oct 19, 2021 at 3:38

Robert McKee · Accepted Answer · 2021-10-19 04:14:05Z

Using this sample data:

CREATE TABLE Orders(Id int, Color varchar(10), Name varchar(50)); INSERT INTO Orders(Id,Color,Name) VALUES (1, 'Red', 'Paper #1'), (2, 'Red', 'Paper #2'), (3, 'Red', 'Paper #3'), (4, 'Green', 'Paper #4'), (5, 'Green', 'Paper #5'), (6, 'Green', 'Paper #6'), (7, 'Green', 'Paper #7');

This query will do:

DECLARE @PageNum int = 1; DECLARE @PageSize int = 2; SELECT * FROM ( SELECT *,ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) AS Row FROM Orders ) t1 WHERE Row BETWEEN ((@PageNum-1)*@PageSize+1) AND (@PageNum*@PageSize) ORDER BY Color,Name

Page 1: http://sqlfiddle.com/#!18/748cc2/27/0

Page 2: http://sqlfiddle.com/#!18/748cc2/25/0

This works. However, with a very large table, it seems extremely slow. Is this due to using ROW_NUMBER()?
I would have to take a look at the explain. Could be any number of factors, some of which might be easily fixed -- such as pre-computing the start/end rows outside of the query. SQL Server might not be able to deduce that it should be generating an index range, or perhaps you are missing a good index for it to use.
Since this is a static table that will never change, I'm able to create a Column called Row which contains the row #, and create a unique index on that column. However, I'm unsure how I would use that column in the query you provided above. I am pretty sure ROW_NUMBER() is the cause of how slow the query takes to execute.

Collectives™ on Stack Overflow

Pagination with grouping

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related