RE: Help with SQL statement Performance

Do you always use the same WHERE Conditions when testing i.e. same CustNo or
different CustNo.
It could be that the optimizer optimizes differently for different values.
... also try to add OPTIMIZE for X ROWS at the end of the SELECT statement.
With this option you affect the Optimization GOAL (which can be *FIRSTIO or
*ALLIO). When replacing X with a small number the optimization goal *FIRSTIO
is used if x is replaced by a large number or ALL optimization goal *ALLIO
is used. Normally the optimization goal does not make a big difference, but
in situations when the Optimizer is not clear on whether an index access or
a table/hash scan is used, it can make a big difference. With *FIRSTIO a
(sub-) optimal index might be used, while with *ALLIO a table scan is
performed.

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!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Greg
Wilburn
Sent: Donnerstag, 9. Dezember 2021 17:31
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: Help with SQL statement Performance

Charles - thanks.

I have ticket open with IBM. I did exactly what you suggested. What's
weird is that the same query will use that index sometimes and not others.

I can literally see in VE when the Index is used (Index Probe) and when it's
not (a corresponding Temporary Hash Table).

When it uses the index, the response time is 1-3 seconds When it doesn't,
the response time is 16-24 seconds

I will post IBM's findings.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Charles Wilt
Sent: Monday, December 6, 2021 5:25 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Help with SQL statement Performance

You might try creating an index on one of the files that include the already
converted number...

create index myidx on oeboxcbx (key1, cast(rel_num as decimal(5,0)) or vice
versa create index myindx on oeordlod (key1, cast(rel_num) as numeric(5,0))

Be sure to include any other join fields needed.
You can also include columns used in your WHERE clause.

when you do your join in your select, include the CAST().

You should be able to tell in VE that the new index is being used.

Charles


On Fri, Dec 3, 2021 at 2:38 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

OEBOXCBX does not contain ODCTT#

I could be wrong but I think the problem is that the "key field"
xxREL# is
ZONED(5,0) in OEBOXCBX and PACKED(5,0) in the other files I'm joining.

ODCTT# comes from Order detail lines (line items) OEBOXCBX is a table
containing items packed for shipping by company, order, release, box,
part

If the company/order/release exists ONE time in OEBOXCBX, I need to
eliminate it from my result set.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Alan Shore via MIDRANGE-L
Sent: Friday, December 3, 2021 4:32 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: Help with SQL statement Performance

Try the following
Create a CTE over the file OEBOXCBX that contains those records where
odctt# > 0 This takes that condition out of the exception join THAT is
where I believe your problem lies But then again - I could be wrong
Like the following

With
PARTOEBOXCBX as (select * from OEBOXCBX where odctt# > 0), PicksByLoc
as ( select odwhs#, odcom#, odprt#, pkloc1, pkloc2, pkloc3,
sum(odctt#) as on_pick from oeordlod join oelocnpk on pkcom#=odcom#
and pkord#=odord# and pkrel#=odrel# and pkprt#=odprt# and
pkorg#=odorg# EXCEPTION JOIN PARTOEBOXCBX ON BXCOM#=ODCOM# AND
BXORD#=ODORD# AND BXREL#=ODREL# group by odwhs#, odcom#, odprt#,
pkloc1, pkloc2, pkloc3
)
select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3, on_pick,
irqoh# from PicksByLoc join icprtmia on iacom#=odcom# and
iaprt#=odprt# join icbldtir on irwhs#=odwhs# and ircom#=odcom# and
irloc1=pkloc1 and
irloc2=pkloc2 and irloc3=pkloc3 and irprt#=odprt# where on_pick >
irqoh# ;


Alan Shore
Solutions Architect
IT Supply Chain Execution

[NHScsignaturelogo]

60 Orville Drive
Bohemia, NY 11716
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
E-mail : ASHORE@xxxxxxxxxxxxxxxxxxxx

'If you're going through hell, keep going.'
Winston Churchill

From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On
Behalf Of Greg Wilburn
Sent: Friday, December 3, 2021 4:18 PM
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Help with SQL statement Performance

This is an extension of my previous topic of SQL View vs CTE performance.
Seems the performance is related to a specific table and not the method.

I've done quite a bit of trial and error to isolate my problem. Now I
don't know to fix it (and index advisor isn't showing anything).

I've tried two different statements with the same result.
If I remove the EXCEPTION JOIN to table OEBOXCBX, my queries go from
20+ seconds down to 1.2 seconds.

The table OEBOXCBX contains 10 million records. There are existing LF
for OEBOXCBX with keys beginning with BXCOM#, BXORD#, BXREL#, +
additional fields (BXBOX#, BXPRT#). But those 3 keys alone are not unique.

VE shows a Hash Table built over this file consuming 20 seconds.
The list of key columns are:
BXORD#
BXCOM#
Cast (BXREL# as DEC(5,0))
** This doesn't make sense because BXREL# is defined using a field
reference file (DDS) as 5 0

If I generate the SQL definition, the field is described like this:
BXREL# NUMERIC(5, 0) NOT NULL DEFAULT 0 ,

Instead of DEC(5, 0)

Any suggestions would be appreciated...

My SQL statements for reference:

First One:
With PicksByLoc as (
select odwhs#, odcom#, odprt#, pkloc1, pkloc2, pkloc3, sum(odctt#) as
on_pick from oeordlod join oelocnpk on pkcom#=odcom# and pkord#=odord#
and pkrel#=odrel# and pkprt#=odprt# and pkorg#=odorg# EXCEPTION JOIN
OEBOXCBX ON BXCOM#=ODCOM# AND BXORD#=ODORD# AND BXREL#=ODREL# where
odctt# > 0 group by odwhs#, odcom#, odprt#, pkloc1, pkloc2, pkloc3
)
select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3, on_pick,
irqoh# from PicksByLoc join icprtmia on iacom#=odcom# and
iaprt#=odprt# join icbldtir on irwhs#=odwhs# and ircom#=odcom# and
irloc1=pkloc1 and
irloc2=pkloc2 and irloc3=pkloc3 and irprt#=odprt# where on_pick >
irqoh# ;

Second Try:
with pickdetail as (
select odwhs#, odcom#, odord#, odrel#, odprt#, odctt#, pkloc1, pkloc2,
pkloc3 from oeordlod
join oelocnpk on pkcom#=odcom# and pkord#=odord# and pkrel#=odrel# and
pkprt#=odprt# and pkorg#=odorg# where odctt# > 0)

select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3,
sum(odctt#) as on_pick, max(irqoh#) as on_hand from Pickdetail join
icprtmia on iacom#=odcom# and iaprt#=odprt# join icbldtir on
irwhs#=odwhs# and ircom#=odcom# and irloc1=pkloc1 and
irloc2=pkloc2 and irloc3=pkloc3 and irprt#=odprt# EXCEPTION JOIN
OEBOXCBX ON BXCOM#=ODCOM# AND BXORD#=ODORD# AND BXREL#=ODREL# where
odctt# > irqoh# group by odwhs#, odcom#, odprt#, ia101, pkloc1,
pkloc2, pkloc3 ; [Logo]<https://www.totalbizfulfillment.com/<
https://www.totalbizfulfillment.com>> Greg Wilburn Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@totalbizfulfillment.c
om <mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx%
3cmailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><
http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>

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

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com<https://amazon.midrange.com>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com