Archives of the TeradataForum

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

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

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