urgent-query performance

[Sep2013]

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

[MFGF]

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.

[TheCognosDave]

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