problem with derived table


problem with derived table

i had a situation where i ahd to select 3 columns from 3 different combination of table i.e

tables: A,B,C each table has a million records

sel temp.a1,temp1.a1,temp2.a1
(sel a1 from A inner join B on conditions) temp,
(sel a1 from A left join C on other conditions) temp1,
(sel a1 from C inner join B on some different conditions) temp2

when i gave F6 the no. of hours returned for executing was 614231 hours and some minutes....

Can anybody let me know when actually to use a derived table?
how do i improve the performance in the above case ....


Re: problem with derived table

it's difficult to say anything without seeing the actual query or the data demographics.

you are doing a product join of all the three derived tables.

Now the effect of that could vary from "ok" to "disaster" depending on the cardinality of the derived tables.

say if you have 2, 3, 2 as the cardinality of the derived tables, then your result set would have 12 records and it won't take much time to process.

but if you had 200000, 3000000, 2000000 ......umm... i needn't tell you about the disaster ?

Re: problem with derived table

Yes. It is pretty difficult to give inputs on this without knowing the actual Query structure.
(for instance you havent mentioned the joining conditions and from which table a1 is coming in each of the derived tables)

But if you are retrieving one column from each of the three tables, i dont think you need to go
for three derived tables as given.

In your Temp, get the all the columns from A and B that you would need to output and to use for joining with C.

Now join temp with C to get the remaining columns.

This is a general suggestion which would reduce the number of records and spool space required.

Exact solution can be given only if the query structure is known in detail.

Annal T

Re: problem with derived table

Thanks a lot guys....