Deterministic Pagination Requirements
The absolute requirement for deterministic pagination in Firebird 2.5 and 3.0 is an explicit ORDER BY clause. Without a defined sort order, the database engine does not guarantee the sequence of records returned. This means a record appearing on page one of a request may shift to page two in a subsequent request, even if the underlying data has not changed.
Mapping ROWS to FIRST and SKIP
The ROWS clause serves as a syntactic shorthand for combining limits and offsets. Internally, it maps to the FIRST and SKIP logic as follows:
- ROWS n: Equivalent to
FIRST n. It limits the result set to the first n records.
- ROWS n TO m: Equivalent to
FIRST (m - n + 1) SKIP (n - 1). It defines a specific window of records starting at row n and ending at row m.
Consistency Across Versions and Complex Objects
Pagination behavior is generally consistent between Firebird 2.5 and 3.0 when applied to simple tables. However, when applied to views or complex joins, the following behaviors apply:
- Optimizer Influence: The engine applies the
FIRST/SKIP limit after the join and sorting logic is processed. If the view contains its own internal ordering or grouping, the outer pagination will still operate on the final resulting set.
- Performance Degradation: In both versions,
SKIP performance degrades linearly as the offset increases. The engine must scan and discard the skipped rows before returning the requested window.
- Join Complexity: When using complex joins, ensure the
ORDER BY column is indexed. If the sort requires a temporary sort file (disk-based), pagination latency increases significantly.
Implementation Verification
To verify the pagination window is functioning correctly, execute the following scoped commands:
-- Page 1: Retrieve first 10 records
SELECT FIRST 10 SKIP 0 ID, NAME FROM MY_TABLE ORDER BY ID;
-- Page 2: Retrieve next 10 records
SELECT FIRST 10 SKIP 10 ID, NAME FROM MY_TABLE ORDER BY ID;
-- Alternative using ROWS syntax (Equivalent to Page 2)
SELECT ID, NAME FROM MY_TABLE ORDER BY ID ROWS 11 TO 20;
Diagnostic Note: To provide a more specific performance recommendation, please specify if your ORDER BY column is a Primary Key or a non-unique index.
firebird