INSERT performance on empty and non-empty table

Database

The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

INSERT performance on empty and non-empty table

Hi,

I am trying to INSERT data from volatile table into a MULTISET table having NUPI. Data of ~1.5 million rows gets copied in 1 sec if table is empty, whereas it takes 4 secs to copy same set of rows when table is not empty.

Can someone please explain me why we see a difference in behaviour here?

Re: INSERT performance on empty and non-empty table

There is a performance optimization that recognizes an empty table. When the table is empty no logging needs to be done for recovery after an aborted operation because the recovery can be accomplished by simply deleting all rows. When there is any data in the table, then logging of the inserts must be done in order to allow rollback to the original state of the table.

Re: INSERT performance on empty and non-empty table

There is a performance optimization that recognizes an empty table. When the table is empty no logging needs to be done for recovery after an aborted operation because the recovery can be accomplished by simply deleting all rows. When there is any data in the table, then logging of the inserts must be done in order to allow rollback to the original state of the table.

Re: INSERT performance on empty and non-empty table

I should have been more specific with my questions. Are these DML operations single row operations or table wide operations?

If the INSERT, MERGE, DELETE operations are not single row operations (eg WHERE PI=<const>) or with 15.10 or greater Teradata if the operation is not local to a partition, then they will require an all AMP table level write lock for the operation. Teradata locking strategies do not allow concurrent write locks to be granted at the same time. If one of these transactions has a write lock on a table and another begins and asks for a write lock on the same table, it will wait until the first transaction completes. Doing many full table operations from many sessions concurrently will not improve completion time and may actually be slower than sending them sequentially through a single session.

Re: INSERT performance on empty and non-empty table

Yes. The INSERT, MERGE - DEL, INSERT are all having joins which are on UPI. And all the concurrent sessions which will run in parallel will not have any objects in common i.e. each session will load a different table. What I want to understand is, will the transient journal created due to multiple BT/ET sessions slow down my queries or the teradata system?