This tutorial has three main sections:

• How to prepare your test environment.
• The main elements of the Array DML commands.
• The typical results of the Array DML test run.

AnyDAC encapsulates all database server specific implementation of the Array DML commands and lets you use identical code for all server types. Obviously, the resulting performance will differ based on the server implementation; especially Oracle, Microsoft SQL Server and IBM DB2 have very powerful support of the Array DML and the resulting performance increase is just amazing. 

Please use the sample code to get a feeling for the potential performance increase within your application and network. 

The following example works with the AnyDAC sample database environment. For further details about the installation of this database look into AnyDAC Demo Databases. You find the demo projects in your sample directory:

• This tutorial code - <AnyDAC>\Samples\Comp Layer\TADQuery\ExecSQL\AD03-ArrayDML.
• A basic example code - <AnyDAC>\Samples\Comp Layer\TADQuery\ExecSQL\Batch.

Imagine a "use case" where you have to INSERT, UPDATE, DELETE or run any other parametrized command N times, typically one command per single record. This means, that each set of input parameters requests to execute a SQL command and is transferred separately between the client and the server. This leads to a heavy load on the network, client and server. 

Array DML allows you to transport not only one, but N-sets of data within one transfer. Have a look at the following example: 

ADQuery1.SQL.Text:= 'insert into ADQA_Batch_test (tint, tstring) values(:f1, :f2)';

You can speed up your code dramatically by using Array DML commands. Such commands transfer not only one, but N sets of parameters. 

ADQuery1.Params.ArraySize := 100;
...
for i := 0 to ADQuery1.Params.ArraySize do begin
  ADQuery1.Params[0].AsIntegers[i] := i;
  ADQuery1.Params[1].AsStrings[i] := 'Test' + IntToStr(i);
end;
ADQuery1.Execute(ADQuery1.Params.ArraySize);

This means the Params property of the query is no more a one- but a two-dimensional array, that allows you to store N sets of parameter values before sending them to the server. 

For more details, please see "Array DML" reference chapter. 

• Can be used for any SQL command that uses parameters (INSERT, UPDATE, DELETE ...).
• The error handling is supported on record level and described in a separate article.
• AnyDAC unifies the Array DML for different server types (no need for you do dig into the API).

The attached test code allows you to experiment within your specific environment. 

Results of the test example can differ a lot depending on host and network performance. A typical picture of a local Oracle on a rather old laptop will still show > 100'000 records per second as you can see in this screen shot: 

A larger Array DML ArraySize results in a higher performance (in our case up to a factor of 2000). We expect that the performance boost in your own environment will surprise you as well. 

Array DML command performance is influenced by:

• The fact that they are a lot faster on slow networks as these commands create less TCP/IP packages.
• They reduce the CPU load on the client side, as most of the time the server has to work on the array command.
• The theoretical speed of > 100'000 rec/sec is not often reached as the server normally has to evaluate triggers and indexes.
• For real large batch inserts (e.g. > 1'000'000 records), you should consider to drop and recreate non primary key indexes to reach a maximum performance.