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

Boost slow (LEFT/RIGHT) OUTER JOINs

Sponsored by PRISE Ltd.
www.prisetools.com

How to optimize slow OUTER JOINs

Case description

We have a (LEFT or RIGHT) OUTER JOIN, and it runs a long time while causing skewed CPU / Spool usage. In practice most of the time during the query execution only 1 AMP will work, while others have nothing to do, causing poor parallel efficiency.

How to identify

The query typically runs long time, contains a "MERGE JOIN" step in the Explain description, and that  step consumes skewed CPU consumption (MaxAMPCPUTime * Number-of-AMPS >> AMPCPUTime) and lasts long.

In the DBQL you should find skewed, high CPU usage queries (
dbc.DBQLogtbl.MaxAMPCPUTime * (hashamp()+1)  / nullifzero(dbc.DBQLogtbl.AmpCPUTime) > 1.2 and dbc.DBQLogtbl.AMPCPUTime > 1000 , depends on system size) which also has "left outer join" expression in the execution plan text (dbc.DBQLExplaintbl.ExplainText like '%left outer joined using a merge join%')
This is only an approximation since the skewness causing step may be a different one.

PRISE Tuning Assistant supplies easy-to-use GUI based search function.

Explanation

Let's assume that we outer join Table1 and Table2 on a condition that causes no product join (merge join instead), eg.:

select Table1.x,Table2.y
from Table1
LEFT JOIN Table2 on Table1.x = Table2.x
...

If Table2 is not a "small" table, Teradata optimizer will choose to "equi-distribute" (place matchable records on the same AMP) the two tables on the join field(s), in our case: Table1.x and Table2.x respectively.
If Table1.x contains significant percentage of NULLs, then the distribution will be skewed, since all "x is NULL" records will get to the same AMP.
We know that the NULL value never results in a join match, so those records are useless to examine, but they have to appear in the resultset, since it is an OUTER JOIN.

Solution

Let's handle the Table1 into two separate subsets: NULL(x) and NotNULL(x), and modify the select this way:

select Table1.x,Table2.y
from Table1
LEFT INNER JOIN Table2 on Table1.x = Table2.x
where Table1.x is not null -- This will eliminate skewed spool
UNION ALL
select Table1.x,NULL
from Table1
where Table1.x IS NULL;


Practical example:
Some of our transactions are contributed by an operator, in this case OpID is filled, else null. We would like  to query the number of transactions by operators including the non-contributed ones. Most of the transactions are non contributed ones (OpID is null).

select
  a.Transaction_id, b.OperatorName as ContribOpName
from TransactionT a
LEFT JOIN OperatorT b on a.OpID = b.OpID


Optimized form:

select
  a.Transaction_id, b.OperatorName as ContribOpName
from TransactionT a
LEFT JOIN OperatorT b on a.OpID = b.OpID
where a.OpID is not null
UNION ALL
select
  a.Transaction_id, NULL as ContribOpName
from TransactionT
where OpID is null;


The execution will not cause a skewed CPU / Spool, because the those records of Table1 that caused peak ( x is NULL ) are excluded from processing of the join.
The second part will supply the "x is NULL" records to the result set without join processing.

The tradeoff is two full scans and a UNION ALL operation, which are comparably much less cost than a strongly skewed redistribution and a JOIN processing.

What's next

Next post will discuss unexpectedly slow INSERTs (hash collision).

Sponsored by PRISE Ltd.
www.prisetools.com

Accelerate PRODUCT JOIN by decomposition

Sponsored by PRISE Ltd.
www.prisetools.com

How to optimize slow product joins

Case description

There are SQL queries that cannot be executed any other way, but using product join method, because of the content of join condition. Eg:

• OR predicate
    Examle:
      ON (a.x = b.x OR a.y = b.y)
  
• BETWEEN / LIKE operator
    Examples:
  

      ON (a.x LIKE b.y)
      ON (a.x LIKE b.y || '%')
      ON (a.b between b.y and b.y)
  

• Comparison (=) of different datatype fields
    Example (a.x is INTEGER, b.x is VARCHAR)
      ON (a.x = b.x)
• Arithmetic expression usage
    Example
      ON (a.x = b.x + 1)
• SQL function (eg. substr() ) or UDF usage
    Example
      ON (substr(a.x,1,characters(b.x)) = b.x) 

Product join is a technique when the execution will match each record combinations from the two joinable tables and evaluates the join condition on each of them. Product join usually causes huge CPU consumption and long response time.

How to identify

The query typically runs long time, contains a "PRODUCT JOIN" step in the Explain description, and that  step consumes high AMPCPUTime and lasts long. Those queries usually have >>1 LHR index (Larry Higa Ratio, showing the CPU and I/O rate), typicall 10s, 100s or more.

In the DBQL you should find high CPU usage queries ( dbc.DBQLogtbl.AMPCPUTime > 1000 , depends on system size) which also has "product join" expression in the execution plan text (dbc.DBQLExplaintbl.ExplainText like '%product join%')

PRISE Tuning Assistant supplies easy-to-use GUI based search function.

Explanation of product join execution

Let's assume that we join tables: Table1 (N records, bigger table) and Table2 (M records, smaller table) Join processing assumes that the matchable record pairs must reside on the same AMP. Since product join compares each Table1 records to each Table2 records, one of the tables' all records must reside on all AMPs, therefore PRODUCT JOIN is preceded by a "Duplicated to all AMPs" step of the smaller table.
Each record pairs will be evaluated, if the JOIN condition satisfies, the result gets to the result spool, otherwise discarded.
The number of required comparisons: (N x M), and the cost (approx. the required CPU time) of one comparison depends on the complexity of the join expression.

Solution

In most cases the JOIN condition of the product join satisfies only small fraction of all possible combinations. In practice we can identify an often situation:
Significant subset of the bigger table's records will fit to a small subset of the smaller table's records.
Telco example: Number analysis. Most of the Call records are directed to national number areas (>90%), but the number area describing contains dominantly international number regions (>80..95%). We can declare that national calls will never fit to international areas. In addition it is very simple to identify both a "Number" and a "Number area" if it is national or international.
The base query looks like that:

select Table1.Number,Table2.Area_code
from Table1
join Table2 ON Table1.Number BETWEEN Table2.Area_Start_number and Table2.Area_end_number;


Let's decompose the query into two parts:

select Table1.Number,Table2.Area_code
from Table1
join Table2 ON Table1.Number BETWEEN Table2.Area_Start_number and Table2.Area_end_number
where substr(Table1.Number,1,1) = '1'   -- USA area code
and substr(Table2.Area_Start_number,1,1) = '1' -- USA area code
and substr(Table2.Area_end_number,1,1)   = '1' -- USA area code
UNION ALL
select Table1.Number,Table2.Area_code
from Table1
join Table2 ON Table1.Number BETWEEN Table2.Area_Start_number and Table2.Area_end_number
where
NOT (substr(Table1.Number,1,1) = '1')   -- non-USA area code
and NOT
(
    substr(Table2.Area_Start_number,1,1) = '1' -- non-USA area code
and substr(Table2.Area_end_number,1,1)   = '1' -- non-USA area code
);


This way we added some low cost operations (full scan on tables to identify national/international) , and the const of UNIONing the results, but we eliminated lots of trivially not satisfing comparisions.

The following figures show the processing cost, the red area represents the number of comparisons, therefore the cost:

Figure1: Original case

Figure2: Decomposed case

Let's do some maths, with imagined combinations:
N1: International calls
N2: National calls
M1: International area descriptions
M2: National area descriptions
90% of calls (N) are national (N2)
90% of area descriptions (M) are international (M1).
Originall we have to do N x M comparisons.
The decomposed query must do
((0.9 x N) x (0.1 x M)) + ((0.1 x N) x (0.9 x M)) = 0.09 x N x M + 0.09 x N x M = 0.18 x N x M

The optimized query will do only 18% of the original comparisons, with tradeoff
of two full scans (I/O intensive) of the base tables and one UNION ALL-ing (low cost)
of the results.

In this case we will get ~4 times faster and CPU saving execution.


Sometimes it can be worth to decompose to 3 or more process phases, depending on data.

It is important, if there are further joins or transformations on the result data, they should be done on the UNION ALL-ed result, and should not be dupliated on the decomposed phases, due to code management reasons.

Why can not do the Teradata PE the same?
The decomposition requires the knowledge of the data, and will vary from query to query, which is currently out of scope and intelligence of an automatic optimizer.

Summary

Eliminate trivially invalid record pairs from the PRODUCT JOIN by breaking the query in more parts. 

What's next

Next post will discuss slow OUTER (LEFT/RIGHT) JOIN.

Sponsored by PRISE Ltd.
www.prisetools.com