Archives of the TeradataForum

Message Posted: Thu, 31 Jan 2003 @ 00:25:39 GMT

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

Hello,

In tuning performance of a query generated by MicroStrategy Desktop, I noticed something that puzzled me regarding the Teradata optimizer: two versions of a query, which appear be functionally equivalent, are having radically different response times. The query is as simple as it gets - an inner join like:

SELECT ...
FROM table1
INNER JOIN table2
ON table1.rid=table2.rid

Due to the size of the larger table (70 million rows). In one of many tests, I took the SQL above (generated by MicroStrategy) and re- wrote it manually as:

SELECT *
FROM table1
WHERE rid
IN (
SELECT rid
FROM table2
)

The query generated by the tool runs in about 1.5 hours, whereas the modified version with the derived table runs in 7 minutes, returning identical results. Regardless of issues like skewing, statistics or other similar considerations:

1. aren't the two queries above equivalent?

2. is there a reason why the optimizer seems to work so differently on each query?

Many thanks in advance.

R.A.

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