Re: SQL optimization

[Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>]

What has the optimizer suggested?   Have you put this into visual explain to see how the optimizer is actually running the statement?

Jim Oberholtzer
Agile Technology Architects



> On Oct 13, 2021, at 7:11 AM, Richard Reeve <rjrjr64@xxxxxxxxx> wrote:
>
> All,
>
>     I have this SQL statement that runs quite slow.  Any idea how I might
> speed this up?
>
> SELECT
>
> WHS.DBWHS_SHIPMENT_NBR
> ,WHS.DBWHS_DOC_NBR
> ,COR.DBCOR_ORDER_NBR
> ,COR.DBCOR_ORDER_TYPE
> ,WHL.DBWHL_DESCRIPTION
> ---,' ' AS sggroup
> ---,' ' AS sold_to-pt
> ,COR.DBCOR_CUST_PO_NBR
> ---, ' ' as fol-doc
> ,WHS.DBWHS_BOL_NBR
> ,WHS.DBWHS_WEIGHT
> ,WHS.DBWHS_CARTONS
> ,WHS.DBWHS_SHIP_DATE
> ,WHS.DBWHS_TRANS_ID
> ---,' ' as proc-id
> ,WHS.DBWHS_METHOD
> ,SHP.DBSHP_CUSTOMER
> ,SHP.DBSHP_NAME
> ,SHP.DBSHP_NAME
> ,SHP.DBSHP_ADDR1
> ,SHP.DBSHP_ADDR2
> ,SHP.DBSHP_CITY
> ,SHP.DBSHP_STATE
> ,SHP.DBSHP_ZIP
> ---,DBBLSHP.''COST CENTER
> ,SPC.DBSPC_COMMENT
> ---,'' as whn
> ,WDH.DBWDH_DESTINATION
> ,WDH.DBWDH_DEST_NAME
> ,SHP.DBSHP_TAX_CODE ---,' ' as tax_juris
> ,WDH.DBWDH_DEST_STATE
> ,WDH.DBWDH_DEST_CNTRY
> ,WDH.DBWDH_DEST_ZIP
> ,WHS.DBWHS_CARRIER
> ,VEN.DBVEN_VENDOR_VNAME---,'v' as carrier name
> ,WHS.DBWHS_FREIGHT_CODE---', as incoterm code
> ---,' ' as incoterm description
> ,WHS.DBWHS_FRT_CHG_CURR
> ,ARH.DBARH_TRAN_AMT
> ---,' ' as acct
> ,ACM.DBACM_CREDIT_LIM
> ---,' ' as pint
> ,WHS.DBWHS_COMPANY
> ,COR.DBCOR_ORDER_DATE
> ---,WDM.DBWDM_UPDATE_DATE
> ,WHS.DBWHS_STATUS
> ,WHS.DBWHS_SHIP_DATE
> ---,'LAW' as ERP-IDENTIFIER
>
> FROM LAWDBF7B.DBWHWHS WHS
>
> LEFT JOIN LAWDBF7B.DBWHWHL WHL ON WHS.DBWHS_DOC_NBR=WHL.DBWHL_DOC_NBR
> LEFT JOIN LAWDBF7B.DBWHWDH WDH ON WHS.DBWHS_DOC_NBR=WDH.DBWDH_DOC_NBR
> LEFT JOIN LAWDBF7B.DBBLSHP SHP ON WHS.DBWHS_CUST_SHIP_TO=SHP.DBSHP_SHIP_TO
> LEFT JOIN LAWDBF7B.DBAPVEN VEN ON WHS.DBWHS_VENDOR=VEN.DBVEN_VENDOR
> LEFT JOIN LAWDBF7B.DBBLSPC SPC ON SHP.DBSHP_CUSTOMER=SPC.DBSPC_CUSTOMER
> LEFT JOIN LAWDBF7B.DBARARH ARH ON ARH.DBARH_CUSTOMER=SHP.DBSHP_CUSTOMER
> LEFT JOIN LAWDBF7B.DBARACM ACM ON ACM.DBACM_CUSTOMER=SHP.DBSHP_CUSTOMER
> LEFT JOIN LAWDBF7B.DBBLCOR COR ON COR.DBCOR_CUSTOMER=SHP.DBSHP_CUSTOMER
>
> WHERE WHS.DBWHS_SHIP_DATE>='20210930' AND WHS.DBWHS_STATUS='3'
>
>
> All the best,
>
> Richard Reeve
> -- 
> 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