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 from (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 ....
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.