|
midrange.com
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
"Wilt, Charles"
<CWilt@xxxxxxxxxx
om> To
Sent by: "Midrange Systems Technical
midrange-l-bounce Discussion"
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc
06/23/2005 01:56 Subject
PM RE: SQL scroll cursor slow
performance
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
Simple,
Use a CTE to split out the summary info from the non-summary.
>From your original example,
C+ with PCHL01Summary as ( select PLPRD, sum(PLQTY) as PLQTY,
C+ PLCTN, sum(PLWGT) as PLWGT
C+ from PCHL01
C+ group by PLCTN, PLPRD
C+ )
C+ select PLPRD, PLQTY,
C+ PLCTN, PLWGT,
C+ 'BULTOS' as PTyp1, 'BUNDLES' as PTyp2,
C+ sum(PHCWT)+sum(PLWGT) as 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 PCHL01Summary 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 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+ order by PLCTN, PLPRD
Can't say for sure this will work as I don't know where all the columns are
coming from. In particular PHCONS.
But you hopefully you get the idea.
Actually as I rework this, I don't see why you used MIN() in the first
place. Why didn't you just add all the non-summary fields to the GROUP by
clause?
For example, say you want total shipped by customer and item, it seems this
is what you had:
select B.customerNbr, A.itemNbr, min(A.description), sum(B.qtyShipped)
from itemMaster A
join orderHist B
on A.itemNbr = B.shippedItem
group by B.customerNbr, A.itemNbr
but this would have been the correct why to handle it:
select B.customerNbr, A.itemNbr, A.description, sum(B.qtyShipped)
from itemMaster A
join orderHist B
on A.itemNbr = B.shippedItem
group by B.customerNbr, A.itemNbr, A.description
As long as you don't have the same item number with 2 different
descriptions. And if you did, then the MIN() wouldn't work anyway.
HTH,
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 2:06 PM
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL scroll cursor slow performance
>
>
> That's just the nature of SQL. When you group based on one
> or more fields,
> there will be other fields that aren't part of the grouping
> and you have to
> instruct the query what to do about it. In other words,
> anytime you use
> GROUP BY, any fields not listed in this list have to have some kind of
> modifier in front of them, like min(), max(), or sum(). I'm
> having trouble
> seeing how common table expressions or a UDF could get around
> that rule.
>
>
>
>
>
>
> "Wilt, Charles"
>
> <CWilt@xxxxxxxxxx
>
> om>
> To
> Sent by: "Midrange Systems
> Technical
> midrange-l-bounce Discussion"
>
> s@xxxxxxxxxxxx
> <midrange-l@xxxxxxxxxxxx>
>
> cc
>
>
> 06/23/2005 12:17
> Subject
> PM RE: SQL scroll cursor
> slow
> performance
>
>
>
> Please respond to
>
> Midrange Systems
>
> Technical
>
> Discussion
>
> <midrange-l@midra
>
> nge.com>
>
>
>
>
>
>
>
>
>
> > -----Original Message-----
> > From: midrange-l-bounces@xxxxxxxxxxxx
> > [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Joe Pluta
> > Sent: Thursday, June 23, 2005 12:38 PM
> > To: 'Midrange Systems Technical Discussion'
> > Subject: RE: SQL scroll cursor slow performance
> >
> >
> > This is a syndrome. I'm going to name it. I'm going to call it
> > "creeping SQL-ism". I think this could be done with a simple
> > read loop
> > and about seven CHAINs. The wide use of IFNULL and MIN in
> situations
> > where there should only be one record or none makes this statement
> > awfully complex. And I always start to wonder when I see the CASE
> > keyword. I have to admit that I'm a bit confused as to how the SQL
> > engine figures out the CASE clause in this statement where different
> > fields are being MIN'd based on the value of a different field's MIN
> > value.
>
> I'm going to have to agree with Joe here. The use of min()
> when you're
> looking for a minimum value is fine. The use of min() to
> work around the
> GROUP BY clause is messy.
>
> Some Common Table Expressions or UDF functions (scalar and/or
> table) may be
> of use here.
>
>
> >
> > 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?
>
> In general, about the same. This particular SQL seems to be
> messier than
> it needs to be.
>
> >
> > 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)?
>
> Performance could still be an issue. But since he's fetching
> one row at a
> time, the OP must not be too concerned about it. Then again,
> is his asking
> about poor performance here.
>
> In general, if I'm dealing with obvious set at time requirements, I'll
> chose SQL.
>
> In particular, I like to encode "business queries" in a UDF
> table function
> for easier reuse and/or outputting in alternative formats.
>
> >
> > 3. Do you think it would perform better in native or SQL?
>
> When done correctly? SQL of course.
>
> >
> > 4. Which do you think would take longer to debug?
>
> I find SQL easier; particularly when you use CTE and UDFs to
> break stuff
> down. It's easy to check the results are what you expect.
> You don't need
> to step thru one line at a time.
>
> However, with an SQL statement this big SQL would be harder.
>
> >
> > 5. Which would be easier to maintain?
>
> see above. the same applies
>
> >
> > (A side issue is to ask why you are running through the exact same
> > cursor four times; where are the report lines going? One
> of the first
> > things I'd be looking at is how to run through the data once,
> > but that's
> > just my old dinosaur thinking.)
> >
> > Joe
> >
>
> Agreed. If the only differences are the headers, open 4
> spool files and
> write them all at the same time for crying out loud.
>
> Even with other differences, there's no reason to read the same data 4
> time, just create separate procedures to process that data
> into 4 spool
> files at the same time.
>
>
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
>
>
> --
> 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.
>
>
>
> --
> 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.
>
>
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
