UNION of two SELECT extremely slow

Hi All,

I'm fighting with the weird problem. I have a public ASP application and suddenly one user reported the problem - very slow rendering of a page. I've localized the problem and it is about UNION operation. There are two SELECT statements, each of them work very very normaly (1-2secs) when being ran separately, but when I try to do UNION of them, the whole query executes more than 3minutes (!!!). It's really weird.
Anyone has any thoughts about it?

Thanks a lot in advance!!!

Here is the query:

select vw.ID as bweid,b.PE_SUBMITTED, vw.BUDGET_ID, vw.CATEGORY_ID, vw.CATEGORY_NAME as CAPTION, vw.USER_DEFINED_CATEGORY as ADDLINE, 0 as hasedit, vw.SUPERCATEGORY_ID as PARENTID, vw.SUPER_CATEGORY_NAME as PARENTCAPTION, vw.PARENT_UDC, vw.CAPTION_UNIT_COST, vw.CAPTION_MULTIPLIER_1, vw.CAPTION_MULTIPLIER_2,  vw.EXPENSE_ACCOUNT_CODE as EXPCODE, vw.UNIT_COST, vw.MULTIPLIER_1, vw.MULTIPLIER_2, vw.COMMENTS, vw.APPLICABLE, vw.DISPLAY_SEQUENCE

            from vw_BWS_entry vw, BUDGET b, budget_item bi
            where vw.budget_id=b.id
            and b.meeting_id='889445'
            and applicable=1
            and vw.BUDGET_ITEM_CATEGORY_ID=bi.BUDGET_ITEM_CATEGORY_ID
            and bi.BUDGET_ID=b.ID
            and vw.BUDGET_ITEM_CATEGORY_ID in


            (

            select distinct bic.id
            from budget_item_category bic,budget_item bi,
invoice_line_item i, budget b
            where bic.ID= bi.BUDGET_ITEM_CATEGORY_ID and
                    bi.ID=i.BUDGET_ITEM_ID and
                    b.ID=bi.BUDGET_ID
                    and b.meeting_id='889445'         )

UNION select vw.ID as bweid, b.PE_SUBMITTED, vw.BUDGET_ID, vw.CATEGORY_ID, vw.CATEGORY_NAME as CAPTION, vw.USER_DEFINED_CATEGORY as ADDLINE, 1 as hasedit, vw.SUPERCATEGORY_ID as PARENTID, vw.SUPER_CATEGORY_NAME as PARENTCAPTION, vw.PARENT_UDC, vw.CAPTION_UNIT_COST,

vw.CAPTION_MULTIPLIER_1, vw.CAPTION_MULTIPLIER_2,
vw.EXPENSE_ACCOUNT_CODE as EXPCODE, vw.UNIT_COST, vw.MULTIPLIER_1,
vw.MULTIPLIER_2, vw.COMMENTS, vw.APPLICABLE, vw.DISPLAY_SEQUENCE
            from vw_BWS_entry vw, BUDGET b, budget_item bi
            where vw.budget_id=b.id
            and b.meeting_id='889445'
            and applicable=1
            and vw.BUDGET_ITEM_CATEGORY_ID=bi.BUDGET_ITEM_CATEGORY_ID
            and bi.BUDGET_ID=b.ID
            and vw.BUDGET_ITEM_CATEGORY_ID not in


            (

            select distinct bic.id
            from budget_item_category bic,budget_item bi,
invoice_line_item i, budget b
            where bic.ID= bi.BUDGET_ITEM_CATEGORY_ID and
                    bi.ID=i.BUDGET_ITEM_ID and
                    b.ID=bi.BUDGET_ID
                    and b.meeting_id='889445'
            )
            order by  DISPLAY_SEQUENCE,ADDLINE 

Regards,
Goran M. Received on Mon Jul 17 2006 - 04:35:58 CDT