1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

SQL Performance issue?

Discussion in 'Software Development' started by myworldntl, Jan 19, 2007.

Thread Status:
Not open for further replies.
  1. myworldntl

    myworldntl Thread Starter

    Joined:
    Jan 19, 2007
    Messages:
    1
    The SQL below was given to me and i have altered and it still works and quicker, but still is too slow...

    The SQL below that i have altered to run faster but i think at least one line is incorrect and and also, the explain plan says that it costs more...

    Unfortunately the customer at work i am doing this for is running Oracle 8....

    Original SQL

    SELECT SYSDATE AS DATE_ROW_CREATED,
    con.con_pos_ref_no AS POSITION_REFERENCE,
    job.party_nm AS JOB_TITLE,
    con.party_lnk_d AS START_DATE,
    con.party_lnk_ed AS END_DATE,
    spt.scale_pt_refcd AS SCALE_POINT,
    phs.party_hrs AS HOURS,
    mpy.con_pos_ref_no AS LINE_MANAGER,
    pay.payroll_nm AS PAYROLL_NAME,
    NVL(GREATEST(con.last_mod_d, pip.last_mod_d, man.last_mod_d, phs.last_mod_d, pgd.last_mod_d),SYSDATE) AS LAST_UPDATED

    FROM

    trentadm.tparty_lnk con,
    trentadm.tparty_in_pay pip,
    trentadm.tpayroll pay,
    trentadm.tparty job,
    trentadm.tpty_det_own pdo,
    trentadm.tparty_hrs phs,
    trentadm.tparty_grade pgd,
    trentadm.tscale_pt spt,
    trentadm.tparty_lnk man,
    trentadm.tparty_lnk mpy

    WHERE 1=1
    AND job.party_id = con.party_id2
    AND con.party_id2 = man.party_id(+)
    AND con.party_id2 = pdo.party_id(+)
    AND man.party_id2 = mpy.party_id2(+)
    AND pip.party_id = con.party_id
    AND pay.payroll_id = pip.payroll_id
    AND pdo.owner_id = phs.party_id(+)
    AND pgd.scale_pt_act = spt.scale_pt_id(+)




    AND pip.party_in_pay_d < SYSDATE
    AND (pip.party_in_pay_ed >= SYSDATE OR pip.party_in_pay_ed IS NULL)




    AND pdo.detail_group(+) = 'CONT_HRS'
    AND pdo.detail_type(+) = 'CONT_HRS'
    AND pdo.pty_det_own_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(pdo.pty_det_own_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


    AND phs.hrs_type_id(+) = 'CONT_HRS'
    AND phs.party_hrs_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(phs.party_hrs_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


    AND pgd.party_grade_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(pgd.party_grade_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


    AND man.link_type_id(+) = 'POSN_POSN'
    AND man.party_lnk_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(man.party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


    AND mpy.link_type_id(+) = 'CONT'
    AND mpy.party_lnk_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(mpy.party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')

    AND 'Y' = DECODE(pgd.party_id(+), con.party_id2, 'Y', 'N')
    --LINE ABOVE IS THE 1 I THINK I GOT WRONG IN THE NEW SQL

    AND con.PARTY_LNK_D <= SYSDATE
    AND (con.PARTY_LNK_ED >= SYSDATE OR con.PARTY_LNK_ED IS NULL)

    AND job.party_d < SYSDATE
    AND (job.party_ed >= SYSDATE OR job.party_ed IS NULL)

    AND EXISTS
    (
    SELECT position_id FROM ZTSDPOSITIONID
    WHERE
    position_id = job.party_id
    )


    NEW SQL

    SELECT SYSDATE AS DATE_ROW_CREATED,
    con.con_pos_ref_no AS POSITION_REFERENCE,
    job1.party_nm AS JOB_TITLE,
    con.party_lnk_d AS START_DATE,
    con.party_lnk_ed AS END_DATE,
    spt.scale_pt_refcd AS SCALE_POINT,
    phs.party_hrs AS HOURS,
    mpy.con_pos_ref_no AS LINE_MANAGER,
    pay.payroll_nm AS PAYROLL_NAME,
    NVL(GREATEST(con.last_mod_d, pip.last_mod_d, man.last_mod_d, phs.last_mod_d, pgd.last_mod_d),SYSDATE) AS LAST_UPDATED

    FROM trentadm.tparty job1

    INNER JOIN
    (
    SELECT party_id, party_id2, con_pos_ref_no, party_lnk_d, party_lnk_ed, last_mod_d FROM trentadm.tparty_lnk
    WHERE PARTY_LNK_D <= SYSDATE
    AND (PARTY_LNK_ED >= SYSDATE OR PARTY_LNK_ED IS NULL)
    ) con ON job1.party_id = con.party_id2

    INNER JOIN
    (
    SELECT party_id, payroll_id, last_mod_d FROM trentadm.tparty_in_pay
    WHERE party_in_pay_d < SYSDATE
    AND (party_in_pay_ed >= SYSDATE OR party_in_pay_ed IS NULL)
    ) pip ON pip.party_id = con.party_id

    LEFT OUTER JOIN
    (
    SELECT party_id, party_id2, last_mod_d FROM trentadm.tparty_lnk
    WHERE party_lnk_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
    AND link_type_id(+) = 'POSN_POSN'
    ) man ON con.party_id2 = man.party_id --(+)

    LEFT OUTER JOIN
    (
    SELECT party_id, last_mod_d, scale_pt_act FROM trentadm.tparty_grade
    WHERE party_grade_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(party_grade_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
    ) pgd ON 'Y' = DECODE(pgd.party_id, con.party_id2, 'Y', 'N')
    --THIS 'ON' is DERIVED FROM THE LINE I ANNOTATED IN THE ORIGINAL SQL

    LEFT OUTER JOIN
    (
    SELECT party_id, owner_id FROM trentadm.tpty_det_own
    WHERE pty_det_own_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(pty_det_own_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
    AND detail_group(+) = 'CONT_HRS'
    AND detail_type(+) = 'CONT_HRS'
    ) pdo ON con.party_id2 = pdo.party_id --(+)


    LEFT OUTER JOIN
    (
    SELECT party_id, party_hrs, last_mod_d FROM trentadm.tparty_hrs
    WHERE party_hrs_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(party_hrs_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
    AND hrs_type_id(+) = 'CONT_HRS'
    ) phs ON pdo.owner_id = phs.party_id --(+)

    INNER JOIN trentadm.tpayroll pay
    ON pay.payroll_id = pip.payroll_id

    LEFT OUTER JOIN
    (
    SELECT party_id2, con_pos_ref_no FROM trentadm.tparty_lnk
    WHERE party_lnk_d(+) < SYSDATE
    AND 'Y' = DECODE(LEAST(party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
    AND link_type_id(+) = 'CONT'
    ) mpy ON man.party_id2 = mpy.party_id2 --(+)


    LEFT OUTER JOIN trentadm.tscale_pt spt ON pgd.scale_pt_act = spt.scale_pt_id --(+)

    WHERE 1=1

    AND (job1.party_ed >= SYSDATE OR job1.party_ed IS NULL)
    AND job1.party_d < SYSDATE
    AND EXISTS
    (
    SELECT position_id FROM ZTSDPOSITIONID
    WHERE
    position_id = job1.party_id
    )
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Performance issue
  1. henryleee1234
    Replies:
    1
    Views:
    56
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/536679

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice