Speed Up Bulk Inserts with FireDAC Array DML in RAD Studio

The problem: one round trip per row

You need to load tens of thousands of rows into a database through FireDAC. A simple loop that calls TFDQuery.Execute once per row forces the application and database to complete one request-and-response cycle, or round trip, for every row. At high volume that chatter dominates the import time.

The useful takeaway

FireDAC Array DML lets you bind multiple parameter value sets to a single parameterized statement and send them together with Execute(NumRows, 0). This can reduce round trips for bulk insert, update, or delete workloads. It is not guaranteed to be faster on every driver, so verify support for your RAD Studio edition and target database before adopting it.

What Array DML changes

Instead of preparing and executing the same statement repeatedly, you set TFDParam.ArraySize to the number of rows in the batch, assign values into indexed parameter slots such as Params[0].AsStrings[i], then execute once. FireDAC transmits the value sets as one unit. The database still inserts one row per value set, but the client does not pay the network and protocol overhead for each one.

Worked example: inserting customer rows in Delphi

Run this in a RAD Studio VCL or console project that already has a configured TFDConnection. The database login must have INSERT permission on the destination table. Array DML does not start a transaction for you, so the example manages one explicitly.

procedure LoadCustomers(const Rows: TArray<TCustomer>);
var
  Query: TFDQuery;
  i: Integer;
begin
  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'INSERT INTO Customers (Name, Email, CreatedAt) VALUES (:Name, :Email, :CreatedAt)';

    Query.Params.ArraySize := 1000;

    for i := 0 to High(Rows) do
    begin
      Query.Params[0].AsStrings[i] := Rows[i].Name;
      Query.Params[1].AsStrings[i] := Rows[i].Email;
      Query.Params[2].AsDateTime[i] := Rows[i].CreatedAt;
    end;

    FDConnection1.StartTransaction;
    try
      Query.Execute(1000, 0);
      FDConnection1.Commit;
    except
      FDConnection1.Rollback;
      raise;
    end;
  finally
    Query.Free;
  end;
end;

Execute(NumRows, 0) sends NumRows value sets starting at offset zero. For a 50,000-row import, run the procedure in chunks of 1,000 to 10,000 rows. One massive array consumes more client memory and can make per-row failures harder to isolate.

Trade-offs and limitations

• Array DML behavior depends on the FireDAC driver and RAD Studio version. Some drivers execute the array server-side; others emulate it with client-side loops, so measured gains vary.
• Older editions may have incomplete support. Confirm that your installed SKU includes FireDAC for the target driver.
• The feature does not manage transactions automatically. Wrap the call in an explicit transaction if you want the batch to commit or roll back as a unit.
• Per-row failures may appear as driver-specific exceptions or error events. Inspect the full exception chain before assuming the batch succeeded or failed entirely.

How to check the result

1. Open the RAD Studio help and search the FireDAC driver support matrix for Array DML to confirm support for your database and installed version.
2. After the insert, run SELECT COUNT(*) FROM Customers and compare the result to the number of value sets you attempted.
3. Use SQL Monitor or database-side tracing to confirm that Array DML produces fewer round trips than the same statement executed in a loop.

Array DML can meaningfully reduce client-to-database chatter for bulk loads, but it is not a universal speedup. Measure against your target database, and fall back to batched conventional inserts if your driver emulates array behavior on the client.