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

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