CODEWITHSUNDEEP
c#sql-serverasp.net-web-apipagination
VR1256
VR1256

Reputation: 1450

Huge data set Cursor Based Pagination with Web API & SQL Server queries

I have a table with 30 Million records & we are using SQL server queries to pull the data.

SELECT FruitName, Price
FROM SampleFruits
ORDER BY Price
OFFSET 5 ROWS FETCH NEXT 20 ROWS ONLY

With above approach with Offset (page) & Fetch (rows) we are having issues with performance, as Offset needs to scan all the records until the offset number it is causing issues.

I was looking at different options & I see the recommendation with large data set is Cursor based pagination. How do the cursor based pagination looks like in SQL query, we should include Previous & Next Page in query itself to send it to consumers and what would be the parameters look like ?.

How does the query looks like to give prev & next in the result set ?

{  
    Fruits:[  
        {  
            name: "Oraange",  
            shelfLife: "2021-06-15",  
            weight: "30 Lbs"  
        },  
        ...  
    ],  
    nextPage: "?",  
    previousPage: ?,
    totalRecords: ?
}

In Web API where do we set the nextPage & previousPage in the query or form it at the business layer in C# ?

Getting totalRecords is a separate query ?

When they want to go backwards do we need to change query to set previousPage, nextpage. Basically how query looks like in Cursor based pagination in SQL server and where does the nextpage & previousPage are set in sql query or done in C#. Also, what are query parameters to the Web API ?

Upvotes: 2

Views: 4683

Answers (2)

Victor
Victor

Reputation: 109

I think you will understand if you read here, under "cursor-based pagination" https://daily.dev/blog/pagination-in-graphql

the cursor should be a unique and ideally able to order, like Id or datetime, etc.

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

The normal recommendation is to:

  • Never fetch totalRecords, as it's often more expensive than fetching the pages.

  • Use a paging query of the form

Like this:

SELECT top (@rows) FruitName, Price
FROM SampleFruits
WHERE ID > @lastId
ORDER BY Price, ID

Passing the last ID from the previous page to get the next page, and always making sure the ORDER BY is unique, and supported by an index.

Upvotes: 1

Related Questions