Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 18 Mar 2003 @ 05:54:33 GMT

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

Subj:   Re: SQL Subquery issue
From:   Fred W Pluebell

What's going on? Check the EXPLAINs. Each Subquery = separate Join; Constants = Residual Conditions applied during retrieve or join step.

Until the Teradata optimizer is enhanced to handle "scalar subqueries" more efficiently (V2R5.x?), you can improve performance significantly by using a derived table instead. It still won't be quite as good as supplying constant values, since you have to execute the subqueries first, and the join to the derived table will be a separate step that can't be combined with the retrieval for some other join.

Assuming the min and max values for the range need to come from separate tables/views, try something like this:

  from  DatabaseName.FA_Customer,
 inner join
 (Select A.MaxID, B.MinID from
  (select max(quarterid) from DatabaseName.lu_quarter) A(MaxID)
   cross join
  (select (max(quarterid)-3) from tabs.lu_quarter_vw) B(MinID)
 ) X(MaxQtr, MinQtr)
 on  QuarterID <= X.MaxQtr
   and  QuarterID >= X.MinQtr
 where QuestionID = 440
   and  SubGroupID = 2

  <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: 28 Jun 2020