Re: SQR PERFORMANCE ISSUES

[Paul Hoyte <phoyte@SHARIDIONNE.COM>]

Deepak:


I made some changes to the sqr that you attached.  try them and see if they
work.  Also, you might want to make sure that the database fields
RT.SESSION_NUMBER and RT.EMPLID are indexed as that will speed up the
search.


Paul Hoyte
Sharidionne, Inc.
(248) 559-6868
www.sharidionne.com
phoyte@sharidionne.com

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Deepak Vij
Sent: Friday, March 16, 2001 8:54 PM
To: SQR-USERS@list.iex.net
Subject: Re: SQR PERFORMANCE ISSUES


I am trying to make them separate selects but dont know how to make them
separate so as to not change the logic

Any help ?  SQR is attached

Thx

Deepak

>>> Rod Wright <rwright@THEROUSECOMPANY.COM> 03/09/01 06:07AM >>>
The Counts are definitely killing you since they have to be performed for
every
record returned by the balance of the Where clause. I find that many times
we
try to accomplish too much in the Where clause. Try making these separate
Select
statements to you Do from within your main Select and then respond
accordingly.

HTH,
Rod





Deepak Vij <DVij@CI.FREMONT.CA.US> on 03/08/2001 01:32:07 PM

Please respond to sqr-users@list.iex.net

To:   SQR-USERS@list.iex.net
cc:    (bcc: Rodney Wright/Trcb/Rouse)

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

COFTL900.sqr