Teradata Transactions & Performance

1
449

Teradata TransactionsThere are several ways of writing SQL statements and making use of transaction handling. Each method has its own advantages, disadvantages, and behavior which you have to consider.

As an example let’s consider two statements which have to be executed in sequence:

(1) DELETE FROM TestTable;
(2) INSERT INTO TestTable SELECT * FROM TestTable2;

Using Explicit Transactions

Above statements could be packed into one explicit transaction:

BEGIN TRANSACTION;
DELETE FROM TestTable;
INSERT INTO TestTable SELECT * FROM TestTable2;
END TRANSACTION;

Another option would be to execute both statements as a multi-statement request, by starting the second statement with a semicolon:

DELETE FROM TestTable
;INSERT INTO TestTable SELECT * FROM TestTable2;

The multi-statement request will behave the same way as the statements packed into an explicit transaction.

Using Single Statements

Another possibility is to run the statements independently, as two implicit transactions:

DELETE FROM TestTable;
INSERT INTO TestTable SELECT * FROM TestTable2;

Whatever solution you chose, there will be no difference from a result point of view, if both statements execute without error.

But what happens if there is an error during executing the INSERT statement (i.e. the second statement)?

If we ran both statements in an explicit transaction (either explicitly defining it or using a multi-statement request),  the content of the table will be the same like it was before the transaction was executed. Neither the DELETE statement nor the INSERT statement has an impact on the target table “TestTable”.

If we ran both statements separately (two implicit transactions), the DELETE statement is committed, but as the INSERT statement failed, table “TestTable” will be empty. This is definitely a disadvantage. But there is another disadvantage if using single statements:

Any session accessing “TestTable” after the DELETE statement took place might see an empty table. If the INSERT statement takes a significant amount of time, this could lead to data inconsistency and wrong results.

All above considerations seem to suggest that we always should prefer to use explicit transactions – why should we ever execute single statements?

The reason is performance:

The DELETE statement will not require the transient journal if we run it as a single (implicit) transaction. Teradata only needs to maintain the Cylinder Index free list, and that’s it. The transient journal is not required as in no case a rollback will be done.

Which solution should be used depends on our ETL design and our performance requirements. Just keep the different behavior of both solutions in mind when designing your SQL queries.

Questions?
If you have any questions about all this, please ask in the comments! I’ll be paying close attention and answering as many as I can. Thank you for reading. Whatever this blog has become, I owe it all to you.
Our Reader Score
[Total: 3    Average: 4.7/5]
Teradata Transactions & Performance written by Roland Wenzlofsky on July 15, 2017 average rating 4.7/5 - 3 user ratings

1 COMMENT

  1. Same for the INSERT/SELECT, when it’s a single request transaction it will be a FastPath Insert/Select avoiding Transient Journa, too. Thus both DELETE & INS/SEL will be much faster.

LEAVE A REPLY

Please enter your comment!
Please enter your name here