Enable FireDAC Connection Pooling in Delphi Desktop Apps to Reduce Latency

Reduce connection open overhead without rewriting data access

Repeatedly opening and closing a TFDConnection in a VCL or FMX desktop app adds round-trip latency and load on the database server. FireDAC connection pooling reuses physical connections behind TFDConnection instances so Open is fast and the server sees a stable connection count under concurrent load.

The useful takeaway is to enable pooling on the connection definition, keep the same definition across forms, and always return connections to the pool with a clean transaction state. Pooling does not eliminate the need for correct try..finally usage; it changes where the cost is paid.

Prerequisites

• Delphi with FireDAC installed. FireDAC ships with the IDE and provides TFDConnection, TFDManager and the driver units.
• A supported database driver configured in the app, e.g., FireDAC.Phys.SQLite, FireDAC.Phys.MSAcc, FireDAC.Phys.MySQL, FireDAC.Phys.PG, FireDAC.Phys.ODBC.
• Target database server allows multiple connections from the app pool and has max connections set above the expected PoolMaximum.
• Application uses a single connection definition name for all forms/modules, or you accept per-definition pools.

Pool settings are per TFDConnection definition, not global. Inconsistent definitions across forms create multiple independent pools.

Configure pooling on TFDConnection

Set pooling properties on the connection component or in code before the first Open. Pooling is disabled by default.

// Run in application startup, e.g., DPR initialization or main form OnCreate
FDConnection1.Params.DriverID := 'MSSQL';
FDConnection1.Params.Database := 'MyDB';
FDConnection1.Params.User_Name := '<User>';
FDConnection1.Params.Password := '<Password>';
FDConnection1.Params.Server := '<Server>';

FDConnection1.ResourceOptions.Pooling := True;
FDConnection1.ResourceOptions.PoolSize := 10;      // target number of pooled handles
FDConnection1.ResourceOptions.PoolMinimum := 2;    // keep warm
FDConnection1.ResourceOptions.PoolMaximum := 20;   // hard cap

PoolSize is the desired number of connections to keep. PoolMinimum pre-creates connections. PoolMaximum caps creation under load. Different databases and drivers implement limits differently, so validate per target.

Optional PoolingOptions can be set via FDManager.ConnectionDefs for named definitions used in multiple components.

Use connections correctly with pooling

With pooling enabled, Close does not destroy the physical handle; it returns it to the pool. Ensure transactions are finished before Close.

FDConnection1.Open;
try
  FDQuery1.Connection := FDConnection1;
  FDQuery1.SQL.Text := 'SELECT Id FROM Items WHERE Active = 1';
  FDQuery1.Open;
  // process
  FDConnection1.Commit; // or Rollback on error
finally
  FDQuery1.Close;
  FDConnection1.Close; // returns handle to pool
end;

Risks: leaving a transaction active when Close is called can leave the pooled handle in an unusable state. Always commit or rollback in exception-safe blocks. Pooling can mask connection leaks because Close appears to work while handles are never returned due to an exception path.

Validate pooling is working

Check at runtime with FDManager.

var
  Active: Integer;
begin
  Active := FDManager.ActiveConnections; // number of physical connections in use
  // log value and compare to PoolMaximum
end;

Practical checks:

• Measure connection open time before and after enabling pooling under repeated Open/Close cycles. Expect lower latency after warm-up.
• Verify pool size stays within PoolMinimum and PoolMaximum under simulated concurrent load from multiple threads or forms.
• Confirm stable connection count on the database server side during load, rather than spikes matching each query.

Limitations: not all drivers support full pooling semantics. SQLite in-memory databases and some ODBC bridges may ignore PoolSize. Pooling does not reduce query execution time.

Recovery and limits

If pooling causes issues, disable it and clear the pool.

FDConnection1.ResourceOptions.Pooling := False;
FDManager.ConnectionDefs.ClearPool(FDConnection1.ConnectionDefName);

Fallback pattern is explicit Close in try..finally and recreating the connection on error. Clearing the pool forces new physical connections on next Open.

Monitor for exhaustion: if ActiveConnections approaches PoolMaximum under normal load, increase PoolMaximum or fix code that holds connections open. Never share a single TFDConnection instance across threads without synchronization; use one instance per thread or use a connection per operation with pooling.