Speeding Up Bulk Data Loads in RAD Studio with FireDAC Array DML

The problem: row‑by‑row inserts are a bottleneck

When you need to load thousands of records from a CSV file, a staging table, or any external source into a database using Delphi or C++Builder, the naïve approach is to loop over the data and call TFDQuery.ExecSQL (or Execute) for each row. Each call causes a separate network round‑trip, a SQL parse, and a transaction overhead. Even on a fast LAN the elapsed time can grow linearly with the row count, making the load impractical for nightly ETL jobs or user‑initiated imports.

Thesis: FireDAC Array DML lets you send many parameter sets in one round‑trip

FireDAC, the data‑access layer shipped with RAD Studio, provides an Array DML mode. Instead of preparing and executing a statement per row, you prepare it once, tell the component how many rows you will send (ArraySize), fill the parameter arrays, and execute a single call that processes all rows. The driver translates this into a bulk API when the underlying DBMS supports it (e.g., SQL Server’s sp_executesql with table‑valued parameters, Oracle’s array bind, or PostgreSQL’s EXECUTE MANY). When a native bulk API is unavailable, FireDAC emulates the behavior by sending the rows in a single packet, still saving the per‑round‑trip parse cost.

How to set up Array DML in code

The essential steps are:

1. Create a parameterized TFDQuery (or TFDStoredProc) with an INSERT, UPDATE, or DELETE statement.
2. Set TFDQuery.Params.ArraySize to the number of rows you intend to process in one batch.
3. Before calling Prepare, assign values to each parameter using the array index syntax, e.g., ParamByName('ID').AsInteger[0] := 1;.
4. Call Prepare (this locks the array size).
5. Fill the arrays for all rows.
6. Execute with TFDQuery.Execute(ARowCount), passing the actual number of rows to process.

Important: ArraySize must be set before Prepare. Changing it after preparation requires a new Prepare call, otherwise you may get undefined behavior or access violations.

Worked example: loading a CSV into a SQL Server table

Assume a simple table:

CREATE TABLE dbo.Products (
    ProductID   INT IDENTITY(1,1) PRIMARY KEY,
    SKU         VARCHAR(20) NOT NULL,
    Name        VARCHAR(100),
    UnitPrice   MONEY
);

The CSV contains columns SKU, Name, UnitPrice. The following Delphi snippet shows the Array DML approach (error handling omitted for brevity):

uses FireDAC.Comp.Client, FireDAC.Stan.Param, System.SysUtils, System.Classes;

procedure LoadProductsFromCSV(const AFileName: string);
var
  FDConn: TFDConnection;
  FDQ: TFDQuery;
  CSV: TStringList;
  i, RowCount: Integer;
  Err: EFDArrayError;
begin
  FDConn := TFDConnection.Create(nil);
  try
    FDConn.ConnectionDefName := 'MySQLServer'; // defined in FDConnectionDef
    FDConn.Open;
    FDQ := TFDQuery.Create(nil);
    try
      FDQ.Connection := FDConn;
      FDQ.SQL.Text := 'INSERT INTO dbo.Products (SKU, Name, UnitPrice) VALUES (:SKU, :Name, :UnitPrice)';
      // Choose a batch size; 500 works well for many scenarios
      FDQ.Params.ArraySize := 500;
      FDQ.Prepare;

      CSV := TStringList.Create;
      try
        CSV.LoadFromFile(AFileName);
        // Assume first line is header, skip it
        RowCount := 0;
        for i := 1 to CSV.Count - 1 do
        begin
          // Fill arrays for the current row
          FDQ.Params.ParamByName('SKU').AsString[RowCount] := CSV.Names[i]; // simplified; real code would split comma
          FDQ.Params.ParamByName('Name').AsString[RowCount] := CSV.ValueFromIndex[i];
          FDQ.Params.ParamByName('UnitPrice').AsCurrency[RowCount] := StrToCurr(CSV.ValueFromIndex[i]);
          Inc(RowCount);
          if RowCount = FDQ.Params.ArraySize then
          begin
            FDQ.Execute(RowCount);
            RowCount := 0; // reset for next batch
          end;
        end;
        // Process any remaining partial batch
        if RowCount > 0 then
          FDQ.Execute(RowCount);
      finally
        CSV.Free;
      end;
    finally
      FDQ.Free;
    end;
  finally
    FDConn.Free;
  end;
except
  on E: EFDArrayError do
  begin
    // E.AErrors contains one EFDException per failed row
    // You can log or decide to continue
  end;
  raise; // re‑throw after logging if desired
end;

This example demonstrates:

• Setting ArraySize before Prepare.
• Filling parameter arrays with AsXxx[Index].
• Executing full batches and a final partial batch.
• Catching EFDArrayError to inspect per‑row failures via E.AErrors.

Trade‑offs and limitations

Array DML is not a silver bullet. Consider the following:

• Transaction scope: The batch executes as a single server‑side statement. If you wrap it in an explicit TFDConnection.StartTransaction / Commit, a failure in any row will roll back the whole batch unless you catch EFDArrayError and decide to continue. For “best‑effort” loads you may prefer to commit after each successful batch and log the bad rows.
• Memory usage: The client allocates memory for ArraySize * parameter count. Very large values (e.g., >10 000) can consume noticeable RAM and may exceed driver‑side packet limits. A typical sweet spot is a few hundred to a few thousand rows, depending on row width and network latency.
• Driver support: While most major drivers (SQL Server, Oracle, PostgreSQL, MySQL, SQLite, InterBase/Firebird) implement a fast path, some older or embedded drivers fall back to emulation. Always verify performance against your target backend.
• Statement restrictions: Certain SQL constructs (e.g., statements with output parameters, certain stored procedures, or queries that return result sets) cannot be used with Array DML. Consult the FireDAC help for “Array DML Limitations”.

Actionable checklist for your next import tool

1. Profile the current row‑by‑row approach with TStopwatch to establish a baseline.
2. Create a test table mirroring your production schema.
3. Implement a small prototype using the pattern above, starting with a modest ArraySize (e.g., 500).
4. Run the prototype against both a local SQLite file and your production server (SQL Server, Oracle, etc.) to see where the gain appears.
5. Introduce a deliberate constraint violation (duplicate key, null‑not‑allowed) and verify that EFDArrayError.AErrors reports the exact row index and error code.
6. Decide on transaction strategy: whole‑batch rollback vs. per‑batch commit with error logging.
7. Adjust ArraySize upward until you observe diminishing returns or hit memory/packet limits, then settle on the optimal value.
8. Wrap the final implementation in a reusable unit or component, exposing properties for BatchSize, OnRowError, and UseTransaction.

By moving from per‑row executions to FireDAC Array DML you can often cut bulk load times by an order of magnitude or more, while retaining fine‑grained error handling. The technique is portable across the major databases supported by RAD Studio, making it a practical engineering decision for any Delphi or C++Builder data‑intensive application.