Performance with transactions
Commit database changes by blocks of transaction speeds performance with some database servers.
To mimic the IBM®
Informix® auto-commit behavior with an ANSI
compliant RDBMS like Oracle® or
DB2®, the database driver must perform an
implicit commit after each statement execution, if the SQL statement is not inside a
transaction block. This generates unnecessary database operations and can slow down big
loops. To avoid this implicit commit, you can control the transaction with BEGIN
WORK
/ COMMIT WORK
around the code containing a lot of SQL
statement execution.
This technique is especially recommended with SQLite, because the SQLite database library performs a lot of operations during a commit.
PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
FOR n=1 TO 100
EXECUTE s USING n, c -- Generates implicit COMMIT
END FOR
You can improve performance if you put a transaction block around the loop:
PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
BEGIN WORK
FOR n=1 TO 100
EXECUTE s USING n, c -- In transaction -> no implicit COMMIT
END FOR
COMMIT WORK
With this code, only 1001 basic SQL operations will be executed ( 1000 inserts plus 1 commit ).
PREPARE s FROM "INSERT INTO tab VALUES ( ?, ? )"
BEGIN WORK
FOR n=1 TO 100
IF n MOD 10 == 0 THEN
COMMIT WORK
BEGIN WORK
END IF
EXECUTE s USING n, c -- In transaction -> no implicit COMMIT
END FOR
COMMIT WORK
Note that the LOAD
instruction automatically starts a transaction, if not
yet initiated. Therefore there is no need to enclose the LOAD
statement
within a BEGIN WORK
/ COMMIT WORK
, except if other SQL
statements are part of the transaction and need to be processed as a single atomic database
change.