Hi, Liew,
Just a question - have you been able to pinpoint exactly where the performance is breaking down?
One tip - on your UNION statements...if you know with absolute certainty that the results with each UNION are unique, you can use UNION ALL instead. UNION will check for duplicates, whereas UNION ALL won't.
If you can post some specific query examples where you're having bad performance, maybe we can go from there.
Kevin