RE: Help with SQL statement Performance -- MIDRANGE-L

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@xxxxxxxxxxxxxxxxxxxxxxx<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>