1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Tuning a expensive query

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Jan 21, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Following is consuming considerable time, how i can bring it to minimum time taking one. I shall v thank full.

    Code (SQL):
    SELECT   ROWNUM AS serial,
             name,
             employer_target,
             employers_Registered,
             employees_target,
             employees_registered,
             contribution_target,
             contribution,
             arrear_target,
             arrears,
             total_contr,
             PR02A_TARGET,
             pr02a_entered
      FROM   (  SELECT   r.name AS name,
                         t.TARGETED_EMPLOYER AS employer_target,
                         t.TARGETED_IP AS employees_target,
                         t.CURRENT_CONTRIBUTION AS contribution_target,
                         t.ARREAR_PRECEDING_YEAR AS arrear_target,
                         (SELECT   COUNT ( * )
                            FROM   core_business.cb_employer e
                           WHERE   TRUNC (e.registration_date) BETWEEN TO_DATE (
                                                                          '7/1/2009',
                                                                          'mm/dd/yyyy'
                                                                       )
                                                                   AND  TRUNC (
                                                                           SYSDATE
                                                                        )
                                   AND e.REGION_FO_CODE = r.REGION_FO_CODE)
                            AS employers_Registered,
                         (SELECT   COUNT ( * )
                            FROM   core_business.cb_insured_person e
                           WHERE   TRUNC (e.created_date) BETWEEN TO_DATE (
                                                                     '7/1/2009',
                                                                     'mm/dd/yyyy'
                                                                  )
                                                              AND  TRUNC (SYSDATE)
                                   AND e.ip_REGION_FO_CODE = r.REGION_FO_CODE)
                            AS employees_registered,
                         (SELECT   SUM(NVL (e.employer_contribution, 0)
                                       + NVL (e.ip_contribution, 0)
                                       + (CASE
                                             WHEN TRUNC (e.EMP_ARREAR_TO) >=
                                                     TO_DATE ('7/1/2009',
                                                              'mm/dd/yyyy')
                                                  OR e.emp_arrear_to IS NULL
                                             THEN
                                                NVL (e.emp_arrear_amount, 0)
                                                + NVL (e.late_fees, 0)
                                             ELSE
                                                0
                                          END)
                                       + (CASE
                                             WHEN TRUNC (e.IP_ARREAR_TO) >=
                                                     TO_DATE ('7/1/2009',
                                                              'mm/dd/yyyy')
                                                  OR e.ip_arrear_to IS NULL
                                             THEN
                                                NVL (e.ip_arrear_amount, 0)
                                             ELSE
                                                0
                                          END))
                            FROM   core_business.cb_contr_emp_pmt_slip e,
                                   core_business.cb_employer c
                           WHERE   TRUNC (e.receipt_date) BETWEEN TO_DATE (
                                                                     '7/1/2009',
                                                                     'mm/dd/yyyy'
                                                                  )
                                                              AND  TRUNC (SYSDATE)
                                   AND c.REGION_fo_CODE = r.REGION_FO_CODE
                                   AND c.emp_area_code = e.emp_area_code(+)
                                   AND c.emp_reg_serial_no = e.emp_reg_serial_no(+)
                                   AND c.EMP_SUB_AREA_CODE = e.EMP_SUB_AREA_CODE(+)
                                   AND c.EMP_SUB_SERIAL_NO = e.EMP_SUB_SERIAL_NO(+))
                            AS contribution,
                         (SELECT   SUM( (CASE
                                            WHEN TRUNC (e.EMP_ARREAR_TO) <
                                                    TO_DATE ('7/1/2009',
                                                             'mm/dd/yyyy')
                                            THEN
                                               NVL (e.emp_arrear_amount, 0)
                                               + NVL (e.late_fees, 0)
                                            ELSE
                                               0
                                         END)
                                       + (CASE
                                             WHEN TRUNC (e.IP_ARREAR_TO) <
                                                     TO_DATE ('7/1/2009',
                                                              'mm/dd/yyyy')
                                             THEN
                                                NVL (e.ip_arrear_amount, 0)
                                             ELSE
                                                0
                                          END))
                            FROM   core_business.cb_contr_emp_pmt_slip e,
                                   core_business.cb_employer c
                           WHERE   TRUNC (e.receipt_date) BETWEEN TO_DATE (
                                                                     '7/1/2009',
                                                                     'mm/dd/yyyy'
                                                                  )
                                                              AND  TRUNC (SYSDATE)
                                   AND c.REGION_fo_CODE = r.REGION_FO_CODE
                                   AND c.emp_area_code = e.emp_area_code(+)
                                   AND c.emp_reg_serial_no = e.emp_reg_serial_no(+)
                                   AND c.EMP_SUB_AREA_CODE = e.EMP_SUB_AREA_CODE(+)
                                   AND c.EMP_SUB_SERIAL_NO = e.EMP_SUB_SERIAL_NO(+))
                            AS arrears,
                         (SELECT   SUM(  NVL (e.employer_contribution, 0)
                                       + NVL (e.ip_contribution, 0)
                                       + NVL (e.emp_arrear_amount, 0)
                                       + NVL (e.ip_arrear_amount, 0)
                                       + NVL (e.late_fees, 0))
                            FROM   core_business.cb_contr_emp_pmt_slip e,
                                   core_business.cb_employer c
                           WHERE   TRUNC (e.receipt_date) BETWEEN TO_DATE (
                                                                     '7/1/2009',
                                                                     'mm/dd/yyyy'
                                                                  )
                                                              AND  TRUNC (SYSDATE)
                                   AND c.REGION_fo_CODE = r.REGION_FO_CODE
                                   AND c.emp_area_code = e.emp_area_code(+)
                                   AND c.emp_reg_serial_no = e.emp_reg_serial_no(+)
                                   AND c.EMP_SUB_AREA_CODE = e.EMP_SUB_AREA_CODE(+)
                                   AND c.EMP_SUB_SERIAL_NO = e.EMP_SUB_SERIAL_NO(+))
                            AS total_contr,
                         (SELECT   SUM (ips_regist)
                            FROM   nadeem_core.cb_employers_ips i
                           WHERE   i.region_fo_code = r.region_FO_code
                                   AND i.reason_status = 'A')
                            AS PR02A_TARGET,
                         (SELECT   COUNT ( * )
                            FROM   core_business.cb_contr_card e,
                                   core_business.cb_employer i
                           WHERE   TRUNC (e.created_date) BETWEEN TO_DATE (
                                                                     '7/1/2009',
                                                                     'MM/DD/YYYY'
                                                                  )
                                                              AND  TRUNC (SYSDATE)
                                   AND i.emp_area_code = e.emp_area_code
                                   AND i.emp_reg_serial_no = e.emp_reg_serial_no
                                   AND i.EMP_SUB_AREA_CODE = e.EMP_SUB_AREA_CODE
                                   AND i.EMP_SUB_SERIAL_NO = e.EMP_SUB_SERIAL_NO
                                   AND i.region_fo_code = r.REGION_FO_CODE)
                            AS pr02a_entered
                  FROM   general_information.cb_region_fo r,
                         CORE_BUSINESS.CB_REGION_TARGET t
                 WHERE       r.region_fo_id = 'R'
                         AND R.STATUS = 'A'
                         AND r.region_fo_code <> 9999
                         AND r.region_fo_code = t.region_fo_code
              ORDER BY   r.region_fo_code)
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Providing the Sql query is not enough to improve the performance of query. Lot of things have to be taken in to consideration like proper indexing, number of records , proper joins etc.

    As a first step remove the Outer select query and see the performance. You can add rownum to the inner query itself using analytic function as ,

    Code (SQL):

    ROW_NUMBER() OVER (ORDER BY  r.region_fo_code)
     
     
  3. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    thks.

    make this "ROW_NUMBER() OVER (ORDER BY r.REGION_FO_CODE)" other necessaries part of query and pl. send
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The suggestion is for you to implement, not demand that others do your work for you.
     
  5. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    i place your code instead of this one ORDER BY r.region_fo_code, but it give error that is way i ask for this, i still need to know where should i place you suggested code, and what to remove .
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Then post your modified code so we can see what you've done and where any mistakes were made.
     
  7. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    /* Formatted on 2010/01/23 15:54 (Formatter Plus v4.8.8) */

    SELECT ROWNUM AS serial, NAME, employer_target, employers_registered,

    employees_target, employees_registered, contribution_target,

    contribution, arrear_target, arrears, total_contr, pr02a_target,

    pr02a_entered

    FROM (

    SELECT r.NAME AS NAME, t.targeted_employer AS employer_target,

    t.targeted_ip AS employees_target,

    t.current_contribution AS contribution_target,

    t.arrear_preceding_year AS arrear_target,

    (SELECT COUNT (*)

    FROM core_business.cb_employer e

    WHERE TRUNC (e.registration_date)

    BETWEEN TO_DATE

    ('7/1/2009',

    'mm/dd/yyyy'

    )

    AND TRUNC (SYSDATE)

    AND e.region_fo_code = r.region_fo_code)

    AS employers_registered,

    (SELECT COUNT (*)

    FROM core_business.cb_insured_person e

    WHERE TRUNC (e.created_date) BETWEEN TO_DATE ('7/1/2009',

    'mm/dd/yyyy'

    )

    AND TRUNC (SYSDATE)

    AND e.ip_region_fo_code = r.region_fo_code)

    AS employees_registered,

    (SELECT SUM

    ( NVL (e.employer_contribution, 0)

    + NVL (e.ip_contribution, 0)

    + (CASE

    WHEN TRUNC (e.emp_arrear_to) >=

    TO_DATE ('7/1/2009', 'mm/dd/yyyy')

    OR e.emp_arrear_to IS NULL

    THEN NVL (e.emp_arrear_amount, 0)

    + NVL (e.late_fees, 0)

    ELSE 0

    END

    )

    + (CASE

    WHEN TRUNC (e.ip_arrear_to) >=

    TO_DATE ('7/1/2009', 'mm/dd/yyyy')

    OR e.ip_arrear_to IS NULL

    THEN NVL (e.ip_arrear_amount, 0)

    ELSE 0

    END

    )

    )

    FROM core_business.cb_contr_emp_pmt_slip e,

    core_business.cb_employer c

    WHERE TRUNC (e.receipt_date) BETWEEN TO_DATE ('7/1/2009',

    'mm/dd/yyyy'

    )

    AND TRUNC (SYSDATE)

    AND c.region_fo_code = r.region_fo_code

    AND c.emp_area_code = e.emp_area_code(+)

    AND c.emp_reg_serial_no = e.emp_reg_serial_no(+)

    AND c.emp_sub_area_code = e.emp_sub_area_code(+)

    AND c.emp_sub_serial_no = e.emp_sub_serial_no(+))

    AS contribution,

    (SELECT SUM

    ( (CASE

    WHEN TRUNC (e.emp_arrear_to) <

    TO_DATE ('7/1/2009', 'mm/dd/yyyy')

    THEN NVL (e.emp_arrear_amount, 0)

    + NVL (e.late_fees, 0)

    ELSE 0

    END

    )

    + (CASE

    WHEN TRUNC (e.ip_arrear_to) <

    TO_DATE ('7/1/2009', 'mm/dd/yyyy')

    THEN NVL (e.ip_arrear_amount, 0)

    ELSE 0

    END

    )

    )

    FROM core_business.cb_contr_emp_pmt_slip e,

    core_business.cb_employer c

    WHERE TRUNC (e.receipt_date) BETWEEN TO_DATE ('7/1/2009',

    'mm/dd/yyyy'

    )

    AND TRUNC (SYSDATE)

    AND c.region_fo_code = r.region_fo_code

    AND c.emp_area_code = e.emp_area_code(+)

    AND c.emp_reg_serial_no = e.emp_reg_serial_no(+)

    AND c.emp_sub_area_code = e.emp_sub_area_code(+)

    AND c.emp_sub_serial_no = e.emp_sub_serial_no(+))

    AS arrears,

    (SELECT SUM ( NVL (e.employer_contribution, 0)

    + NVL (e.ip_contribution, 0)

    + NVL (e.emp_arrear_amount, 0)

    + NVL (e.ip_arrear_amount, 0)

    + NVL (e.late_fees, 0)

    )

    FROM core_business.cb_contr_emp_pmt_slip e,

    core_business.cb_employer c

    WHERE TRUNC (e.receipt_date) BETWEEN TO_DATE ('7/1/2009',

    'mm/dd/yyyy'

    )

    AND TRUNC (SYSDATE)

    AND c.region_fo_code = r.region_fo_code

    AND c.emp_area_code = e.emp_area_code(+)

    AND c.emp_reg_serial_no = e.emp_reg_serial_no(+)

    AND c.emp_sub_area_code = e.emp_sub_area_code(+)

    AND c.emp_sub_serial_no = e.emp_sub_serial_no(+))

    AS total_contr,

    (SELECT SUM (ips_regist)

    FROM nadeem_core.cb_employers_ips i

    WHERE i.region_fo_code = r.region_fo_code

    AND i.reason_status = 'A') AS pr02a_target,

    (SELECT COUNT (*)

    FROM core_business.cb_contr_card e,

    core_business.cb_employer i

    WHERE TRUNC (e.created_date) BETWEEN TO_DATE ('7/1/2009',

    'MM/DD/YYYY'

    )

    AND TRUNC (SYSDATE)

    AND i.emp_area_code = e.emp_area_code

    AND i.emp_reg_serial_no = e.emp_reg_serial_no

    AND i.emp_sub_area_code = e.emp_sub_area_code

    AND i.emp_sub_serial_no = e.emp_sub_serial_no

    AND i.region_fo_code = r.region_fo_code)

    AS pr02a_entered

    FROM general_information.cb_region_fo r,

    core_business.cb_region_target t

    WHERE r.region_fo_id = 'R'

    AND r.status = 'A'

    AND r.region_fo_code <> 9999

    AND r.region_fo_code = t.region_fo_code and ROW_NUMBER() OVER (ORDER BY r.REGION_FO_CODE) ;


    --ORDER BY r.region_fo_code)
     
  8. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You were not asked to comment the Order By clause.

    But were asked to

    1. Remove the Outer select query. ie no need for inline views. Inline View

    2. Replace " ROWNUM AS serial" in the query with " ROW_NUMBER() OVER (ORDER BY r.REGION_FO_CODE) " for serial number in the select clause.
    Row_Number
     
  9. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I Replace " ROWNUM AS serial" in the query with " ROW_NUMBER() OVER (ORDER BY r.REGION_FO_CODE) "

    Following Error Come, "r.REGION_FO_CODE" is invalid identifier
     
  10. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    Now been able to follow your point i remove outer select and put code row_number().... below, it is working. but it has consumed same time
     
  11. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Hmhm.. Then you might have tried without removing Outer select query. Copy and paste the query.

    I repeat . You have to remove the outer select Query first . Then do the replacement.
     
  12. SteveS

    SteveS Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Stroudsburg, PA USA
    How many rows are returned by this:


    FROM general_information.cb_region_fo r,
    CORE_BUSINESS.CB_REGION_TARGET t
    WHERE r.region_fo_id = 'R'
    AND R.STATUS = 'A'
    AND r.region_fo_code <> 9999
    AND r.region_fo_code = t.region_fo_code

    Each of the subqueries you have under the select part of the inner query will have to be evaluated one-at-a-time for each row selected in the 'from' clause shown above.

    You probably need to rewrite and get those results by using joins.

    When you use a 'column level' select statement, it can cause serious performance problems.
     
  13. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    It return 37 rows, whole query is grouped on region_fo_code, there are 37 such groups.
     
  14. SteveS

    SteveS Active Member

    Messages:
    7
    Likes Received:
    1
    Trophy Points:
    65
    Location:
    Stroudsburg, PA USA
    What does your execution plan look like?

    explain plan for
    <your query>
    /

    set pages 300
    set lines 300
    set trimspool on
    select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,null));