DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Query Rewrite to improve performace

  1. #1
    Join Date
    Dec 2004
    Posts
    15

    Query Rewrite to improve performace

    Hi

    I would like to rewrite the following query to improve performance. Can anyone help?

    SELECT TPA_CLNT.CNNO,
    VU1.PRTID,
    VU1.LSTREPDT,
    SUM(T1158.LOREPAMT) TTLSTREPAMT
    FROM ( SELECT T1.PRTID,
    MAX(T2.TREFDT) LSTREPDT
    FROM TABLE2 T2, TABLE1 T1
    WHERE T1.TRANNO = T2.TRANNO
    AND T1.LOREPAMT <> 0
    AND T2.TRSTATCD = 'CM'
    AND T2.TRTYP = 'LR'
    AND T2.RTEFDT >= CURRENT DATE- 5 YEARS
    GROUP BY T1.PRTID ) VU1
    ,TABLE1 T1,TABLE3 T3,
    VLP_TXNHIST_TPA_CL TPA_CLNT,
    TABLE2 T2
    WHERE T1.PRTID = VU1.PRTID
    AND T3.PRTID = VU1.PRTID
    AND T3.LOANNO = T1.LOANNO
    AND T3.LOANSTAT = ' '
    AND T3.PROPNO = TPA_CLNT.PROPNO
    AND T3.LOSTATDT >= VU1.LSTREPDT
    AND T2.TRANNO = T1.TRANNO
    AND T2.TREFDT = VU1.LSTREPDT
    AND T2.TRSTATCD = 'CM'
    AND T2.TRTYP = 'LR'
    AND T2.RTEFDT >= CURRENT DATE- 5 YEARS
    GROUP BY TPA_CLNT.CNNO,
    VU1.PRTID,
    VU1.LSTREPDT ;

    Thanks in advance
    rammi

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    well whats wrong with it, have you traced it see where the time is being taken? got an explain plan? done anything?

Posting Permissions

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


Click Here to Expand Forum to Full Width