Using Batch Updates in Delphi Data Access Components

September 11th, 2015

Data amount processed by modern databases grows steadily. In this regard, there is an acute problem – database performance. Insert, Update and Delete operations have to be performed as fast as possible. Therefore Devart provides several solutions to speed up processing of huge amounts of data. So, for example, insertion of a large portion of data to a DB is supported in the Loader. Unfortunately, Loader allows to insert data only – it can’t be used for updating and deleting data.

The new version of Devart Delphi Data Access Components introduces the new mechanism for large data processing — Batch Operations. The point is that just one parametrized Modify SQL query is executed. The plurality of changes is due to the fact that parameters of such a query will be not single values, but a full array of values. Such approach increases the speed of data operations dramatically. Moreover, in contrast to using Loader, Batch operations can be used not only for insertion, but for modification and deletion as well.

Let’s have a better look at capabilities of Batch operations with an example of the BATCH_TEST table containing attributes of the most popular data types.

Batch_Test table generating scripts

For Oracle:

For MS SQL Server:

For PostgreSQL:

For InterBase:

For MySQL:

For SQLite:

Batch operations execution

To insert records into the BATCH_TEST table, we use the following SQL query:

When a simple insertion operation is used, the query parameter values look as follows:

1 100 2.5 ‘String Value 1’ 01.09.2015

After the query execution, one record will be inserted into the BATCH_TEST table.

When using Batch operations, the query and its parameters remain unchanged. However, parameter values will be enclosed in an array:

1 100 2.5 ‘String Value 1’ 01.09.2015
2 200 3.15 ‘String Value 2’ 01.01.2000
3 300 5.08 ‘String Value 3’ 09.09.2010
4 400 7.5343 ‘String Value 4’ 10.10.2015
5 500 0.4555 ‘String Value 5’ 01.09.2015

Now, 5 records are inserted into the table at a time on query execution.

How to implement a Batch operation in the code?

Batch INSERT operation sample

Let’s try to insert 1000 rows to the BATCH_TEST table using a Batch Insert operation:

This command will insert 1000 rows to the table with one SQL query using the prepared array of parameter values. The number of inserted rows is defined in the Iters parameter of the Execute(Iters: integer; Offset: integer = 0) method. In addition, you can pass another parameter – Offset (0 by default) – to the method. The Offset parameter points the array element, which the Batch operation starts from.

We can insert 1000 records into the BATCH_TEST table in 2 ways.

All 1000 rows at a time:

2×500 rows:

500 rows, then 300, and finally 200:

Batch UPDATE operation sample

With Batch operations we can modify all 1000 rows of our BATCH_TEST table just this simple:

Batch DELETE operation sample

Deleting 1000 rows from the BATCH_TEST table looks like the following operation:

Performance comparison

The example with BATCH_TEST table allows to analyze execution speed of normal operations with a database and Batch operations:

DAC Name Operation Type 25 000 records
Standard Operation (sec.) Batch Operation (sec.)
ODAC / UniDAC (with OracleUniProvider) Insert 17.64 0.59
Update 18.28 1.20
Delete 16.19 0.45
LiteDAC / UniDAC (with SQLiteUniProvider) Insert 2292 0.92
Update 2535 2.63
Delete 2175 0.44
PgDAC / UniDAC (with PostgreSQLUniProvider) Insert 346.7 1.69
Update 334.4 4.59
Delete 373.7 2.05
IBDAC / UniDAC (with InterBaseUniProvider) Insert 55.4 3.03
Update 81.9 3.58
Delete 61.3 0.91
MyDAC / UniDAC (with MySQLUniProvider) Insert 1138 11.02
Update 1637 26.72
Delete 1444 17.66
SDAC / UniDAC (with SQLServerUniProvider) Insert 19.19 3.09
Update 20.22 7.67
Delete 18.28 3.14
The less, the better.

It should be noted, that the retrieved results may differ when modifying the same table on different database servers. This is due to the fact that operations execution speed may differ depending on the settings of a particular server, its current workload, throughput, network connection, etc.

Thing you shouldn’t do when accessing parameters in Batch operations!

When populating the array and inserting records, we accessed query parameters by index. It would be more obvious to access parameters by name:

However, the parameter array would be populated slower, since you would have to define the ordinal number of each parameter by its name in each loop iteration. If a loop is executed 10000 times – performance loss can become quite significant.

19 Responses to “Using Batch Updates in Delphi Data Access Components”

  1. raouf rahiche Says:

    thank you very much
    and thanks also to mariadb

  2. DAC Team Says:

    You are welcome;)

  3. FredS Says:

    That is an impressive speed increase. I switched to Firebird because a Select ran very slow on this large table I used. Firebird crunched that like it was nothing. Then found out that Bulk Firebird inserts are very slow, even when using “EXECUTE BLOCK ” for ~50k records one could watch the Progress meter do its thing but this is super fast..

    My code is inserting in 1k record chunks but I set that to 50k for a test and it was nearly instant.


  4. DAC Team Says:

    Thank you for kind words. However, we think there is still much to develop.

  5. Ruslan Says:


    Does it works with MSAccess data provider?

    Thank you!

  6. DAC Team Says:

    The latest UniDAC version 6.2.9 supports Batch operations for MSAccess data provider.

  7. Jeff Says:

    I have to reply you a problem about batch insert, as I tried result, when I try to batch insert data with chinese character, I found chinese character is NOT correct in mysql db.

    But, if you just insert one record, It’s correct.

    Why ??

  8. Jeff Says:

    Hi Sir:
    I got a problem when I batch insert with chinese character(chinese character doesn’t show correctly).
    But, the wired thing is when I just insert one record, chinese character show correctly.
    Anybody have the same problem ?

  9. DAC Team Says:

    Hi, Jeff! Thank you for your notice. We will investigate the issue with Chinese characters on batch insert to MySQL and post here about the results. Are you using MyDAC or UniDAC? And what version of the product are you using?

  10. Jeff Says:

    Thanks DAC Team,
    I am using MyDAC 8.6 trial version.
    I am evaluating the solution of using MySQL on delphi.


  11. Jeff Says:

    For the record,
    I try to use component MyLoader to implement batch insert and It’s working fine !
    I save the data into array, when PutData event triggered, I loop the array to PutColumnData.


  12. Tonni Says:


    Tested batch update in one situation but not get this to work.

    Is the procedure exact same when you’ve got Value parameters and also parameters in where clause? Or do I just have to include the key values also?


  13. pini Says:

    I’m using SDAC ver 6.10.21.
    I wanted to use the Batch-Update example but the TMSQuery
    doesn’t seems to have ‘ValueCount’ params –> MSQuery1.Params.ValueCount
    Also, the Params 2D-array seems that it is not exist…
    MSQuery1.Params[0][i].AsInteger := i + 1;
    MSQuery1.Params[1][i].AsInteger := i + 2000 + 1;

    Am i using incorrect version?


  14. DAC Team Says:

    Hello, Pini.
    In the SDAC version 6.10.21 support for batch operations wasn’t yet added. It was added in SDAC 7.2.7 released on 09.09.2016.

  15. Engin Says:

    Batch Test not working? I have Error “The SQL statement is not allowable for a bulk”
    DB :MySQL

    CREATE TABLE afaturalar (
    AktarimID int(11) DEFAULT NULL,
    FirmaSicilNo varchar(20) DEFAULT NULL,
    Yil smallint(6) DEFAULT NULL,
    Tarih date DEFAULT NULL,
    Seri varchar(30) DEFAULT ”,
    Sira varchar(30) DEFAULT ”,
    AltFirmaSicilNo varchar(20) DEFAULT NULL,
    AltFirmaUnvan varchar(400) DEFAULT ”,
    Hizmet varchar(100) DEFAULT ”,
    Miktar varchar(30) DEFAULT ”,
    Matrah decimal(12,2) DEFAULT ‘0.00’,
    Kdv decimal(12,2) DEFAULT ‘0.00’,
    GGBTescilNo varchar(50) DEFAULT ”,
    KdvDonem varchar(20) DEFAULT ”,
    IhracatFirmaSicilNo varchar(20) DEFAULT ”,
    ExcelSatirNo int(11) DEFAULT NULL,

    Delphi Code;
    Qry.SQL.Text := ‘ INSERT INTO afaturalar VALUES (DEFAULT, :AktarimID, :FirmaSicilNo, :Yil, :Tarih, :Seri, :Sira, :AltFirmaSicilNo, :AltFirmaUnvan, :Hizmet, :Miktar, :Matrah, :Kdv, :GGBTescilNo, :KdvDonem, :IhracatFirmaSicilNo, :ExcelSatirNo)’;

    Qry.Params[0].DataType := ftInteger; //AktarimID
    Qry.Params[1].DataType := ftString; //FirmaSicilNo
    Qry.Params[2].DataType := ftSmallint; //Yil
    Qry.Params[3].DataType := ftDate; //Tarih
    Qry.Params[4].DataType := ftString; //Seri
    Qry.Params[5].DataType := ftString; //Sira
    Qry.Params[6].DataType := ftString; //AltFirmaSicilNo
    Qry.Params[7].DataType := ftString; //AltFirmaUnvan
    Qry.Params[8].DataType := ftString; //Hizmet
    Qry.Params[9].DataType := ftString; //Miktar
    Qry.Params[10].DataType := ftFloat; //Matrah
    Qry.Params[11].DataType := ftFloat; //Kdv
    Qry.Params[12].DataType := ftString; //GGBTescilNo
    Qry.Params[13].DataType := ftString; //KdvDonem
    Qry.Params[14].DataType := ftString; //IhracatFirmaSicilNo
    Qry.Params[15].DataType := ftInteger; //ExcelSatirNo
    Qry.Params.ValueCount := 1000;

    for i := 0 to Qry.Params.ValueCount – 1 do begin
    Qry.Params[0][i].AsInteger := i;
    Qry.Params[1][i].AsString := i.ToString;
    Qry.Params[2][i].AsSmallInt := i;
    Qry.Params[3][i].AsDate := Now;
    Qry.Params[4][i].AsString := i.ToString;
    Qry.Params[5][i].AsString := i.ToString;
    Qry.Params[6][i].AsString := i.ToString;
    Qry.Params[7][i].AsString := i.ToString;
    Qry.Params[8][i].AsString := i.ToString;
    Qry.Params[9][i].AsString := i.ToString;
    Qry.Params[10][i].AsFloat := i;
    Qry.Params[11][i].AsFloat := i;
    Qry.Params[12][i].AsString := i.ToString;
    Qry.Params[13][i].AsString := i.ToString;
    Qry.Params[14][i].AsString := i.ToString;
    Qry.Params[15][i].AsInteger := i;

    I tried but not working

  16. Tanvir Says:

    I am using batch insert which works fine when I have multiple rows (means rowcount>1).
    The statement is Query1.Execute(rowcount);
    However, it throws exception when the value of rowcount = 1 (because I have only one row in the batch and I don’t have any remaining rows to insert. The exception is : “Could not convert variant of type (Array Variant) into type (OleStr). Is there any unified way to use the same batch insert facility to handle the remaining single row to be inserted with the same command?

  17. DAC Team Says:

    Hello, Tanvir!
    Please specify the name and exact version of our product, which you are using when executing Batch Insert.

  18. Mulham Swaidan Says:

    Does this approach of batch update/insert work with Blob fields? (using latest UniDAC product to connect to a SQLite database).

  19. DAC Team Says:

    Hello, Mulham!
    Yes, you can use batch operations when working with BLOB fields in UniDAC with SQLite database.

Leave a Comment