Archives of the TeradataForum

Message Posted: Wed, 19 Mar 2003 @ 17:04:35 GMT

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

Walter,

Actually I tried the same query with one subquery. Then it is amazingly fast. LU_QUARTER table has 20 rows and QUARTER_ID is its Primary Index. FA_Customer has 100 Million rows. Platform is V2R4.1

SELECT  SubGroupID
>           QuestionID
>           QuarterID
>           QuestionID,
>           AnswerID,
>           sum(WeightNo)
>   from  DatabaseName.FA_Customer
>  Where Quarter_ID >= (Select (max(quarterid)-3) from tabs.lu_quarter)
> >  and QuestionID = 440
>    and  SubGroupID = 2

If I put a range, the performance will be the worst. The range query is given below.

SELECT  SubGroupID
>           QuestionID
>           QuarterID
>           QuestionID,
>           AnswerID,
>           sum(WeightNo)
>   from  DatabaseName.FA_Customer
>  Where Quarter_ID >= (Select (max(quarterid)-3) from tabs.lu_quarter)
     and Quarter_ID < (Select max(quarterid) from tabs.lu_quarter)
> >  and QuestionID = 440
>    and  SubGroupID = 2

Actually the query is created by Micro-Strategy and therefore, we don't have much option to re-write it in the way Fred suggested.

Thanks

Ganga Palakattil

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