Optimizing Large Dataset Retrieval with FireDAC TFDQuery Pagination

Preventing Memory Exhaustion in Large Data Grids

Loading a table with hundreds of thousands of rows into a client application often leads to OutOfMemory exceptions or severe UI freezing. The core problem is the default behavior of many database components to fetch the entire result set into local memory before displaying the first record. In RAD Studio, the solution is to shift from full-set retrieval to On-Demand fetching using FireDAC's TFDQuery.

By configuring TFDQuery to fetch records in chunks, you reduce the initial memory footprint and decrease the time it takes for the application to become responsive, as the first few rows are rendered while the rest remain on the server.

Implementing On-Demand Fetching

To implement pagination without writing complex OFFSET and FETCH NEXT SQL logic for every database provider, use the FetchOptions property. This abstracts the pagination logic across different database drivers.

Configuration Example

The following configuration demonstrates how to set up a TFDQuery to load records in blocks of 50. This is typically done during the form's OnCreate event or within a data module.

// Required units: FireDAC.Comp.Client, FireDAC.Stan.Intf
procedure TDataForm.ConfigureQueryPagination;
begin
  // 1. Prevent the query from loading all records into memory
  FDQuery1.FetchOptions.Mode := fmOnDemand;

  // 2. Define the chunk size for each network round-trip
  FDQuery1.FetchOptions.RowSetSize := 50;

  // 3. Use parameterized queries to prevent SQL injection
  FDQuery1.SQL.Text := 'SELECT * FROM Orders WHERE CustomerID = :CustID ORDER BY OrderDate DESC';
  FDQuery1.ParamByName('CustID').AsString := 'ALFKI';

  // 4. Open the query
  FDQuery1.Open;
end;

Mechanism Breakdown

• fmOnDemand: This mode tells FireDAC to fetch only the first RowSetSize records. As the user scrolls through a TDBGrid or moves the cursor to a record beyond the current local cache, FireDAC automatically triggers a request for the next block of rows.
• RowSetSize: This integer determines the balance between memory usage and network latency. A value of 50 means 50 rows are retrieved per trip.
• Parameterized Queries: Using :CustID ensures the database engine can reuse the execution plan and protects the application from malicious input.

Performance Trade-offs and Limits

While on-demand fetching solves memory issues, it introduces specific engineering constraints that must be managed.

Network Latency vs. Memory

Choosing the RowSetSize is a balancing act. If the value is too low (e.g., 1 or 5), the application will perform frequent network round-trips, causing a "stuttering" effect during fast scrolling. If it is too high (e.g., 5,000), you risk the same memory pressure you were trying to avoid.

Client-Side vs. Server-Side Filtering

FireDAC allows you to set FDQuery1.Filtered := True and define a Filter string. It is critical to understand that client-side filtering only applies to records already fetched. If you have 1,000,000 rows on the server but have only fetched 50 via fmOnDemand, a client-side filter will not find records in the remaining 999,950. For data reduction, always use the SQL WHERE clause.

Feature	Server-Side (SQL WHERE)	Client-Side (TFDQuery.Filter)
Performance	High (Indexed)	Low (Linear scan of local cache)
Data Scope	Entire Table	Only Fetched Rows
Network Load	Minimal (Filtered results only)	Higher (Fetches blocks to filter)

Verification and Diagnostics

To verify that pagination is working and not loading the full dataset, follow these steps:

1. Attach a TFDQuery to a TDBGrid.
2. Set FetchOptions.Mode := fmOnDemand and RowSetSize := 10.
3. Open the query and check the RecordCount property. In many database configurations, RecordCount will show the total rows on the server, but the actual memory usage will remain low.
4. Use a network monitor or the FireDAC monitor to observe that new SQL requests are sent only when you scroll past the 10th, 20th, and 30th records.

Rollback Procedure

If the on-demand behavior causes UI lag that is unacceptable for your specific network environment, revert to full fetching by setting FetchOptions.Mode := fmAll. Note that this should only be done if the result set is guaranteed to be small (e.g., < 1,000 rows).