hi,
can anyone tell me the alternative for union all.for ex i have 36 tables named cpn1 to cpn 9 and cpna to cpnz & i have to get all cpn table data in 1 query so i used union all but it takes around 30 min to display result.
i want better option who give me output in less time then union all.i tried with manual insert in temp table but still it takes time.
It may not be the UNION ALL that is the culprit. Try running the 36 queries without the UNION ALL (thus returning 36 result sets) and see if the performance is any better. My guess is that it will still take the same time.
If that is the case, then your task is to figure out why the individual pieces are taking long time and to narrow it down to the ones that are the worst performers.
If you are using UNION instead of UNION ALL, what I said above is not necessarily true.