Archives of the TeradataForum

Message Posted: Mon, 22 Dec 2003 @ 14:44:44 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Bill:

Regarding the multiple commands being a single transaction. I think you have heard something but not understood it entirely. The following SQL statements would be handled as a single transaction when submitted via bteq:

Delete from TableX
; Update TableY ....
; Select * from;

The bteq logic is that anytime the semi-colon is followed by another SQL command ON THE SAME LINE, the statements are optimized and executed as a single transaction. However, in Queryman/SQL Assistant, these are handled and executed as three separate transactions when running in Teradata mode.

As a single transaction, the locks and all Transient Journal (TJ) entries from the delete update are held until the select finishes. There, the multi-statement SQL execution is more resource intensive than if run individually. Furthermore, this multi-statement execution is going to lock other users from accessing all effected rows. This may be what you are witnessing as impacting your system performance.

As a single transaction, deleting all rows from a table is very, very fast because rows are not put into the TJ. Deleting all rows, as in the above transaction, becomes much slower. Every row must be copied into the TJ to maintain transaction integrity.

Hope this helps,

Michael Larkins
Certified Teradata Master

	<Prev	Next>		<<First	<Prev	Next>	Last>>