Re: Improve SQR

[Frank Ellington <fellington@MEMC.COM>]

     Just a thought.....

     Get rid of the PS_PERSONNEL table, in that it is really a view and you
     are only pulling the UNION_CD from there.  Instead, coordinate the
     PS_EMPLOYMENT and PS_JOB, with ps_job being effdt'd and seq'd.  Pull
     the UNION_CD from ps_employment. I tested in SQL*PLUS and there was a
     slight decrease in processing time, no I didn't try any bench marking
     though.

     Also, change the record building to:
     let $Rcd = &A.DeptId             || $FldDelimiter
                     || &RYear        || $FldDelimiter
                     || &RMonth       || $FldDelimiter ...etc.

     That has to save some over-head..

     Whatever, please let me know what the final solution is,

     Thanks,
     Frank


______________________________ Reply Separator _________________________________
Subject: Improve SQR
Author:  SQR-USERS@USA.NET at MEMCPSMTP
Date:    12/22/97 8:37 AM


     I am attempting to run the following, there are over 200,000 rows in
     the ps_pay_earnings table.  Does anybody out there know how this could
     be improved?

     begin-procedure Get-Reg-Earns

     begin-select

     a.deptid
     rpad(p.union_cd,3,' ')       &punioncd
     to_char(a.pay_end_dt,'YYYY') &RYear
     to_char(a.pay_end_dt,'MM')   &RMonth
     sum(a.reg_hrs)               &RegHrs
     sum(a.reg_earn_hrs)          &RegErnHrs
     sum(a.reg_hrly_earns)        &RegHrlyErns
     sum(a.reg_earns)             &RegErns

       let  #TotRegHrs  = &RegHrs      + &RegErnHrs
       move #TotRegHrs to $TotRegHrs 99999.99

       let  #TotRegErns = &RegHrlyErns + &RegErns
       move #TotRegErns to $TotRegErns 9999999.99


     !  move &OTHrs      to $OTHrs         99999.99
     !  move &OTHrlyErns to $OTHrlyErns  9999999.99

       let $Rcd = &A.DeptId             || $FldDelimiter
       let $Rcd = $Rcd || &RYear        || $FldDelimiter
       let $Rcd = $Rcd || &RMonth       || $FldDelimiter
       let $Rcd = $Rcd || 'REG'         || $FldDelimiter
       let $Rcd = $Rcd || &pUnionCd     || $FldDelimiter
       let $Rcd = $Rcd || $TotRegHrs    || $FldDelimiter
       let $Rcd = $Rcd || $TotRegErns

       !show $Rcd
       write 1 from $Rcd:41
     FROM PS_PAY_EARNINGS A, PS_PERSONNEL p
     where   a.pay_end_dt between $FromDate and $ThruDate
     and     a.emplid = p.emplid
     and     a.empl_rcd# = p.empl_rcd#
     and  P.EFFDT =
             (SELECT MAX(EFFDT) FROM PS_PERSONNEL
             WHERE P.EMPLID = EMPLID
               AND P.EMPL_RCD# = EMPL_RCD#
               AND EFFDT <= a.Pay_End_dt)
         AND P.EFFSEQ =
             (SELECT Max(EFFSEQ) FROM PS_PERSONNEL
             WHERE P.EMPLID = EMPLID
               AND P.EMPL_RCD# = EMPL_RCD#
               AND P.EFFDT = EFFDT)
     group by a.deptid,
              p.union_cd,
              to_char(a.pay_end_dt,'YYYY'),
              to_char(a.pay_end_dt,'MM')
     end-select

     end-procedure Get-Reg-Earns


     Thanks in advance

     Bill Bowers
     St. Peter Hospital
     (360) 493-5437