Re: UNION of two SELECT extremely slow

gmilosavljevic wrote:
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!!!
<SNIP SQL Code>
Regards,
Goran M.

Without seeing the wait events, data size, plan, Oracle version, and
init.ora parameters everything is likely to be a guess. One of the
things that I see is that you use UNION rather than UNION ALL. My
guess is that the query is hitting the temporary tablespace for
sorting, distinct, and even join operations.

Another way to help the SQL statement is to remove the subquery, if at
all possible. This can typically be done by converting it into an
inline view. The inline view only needs to be resolved once, rather
than once per row. For example, the second half of your SQL statement
will look like this (note the NOT IN syntax was replaced by an outer
join and then the right side of the join is specified as being null):

UNION ALL
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,
(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') BNI
WHERE
VW.BUDGET_ITEM_CATEGORY_ID=BNI.ID(+)
AND BNI.ID IS NULL
AND 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
ORDER BY
DISPLAY_SEQUENCE,
ADDLINE;

Depending on the version of Oracle, the hidden init.ora parameters, and
the size of the data set, the above may execute many times faster than
the original SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

.

Relevant Pages

  • Re: Querying a dataset
    ... > Rep, Area, Period, Qty. ... > I want to display this data on a datagrid like: ... just glancing at your sql statement. ... > UNION ALL ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Need someone to look at SQL statement FIXED THE PROBLEM
    ... >> UNION ... If I copy just the bottom portion below the second UNION ... >> into the SQL statement it runs perfectly. ...
    (microsoft.public.access.queries)
  • Re: Import .csv question
    ... I found that the SQL statement that you ... > UNION ... > Sub ImportOneFile(FileSpec As String) ... > Dim strFolder As String ...
    (microsoft.public.access.externaldata)
  • Error Message - Help Needed
    ... I'm trying to use a sql statement in an asp page. ... I have 2 separate statements that work, but I'm trying to UNION them. ... FROM AS SumOfSDAEXP ... The tables that end in A are doing current sales, the table that ends in B is doing historical sales, both have EXACTLY the same column names. ...
    (microsoft.public.sqlserver.programming)