FireDAC Array DML: Batching Delphi Inserts to Cut Network Round Trips

Why row-by-row inserts stall

You have a Delphi routine that needs to insert a few thousand rows into a remote database. The code is simple: a loop, a parameterized query, and ExecSQL. Yet the operation drags on far longer than the database itself needs. The bottleneck is rarely the INSERT statement; it is the network round trip and per-statement overhead that FireDAC pays for every single row.

In a typical client-server setup, each call to ExecSQL travels to the database server and waits for acknowledgement. Even on a fast LAN, that latency multiplies: 5,000 rows means 5,000 round trips. FireDAC also prepares and manages parameter binding for each execution. The result is that your application spends most of its time waiting, not computing.

Batching with FireDAC Array DML

FireDAC Array DML lets you set Params.ArraySize to the number of rows you want to send, assign values to each parameter array, and call ExecSQL once. Instead of thousands of separate statements, the driver transmits the batch in a single database call, or a small number of calls, depending on the driver.

The following Delphi method shows the pattern. Run it from a Delphi unit or data module where you already have an open TFDConnection. The database user needs INSERT permission on the target table. Replace AConnection with your live connection object and Count with your batch size.

procedure BatchInsertOrders(AConnection: TFDConnection; Count: Integer);
var
  FDQuery: TFDQuery;
  i: Integer;
begin
  FDQuery := TFDQuery.Create(nil);
  try
    FDQuery.Connection := AConnection;
    FDQuery.SQL.Text :=
      'INSERT INTO orders (customer_id, amount, created_at) ' +
      'VALUES (:customer_id, :amount, :created_at)';

    // Tell FireDAC how many rows are in this batch.
    FDQuery.Params.ArraySize := Count;

    // Fill the parameter arrays.
    for i := 0 to Count - 1 do
    begin
      FDQuery.ParamByName('customer_id').AsIntegers[i] := 1000 + i;
      FDQuery.ParamByName('amount').AsFloats[i]      := 99.95 + (i * 0.01);
      FDQuery.ParamByName('created_at').AsDateTimes[i] := Now;
    end;

    FDQuery.ExecSQL;
  finally
    FDQuery.Free;
  end;
end;

In this snippet, Count is the batch size. FireDAC maps each parameter to an array of Count values. When ExecSQL runs, the driver sends the whole set. If your connection uses an explicit transaction, wrap the call in StartTransaction and Commit so the batch is atomic. After execution, compare the number of rows in the table against Count to confirm the insert landed.

Aspect	Row-by-row	Array DML
Round trips	One per row	One per batch
Client memory	Minimal	Grows with ArraySize
Error isolation	Per-row	Batch-level or index-level
Driver support	Universal	Varies by DBMS

Trade-offs: memory, errors, and driver support

Memory scales with ArraySize

Each parameter array holds ArraySize values in client memory. A batch of 10,000 wide rows is usually fine, but pushing ArraySize into the hundreds of thousands can increase RAM pressure and may exceed parameter limits on the database side. Start with a conservative size such as 500 or 1,000 and measure.

Error handling becomes coarser

If row 3,000 violates a unique constraint, the behavior depends on your FireDAC driver and DBMS. Some drivers abort the entire batch; others may report an error at a specific array index. You can no longer assume that one bad row leaves the rest inserted. A common mitigation is to catch the exception, then retry the failed batch row-by-row or in smaller chunks to isolate the problem.

Driver support is not uniform

FireDAC's Array DML capability varies by driver and target database. Some backends receive the batch as a single optimized statement; others fall back to row-by-row execution under the hood. Before you commit to the pattern, check the FireDAC driver documentation for your specific DBMS and confirm that ArraySize is supported. Exact behavior also depends on your FireDAC version, so verify against the release you are using.

Verifying the gain and where to start

Do not assume the batch is faster without evidence. Build a small test harness that inserts identical data twice: once with ArraySize = 1 in a loop, and once with a larger ArraySize. Compare wall-clock time. Then attach a TFDMonitor to the connection, or use your database's native profiler, and inspect the trace. If Array DML is active, you should see one statement trace event for the batch rather than one per row.

• Compare execution time of a row-by-row insert loop versus an Array DML batch using identical data in a small Delphi test app.
• Inspect the SQL monitor (TFDMonitor) or database profiler to confirm the batch is sent as a single statement rather than multiple round trips.
• Check the FireDAC driver documentation for your target DBMS to confirm ArraySize support and any backend-specific limits.
• Test with a deliberately malformed row to observe how errors surface and whether the transaction can be partially recovered.

Array DML is not a magic switch; it is a tuning lever. Pick a batch size that balances memory and throughput, verify the round-trip reduction with a monitor, and keep a fallback path for error recovery. For bulk inserts over a network, that combination usually turns a sluggish loop into a manageable operation.