The Array DML execution technique submits a single DBMS command with an array of parameters. Each command parameter has array of values and all parameters have arrays of the same length. Then AnyDAC requests the DBMS to execute a command once for each row in arrays. This technique reduces the amount of communication between DBMS and client, enables DBMS to stream command execution. That speeds up a execution in times. 

Following picture shows that: 

In AnyDAC terms "batch command execution" and "Array DML execution" are used often as synonyms. Array DML may be used almost for all parameterized commands, including stored procedure calls. AnyDAC implements Array DML using native DBMS API capabilities or emulates Array DML execution, if the DBMS API does not support it. 

The following table lists DBMS and Array DML features:

Note:

• "Array DML Mode" description see in "Error Handling" chapter.
• "Array DML Limit Symptoms" description see in "Troubleshooting" chapter.

Before Array DML execution, the application code must setup parameter value arrays. First, setup array length by assigning value to Params.ArraySize. Assigning this property value, implicitly assigns specified array length to all parameters ArraySize property. So, Params collection must be not empty before assigning to Params.ArraySize. Second, assigns values to parameter arrays. TADParam class has a set of AsXXXs[AIndex: Integer] properties, similar to AsXXX properties. And other properties and methods accepting as first parameter array index. For example: 

ADQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// here ADQuery1.Params collection is filled by 3 parameters
ADQuery1.Params.ArraySize := 100;
for i := 0 to 100-1 do begin
  ADQuery1.Params[0].AsIntegers[i] := i;
  ADQuery1.Params[1].AsStrings[i] := 'qwe';
  ADQuery1.Params[2].Clear(i);
end;

TADCustomCommand, TADQuery and TADStoredProc have the Execute(ATimes: Integer = 0; AOffset: Integer = 0) method. Here, ATimes defines the length of the array. AOffset is index of first item in the array. So, the command will be executed (ATimes - AOffset) times, starting from AOffset row. ATimes must be equal or less Params.ArraySize. For example: 

ADQuery1.Execute(100, 0);

After Array DML execution, the property RowsAffected has the number of successful executions, not the total number of affected rows by all executions. For example: 

ShowMessage(IntToStr(ADQuery1.RowsAffected));

TADAdaptedDataSet, TADQuery and TADStoredProc have ability to trap errors using OnExecuteError event handlers. If the error handler is not assigned and an error happens, then Execute will raise an exception and RowsAffected will be updated. 

If TADAdaptedDataSet.OnExecuteError event handler is assigned, it will get original exception object, current times and offset, and may return AAction value, talking what to do next. The AError.Errors[...] contains one or more errors. AError.Errors[i].RowIndex is a failed row index. 

For example: 

procedure TForm1.ADQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AError: EADDBEngineException; var AAction: TADErrorAction);
begin
  if AError.Errors[0].Kind = ekUKViolated then
    AAction := eaSkip
  else
    AAction := eaFail;
end;

The exact behavior depends on a DBMS and its corresponding Array DML mode:

Note, setting ResourceOptions.ArrayDMLSize to 1 implicitly sets array execution mode to aeUpToFirstError. To get currently connected DBMS Array DML mode use: 

if ADConnection1.ConnectionMetaDataIntf.ArrayExecMode = aeOnErrorUndoAll then
  ....

It is important to properly setup parameters, including setting property Size for the string parameters. For example, AnyDAC in case of Oracle will allocate 4000 bytes for each ftString / ftWideString parameter, when Size is not explicitly specified. So, for 10,000 of values will be allocated 40 Mb buffer. If there are many parameters, then application can eat all the system memory. 

Most DBMS have implicit limit for the Array DML size. It depends on the DBMS client library buffer size or the maximum allowed network packet. When a limit is reached use ResourceOptions.ArrayDMLSize option to transparently split large Array DML into few lesser slices.

Command Batches

Array DML with IADPhysCommand: 

var
  oCmd: IADPhysCommand;
……
  with oCmd do begin
    CommandText := 'insert into Customers (ID, Name) values (:ID, :Name)';
    // Set up parameter types
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftString;
    Params[1].Size := 40;
    // Set up parameters' array size
    Params.ArraySize := 10000;
    // Set parameter values
    for i := 0 to 10000 - 1 do begin
      Params[0].AsIntegers[i] := i;
      Params[1].AsStrings[i] := 'Somebody ' + IntToStr(i);
    end;
    // Execute batch
    Execute(10000, 0);
  end;

Array DML with TADQuery and error handling: 

procedure TForm1.ADQuery1ExecuteError(ASender: TObject; ATimes,
  AOffset: Integer; AException: EADDBEngineException; var AAction: TADErrorAction);
begin
  case AException.Errors[0].Kind of
  ekPKViolated:
    begin
      // fix ID to be unique
      ADQuery.Params[0].AsIntegers[AException.Errors[0].RowIndex] := AException.Errors[0].RowIndex;
      AAction := eaRetry;
    end;
  ekFKViolated:
    // if Region with RegionID is not found, then just skip row
    AAction := eaSkip;
  else
    AAction := eaFail;
  end;
end;

procedure TForm1.Button1Click(ASender: TObject);
begin
  with ADQuery1 do begin
    SQL.Text := 'insert into Customers (ID, RegionID, Name, Note) values (:ID, :RegionID, :Name, :Note)';
    // Set up parameter types
    Params[0].DataType := ftInteger;
    Params[1].DataType := ftInteger;
    Params[2].DataType := ftString;
    Params[2].Size := 40;
    Params[3].DataSize := ftMemo;
    // Set up parameters' array size
    Params.ArraySize := 10000;
    // Set parameter values
    for i := 0 to 10000 - 1 do begin
      if i mod 100 = 0 then
        // force PK violation
        Params[0].AsIntegers[i] := i - 1
      else
        Params[0].AsIntegers[i] := i;
      Params[1].AsIntegers[i] := GetRegionIdForCustomer(i);
      Params[2].AsStrings[i] := 'Somebody ' + IntToStr(i);
      Params[3].Clear(i);
    end;
    // Execute batch
    Execute(10000, 0);
  end;
end;