Re: SQR PERFORMANCE ISSUES

[ANDERSD2 <ANDERSD2@OAKWOOD.ORG>]

Thinking you should do the following??

        move 0 to #Employee_Sessions
begin-select
RT.Emplid
RT.Empl_Rcd#
to_char(RT.Date_Under_Rpt, 'MM/DD/YYYY')        &RT.Date_Under_Rpt
count(RT.Session_Number)        &Employee_Sessions
        if &Employee_Sessions > 1
                move &Employee_Sessions to #Employee_Sessions
                move &RT.Date_Under_Rpt to $Date_Under_Rpt
                report the data to LIS (or wherever)
        end-if

from    PS_TL_Rapid_Time RT
where (RT.Date_Under_Rpt >= $From_Date
        and RT.Date_Under_Rpt <= $Thru_Date)
group by RT.Emplid, RT.Empl_Rcd#, RT.Date_Under_Rpt
end-select

-----Original Message-----
From: Deepak Vij [mailto:DVij@CI.FREMONT.CA.US]
Sent: Thursday, March 08, 2001 1:32 PM
To: SQR-USERS@list.iex.net
Subject: SQR PERFORMANCE ISSUES


I am working on this RAPID TIME EXCEPTION REPORT
and am using a  PS_TL_RAPID_TIME table thrice in a SQL

SELECT
RT.SESSION_NUMBER  Primary Key
RT.EMPLID    Primary Key
RT.EMPL_RCD#  Primary Key
TO_CHAR(RT.DATE_UNDER_RPT,'MM/DD/YYYY')   &RT.DATE_UNDER_RPT  Primary Key
RT.TIME_RPTG_CD   Primary Key
RT.HRS_UNITS
FROM PS_TL_RAPID_TIME RT

WHERE  RT.DATE_UNDER_RPT BETWEEN $from_date AND $thru_date

AND 1 < (SELECT COUNT(DISTINCT B.SESSION_NUMBER)
             FROM PS_TL_RAPID_TIME B

WHERE B.EMPLID         = RT.EMPLID

AND B.EMPL_RCD#      = RT.EMPL_RCD#

AND B.DATE_UNDER_RPT = RT.DATE_UNDER_RPT

AND RT.DATE_UNDER_RPT BETWEEN $from_date AND $thru_date)

   OR 1 < (SELECT COUNT(*)
!WHERE 1 < (SELECT COUNT(*)

FROM PS_TL_RAPID_TIME A2
WHERE A2.SESSION_NUMBER = RT.SESSION_NUMBER

 AND A2.EMPLID         = RT.EMPLID

AND A2.EMPL_RCD#      = RT.EMPL_RCD#

 AND A2.DATE_UNDER_RPT = RT.DATE_UNDER_RPT

AND RT.DATE_UNDER_RPT BETWEEN $from_date AND $thru_date

 AND A2.TIME_RPTG_CD   = RT.TIME_RPTG_CD)

ORDER BY RT.EMPLID, RT.DATE_UNDER_RPT, RT.TIME_RPTG_CD, RT.SESSION_NUMBER
end-select.

It is taking 2-3 Hrs to process and has only 140000 Rows

* Feild in BOLD are the Primary Keys

ANY SUGGESTIONS ?

DEEPAK