Re: SQL View vs CTE

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Given that your dealing with a aggregate, I'd highly recommend an
appropriate EVI index that includes sum(odctt#)

Doing so in our environment took a query from 12s to 0.8s...

May not sound like much, but when the query is run for multiple tenants
once an hour...it adds up :)

Charles

On Thu, Dec 2, 2021 at 6:38 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

> Thanks ALL.. awesome insight.
>
> I will try creating a view then running.  For the record, this is the
> statement.
> The CTE sums all of the pick tickets by item & location.  It then compares
> that result with the quantity of the item in the location.  The result is a
> "report" that shows warehouse staff what pick locations are going to run
> out of stock today.
>
> With PicksByLoc as (
> select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3, sum(odctt#)
> as on_pick from oeordlod
> join icprtmia on iacom#=odcom# and iaprt#=odprt#
> 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#, ia101, pkloc1, pkloc2, pkloc3
> )
>
> select odwhs#, odcom#, odprt#, ia101, pkloc1, pkloc2, pkloc3, on_pick,
> irqoh# from PicksByLoc
> join icbldtir on irwhs#=odwhs# and ircom#=odcom# and irloc1=pkloc1 and
> irloc2=pkloc2 and irloc3=pkloc3 and irprt#=odprt#
> where  on_pick > irqoh#;
>
>
> -----Original Message-----
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
> Greg Wilburn
> Sent: Wednesday, December 1, 2021 4:25 PM
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
> Subject: SQL View vs CTE
>
> I have a complex SQL statement that I'm going to use as a data source for
> a browser application.  In ACS Run SQL scripts it consistently takes about
> 22 seconds to run.  The statement contains a CTE followed by a join to the
> CTE.  The files involved have a large number of records.
>
> So I'm wondering if someone can tell me whether creating an SQL View in
> place of the CTE would yield any performance increase (I haven't spent much
> time looking at the Visual Explain in ACS).
> I'm thinking it wouldn't make much difference.
>
> TIA
> Greg
> [Logo]<https://www.totalbizfulfillment.com/>    Greg Wilburn
> Director of IT
> 301.895.3792 ext. 1231
> 301.895.3895 direct
> gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>
> 1 Corporate Dr
> Grantsville, MD 21536
> 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
> 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