QUICKTIPS 2 - DELETE or CREATE+INSERT?

Sponsored by PRISE Ltd.
www.prisetools.com

Delete or Insert? Which one is faster?

The question 

If I got to delete rows from a table, practically I have two options:

• Issue a DELETE command with the desired WHERE / JOIN condition
• Create an empty table with the same structure and INSERT the remaining records using the negated WHERE condition and the rename tables

The first one is simple and straightforward, the second is complicated and will require more perm space. So it is a question?

Yes, it is. Because of the backgrounds of the execution:

• DELETE
  It will modify each blocks that contain record(s) to be deleted. It has to write Transient Journal (transaction log) to enable transaction related functionality. In addition those data blocks which fill-up-rate falls below a threshold (eg. 50%) they will be merged. All these may cause really lot of I/O overhead. In some cases the deletion of the rows will happen in "random-like" order therefore the I/O access will be random, which is painfully slow.
• INSERT
  In this case it is quite simple: we insert into an empty table, which causes almost zero Transient Journal data, and the system has to write only that amount of data blocks, what can store the records that will remain, once each block. An additional advantage: the I/O will always be "linear" instead of "random-like" therefore it can be multi-block operation, which is pretty fast.

Shall we forget about the delete?

The answer

No, absolutely not. DELETE is good enough if the amount of the records that must be deleted is not that much.
We have two factors that will help us in the decision:

• How many records to be deleted?
• How many records will remain after deletion?

I do not have well defined universal thresholds where it is worth to choose this or that, it depends.
I used to use the CREATE+INSERT+RENAME method if

• The table is big enough (> > 0,1-0,5Gbytes or 10-50k rows / AMP)
  and
  I got to delete more than 20..40% of the records

OR  

• The table has high hash collision (HC) rate on the PI
  and
  The table is MULTISET*
  and
  The delete is a JOIN-ed one

So it depends, got to measure. PRISE Tuning Assistant can help you to build up your own rule of thumb, applicable on your environment, by showing the resource consumption in pretty details.


* If the HC rate is high and the table is SET then the INSERT will be very slow because of the duplication check method

Sponsored by PRISE Ltd.
www.prisetools.com

QUICKTIPS - 1. DELETE+INSERT - together or separetely?

Sponsored by PRISE Ltd.
www.prisetools.com

Shall I run DELETE and INSERT separately?

We often have permanent or temporary tables in our data warehouse which must be purged before they are populated again. We can do the operation within a transaction or separately. It is not the same...

Options - (example)

The examples assume Teradata (BTET)  mode, which is Auto-commit by default

1. Together: both statements run in the same transaction
   
   
   1. Explicit transaction
      
      begin transaction;
      
      delete from TABLE1;
      
      insert into TABLE1 SELECT .....;
      
      end transaction;
      
      
      
   2. Multi-statement
      
      delete from TABLE1
      ;insert into TABLE1 SELECT .....;
      


2. Separately: the statements run in separate transaction
   
   delete from TABLE1;
   
   insert into TABLE1 SELECT .....;
   
   
   

(Please note if you run it in Teradata Administrator aka. WinDDI, this will be run in one explicit transaction, since the application will place a BT; before the script and an ET; after.)

What is the difference?

The result

If everything is OK, we will not experience any difference at the end. The same data will be found in TABLE1 after we finished each ways. The difference shows if the INSERT step fails eg. for running out of perm space:

• In A. cases the all transaction will be rolled back, therefore we will see all the data in the table we had before the delete.
• In B. case we will delete the data from the tab, independently the success of the INSERT phase. After a failed INSERT the table will be empty

We have another additional transient difference:

• In A. case we will not "see" an empty table from another session in any point of time: until a point the table seems as a "before" version, and when the transaction finished it will switch to an "after" image for other sessions in no time.
• In B. case when the DELETE finished, the table seems to be empty up to the moment of COMMITing the INSERT statement. So if the INSERT lasts 2 hours, others will face and empty TABLE1 for 2 hours.

So I do not recommend to use option B. if the "empty table" state is not valid.

The performance

The A. and B. options will be executed significantly different manner. The DELETE operation will know that all the data will be dropped, so internally it just administers the data blocks to be free instead of clearing their content record-by-record, BUT only if the statement is the only statement in the transaction! In this case only the data block administration must be logged (to be able to roll back for some reason) instead of every block changes. This results far less Transient Journal activity besides less block I/Os.

Let's see some measurements.
I've put 10M records into a table, and then executed the same DELETE + INSERT statement on it. The results are the following:

1. CPU:    9  sec I/O: 39K
2. CPU  >0.1sec I/O:   4K

It is worth to mind the difference if you got to do it with billions of records.

Sponsored by PRISE Ltd.
www.prisetools.com

Monitor ongoing SQLs - progress indication

Sponsored by PRISE Ltd.
www.prisetools.com

How can I monitor the progress of my SQL?

We often have SQL operations that last hours or more, and we do want to know when will they expectedly finish. There is no progress bar or something similar tool in Teradata, but we have some useful information parcels to be used to make our estimations.

Let's see how.

Execution steps

SQL statements are broken down to execution steps by the Parsing Engine (PE), which steps are executed sometimes sequentially, sometimes parallel to each other.

This level of monitoring can easily be done by either Teradata Viewpoint or DB_MON tools. It will show you which step(s) was/is/will be executed, and some information about them:

• What is the estimated runtime (for all steps)
• How long did the finished ones run (only for finished ones)
• For how long the currently executed is/are running (only for the running ones)

First we can compare the estimations and the fact runtimes for the finished steps. It can give a guess rate we can use for the further estimations. Naturally it is far from exact calculation, but is just a guideline. The difference comes from estimation bias and server load/priority conditions.

So at the end we can use the estimations' sum multiplied by the finished steps' fact/estimation rate as a very rough guess. 

Within a step

Typically a long running SQL statement contains a few number of long running steps besides more lightweight ones. So the runtime is dominated by some few heavy steps, and in this case the interesting question is: what is the progress rate of the actually running step?

The answer is not that very simple, one must know what happens behind - more or less.

Before examining  exact cases, I provide some basic information and tricks.

Tricks to know

Typically used SQL scripts by myself

Current spool used by a user (1)

We can query the data dictionary for the actually allocated spool space by a user. This is very important that this is related to a user, not a session (sum of all logged in sessions of that specific user). 

The current spool covers all objects currently stored in spool: 

• used by the currently executed query step
• all the other spools generated by previous steps and still not dropped
• all existing volatile tables either. 

So be careful with it.

The current spool information is usually interesting from two aspects:

• Total spool usage of a user, now (a):
  SELECT cast(sum(currentspool) /1024/1024/1024 as decimal(15,3)) Current_spool,
Current_spool / (hashamp()+1) Average_spool, cast(max(currentspool) /1024/1024/1024 as decimal(15,3)) MaxAMP_spool,
cast(100.0 - ((Average_spool / nullifzero(MaxAMP_spool)) * 100.0) as decimal(4,1)) Spool_skew
FROM dbc.diskspace WHERE databasename = '<user>';

• Spool usage by AMP of a user, now (b):
  SELECT vproc, cast(currentspool /1024/1024 as decimal(15,3)) Current_AMP_spool_MB
FROM dbc.diskspace WHERE databasename = '<user>'
ORDER BY 2 DESC;


Current size of a table (2)

• Total size of a table (a):
  SELECT databasename,tablename,cast(sum(currentperm) /1024/1024/1024 as decimal(15,1)) Current_perm_GB
FROM dbc.allspace
WHERE databasename = '<db_name>' and tablename = '<table_name>'
GROUP BY 1,2;
• Table size by AMP (b)
  SELECT databasename,tablename,vproc,cast(currentperm /1024/1024/1024 as decimal(15,3)) Current_perm_MB
FROM dbc.allspace
WHERE databasename = '<db_name>' and tablename = '<table_name>'
ORDER BY 4 DESC;

Current allocated and free space in a database (3)

• Total allocated/free space in a database, currently (a):
  SELECT databasename,
cast(sum(currentperm) /1024/1024/1024 as decimal(15,1)) Used_perm_GB,
cast(sum(maxperm-currentperm)/1024/1024/1024 as decimal(15,1)) Free_perm_GB
FROM dbc.diskspace
WHERE databasename = '<db_name>'
GROUP BY 1;
• Sizes in a database by AMP (b)
  Run before the operation:
  CREATE VOLATILE TABLE CPERM as
(SELECT vproc,currentperm, maxperm-currentperm freeperm FROM dbc.diskspace WHERE databasename = '<db_name>') with data PRIMARY INDEX( vproc) on commit preserve rows;
  Run during the operation any time of interest:
  SELECT databasename,
cast(sum(a.currentperm-b.currentperm) /1024/1024/1024 as decimal(15,1)) Used_perm_GB,
cast(sum(a.maxperm-a.currentperm-b.freeperm)/1024/1024/1024 as decimal(15,1)) Free_perm_GB
FROM dbc.diskspace a join CPERM b on a.vproc=b.vproc
WHERE databasename = '<db_name>' GROUP BY 1

  

Transient journal size and free space in DBC (4)

When running an SQL statement which modifies table data, it will generate more or less transaction log (called TransientJournal in Teradata). Its size sometimes helps to determine the progress of an operation. Please note that this information is very-very informative, since we do not know how much journal will be written, and in addition all the transactions use the same journal table, and the purge of the released journal area is an asynchronous task. It is important, that the journal must fit into DBC, so the following query shows the DBC's free space along with the current journal size. Please note: this query examines the conditions on total level instead of AMP level, so if you run out of DBC space on one AMP the system will fail, in spite you see lots of total free spaces.

• How's going with the transient journal:
  SELECT tj.Current_perm_GB TransientJournal_current_GB, dbspc.Free_perm_GB DBC_free_perm_GB FROM
(SELECT databasename,tablename,cast(sum(currentperm) /1024/1024/1024 as decimal(15,1)) Current_perm_GB
FROM dbc.allspace WHERE databasename = 'DBC' and tablename = 'TransientJournal' GROUP BY 1,2 ) tj cross join
(SELECT cast(sum(maxperm-currentperm)/1024/1024/1024 as decimal(15,1)) Free_perm_GB FROM dbc.diskspace WHERE databasename = 'DBC') dbspc;


Within a step, continued...

The progress examination method will differ depending on the type of the operation - unfortunately this is how it goes. The reasons are hidden behind the internal processes of Teradata... 

INSERT INTO xxx SELECT ... FROM ...

This type of operation typically consists of the following steps:

1. Retrieve data from the source into a SpoolX
   
   This step can be examined by checking the executing user's spool space (query 1.a/1.b). If the data is skewed, it is worth to check the spool on AMP level (1.b), mainly after the growth of the total will get slower. This is because most of the AMPs are finished already, but a few of them must process the "peak" 
2. Merge SpoolX into "xxx" table
   
   I monitor this step in two sections: first check the target table's size (query 2.a/2.b), which will increase as the merge process advances. The target table's size will increase by the size of the source spool, (except in some special cases like SET table duplication elimination) so if the target table was not empty before the insert, just watch the difference between initial and current size. It goes linearly along the time up to a specific point, when the first AMP finishes its work.When an AMP has been finished the merge, it will release the corresponding source spool area ( which was produced by step 1. ). So when the growth of the target slows down, switch to check the executing user's spool space by AMP (query 1.b), and watch how many AMPs of the all will have zero or minimal spool occupied (the non-finished ones show the full spool size and do not decrement until they finish). This phase is quite short if the data is not skewed AND the system is well balanced (co-existing node generations...) AND another sessions do not cause skewed load to the system, otherwise the first and last finishing AMP may differ significantly. In addition, the last AMP can run extremely long when the hash collision problem escalates. Unfortunately the progress within a single AMP is not visible* (see note below).
   When the target table was non empty when the INSERT started, we usually experience significant increase in the size of the Transient Journal (query 4). This is not a good progress indicator, since we do not have a guideline how big is the target journal size, and other transactions use the same journal. In addition the release of the unused journal area is an asynchronous process, which is may be delayed from the SQL operations finish. The purpose of monitoring this object - together with the DBC free space - is to ensure that we do not eat up all the free DBC space. Of course it is a rare situation, but is is a very unpleasant situation when half-a-day run is rolled back because we run out of DBC space...
3. Commit transaction
   This step usually lasts a very short time, except when the table is very skewed. Usually I do not monitor this step.

CREATE TABLE AS ...

The basic execution structure (from our aspect) will not differ in major things, except one. The "preparation step(s)" eg. retrieve into a spool can be measured exactly the same way as we did at the "INSERT... SELECT..." case, but the MERGE is different: at the time of execution we do not have the target object in the data dictionary, which size we could measure. However we are not absolutely lost: the free space in the target database (query 3.a/3.b) is kept maintained during the MERGE, so if we measure it, and compare to the amount when the process started, we get a good progress indicator of the MERGE step. Take care, it only works if nobody modifies the Maxperm (size) of the target database neither other process puts or removes data into/from it during our operation.

1. Save the initial condition into a volatile table (it is very small spool)
2. Query the current increment globally

When the size increasing slows down we can go back to check the spool size at AMP level, and watch how many AMPs has already been finished.

* Note: The progress of a single AMP is not measurable using the provided methods, but we can have some indirect deductions at the MERGE step. If we know the expected size or record number ratio of the smallest and biggest AMP, and we can measure the finish time of the smallest AMP (when the spool size on an AMPs begin to fall to zero or very low value (the MERGE is finished on that AMP), we can extrapolate the runtime on the biggest AMP (this will be the total time). But only if hash collision is not significant: MULTISET TABLE, or low "high-mode-frequency" of the PI columns, otherwise the runtime is dominantly determined by the hash collision level (where Nx is the top mode-frequency values of the PI; O(sum(Nx*Nx)) ), not the number of records

Sponsored by PRISE Ltd.
www.prisetools.com

Curing slow INSERTs and CREATE TABLEs I.

Sponsored by PRISE Ltd.
www.prisetools.com

Eliminating hash collisions

Case description

We have an INSERT or CREATE TABLE operation that runs unreasonably long time compared to the affected number of rows, in spite the table is not skewed.

What is hash collision?

Hash collision is when two or more records in a table have the same hash value.

SET type of tables ensure that there are no more records with exactly the same record content within a table. How does Teradata do it?

Teradata stores the records in a hash filesystem, where each record has a hash value calculated from the Primary Index (PI) value. If the PI values are the same in more records, they will surely have the same hash value either.

When INSERTING a record, Teradata has to compare the new record to the table's only those records that have the same hash value that new record has, since all records with different hash value will surely differ at least at the PI columns.
If we have to INSERT N records with the same hash value into an empty table, Teradata has to do N*(N-1)/2 times - very CPU demanding - full record comparisons.

How to identify

Hash collisions can be easily found by using PRISE Tuning Assistant tool also, or follow this method:

DBQL filtering for qualifying queries:
The Merge (MRG) phase of the INSERT/CREATE TABLE operation consumes lot of CPU.
Look for high CPU consuming 'MRG' steps in the dbc.DBQLStepTbl:

sel a.cputime,a.MaxAmpCPUTime * (hashamp() +1) CoveringCPUTIme,  a.stepname,a.RowCount,b.* from
     dbc.DBQLStepTbl a
join dbc.DBQLogTbl   b on a.ProcId=b.ProcId and a.QueryId=b.QueryId
where
    a.StepName in ('MRG' /*, 'MRU' for UPDATEs also*/)
and a.CPUTime > 100 /* Performance boost: eliminates most of the records (small cpu seconds) at low processing cost. Adapt number to your site */
qualify sum(1) over (order by a.cputime desc rows unbounded preceding) <= 100;
 

At a specific SQL statement (INSERT or CREATE TABLE) you have to check your PI for level of hash collisions (number of records where the hash values are the same) in the target table.

How to make sure that the hash-collision is the reason? Let the target table be TableA, with primary index: ColA,ColB,ColC (can be any number of columns in practice)

select top 100 hashrow(ColA,ColB,ColC), count(*) from TableA group by 1 order by 2 desc;


The top row(s) will show the most frequent hash values. Count values >>1 mean significant hash collisions in the order of N * N. Each high frequency hash value will generate a hash-collision group causing comparisons in the order of N*N.

If the table still not exists, embed the producing "SELECT" statement into the script above, and count those field values that would get to the PI columns.

Explanation

If we use "SET" type of table (this is the default setting), Teradata ensures that there will be no perfectly alike records in the table. This can be ensured by comparing the inserted/updated record with the existing ones.
Teradata's "hash filesystem" gives a very effective trick: only those records must be compared, whose RowID (hash) equals, otherwise at least the PI fields must differ.
If we've chosen the Primary Index for UNIQUE, or non-UNIQUE, but on field(s) that are almost unique, then the "SET comparison"
restricts to zero or one records in most cases.

Solution

For good solution unfortunately we have to modify the table structure.

• Option 1: Change table type to MULTISET. This will eliminate duplication checks, but its disadvantage is the same. If the process falls back on the de-duplication of SET table, you have to replace it with programmed de-duplication (group by, left join...).
• Option2: Change the table's PI to a unique or nearly unique column set. Be prudent, consider the workload also. (joins, where conditions, group by expressions, etc.)

Summary

Tables with strongly non unique PI are highly dangered for hash collision slowed INSERTs/CREATE TABLEs (or UPDATEs), even if they are not skewed. Use "more unique" PI, or MULTISET table.

Typical mistake: if a CREATE TABLE ... as SELECT... lacks the PRIMARY INDEX() section. In this case Teradata chooses the first column as PI, which often causes terrible performance.

What's next

Next post will discuss Multi Value Compress (MVC) optimization.

Sponsored by PRISE Ltd.
www.prisetools.com