The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement SEEK clause

previous : next

SEEK clause

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

One of the previous chapters talked about OFFSET pagination using LIMIT .. OFFSET, or OFFSET .. FETCH or some other vendor-specific variant of the same. This can lead to significant performance issues when reaching a high page number, as all unneeded records need to be skipped by the database.

A much faster and more stable way to perform pagination is the so-called keyset pagination method also called seek method. jOOQ supports a synthetic seek() clause, that can be used to perform keyset pagination (learn about other synthetic sql syntaxes). Imagine we have these data:

+------+-------+---------------+
|   ID | VALUE | PAGE_BOUNDARY |
+------+-------+---------------+
|  ... |   ... |           ... |
|  474 |     2 |             0 |
|  533 |     2 |             1 | <-- Before page 6
|  640 |     2 |             0 |
|  776 |     2 |             0 |
|  815 |     2 |             0 |
|  947 |     2 |             0 |
|   37 |     3 |             1 | <-- Last on page 6
|  287 |     3 |             0 |
|  450 |     3 |             0 |
|  ... |   ... |           ... |
+------+-------+---------------+

Now, if we want to display page 6 to the user, instead of going to page 6 by using a record OFFSET, we could just fetch the record strictly after the last record on page 5, which yields the values (533, 2). This is how you would do it with SQL or with jOOQ:

SELECT id, value
FROM t
WHERE (value, id) > (2, 533)
ORDER BY value, id
LIMIT 5
 

DSL.using(configuration)
   .select(T.ID, T.VALUE)
   .from(T)
   .orderBy(T.VALUE, T.ID)
   .seek(lastValue, lastId) // from last page: value = 2, id = 533
   .limit(5)
   .fetch();

As you can see, the jOOQ SEEK clause is a synthetic clause that does not really exist in SQL. However, the jOOQ syntax is far more intuitive for a variety of reasons:

• It replaces OFFSET where you would expect
• It doesn't force you to mix regular predicates with "seek" predicates
• It is typesafe
• It emulates row value expression predicates for you, in those databases that do not support them

This query now yields:

+-----+-------+
|  ID | VALUE |
+-----+-------+
| 640 |     2 |
| 776 |     2 |
| 815 |     2 |
| 947 |     2 |
|  37 |     3 |
+-----+-------+

Note that you cannot combine the SEEK clause with the OFFSET clause.

More information about this great feature can be found in the jOOQ blog:

• https://blog.jooq.org/faster-sql-paging-with-jooq-using-the-seek-method/
• https://blog.jooq.org/faster-sql-pagination-with-keysets-continued/

Further information about offset pagination vs. keyset pagination performance can be found on our partner page:

The jOOQ User Manual SQL building SQL Statements (DML) The SELECT statement SEEK clause

previous : next