Performance of the Array DML Command (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with Commands (FireDAC)


This article describes the high performance of the Array DML feature supported by FireDAC. This first article will lead you through an easy example that shows how to insert thousands of records per second by writing just a few lines of code.

Introduction

FireDAC encapsulates all database server-specific implementation of the Array DML commands and lets you use identical code for all server types. The resulting performance will differ based on the server implementation. Mainly Oracle, Microsoft SQL Server, and IBM DB2 have very powerful support of the Array DML and the resulting performance increase is noticeable.

We recommend that you use the sample code to get a feeling of the potential performance increase within your application and network.

How to Prepare your Test Environment

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

  • The code used in this tutorial: FireDAC\Samples\Comp Layer\TFDQuery\ExecSQL\AD03-ArrayDML.
  • A basic code example: FireDAC\Samples\Comp Layer\TFDQuery\ExecSQL\Batch.

Main elements of the Array DML commands

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

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

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

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

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

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

For more details, see the Array DML reference chapter.

Usage Hints

  • 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.
  • FireDAC unifies the Array DML for different server types (no need for you do dig into the API).

Typical Results of the Array DML Test Run

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

ArrayDMLTestPicture.png

The 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 screenshot:

ArrayDMLTestResults.png

A larger Array DML ArraySize results in a higher performance (in our case up to a factor of 2000).

AD03 Results.png

Performance Hints

The Array DML command performance is influenced by the following:

  • They are noticeably 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 frequently reached, as the server normally has to evaluate triggers and indexes.
  • For very large batch inserts (e.g. > 1'000'000 records), you should consider dropping and recreating non primary key indexes to reach a maximum performance.


See Also