Re: SQL optimization

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