Hi guys, I am using the following query, bt it takes long time to give results. Please let me know if i can improve its performance somehow.
SELECT SUM (WO_COSTS),
SUM (DIRECT_COSTS),
SUM (WO_CAPITALISED),
SUM (DIRECT_CAPITALISED),
SUM (REVENUE),
ACCOUNT_CODE
from (
SELECT /*+ PARALLEL(A4, 8) PARALLEL(A5, 8) */
SUBSTR (A4.ACCOUNT_CODE, 13, 3) ACCOUNT_CODE,
SUM ((CASE
WHEN ( SUBSTR (A4.ACCOUNT_CODE, 13, 3) >= '000'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '0%%'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '1%%'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '698'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '690'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '691'
AND A5.ACCOUNT_CODE IS NOT NULL
) THEN A4.TRAN_AMOUNT
ELSE 0
END)
) WO_COSTS,
SUM ((CASE
WHEN ( SUBSTR (A4.ACCOUNT_CODE, 13, 3) >= '000'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '0%%'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) NOT LIKE '1%%'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '698'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '690'
AND SUBSTR (A4.ACCOUNT_CODE, 13, 3) <> '691'
AND A5.ACCOUNT_CODE IS NULL
) THEN A4.TRAN_AMOUNT
ELSE 0
END)
)DIRECT_COSTS,
SUM ((CASE
WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='698' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='690' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) ='691' AND A5.ACCOUNT_CODE IS NOT NULL) THEN A4.TRAN_AMOUNT
ELSE 0
END)
) WO_CAPITALISED,
SUM ((CASE
WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '698' AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '690' AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
WHEN (SUBSTR (A4.ACCOUNT_CODE, 13, 3) = '691' AND A5.ACCOUNT_CODE IS NULL ) THEN A4.TRAN_AMOUNT
ELSE 0
END)
)DIRECT_CAPITALISED,
SUM ((CASE
WHEN SUBSTR (A4.ACCOUNT_CODE, 13, 3) LIKE '1%%' or
SUBSTR (A4.ACCOUNT_CODE, 13, 3) LIKE '0%%' THEN A4.TRAN_AMOUNT
ELSE 0
END)
)REVENUE
FROM hdwu.MSF90001 A4,
hdwu.MSFX9901 A5
WHERE
A4.DSTRCT_CODE = A5.DSTRCT_CODE(+)
AND A4.PROCESS_DATE = A5.PROCESS_DATE(+)
AND A4.TRANSACTION_NO = A5.TRANSACTION_NO(+)
AND A4.REC900_TYPE = A5.REC900_TYPE(+)
AND A4.USERNO = A5.USERNO(+)
AND A4.DSTRCT_CODE = 'RPDD'
AND A4.POSTED_STATUS NOT IN ('U','N')
AND A4.FULL_PERIOD <= #prompt('EndPeriodPrompt','string')#
AND SUBSTR (A4.ACCOUNT_CODE, 5, 1) = '3'
AND A5.DSTRCT_CODE(+) = 'RPDD'
AND A5.FULL_PERIOD(+) <= #prompt('EndPeriodPrompt','string')#
GROUP BY SUBSTR (A4.ACCOUNT_CODE, 13, 3)
)
GROUP BY ACCOUNT_CODE
What tool are you defining the query in? Why are you hard-coding a query like this? One of the main benefits of a reporting tool is that it codes queries for you based on what you define in the user interface...
Regards,
MF.
I can see lots of reasons why your query is slow. Looks like you're using Oracle, is that right ?
Here's what you need to do my friend:
Look at the Explain plan for starters.
1) SUBSTR() in a qualifier like this " SUBSTR (A4.ACCOUNT_CODE, 13, 3) <= '999'" is very slow. it forces a full table scan through a single node. Avoid using functions in your qualifiers to get better performance.
2) I can garuntee your Oracle hint at the beginning is being ignored. /*+ PARALLEL(A4, 8) PARALLEL(A5, 8) */ because of the reason above.
3) Your outer derived table is causing oracle to spool the pre-aggregated results before totalling. why ? Just do this in the inner SQL. It'll be faster.
4) Your left outer joins work, but inner joins would be better.
5) Since you're doing full table scans, indexes won't give you much, unless you can match indexes with the joined fields too. However, you could look closely at partitioning. If your table is partitioned, check the Explain to see if the optimizer is looking at only the partitions you want. If not, then qualify your partition in the query.
basically... you gotta re-write this mess so it doesn't do those bad things ! If I had access to the database, I could do it for you, but not without seeing the data, and the table/view definitions. Of course .. .that'd cost you ;-)
cheers,
Dave