How to speed up DMLs in WinCC OA CtrlADO with dbBulkCommand...

When you insert and/or update a lot of rows (lot of UPDATE or INSERT statements) you first of all should take care about transactions and the second performance improvement is to use the “Bulk” Statement in WinCC OA.

The CtrlADO function is “dbBulkCommand”. For easy usage you can use the “rdbExecuteBulk” function which is implemented in the library “rdb.ctl” (this library is delivered with WinCC OA). This function you can give an array of SQL Statements
(dyn_string parameter).

The loop of executing statements in dbBulkCommand is implemented in C++, so it will be much faster than doing a loop of statements in the interpreted WinCC OA Control language.

By using this function you will create an array of SQL Statements (INSERT, UPDATE, DELETE) and execute all of the statements inside of the array with one single call of dbBulkCommand or rdbExecuteBulk! 

#uses "CtrlADO.dll"
#uses "rdb.ctl"
dbConnection db;
dyn_string dsSQL;
rdbOpen(db, "DSN=mydb;UID=scott;PWD=tiger");
for ( int i=1; i<=10000; i++ ) {
dsSQL[i]="UPDATE emp SET sal=sal*0.10 WHERE id=”+i;
rdbExecuteBulk(db, dsSQL);

Last update:
2014-09-09 19:08
Andreas Vogler
Average rating: 5 (1 Vote)

You cannot comment on this entry

Chuck Norris has counted to infinity. Twice.