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
- HTTPie
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'
http GET 'http://localhost:8080/v1/rdbms/db/film?limit=5&fields=title,release_year:year' \
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
- HTTPie
curl --request GET \
--url 'http://localhost:8080/v1/rdbms/db/film?offset=20&limit=10' \
--header 'User-Agent: insomnia/8.6.1'
http GET 'http://localhost:8080/v1/rdbms/db/film?offset=20&limit=10' \
User-Agent:insomnia/8.6.1
The query above will skip 20 rows and then fetch the next 10 rows.
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.