Efficient Data Pagination Using OFFSET-FETCH in SQL Stored Procedures with Complex Joins

 Efficient Data Pagination Using OFFSET-FETCH in SQL Stored Procedures with Complex Joins


Introduction


When dealing with large datasets in SQL Server, fetching all records at once can lead to performance issues. Instead, we can implement pagination using OFFSET-FETCH, especially when working with complex joins. This method allows fetching a subset of data efficiently, improving both performance and scalability.


This article will guide you through implementing OFFSET-FETCH in a SQL stored procedure with complex joins.



---


Understanding OFFSET-FETCH in SQL Server


The OFFSET and FETCH clauses are used with ORDER BY to skip a specified number of rows and retrieve only a limited set of records.


Syntax


SELECT columns

FROM TableName

ORDER BY ColumnName

OFFSET @Offset ROWS

FETCH NEXT @PageSize ROWS ONLY;


OFFSET @Offset ROWS: Skips the specified number of rows.


FETCH NEXT @PageSize ROWS ONLY: Fetches the next batch of records.




---


Implementing Pagination in a Stored Procedure with Complex Joins


Scenario


Assume we have the following tables:


1. Orders (OrderID, CustomerID, OrderDate, TotalAmount)



2. Customers (CustomerID, CustomerName, Country)



3. OrderDetails (OrderDetailID, OrderID, ProductID, Quantity, UnitPrice)



4. Products (ProductID, ProductName, CategoryID, Price)




We want to retrieve paginated order details, including customer information, product details, and total order amount, while allowing sorting by OrderDate.



---


Creating the Stored Procedure


CREATE PROCEDURE GetPaginatedOrders

    @PageNumber INT,

    @PageSize INT

AS

BEGIN

    SET NOCOUNT ON;


    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;


    WITH OrderData AS 

    (

        SELECT 

            o.OrderID, 

            c.CustomerName, 

            c.Country, 

            o.OrderDate, 

            p.ProductName, 

            od.Quantity, 

            od.UnitPrice, 

            (od.Quantity * od.UnitPrice) AS TotalPrice,

            ROW_NUMBER() OVER (ORDER BY o.OrderDate DESC) AS RowNum

        FROM Orders o

        INNER JOIN Customers c ON o.CustomerID = c.CustomerID

        INNER JOIN OrderDetails od ON o.OrderID = od.OrderID

        INNER JOIN Products p ON od.ProductID = p.ProductID

    )

    SELECT * 

    FROM OrderData

    ORDER BY OrderDate DESC

    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

END;



---


Breaking Down the Query


1. Using CTE (Common Table Expression):


We first join the Orders, Customers, OrderDetails, and Products tables.


We calculate the TotalPrice (Quantity * UnitPrice).


We use ROW_NUMBER() to generate row numbers for sorting (optional).




2. Pagination Logic:


The @PageNumber and @PageSize parameters control the number of records fetched.


The @Offset value determines the starting point of the result set.


The OFFSET-FETCH clause efficiently retrieves only the required rows.






---


Executing the Stored Procedure


To fetch page 2 with 10 records per page, run:


EXEC GetPaginatedOrders @PageNumber = 2, @PageSize = 10;



---


Advantages of Using OFFSET-FETCH for Pagination


✔ Efficient Memory Usage: Only a subset of data is loaded into memory.

✔ Scalability: Works well with millions of records.

✔ Improved Query Performance: Fetching only the required rows reduces execution time.

✔ Flexibility: Supports sorting and filtering based on business requirements.



---


Alternative Approach: Using ROW_NUMBER()


If SQL Server 2012 or earlier is used, ROW_NUMBER() can be used instead of OFFSET-FETCH.


WITH OrderData AS 

(

    SELECT 

        o.OrderID, c.CustomerName, c.Country, o.OrderDate, 

        p.ProductName, od.Quantity, od.UnitPrice, 

        (od.Quantity * od.UnitPrice) AS TotalPrice,

        ROW_NUMBER() OVER (ORDER BY o.OrderDate DESC) AS RowNum

    FROM Orders o

    INNER JOIN Customers c ON o.CustomerID = c.CustomerID

    INNER JOIN OrderDetails od ON o.OrderID = od.OrderID

    INNER JOIN Products p ON od.ProductID = p.ProductID

)

SELECT * 

FROM OrderData

WHERE RowNum BETWEEN (@PageNumber - 1) * @PageSize + 1 

                  AND @PageNumber * @PageSize;


This achieves the same result but may be slower for large datasets compared to OFFSET-FETCH.



---


Conclusion


Using OFFSET-FETCH in SQL Server stored procedures allows efficient pagination while handling comp

lex joins. It ensures optimal performance and scalability, making it ideal for large datasets.

Comments

Post a Comment

Popular posts from this blog

Maxpooling vs minpooling vs average pooling

Image
Pooling is performed in neural networks to reduce variance and computation complexity. Many times, beginners blindly use a pooling method without knowing the reason for using it. Here is a comparison of three basic pooling methods that are widely used. The three types of pooling operations are: Max pooling: The maximum pixel value of the batch is selected. Min pooling: The minimum pixel value of the batch is selected. Average pooling: The average value of all the pixels in the batch is selected. The batch here means a group of pixels of size equal to the filter size which is decided based on the size of the image. In the following example, a filter of 9x9 is chosen. The output of the pooling method varies with the varying value of the filter size. The operations are illustrated through the following figures. Average, Max and Min pooling of size 9x9 applied on an image We cannot say that a particular pooling method is better over others generally. The choice of pooling operation is made...
Read more

Understand the Softmax Function in Minutes

Image
Learning machine learning? Specifically trying out neural networks for deep learning? You likely have run into the  Softmax function, a wonderful  activation function  that turns numbers aka logits into probabilities that sum to one. Softmax function outputs a vector that represents the probability distributions of a list of potential outcomes.  It’s also a core element used in  deep learning   classification  tasks. We will help you understand the Softmax function in a beginner friendly manner by showing you exactly how it works — by coding your very own Softmax function in python. If you are implementing Softmax in Pytorch and you already know Pytorch well, scroll down to the Deep Dive section and grab the code. This article has gotten really popular: 5800+ claps. It is updated constantly. Latest update Jan 2020 added a TL;DR section for busy souls. Dec 2019 (Softmax with Numpy Scipy Pytorch functional. Visuals indicating the location of Softmax func...
Read more

Percentiles, Deciles, and Quartiles

Image
Percentiles Percentile: the value below which a percentage of data falls. Example: You are the fourth tallest person in a group of 20 80% of people are shorter than you: That means you are at the  80th percentile . If your height is 1.85m then "1.85m" is the 80th percentile height in that group. In Order Have the data  in order , so you know which values are above and below. To calculate percentiles of height: have the data in height order (sorted by height). To calculate percentiles of age: have the data in age order. And so on. Grouped Data When the data is grouped: Add up all percentages  below  the score, plus  half  the percentage  at  the score. Example: You Score a B! In the test 12% got D, 50% got C, 30% got B and 8% got A You got a B, so add up all the 12% that got D, all the 50% that got C, half of the 30% that got B, for a total percentile of 12% + 50% + 15% =  77% In other words you...
Read more