Author Topic: urgent-query performance  (Read 7492 times)

Offline Sep2013

  • Community Leader
  • *****
  • Join Date: Nov 2012
  • Posts: 75
  • Forum Citizenship: +0/-1
urgent-query performance
« on: 24 Jan 2013 05:48:25 pm »
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

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,362
  • Forum Citizenship: +624/-10
  • Cognos Software Muppet
Re: urgent-query performance
« Reply #1 on: 25 Jan 2013 08:15:46 am »
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.
Meep!

Offline TheCognosDave

  • Full Member
  • ***
  • Join Date: Sep 2013
  • Posts: 33
  • Forum Citizenship: +2/-0
Re: urgent-query performance
« Reply #2 on: 18 Oct 2013 12:13:17 pm »
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

 


       
Twittear