|
midrange.com
Duane,
I've noticed similar performance improvements when using CTE constructs. I
suppose I always assumed that the CTE expressions were faster because it
potentially reduces the number of rows to process, which allows the joins to
process faster. I generally use CTE to pre-summarize sales data before
producing report data...
with SlsCy (Cust, Sales, Cost, TrnCnt) as (
Select Cst#, sum(Extd-Disc), sum(Cost), count(distinct order#)
from saleshist where tdate >= '2005-10-01' and tdate <= '2005-10-31'
),
SlsLy (Cust, Sales, Cost, TrnCnt) as (
Select Cst#, sum(Extd-Disc), sum(Cost), count(distinct order#)
from saleshist where tdate >= '2004-10-01' and tdate <= '2004-10-31'
)
/* Show all current-year customers, with their sales from last year.... */
Select SlsCy.Cust, SlsCy.Sales, SlsLy.Sales ....
>From SlsCy left outer join
SlsLy on (SlsCy.Cust=SlsLy.Cust)
Union All
/* Now, add in the customers with sales last year, but not this year.......
*/
Select SlsCy.Cust, SlsCy.Sales, SlsLy.Sales ....
>From SlsLy exception join
SlsCy on (SlsCy.Cust=SlsLy.Cust)
(I wish DB2/400 supported full join......)
A similar benefit seems to occur when you move filtering in a join statement
from the Where clause into the ON join criteria. It's particularly
noticable when joining very large files into a summary result set, such as
for a dashboard sales report.
Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Christen, Duane J.
Sent: Thursday, November 10, 2005 12:40 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Returning Values from two different Tables
Normally no, and I would have excluded it from the post if I would have
thought before posting, but with the common table expression I get better
response times than without. I ran both situations through visual explain
which didn't explain why the CTE was faster. At a guess the CTE hung around
in the background longenough to be reused.
Duane Christen
-----Original Message-----
From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx]
Sent: Thursday, November 10, 2005 12:11 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Returning Values from two different Tables
Duane,
You didn't need the common table expression.
This would have worked:
C/Exec Sql
C+ declare carelistcursor scroll cursor for
C+ select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn,
C+ pcdate, pcbtn, pcani, 0 as pcarcseq#
C+ from pdpcare a
C+ where pcprod = :product and pcpkey = :pKey
C+ union all
C+ select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn,
C+ pcdate, pcbtn, pcani, pcarcseq#
C+ from papcare
C+ where pcprod = :product and pcpkey = :pKey
C+ order by pcseq#, pcarcseq#
C+ for read only
C/End-Exec
HTH,
Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Christen, Duane J.
Sent: Thursday, November 10, 2005 11:22 AM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Returning Values from two different Tables
Brian;
I just promoted a project this week with the same requirements. This is
the
embeded sql I built for my project. You can modify it for your needs.
C/Exec Sql
C+ declare carelistcursor scroll cursor for
C+ with carelist as
C+ (
C+ select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn,
C+ pcdate, pcbtn, pcani, 0 as pcarcseq#
C+ from pdpcare a
C+ where pcprod = :product
C+ union all
C+ select pcpkey, pcseq#, pcstatus, pctrancode, pcstatusid, pcocn,
C+ pcdate, pcbtn, pcani, pcarcseq#
C+ from papcare
C+ where pcprod = :product
C+ )
C+ select *
C+ from carelist
C+ where pcpkey = :pKey
C+ order by pcseq#, pcarcseq#
C+ for read only
C/End-Exec
Duane Christen
-----Original Message-----
From: Brian Piotrowski [mailto:bpiotrowski@xxxxxxxxxxxxxxx]
Sent: Thursday, November 10, 2005 9:04 AM
To: RPG programming on the AS400 / iSeries
Subject: Returning Values from two different Tables
Hi All,
I have some SQL code in my RPG program that pulls data from two
different tables. For example, in TableA I have some records that are
in a specific date range. In TableB, I have some records that match the
same date range plus and additional parameter (ie: fieldb = 'XX'). I
then need to concatenate the results of both of these tables into a
single list. So the data would look something like this:
Data Results:
==========
(TableA) 2005/11/04 11:04am
(TableA) 2005/11/04 11:06am
(TableB) 2005/11/04 11:07am
(TableA) 2005/11/04 11:09am
(TableB) 2005/11/04 11:10am
Etc..
Would I be better off to bring the select results into an array, sort
and display the results, or could I do this through and SQL statement
without having to worry about arrays and such?
Thanks for your thoughts.
Brian.
-=-=-=-=-=-=-=-=-=-=-=-=-=-
Brian Piotrowski
Specialist - I.T.
Simcoe Parts Service, Inc.
PH: 705-435-7814
FX: 705-435-6746
-=-=-=-=-=-=-=-=-=-=-=-=-=-
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.
