[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: [sqr-users] Program running more than 12 hrs needs performance tuning
The main problem in your select is the use of a SELECT statement as a
target of "NOT IN". This results in a full table scan on the subquery
for every row on the main query.
IN (SELECT... and NOT IN (SELECT... should pretty much never be used.
Instead, use EXISTS (SELECT... and NOT EXISTS (SELECT...
If the key structure is used, this will perform an index scan rather
than a full table scan.
On Fri, 8 Aug 2003 11:36:43 +0530, you wrote:
>Hi Everybody
>
>I am executing a SQR pgm in Batch (Mainframes - OS390/DB2), it involves a
>complex query.
>
>The total number of row fetched by that query is around 12 lak and hence the
>program executes for around 12 hrs, to reduce the runtime i have reduced the
>total number of row to be processed (i.e. around 5 laks), still complicating
>the query, but still even for 5 lake rows fetched the pgm executes dor 12 hrs.
>
>The complex query when executed in Batch SPUFI, takes only 10 mins.
>
>I dont know wht to do please help me out.
>
>I have pasted the query below. Other than thsi query the pgm does some
>processing too, such as calculating of service days and hrs for each employee
>(i.e each row).
>
>Begin-Procedure Main-Process
>Begin-Select
>A.EFFDT
>A.EFFSEQ
>A.ELIG_CONFIG1
>A.BEN_STATUS
>!A.PAYGROUP
>B.HIRE_DT
>B.REHIRE_DT
>B.TERMINATION_DT
>B.GH_GSELIG_DT
>B.GH_PRVELIG_CONFIG1
>C.EMPLID
>C.NAME
>C.BIRTHDATE
>>From
> PS_JOB A,
> PS_EMPLOYMENT B,
> PS_PERSONAL_DATA C
>Where
> C.EMPLID NOT IN(SELECT Z.EMPLID
> From
> AHRSO75Q.PS_JOB Z
> Where
> Z.EFFDT = (SELECT MAX(Z2.EFFDT)
> From AHRSO75Q.PS_JOB Z2
> Where Z2.EMPLID = Z.EMPLID
> AND Z2.EFFDT <= $extract_dt
> and Z2.EMPL_RCD# = 0)
>
> and Z.EFFSEQ = (select max(Z2.EFFSEQ)
> From AHRSO75Q.PS_JOB Z2
> Where Z2.EMPLID = Z.EMPLID
> and Z2.EFFDT = Z.EFFDT
> and Z2.EMPL_RCD# = 0)
>
> and Z.COMPANY = '234'
> and Z.EMPL_RCD# = 0
> AND Z.EMPL_STATUS IN('T','D','Q','R','U','V')
> AND Z.EFFDT <= '2000-12-31')
> and A.Emplid = B.Emplid
> and B.Emplid = C.Emplid
>
> and A.EFFDT = (select max(A2.EFFDT)
> From PS_JOB A2
> Where A2.EMPLID = A.EMPLID
> and A2.EFFDT <= $extract_dt
> and A2.EMPL_RCD# = 0)
>
> and A.EFFSEQ = (select max(A2.EFFSEQ)
> From PS_JOB A2
> Where A2.EMPLID = A.EMPLID
> and A2.EFFDT = A.EFFDT
> and A2.EMPL_RCD# = 0)
>
> and A.COMPANY = '234'
> and A.EMPL_RCD# = 0
>Order By C.EMPLID
>with ur
>End-Select
>
>Thanks in advance
>
>bye
>Aravind
>
>_______________________________________________
>sqr-users mailing list
>sqr-users@sqrug.org
>http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users