Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: query taking long to execute

    Hi

    Below query is taking so long ..how can we improve the performance.


    SELECT a.Month,
    a.WEEK,
    a.ATTID,
    a.Emp_Code,
    a.PMT_ID,
    b.PRJ_MGR_ATTUID,
    b.SPM_Last_Name
    || ' '
    || b.SPM_First_Name AS Project_Manager,
    a.PMT_Title,
    a.Reporting_Code,
    a.MOTS_ID,
    a.MOTS_ACRONYM,
    a.Work_Item,
    a.HOURS,
    a.WH_TASK_CODE,
    ' B:Invalid Task Code Usage' AS MSG1,

    -- '2012/01',
    a.PRIOR_PERIOD_CORRECTION,
    b.Path_Type,
    a.ITUP_TYPE,
    a.Fixed_Bid,
    'TEST SUPPORT PROJECT '
    || TEST_SUPPORT AS IS_TEST_SUPPORT
    FROM
    (SELECT DISTINCT a.Month,
    a.WEEK,
    a.CONTRACT,
    a.ACCOUNT,
    a.DIRECTOR,
    a.MANAGER,
    a.ATTID,
    a.Emp_Code,
    b.LAST_NAME,
    b.FIRST_NAME,
    a.PROJECT_TYPE,
    g.Path_Type,
    a.PMT_ID,
    a.PMT_Title,
    a.Reporting_Code,
    a.MOTS_ID,
    a.MOTS_ACRONYM,
    c.EQUIVALENT_TASK,
    a.Activity AS Work_Item,
    a.WH_TASK_CODE,
    CASE
    WHEN f.PMT IS NOT NULL
    THEN 'TEST SUPPORT PROJECT'
    ELSE NULL
    END TEST_SUPPORT,
    a.Fixed_Bid,
    SUM(a.Hours) AS HOURS,
    a.FIXED_BID_SOURCE,
    a.FIX_BID_DATE,
    a.PREVIOUS_FB_STATE,
    d.ITUP_TYPE,
    a.PRIOR_PERIOD_CORRECTION
    --
    FROM INVOICE_DATA a,
    TASK_CODES_BY_CATEGORY h,
    FORCE_ROSTER b,
    ATT_PROJECT_LIST g,
    TASK_CODES c,
    APPROVED_P3_PROJECTS e,
    MST_FB_STATUS f,
    MST_FB_STATUS d
    --added condition prior_period_correction
    WHERE a.prior_period_correction IS NULL
    AND a.ATTID = b.ATTID
    AND a.PROJECT_TYPE = h.CATEGORY(+)
    AND h.CATEGORY(+) IS NULL
    AND a.WH_TASK_CODE = h.TASK_CODE(+)
    AND a.PMT_ID = g.PMT_ID
    AND a.WH_TASK_CODE = c.TASK_CODE
    AND a.PMT_ID = e.PMT_ID(+)
    AND a.PMT_ID = f.PMT(+)
    AND Upper(a.MOTS_ACRONYM) = Upper(f.MOTS_ACR(+))
    AND Upper(a.CONTRACT) = Upper(f.CONTRACT_TYPE(+))
    AND f.APP_STATUS(+) = 'TEST SUPPORT'
    AND f.MOTS_ACR(+) IS NULL
    AND Upper(a.MOTS_ACRONYM) = Upper(d.MOTS_ACR(+))
    AND a.PMT_ID = d.PMT(+)
    AND Upper(a.CONTRACT) = Upper(d.CONTRACT_TYPE(+))
    GROUP BY a.Month,
    a.WEEK,
    a.CONTRACT,
    a.ACCOUNT,
    a.DIRECTOR,
    a.MANAGER,
    a.ATTID,
    a.Emp_Code,
    b.LAST_NAME,
    b.FIRST_NAME,
    a.PROJECT_TYPE,
    g.Path_Type,
    a.PMT_ID,
    a.PMT_Title,
    a.Reporting_Code,
    a.MOTS_ID,
    a.MOTS_ACRONYM,
    c.EQUIVALENT_TASK,
    a.Activity,
    a.WH_TASK_CODE,
    CASE
    WHEN f.PMT IS NOT NULL
    THEN 'TEST SUPPORT PROJECT'
    ELSE NULL
    END,
    a.Fixed_Bid,
    a.FIXED_BID_SOURCE,
    a.FIX_BID_DATE,
    a.PREVIOUS_FB_STATE,
    d.ITUP_TYPE,
    A.PRIOR_PERIOD_CORRECTION
    HAVING a.Month = '2013/05'
    -- HAVING a.Month = '2012/01'
    AND A.PROJECT_TYPE <> 'MAINTENANCE'
    AND A.PROJECT_TYPE <> 'ADMINISTRATIVE'
    AND UPPER(G.PATH_TYPE) NOT LIKE '%POST DEPLOY%' --modified to upper(g.path_type)
    AND upper(a.MOTS_ACRONYM) <> 'ACCOUNT OVERSIGHT - NONBILLABLE'
    AND SUM(NVL(a.HOURS, 0)) <> 0
    ) a,
    --
    ATT_PROJECT_LIST B
    WHERE a.PMT_ID = B.PMT_ID;


  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If I see it well, all but "AND SUM(NVL(a.HOURS, 0)) <> 0" can be moved out from HAVING clause and put into WHERE.

    Why do you have ATT_PROJECT_LIST twice in this query? Once in a subquery, and then again in the main query? Can't you achieve the same result using subquery's SELECT only?

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT a.month, 
           a.week, 
           a.attid, 
           a.emp_code, 
           a.pmt_id, 
           b.prj_mgr_attuid, 
           b.spm_last_name 
           || ' ' 
           || b.spm_first_name          AS Project_Manager, 
           a.pmt_title, 
           a.reporting_code, 
           a.mots_id, 
           a.mots_acronym, 
           a.work_item, 
           a.hours, 
           a.wh_task_code, 
           ' B:Invalid Task Code Usage' AS MSG1, 
           -- '2012/01', 
           a.prior_period_correction, 
           b.path_type, 
           a.itup_type, 
           a.fixed_bid, 
           'TEST SUPPORT PROJECT ' 
           || test_support              AS IS_TEST_SUPPORT 
    FROM   (SELECT DISTINCT a.month, 
                            a.week, 
                            a.contract, 
                            a.account, 
                            a.director, 
                            a.manager, 
                            a.attid, 
                            a.emp_code, 
                            b.last_name, 
                            b.first_name, 
                            a.project_type, 
                            g.path_type, 
                            a.pmt_id, 
                            a.pmt_title, 
                            a.reporting_code, 
                            a.mots_id, 
                            a.mots_acronym, 
                            c.equivalent_task, 
                            a.activity   AS Work_Item, 
                            a.wh_task_code, 
                            CASE 
                              WHEN f.pmt IS NOT NULL THEN 'TEST SUPPORT PROJECT' 
                              ELSE NULL 
                            END          TEST_SUPPORT, 
                            a.fixed_bid, 
                            SUM(a.hours) AS HOURS, 
                            a.fixed_bid_source, 
                            a.fix_bid_date, 
                            a.previous_fb_state, 
                            d.itup_type, 
                            a.prior_period_correction 
            -- 
            FROM   invoice_data a, 
                   task_codes_by_category h, 
                   force_roster b, 
                   att_project_list g, 
                   task_codes c, 
                   approved_p3_projects e, 
                   mst_fb_status f, 
                   mst_fb_status d 
            --added condition prior_period_correction 
            WHERE  a.prior_period_correction IS NULL 
                   AND a.attid = b.attid 
                   AND a.project_type = h.category(+) 
                   AND h.category(+) IS NULL 
                   AND a.wh_task_code = h.task_code(+) 
                   AND a.pmt_id = g.pmt_id 
                   AND a.wh_task_code = c.task_code 
                   AND a.pmt_id = e.pmt_id(+) 
                   AND a.pmt_id = f.pmt(+) 
                   AND Upper(a.mots_acronym) = Upper(f.mots_acr(+)) 
                   AND Upper(a.contract) = Upper(f.contract_type(+)) 
                   AND f.app_status(+) = 'TEST SUPPORT' 
                   AND f.mots_acr(+) IS NULL 
                   AND Upper(a.mots_acronym) = Upper(d.mots_acr(+)) 
                   AND a.pmt_id = d.pmt(+) 
                   AND Upper(a.contract) = Upper(d.contract_type(+)) 
            GROUP  BY a.month, 
                      a.week, 
                      a.contract, 
                      a.account, 
                      a.director, 
                      a.manager, 
                      a.attid, 
                      a.emp_code, 
                      b.last_name, 
                      b.first_name, 
                      a.project_type, 
                      g.path_type, 
                      a.pmt_id, 
                      a.pmt_title, 
                      a.reporting_code, 
                      a.mots_id, 
                      a.mots_acronym, 
                      c.equivalent_task, 
                      a.activity, 
                      a.wh_task_code, 
                      CASE 
                        WHEN f.pmt IS NOT NULL THEN 'TEST SUPPORT PROJECT' 
                        ELSE NULL 
                      END, 
                      a.fixed_bid, 
                      a.fixed_bid_source, 
                      a.fix_bid_date, 
                      a.previous_fb_state, 
                      d.itup_type, 
                      A.prior_period_correction 
            HAVING a.month = '2013/05' 
                   -- HAVING a.Month = '2012/01' 
                   AND A.project_type <> 'MAINTENANCE' 
                   AND A.project_type <> 'ADMINISTRATIVE' 
                   AND Upper(G.path_type) NOT LIKE '%POST DEPLOY%' 
                   --modified to upper(g.path_type) 
                   AND Upper(a.mots_acronym) <> 'ACCOUNT OVERSIGHT - NONBILLABLE' 
                   AND SUM(Nvl(a.hours, 0)) <> 0) a, 
           -- 
           att_project_list B 
    WHERE  a.pmt_id = B.pmt_id;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Quote Originally Posted by reenasharma347 View Post
    Hi

    Below query is taking so long ..how can we improve the performance.

    SELECT a.Month,
    a.WEEK,
    . . . E t c . . .

    Have you analyzed the explain (or execution) plan?
    What does it tell you?

    Perhaps you could learn something about performance if you Read The Fine Manual: Oracle® Database Performance Tuning Guide
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    given the large number of outer joins, I doubt this statement can be improved very much
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Besides, the optimizer is unable to use an index if the predicate is an inequality condition or if there is an expression or an implicit data type conversion on the indexed column.

    Like all these:
    Code:
    . . .
       AND A.PROJECT_TYPE <> 'MAINTENANCE'
       AND A.PROJECT_TYPE <> 'ADMINISTRATIVE'
       AND UPPER(G.PATH_TYPE) NOT LIKE '%POST DEPLOY%' --modified to upper(g.path_type)
       AND upper(a.MOTS_ACRONYM) <> 'ACCOUNT OVERSIGHT - NONBILLABLE'
    . . .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Should the a.month = '2013/05' be in the where clause instead of the having clause. The having clause is executed after the group by so its much better to filter in the where clause instead so you dont group records which your going to throw away later.

    Also why do you have the distinct clause in the group by subquery they do the same thing?

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •