Use FireDAC Array DML to write many rows in one Execute call

Problem and takeaway

Calling TFDQuery.Execute inside a loop forces one client-server round-trip per row. For hundreds or thousands of inserts, updates, or deletes, network latency dominates the operation. FireDAC Array DML binds arrays of parameter values to a single prepared INSERT, UPDATE, or DELETE statement, so one Execute call can write many rows.

Useful takeaway: set ArraySize to the row count, assign equal-length arrays to each parameter, call Execute once, and verify that the affected row count matches the array length.

How Array DML works

FireDAC prepares the statement once. Instead of binding a single value to :name, you bind an array of values. The driver transmits the array to the database in one batch. A round-trip is one exchange between the application and the database server; reducing round-trips is the main performance lever here. A prepared statement is a query template the database compiles once and can execute many times with different parameters.

Worked Delphi configuration

This example runs in a RAD Studio Delphi project with an active FireDAC connection. The application user needs INSERT permission on the target table. Exact array-assignment syntax varies by FireDAC version; check your version’s documentation if AsArray is unavailable.

procedure BatchInsertCustomers(const Names, Cities: TArray);
var
  Query: TFDQuery;
begin
  if Length(Names) <> Length(Cities) then
    raise Exception.Create('Parameter arrays must have equal length');

  Query := TFDQuery.Create(nil);
  try
    Query.Connection := FDConnection1;
    Query.SQL.Text := 'INSERT INTO customers (name, city) VALUES (:name, :city)';

    // Tell FireDAC how many rows this Execute will process.
    Query.ArraySize := Length(Names);

    // Bind same-length arrays to each parameter.
    Query.ParamByName('name').AsArray := Names;
    Query.ParamByName('city').AsArray := Cities;

    // Wrap in an explicit transaction for atomicity and throughput.
    FDConnection1.StartTransaction;
    try
      Query.Execute;
      FDConnection1.Commit;
    except
      FDConnection1.Rollback;
      raise;
    end;

    // Expected check: affected rows should equal the array length.
    // If it does not, inspect the driver error or row-level failures.
  finally
    Query.Free;
  end;
end;

Placeholders: FDConnection1 is your active TFDConnection; customers is the target table. Required permission: INSERT on customers. Risk: if ArraySize is left at a large value and the same TFDQuery is later reused for a scalar execute, FireDAC may attempt multiple executions unless you reset ArraySize to 1.

Loop versus Array DML

Approach	Round-trips	Typical use
Single-row loop	One per row	Small batches, per-row error handling
Array DML	One for the batch	Bulk inserts, updates, or deletes with the same statement

Limits and common mistakes

• Equal array lengths. Every parameter array must have the same length. Mismatched lengths can raise exceptions or cause driver-dependent behavior.
• Stale array state. Reset ArraySize := 1 after Array DML if you reuse the query for single-row operations.
• Generated keys. Retrieving auto-generated keys from array inserts is driver-specific and often unavailable. Do not rely on it without testing your driver.
• Batch size. Very large arrays can exhaust client memory or exceed server packet limits. Chunk large imports, for example 1,000 to 10,000 rows per batch, and monitor memory.
• Driver variance. Some drivers emulate Array DML internally with little speed gain. Small batches may show no measurable improvement.

Verification

1. Build a small project in your target RAD Studio version and run the batch insert against a test table.
2. Check that Query.RowsAffected equals the length of the bound arrays.
3. Use TFDMonitor or database tracing to confirm the driver sends one prepared statement with array binding rather than separate statements per row.
4. Test mismatched array lengths and an oversized batch in a non-production environment to observe exception behavior and memory impact.

When not to use Array DML

Avoid Array DML when you need per-row error isolation, when generated keys must be returned for every inserted row, or when the target driver does not support native array binding. Measure against your database before adopting it for production imports.