Archives of the TeradataForum

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

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

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:

SELECT  SubGroupID
          QuestionID
          QuarterID
          QuestionID,
          AnswerID,
          sum(WeightNo)
  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>>