Pagination in SQL with guidelines and examples

Pagination in SQL is the procedure of retrieving a subset of data results from a query in smaller, manageable chunks or pages.

This is often used when dealing with large result sets to improve performance and provide a better user experience.

Pagination in SQL is used to retrieve and display a subset of data results at a time, rather than fetching the entire result set in a single query. There are several reasons why pagination is commonly used,

Benefits of Pagination in SQL

Pagination SQL – Performance Optimization

Fetching all the data at once can be inefficient and resource-intensive. It could literally break your system or crash your services or System.

When dealing with large result sets, by implementing pagination, you can retrieve or display a smaller chunk of data, reducing the amount of data transferred over the network and improving query performance.

Example: Most services failure generally occurs due to a huge set of data request on the server in one GO.

Pagination SQL – Content-Base Loading in UX guidelines

To improve user experience, pagination allows users to navigate through the data in a more manageable and convenient manner.

It provides a way to break down the results into smaller, organized pages, making it easier for users to locate and access the information they need.

Reduced Network Latency

Repeatedly fetching large data in a single query can result in increased network latency on the database, especially in distributed systems or when dealing with remote databases.

However, actual network latency and data transfer time depend on the underlying network infrastructure and the size of the dataset being retrieved.

Better Resource Management

Resources like memory GB or CPU cost you money. With pagination, you only load a portion of the result set into memory at a time, reducing the memory footprint and allowing for better resource utilization.

When working with memory-constrained environments, fetching and storing small amounts of data will help the system’s performance and responsiveness.

Scalability

Pagination enables applications to scale efficiently by fetching and processing data in smaller chunks.

This approach minimizes the impact on system resources, making it easier to handle increasing user loads and larger datasets without overwhelming the database or application servers.

Data streaming and Real-Time Updates

Data streaming in chunks helps can in providing real-time updates with delta changes efficiently.

When new data is added or modified in the database, pagination allows you to refresh the displayed results by fetching a new page without reloading the entire result set. This can be useful for displaying live or frequently updated data in real time.

Overall, pagination in SQL provides a balance between query performance, resource utilization, and user experience.

It allows for efficient data retrieval, reduces network and memory overhead, and enables users to navigate and access data in a more organized and manageable way.

Pagination in SQL using – LIMIT and OFFSET

Let’s assume you have a table called customers with columns customer_id, first_name, and last_name, and you want to retrieve the customers in pages of 10 results each.

Let’s assume here our Page size is 10 i.e. it can hold 10 records in each page.

Retrieve the first 10 Records

SELECT * 
FROM Employee
ORDER BY Employee_id
LIMIT 10 OFFSET 0;

In the above query,

• LIMIT 10 specifies that we want to retrieve a maximum of 10 rows, and
• OFFSET 0 indicates that we start from the first row (0-based index).
• This retrieves the first page of 10 customers sorted by customer_id.

Retrieve the next 10 Records

To retrieve the second page, you can use an OFFSET value of 10:

SELECT * 
FROM Employee
ORDER BY Employee_id
LIMIT 10 OFFSET 10;

• The above query skips the first 10 rows and fetches the next 10 rows, giving you the second page of customers.

You can continue this pattern to retrieve subsequent pages by incrementing the OFFSET value.

Retrieve the third set of 10 Records and so on

For example, to retrieve the third page, you would use an OFFSET value of 20

SELECT * 
FROM Employee
ORDER BY Employee_id
LIMIT 10 OFFSET 20;

So just by adjusting the OFFSET value and keeping the LIMIT value constant, you can navigate through the entire result data set and retrieve data in pages efficiently.

It’s important to note that when using pagination with LIMIT and OFFSET, the performance may degrade as you go deeper into the result set.

This is because the database needs to skip a certain number of rows before returning the requested page.

Pagination in SQL using – ROW_NUMBER()

This is an alternative pagination technique such as using keyset pagination or the ROW_NUMBER() the function can provide better performance and scalability.

Pagination using the ROW_NUMBER() function is a common technique that assigns a sequential number to each row in the result set, which is used to divide the data into pages.

SELECT *
FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY Employee_id) AS row_num
  FROM Employess) AS numbered_rows
WHERE row_num BETWEEN 11 AND 20;

In the above query,

• The inner query uses the ROW_NUMBER() function to assign a row number to each row in the customers table, ordered by the customer_id.
• The result of the inner query is then aliased as numbered_rows.
• The outer query filters the result to retrieve rows where the row_num falls within the desired page range. For example – we retrieve rows 11 to 20, effectively representing the second page of data with a page size of 10.
• You can adjust the BETWEEN clause to specify different page ranges to retrieve the desired subsets of data. For example, BETWEEN 1 AND 10 retrieves the first page, BETWEEN 21 AND 30 retrieves the third page, and so on.

LIMIT/OFFSET Vs ROW_NUMBER

• Using ROW_NUMBER() for pagination offers more flexibility and control compared to the LIMIT and OFFSET approach.
• It avoids the performance degradation often associated with large OFFSET values.
• Allows you to efficiently navigate through the result set by directly selecting the desired page range.
• ROW_NUMBER() let you combine query with other SQL clauses, such as ORDER BY, WHERE, and JOIN, providing more refinement and filtering the paginated results

Additionally, it’s recommended to have appropriate indexes on the columns used in the ORDER BY clause to improve the efficiency of pagination queries.

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

---

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

---