RE: SQL scroll cursor slow performance

["Wilt, Charles" <CWilt@xxxxxxxxxxxx>]

Glad to help,

I think you could also get rid of the following:

     C+   left outer join
     C+      (select F2FRUM, min(F2TOUM) as F2TOUM, min(F2FACT) as F2FACT
     C+        from DGF2 group by F2FRUM) DGF2
     C+     on F2FRUM=F5IUM

and just use

     C+   left outer join DGF2
     C+     on F2FRUM=F5IUM


maybe not, depends on the data.  You should be at least able to get rid of the 
MIN().


As far as the ifnull().   My personal preference is to encapsulate the required 
results set into a UDF table function, in which case I'd just leave the NULL 
values in there until I need to take them out.

This way I can reuse the UDF without losing the null values until I'm ready to, 
ie. right before the output for the user is created.



Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of darren@xxxxxxxxx
> Sent: Thursday, June 23, 2005 3:45 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL scroll cursor slow performance
> 
> 
> I had already begun re-writing it attempting to find a way to 
> get rid of
> the min(), because I don't like them but usually find them 
> necessary.  I
> came up with something similar to yours as below.  Basically 
> PCHL01 and
> PCLL01 are header/detail relationship files, and they are 
> also the primary
> so they can be joined and grouped in their own temporary 
> file, allowing
> everything else to just be reference.  I was skeptical that 
> it would help,
> but the program runs in less than a second now.  Thank you for the
> assistance.  Now, if I could just get rid of those annoying 
> ifnull's...
> thanks again for the suggestions.
> 
> 
> 
> 
>      C/EXEC SQL
>      C+ declare C1 scroll cursor for
>      C+ select PLPRD, PLQTY,
>      C+        PLCTN, PLWGT,
>      C+        'BULTOS' as PTyp1, 'BUNDLES' as PTyp2,
>      C+        GWgt,
>      C+        ifnull(FKENGD,' ') as EDSC,
>      C+        ifnull(FKSPND,' ') as SDSC,
>      C+        ifnull(F5COO,' ') as F5COO,
>      C+        ifnull(F2TOUM,' ') as F2TOUM,
>      C+        ifnull(HARPMT,' ') as HARPMT,
>      C+        ifnull(F5SCST,0), ifnull(F2FACT,1),
>      C*        multiply by duty rate percentage
>      C+          ifnull(case
>      C+            when :FEDATE>=F4COOD
>      C+              then 0
>      C+            when :FEDATE<F4COOD
>      C+              and FLPROF='Y'
>      C+              then FLPROR
>      C+            when :FEDATE<F4COOD
>      C+              and FLPROF<>'Y'
>      C+              then FLNORR
>      C+            end,0)
>      C+
>      C+   from
>      C+     (select PHCMP, PHFAC, PLPRD, sum(PLQTY) as PLQTY, PLCTN,
>      C+             sum(PLWGT) PLWGT, sum(PHCWT)+sum(PLWGT) as GWgt
>      C+      from PCHL01 join PCLL01
>      C+        on PHCTN=PLCTN
>      C+      where PHCONS=:Cons
>      C+      group by PHCMP, PHFAC, PLCTN,PLPRD) PrimaryFile
>      C+
>      C+   left outer join DGF4
>      C+     on F4PROD=PLPRD
>      C+     and F4CMP=PHCMP
>      C+     and F4FAC=PHFAC
>      C+   left outer join HARF
>      C+     on HARCOD=F4HARM
>      C+   left outer join DGF5
>      C+     on F5CMP=PHCMP
>      C+     and F5Fac=PHFAC
>      C+     and F5PROD=PLPRD
>      C+   left outer join
>      C+      (select F2FRUM, min(F2TOUM) as F2TOUM, 
> min(F2FACT) as F2FACT
>      C+        from DGF2 group by F2FRUM) DGF2
>      C+     on F2FRUM=F5IUM
>      C+   left outer join DGFK
>      C+     on FKHARM=F4SUBH
>      C+     and FKFAMN=F4FAMN
>      C+   left outer join DGFL
>      C+     on FLHARM=F4SUBH
>      C+
>      C+ order by PLCTN, PLPRD
>      C/END-EXEC
> 
> 
> 
> 
>                                                               
>