Archives of the TeradataForum

Message Posted: Tue, 08 Jun 2004 @ 14:33:05 GMT


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

Subj:		Re: Performance problem while creating a volatile table

From:		Dieter Noeth

Naveen Ram Prasanna Kambhoji wrote:

It worked absolutely fine ........but still i need to tune this query & reduce it to 30 minutes which is my target window time.... I have no idea abt the business logic in the query...Is there any other ways where we can achieve performance..Is it possible to split the query still resulting in same results. When I did explain plan I found that the ad.PublishDate BETWEEN StartDate and StopDate is taking more time. It is doing a FTS which is resulting in product join followed by SORT. Also specially the self join with TMP_UNIVERSE is more costly......

It's hard to tell just be reading a comlex source code...

Any join using Between or inequality will result in a product join. As long as the number of rows in one of those table is relatively small, this should be no problem.

Did you check if statistics exist on those columns used in Join and Where conditions?

Which release are you running? Before V2R5.0.3 (or 5.0.2?) some optimizations wouldn't be applied to ANSI Join Syntax, so rearranging the Join order might help a bit in older releases.

Another way before V2R5.0 would be using Derived Tables to force the optimizer to use a diferent join order.

But the main problem is probably that you as a newbie to Teradata are supposed to optimize that complex query. Even afters years this might be an advanced task...

Dieter


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

Attachments

Library

Quick Reference