RE: SQL scroll cursor slow performance

["Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>]

> From: Richard ECUYER
> 
> >
> > 1. Do you think this would require more or less complexity to write
> > using native I/O statements?
>            1- I thin that native I/O require less complexity when
> selection
> (or sum ...) criteria are complex.
> 
> 
> > 2. What reasons would you give for choosing SQL or native (remember,
for
> > performance purposes all the indexes have to exist, so that's not an
> > reason to choose SQL)?
>             2- I choose SQL when i make a simple select with variables
> filters, it is like openqryfile but very, very more simple to read for
> others.
>                 Another advantage for me, i can use the same template
> (especially for sub-file) with SQL or native IO.


Thanks for your input, Richard!  However, I was really hoping for
answers specific to this single statement, rather than a broad
comparison of SQL vs. native.  I should have included the SQL in my
previous post!

For example, the statement does not have variable filters but it does
have complex selection and grouping criteria (I'm including it below).
Based on that, would you think this specific statement was easier to
write in native I/O or SQL?  Can you answer the same questions looking
only at the single statement in question?

Poll questions (anybody who feels like it, please answer):

1. Do you think this would require more or less complexity to write
using native I/O statements?

2. What reasons would you give for choosing SQL or native (remember, for
performance purposes all the indexes have to exist, so that's not an
reason to choose SQL)?

3. Do you think it would perform better in native or SQL?

4. Which do you think would take longer to debug?

5. Which would be easier to maintain?

Joe


>      C+ declare C1 scroll cursor for
>      C+ select PLPRD, sum(PLQTY) as PLQTY,
>      C+        PLCTN, sum(PLWGT) as PLWGT,
>      C+        'BULTOS' as PTyp1, 'BUNDLES' as PTyp2,
>      C+        sum(PHCWT)+sum(PLWGT) as GWgt,
>      C+        ifnull(min(FKENGD),' ') as EDSC,
>      C+        ifnull(min(FKSPND),' ') as SDSC,
>      C+        ifnull(min(F5COO),' ') as F5COO,
>      C+        ifnull(min(F2TOUM),' ') as F2TOUM,
>      C+        ifnull(min(HARPMT),' ') as HARPMT,
>      C+        ifnull(min(F5SCST),0), ifnull(min(F2FACT),1),
>      C*        multiply by duty rate percentage
>      C+          ifnull(case
>      C+            when :FEDATE>=min(F4COOD)
>      C+              then 0
>      C+            when :FEDATE<min(F4COOD)
>      C+              and min(FLPROF)='Y'
>      C+              then min(FLPROR)
>      C+            when :FEDATE<min(F4COOD)
>      C+              and min(FLPROF)<>'Y'
>      C+              then min(FLNORR)
>      C+            end,0)
>      C+
>      C+   from PCHL01 join PCLL01
>      C+     on PHCTN=PLCTN
>      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+   where PHCONS=:Cons
>      C+
>      C+   group by PLCTN, PLPRD
>      C+ order by PLCTN, PLPRD