Server-Side Pagination in Delphi with FireDAC LIMIT and OFFSET

The problem: loading every row blocks the UI

When a Delphi application binds a grid directly to a table or view, FireDAC may attempt to materialize the entire result set in memory. On a table with tens of thousands of rows, this causes slow startup, high memory use, and an unresponsive user interface while rows arrive. The practical fix is server-side pagination: ask the database for only the rows needed for the current page.

Useful takeaway

Bind :PageSize and :Offset parameters in a parameterized SQL statement, then execute TFDQuery.Open to fetch a single page. Reuse the query object across page requests instead of creating and destroying it repeatedly.

How FireDAC delivers one page

FireDAC is the data-access layer included with modern Delphi versions. TFDQuery represents a dataset, and TFDConnection manages the database connection. Parameterized SQL means placeholders such as :PageSize are sent separately from the query text, which helps prevent SQL injection and lets the database cache the execution plan.

LIMIT tells the database how many rows to return. OFFSET tells it how many rows to skip before starting to return results. Together they define a window over an ordered result set. ORDER BY is required; without it, the database can return rows in any physical order, so page boundaries become meaningless.

Worked configuration

This example assumes Delphi XE8 or later with FireDAC installed, and a database that supports LIMIT and OFFSET syntax, such as PostgreSQL, SQLite, Firebird 3+, or MySQL. Oracle and older SQL Server versions use different syntax and are not covered here.

unit PaginationUnit;

interface

uses
  System.SysUtils, FireDAC.Comp.Client, FireDAC.Comp.DataSet;

type
  TPageFetcher = class
  private
    FQuery: TFDQuery;
    FPageSize: Integer;
    FOffset: Integer;
  public
    constructor Create(AConnection: TFDConnection);
    destructor Destroy; override;
    procedure FetchPage(APageNumber: Integer);
    property PageSize: Integer read FPageSize write FPageSize;
  end;

implementation

constructor TPageFetcher.Create(AConnection: TFDConnection);
begin
  inherited Create;
  FQuery := TFDQuery.Create(nil);
  FQuery.Connection := AConnection;
  // Materialize the page in the client dataset after the server returns it.
  FQuery.FetchOptions.Mode := fmAll;
  FQuery.SQL.Text :=
    'SELECT id, name, created_at ' +
    'FROM orders ' +
    'ORDER BY created_at DESC, id DESC ' +
    'LIMIT :PageSize ' +
    'OFFSET :Offset';
  FPageSize := 50;
end;

destructor TPageFetcher.Destroy;
begin
  FQuery.Free;
  inherited;
end;

procedure TPageFetcher.FetchPage(APageNumber: Integer);
begin
  if APageNumber < 1 then
    raise EArgumentException.Create('Page number must be 1 or greater.');

  FOffset := (APageNumber - 1) * FPageSize;

  FQuery.ParamByName('PageSize').AsInteger := FPageSize;
  FQuery.ParamByName('Offset').AsInteger := FOffset;

  FQuery.Open;
  // FQuery.RecordCount now reflects the fetched page, not the total table size.
end;

end.

Run this code inside a VCL or FireMonkey form button handler, or from a data module method. The calling code needs read permission on the database user account and a live TFDConnection. Placeholders :PageSize and :Offset are replaced at execution time by FireDAC.

Do not create a new TFDQuery inside a loop that fetches multiple pages. Preparing the statement once and rebinding parameters reduces per-request overhead. If your database supports connection pooling, set PoolSize in TFDConnection.Params so connections can be reused across page requests.

Cursor and fetch behavior

FetchOptions.Mode := fmAll instructs FireDAC to retrieve the full page into client memory after the server sends it. Use fmPartial if you want to stream rows as the grid scrolls, though streaming can keep a server cursor open longer. CursorLocation := clUseMemory moves cursor handling to the client and can reduce server-side locking, but it increases client memory use relative to a server cursor.

Limits and common mistakes

• OFFSET degrades on deep pages. As the offset grows, the database still scans and discards earlier rows before returning the requested window. For page 1,000 of a large table, consider keyset pagination, which filters on the last seen value instead of skipping rows.
• Unindexed ORDER BY columns force table scans. Create an index on the columns used in ORDER BY, preferably matching the sort direction. Without an index, every page request may read the whole table.
• Hard-coded limits. Expose PageSize as a property or bind it to a UI control so operators can adjust it without recompiling.
• Missing explicit fetch mode. If you do not set FetchOptions.Mode, FireDAC may stream rows in a way that conflicts with your UI expectations. Set it explicitly.
• Assuming RecordCount is the table total. After Open, RecordCount reflects only the rows in the current page, not every row in the table.

Verification

1. Compile and run the example in Delphi XE8 or later with FireDAC installed.
2. Enable FireDAC SQL monitoring or use your database's query log. Confirm the executed SQL contains LIMIT and OFFSET with the bound values.
3. Request page two and confirm the first row differs from page one.
4. Inspect the database execution plan for the ORDER BY query and confirm it uses an index rather than a sequential scan.
5. Measure client memory with a large PageSize while using fmAll to confirm consumption stays within acceptable limits.

This is a read-only operation, so no rollback procedure is required. If the connection fails, wrap FQuery.Open in a try/except block and handle EFDException or connection errors explicitly rather than allowing silent failures.

Practical check

After fetching a page, compare FQuery.RecordCount against FPageSize for all pages except possibly the last. If the count matches on intermediate pages and the grid shows the expected subset, pagination is working. For the final page, the count may be smaller than PageSize, which is expected.