Speed Up RAD Studio Database Code with FireDAC Prepared Statements and Connection Pooling

The Problem: Repeated SQL Parsing Slows Your App

When the same SQL text is sent to the database over and over, the server must parse, optimize and create an execution plan each time. In RAD Studio applications that use FireDAC, this overhead appears as a steady latency that grows with the number of rows processed.

Solution: Prepare Statements and Pool Connections

FireDAC can keep a compiled version of a statement on the client side. Setting Prepared := True (or calling Prepare) tells FireDAC to store the parsed statement and reuse it for subsequent executions. Connection pooling, enabled through the TFDConnection.Params entries Pooling=True, PoolSize and PoolTimeout, lets the application reuse existing physical connections instead of paying the handshake cost for each request.

Worked Example: Preparing a SELECT and Reusing It

// Delphi – FireDAC with PostgreSQL
var
  Conn: TFDConnection;
  Qry: TFDQuery;
begin
  Conn := TFDConnection.Create(nil);
  try
    Conn.Params.DriverID := 'PG';
    Conn.Params.Database := 'mydb';
    Conn.Params.UserName := 'appuser';
    Conn.Params.Password := 'apppwd';
    // enable pooling
    Conn.Params.Add('Pooling=True');
    Conn.Params.Add('PoolSize=5');
    Conn.Params.Add('PoolTimeout=60');
    Conn.LoginPrompt := False;
    Conn.Open;

    Qry := TFDQuery.Create(nil);
    try
      Qry.Connection := Conn;
      Qry.SQL.Text := 'SELECT id, name FROM customers WHERE region = :region';
      Qry.Prepared := True;   // marks statement for preparation
      Qry.Prepare;            // explicit prepare – done once

      // process 10 000 rows, changing only the parameter value
      for var i := 0 to 9999 do
      begin
        Qry.ParamByName('region').AsString := 'North';
        Qry.Open;
        // …read fields…
        Qry.Close;
      end;
    finally
      Qry.Free;
    end;
  finally
    Conn.Free;
  end;
end;

Trade‑offs and Limitations

• Each prepared statement consumes client‑side memory; the footprint grows with the number of distinct SQL texts you prepare.
• A prepared statement is tied to the connection on which it was prepared. When using a pool, you must either prepare the statement on every connection that may retrieve it, or create a fresh TFDQuery per connection.
• Connection pooling is an Enterprise‑only feature; the Express edition does not expose the Pooling parameter.
• Schema changes (e.g., adding a column) do not automatically invalidate prepared statements; you must re‑prepare after DDL.

Actionable Checklist

1. Verify your RAD Studio license includes FireDAC Enterprise features.
2. Add Pooling=True, a sensible PoolSize (start with 5‑10) and PoolTimeout to your TFDConnection.Params.
3. Set Prepared := True on each TFDQuery (or call Prepare) before the first execution.
4. Run a realistic load test (e.g., 10 000 iterations) and measure elapsed time and RAM usage.
5. Adjust PoolSize up or down while watching the database server’s connection count and your process’ memory.