Skip to main content

Handle large result-sets with Pagination

Sometimes the result of a query execution can fetch many records. It is inefficient to fetch a large number of rows. It can generate a lot of network traffic as the consuming application may not require all the records. DB2Rest implements offset pagination to support limiting the results of a query.

The LIMIT clause​

The simplest and most straightforward way to limit the number of rows returned by a query is to use the LIMIT clause. The LIMIT clause allows you to specify the maximum number of rows that you want returned from your query.

Here’s an example of how to use the LIMIT clause:

curl --request GET \
--url 'http://localhost:8080/v1/rdbms/db/film?limit=5&fields=title,release_year:year' \
--header 'User-Agent: insomnia/8.6.1'

This query will return the first 5 rows from the film table. Note that the order in which the rows are returned depends on the order specified in the SORT clause (if one is present). If no SORT clause is present, the order of the rows is undefined.

HTTP/1.1 200
Content-Type: application/json
Transfer-Encoding: chunked

[
	{
		"title": "ACADEMY DINOSAUR",
		"year": 2006
	},
	{
		"title": "ACE GOLDFINGER",
		"year": 2006
	},
	{
		"title": "ADAPTATION HOLES",
		"year": 2006
	},
	{
		"title": "AFFAIR PREJUDICE",
		"year": 2006
	},
    {
		"title": "AFFAIR PREJUDICE 2",
		"year": 2010
	}
]

Using LIMIT With OFFSET​

In addition to the LIMIT clause, the OFFSET clause can be used to skip a specified number of rows before starting to return results. Here’s an example of how to use the LIMIT and OFFSET clause:

curl --request GET \
--url 'http://localhost:8080/v1/rdbms/db/film?offset=20&limit=10' \
--header 'User-Agent: insomnia/8.6.1'

The query above will skip 20 rows and then fetch the next 10 rows.

warning

If offset is specified without limit, then offset will be ignored.

The Global LIMIT​

By default, DB2Rest puts a limit of 100 for read queries. The value can be changed by setting the value of the DEFAULT_FETCH_LIMIT configuration parameter.

Performance can vary depending on the amount of RAM given to the Java Heap (-Xmx Java Option) for DB2Rest, as well as the number of fields requested, their content length, or even database query performance and indexing itself, and thus the DEFAULT_FETCH_LIMIT or &limit parameter can be adjusted and even increased significantly given adequate resources. DB2Rest has been tested using 1000's of rows as the limit, even with only 2GB of RAM -Xmx 2G and simple records being returned from a PostgreSQL query and can still perform extremely fast for most applications.