Re: SQL Sum(Distinct()) -- MIDRANGE-L

http://www.sql-server-performance.com/tips/tuning_joins_p1.aspx

One of the best ways to boost JOIN performance is to limit how many rows need to be JOINed. This is especially
beneficial for the outer table in a JOIN. Only return absolutely only those rows needed to be JOINed, and no more.

--

Michael Schutte
Admin Professional

Announcing Bob Evans Bob-B-Q® Road Trip! For a limited time, America's best
Bob-B-Q® tastes are all at Bob Evans! For more information, visit
www.bobevans.com/menu/seasonal.aspx

midrange-l-bounces@xxxxxxxxxxxx wrote on 06/22/2010 08:51:32 AM:

Visual explain should be able to show you what if anything is different.

Charles

On Tue, Jun 22, 2010 at 8:29 AM, <Michael_Schutte@xxxxxxxxxxxx> wrote:

I'm not sure how it works under the covers. Others have tried to prove

wrong but I see a difference in performance gain when you put record
selection from the detail file. My thinking and from readings from

the

web (I know not the best of resources), is that if you select records

from

both tables, then less records need to be looked at when the join

occurs.

Personally, I would use a cte (common table expression) to summarize

your

detail file making any record selections that you can, then join the

cte to

your header.

with detail as (
select ONRCU, SUM(ITNSA) AS ITNSA
from ordtl
where ...
group by ONRCU )
select sum(itnsa), sum(ordam)
into
from orhdr join detail using onrcu
where ...

--

Michael Schutte
Admin Professional

Announcing Bob Evans Bob-B-Q® Road Trip! For a limited time, America's

best

Bob-B-Q® tastes are all at Bob Evans! For more information, visit
www.bobevans.com/menu/seasonal.aspx

midrange-l-bounces@xxxxxxxxxxxx wrote on 06/21/2010 04:45:08 PM:

Birgitta,

Question on this SQL you posted for me today:

Exec SQL
Select Sum(d.ITNSA),
Sum(h.ORDAM)
Into :ActItmSlsAmt,
:OrdActOrdAmt
From ORHDR h Join (Select ONRCU,
Sum(ITNSA) As ITNSA
From ORDTL
Group By ONRCU)
d On h.ONRCU = d.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;

The inner Select doesn't have a Where clause. Does this mean that

every

time this statement is executed, it will create a temporary file

of /*all*/

the records on ORDTL? Or is SQL smart enough to not do that?

Thanks.

On Mon, Jun 21, 2010 at 11:11 AM, Birgitta Hauser <Hauser@sss-
software.de>wrote:

Hi,

you may try something like this:

Exec SQL
Select Sum(D.ITNSA), Sum(H.ORDAM)
Into :Fld1, :Fld2
From OrdHdr h join (Select Oncru, sum(ITNSA) ITNSA
From OrdDtl
Group By ONCRU) d
On h.Oncru = d.Oncru
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the

stars." (Les

Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not

training

them

and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Jeff Crosby
Gesendet: Monday, 21. June 2010 16:33
An: Midrange Systems Technical Discussion
Betreff: SQL Sum(Distinct())

Been a while since I've asked an SQL question, so I'm due. :)

I will soon, I think, have a need within an RPG program to get a

couple

of

pieces of summary information from an order header/detail pair of

files.

One piece from the header file and one piece from the detail file.

can

get the 2 pieces separately like this:

Exec SQL
Select SUM(ORDAM)
Into :ORDAM
From ORHDR
Where CUSNR = :CUSNR and
ROUTE = :ROUTE and
STOPX = :STOPX and
SHPDTISO = :SHPDTISO;

Exec SQL
Select SUM(d.ITNSA)
Into :ITNSA,
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;

I wondered if I could get the 2 pieces with a single statement, so I

tried

this:

Exec SQL
Select SUM(d.ITNSA),
SUM(h.ORDAM)
Into :ITNSA,
:ORDAM
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;

It executed, but the RPG field ORDAM was waAAAyyy off because field

h.ORDAM

from the header file was summed for each record in the detail file.

understand why that is and it makes sense. I did some googling and

came up

with this, using Distinct:

Exec SQL
Select SUM(d.ITNSA),
SUM(Distinct(h.ORDAM))
Into :ITNSA,
:ORDAM
From ORDTL d Join ORHDR h on d.ONRCU = h.ONRCU
Where h.CUSNR = :CUSNR and
h.ROUTE = :ROUTE and
h.STOPX = :STOPX and
h.SHPDTISO = :SHPDTISO;

That seemed to me like it would have a problem, because 2 selected

order

header records COULD have the same h.ORDAM value. Won't happen

often,

but

it can and does happen sometimes. If I understand Distinct right,

would

only include one of them in the Sum function. So I did some testing

and

found that to be true.

So is there a way to do what I want in one statement? The flip side

that
is, if the statement is very complex, I would rather do it in 2

statements

because I believe in KISS.

Thanks.

--
Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of

company. Unless I say so.
--
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.

--
Jeff Crosby
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
www.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of

company. Unless I say so.
--
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.