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.
Enthusiast

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?

 

Thanks

Abdulaziz

  • performance
  • Teradata

Accepted Solutions
Teradata Employee

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.

1 ACCEPTED SOLUTION
6 REPLIES
Teradata Employee

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.

Enthusiast

Re: INSERT performance on empty and non-empty table

Thanks Todd.

 

Now that we are talking about logging i have one more question :)

 

Can you please let me know if there will be any sort of impact on query/system performance if I run parallel BT/ET transactions with multiple DML statements in each of them? 

Teradata Employee

Re: INSERT performance on empty and non-empty table

Does "parallel" in this contecxt mean multiple sessions each running concurrent BT/ET transactions.

 

What kind of DML operations?

Enthusiast

Re: INSERT performance on empty and non-empty table

Yes. There are multiple sessions each running concurrent BT/ET transactions. SQL would look like

 

BT;

INSERT ;

MERGE - DELETE;

INSERT;

ET;

 

I will be having multiple sql files with the sql statements mentioned above and all of these files will be executed simultaneously/concurrently.

Teradata Employee

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.

Enthusiast

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?