Re: SQL scroll cursor slow performance

At the first look i will say : houla native I/O sure, at the second i will
say : bah it seems pretty clear that SQL may be definitivly the answer.

What will make my choice will be (if i need) the performance.
The other will be who is going (able) to maintain SQL ? not any programmers
here know SQL (or just some select * from where ....)

I must answer also, that i have never used such statements...

----- Original Message ----- 
From: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx>
Sent: Thursday, June 23, 2005 7:09 PM
Subject: RE: SQL scroll cursor slow performance


> > 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
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>