SQL Query Pagination

The following APIs have been enhanced to deliver improved SQL performance. Previously, the pagination parameters (maxRows and rowFrom) were applied after retrieving the complete result set, resulting in unnecessary post-processing. Our analysis indicates that integrating pagination directly within the SQL query reduces execution time significantly, from approximately 10 seconds to 1.5 seconds ​

Server Side API

REST 1.0

Client Side AJAX

Key Changes

The platform now adds an implicit LIMIT (or database equivalent) clause to parsed SQL queries.

Pagination is applied directly at the database level using syntax for each supported engine.

Example: SQL transformation

This example shows how the platform rewrites SQL queries to apply pagination at the database level using API parameters:

Input

rbv_api.selectQuery2(
"SELECT id, name, score FROM Student WHERE department = ? AND subject = ?",
0,
10,
"CS",
"Subject1"
);

MySQL Output

SELECT id, name, score
FROM Student
WHERE department = ? AND subject = ?
LIMIT 10

SQL Server Output

SELECT id, name, score
FROM Student
WHERE department = ? AND subject = ?
ORDER BY id
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Best Practices

Follow these best practices to ensure predictable, efficient, and consistent behavior when using SQL query APIs with the optimized pagination model:

  • Specify an explicit ORDER BY clause if business logic requires a predictable result order.

  • For databases like SQL Server, which require an ordering expression for pagination, the platform automatically applies an ORDER BY on the primary key (id) or another suitable field if none is provided.

  • The selectValue and selectNumber APIs return at most one result; the platform implicitly applies LIMIT 1.

  • The selectQuery and selectQuery2 APIs derive LIMIT/OFFSET behavior from their parameters.

  • Use the testQuery window to observe SQL parsing and pagination. A default range of [0, 10] rows applies automatically to all test queries.