Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 18 Aug 2015 @ 21:35:06 GMT


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


Subj:   Re: Case logic optimization
 
From:   Tom Stanek

The only optimization I can suggest is to order your CASE logic in order of the frequency in which the condition will occur. In your example, the CASE statement would look something like the following;

     CASE WHEN POP = 'A' THEN 1
             WHEN POP = 'D' THEN 5
             WHEN POP = 'C' THEN 3
             WHEN POP = 'B' THEN 2
         END AS Flag,

This re-ordering will dramatically reduce the number of comparisons being done to resolve the CASE statement (well over 1 Billion comparisons). I don't anticipate this reducing your response time dramatically (perhaps 15% or so), but it will reduce the resource consumption of the query and, thus, overall response time.

Just out of curiosity, what is the response time if you run the same query without the CASE statement? That should be your baseline to determine the impact of the CASE statement.

The other thing to consider is the degree of parallelism for the resulting table. You might want to run a HASH function against the source table to determine how evenly distributed your resulting table will be using the PI of HCI and date.


Tom Stanek



     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019