Adding SQL Server Pagination to an Outsystems REST API method

Why pagination?

When building an API that returns data, you always have to keep the size of the response in mind. An API method that fetches data from a database, even with some filtering in place, can possibly return millions of records causing a big network traffic and eventually timeouts.

How to implement?

There are many ways to handle SQL pagination, but since SQL Server 2012 there is a new syntax available: the FETCH { FIRST | NEXT } , which provides a super easy way of handling this.
I will show you that approach and also the workaround that is normally used with a ROW_NUMBER() function.

Suppose you want to expose a REST API that is returning a lot of data from your application which will be used for integration purposes with another system. Then you can use the following steps:

Create the REST API:

First, you need to create a REST API in Outsystems (Read more), with 2 inputs in your new GET method. Name them PageSize and PageNumber and set them both to the type Integer.

REST API method example

Inputs:

• PageSize: Size of the page. So, if you want to return 10 records, that will be the page size.
• PageNumber: This will be the offset or the page to start the search. This is controlled by the requester.

Add Pagination to the SQL Query:

Second, you will need to get your data with a SQL query. I will now show you two Advanced query examples.

Let’s check the OFFSET and FETCH Pagination:

Using OFFSET and FETCH to limit the rows returned

Using OFFSET and FETCH as a paging solution requires running the query one time for each “page” of data returned to the client application. For example, to return the results of a query in 10-row increments, you must execute the query one time to return rows 1 to 10 and then run the query again to return rows 11 to 20 and so on. Each query is independent and not related to each other in any way. This means that, unlike using a cursor in which the query is executed once and state is maintained on the server, the client application is responsible for tracking state. Read more

The OFFSET and FETCH Read more

So, to achieve this, the query must be ordered ASC preferably by ID (if new records are added, the data returned will be consistent. If you order by another attribute or different order — DESC — if new records are added you will miss those.)
Then you just need to add the OFFSET and FETCH NEXT as shown below:

The OFFSET clause specifies the number of rows to skip.
The FETCH clause specifies the number of rows to return after the OFFSET clause has been processed.
The OFFSET clause is mandatory while the FETCH clause is optional.
The FIRST and NEXTare synonyms respectively so you can use them interchangeably.
Read more

How to calculate the Offset?

PageSize*(PageNumber-1)

With this expression set, you can send the offset as an input parameter to the query. The input PageSize, you get from the input parameter of the API method.

Using ROW_NUMBER()

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. Read more

The following example calculates row numbers for all rows in the RentalBooking table ordered by RentalBookingId and returns only rows that are between the offset and the limit of the offset.

How to calculate the limit in the previous example?

(PageSize*PageNumber)

And the Offset?

PageSize*(PageNumber-1)+1

So, when you have the inputs:
PageSize: 10
PageNumber:5

Then the WHERE clause will look like:

WHERE RowNumber >= 41 AND RowNumber <= 50

Example requests made by client:

The client will have to control the state, so e.g. he will start to request the page number 1, with a size of 20. Then subsequently will request the next page numbers.

API/rest/GetRentals/GetAllRentals?PageSize=20&PageNumber=1 API/rest/GetRentals/GetAllRentals?PageSize=20&PageNumber=2
API/rest/GetRentals/GetAllRentals?PageSize=20&PageNumber=3

Example responses by API:

The client, besides receiving the business data requested, should also receive some information on what dataset he is receiving, e.g. what is the page number returned, page size and the total count.

Example of the structure returned by the REST API method

The JSON response would look like this:
Call: /rest/GetRentals/GetAllRentals?PageSize=2&PageNumber=2

Response:

Conclusion

OFFSET and FETCH NEXT is in my opinion the easiest way to implement pagination. The syntax is clear, clean and easy to understand.
ROW_NUMBER approach uses CTE tables and you will need some experience in T-SQL to fully understand what’s happening there.

Regarding performance, even with large datasets of around 3 M records, I didn’t notice a big difference in both approaches.

Every documentation that I’ve found, points to the FETCH NEXT approach to be twice as fast as the ROW_NUMBER. But honestly I couldn’t get those results from my tests. With the tests I made, both queries had almost identical performance (I’m sure I need to dig a bit deeper into the SQL execution plans analysis — maybe I will cover this in another post!) which led me to recommend the FETCH NEXT based on the simplicity to use.

Make sure your development and production environments have at least SQL Server 2012 to be able to use the OFFSET and FETCH NEXT.

See you on the next story!