This application shows several queries for implementing pagination.

A testscreen is available to see the performance of the different queries.

The test can run several times by setting the field iteration. If the test runs more frequent the result will be better.

Based on the results you can choose what kind of query is the best for your solution.

For screens a user will just paginate the first screens, so the solution can be different from an api used to get all data.

I will try to add the queries for Oracle too, because I know the Oracle-SQL has more options that can be used.