We help IT Professionals succeed at work.

Make more performing the query.

Hello expert.
There is a way to speed up the following stored procedure:

ALTER PROCEDURE [dbo].[UpdateSalesOrderDetailFields]

AS
BEGIN
UPDATE    CurrentSalesOrderTemp
SET           AD_Mode = vBM_BillOptionHeader.OptionDesc1
FROM        CurrentSalesOrderTemp INNER JOIN
                  vSO_SalesOrderHistoryDetail ON CurrentSalesOrderTemp.SONo = vSO_SalesOrderHistoryDetail.SalesOrderNo INNER JOIN
                  vBM_BillOptionHeader ON vSO_SalesOrderHistoryDetail.BillOption2 = vBM_BillOptionHeader.BillOption
WHERE     (vBM_BillOptionHeader.BillOptionCategory = '2')

UPDATE    CurrentSalesOrderTemp
SET           TempUnits = vBM_BillOptionHeader.OptionDesc1
FROM        CurrentSalesOrderTemp INNER JOIN
                  vSO_SalesOrderHistoryDetail ON CurrentSalesOrderTemp.SONo = vSO_SalesOrderHistoryDetail.SalesOrderNo INNER JOIN
                  vBM_BillOptionHeader ON vSO_SalesOrderHistoryDetail.BillOption2 = vBM_BillOptionHeader.BillOption
WHERE     (vBM_BillOptionHeader.BillOptionCategory = '1')

UPDATE    CurrentSalesOrderTemp
SET           FiberCableCode = vBM_BillOptionHeader.UDF_CODE
FROM        CurrentSalesOrderTemp INNER JOIN
                  vSO_SalesOrderHistoryDetail ON CurrentSalesOrderTemp.SONo = vSO_SalesOrderHistoryDetail.SalesOrderNo INNER JOIN
                  vBM_BillOptionHeader ON vSO_SalesOrderHistoryDetail.BillOption2 = vBM_BillOptionHeader.BillOption
WHERE     (vBM_BillOptionHeader.BillOptionCategory = '3')

UPDATE    CurrentSalesOrderTemp
SET           FiberCableDesc = vBM_BillOptionHeader.OptionDesc1
FROM        CurrentSalesOrderTemp INNER JOIN
                  vSO_SalesOrderHistoryDetail ON CurrentSalesOrderTemp.SONo = vSO_SalesOrderHistoryDetail.SalesOrderNo INNER JOIN
                  vBM_BillOptionHeader ON vSO_SalesOrderHistoryDetail.BillOption3 = vBM_BillOptionHeader.BillOption
WHERE     (vBM_BillOptionHeader.BillOptionCategory = '3')

UPDATE    CurrentSalesOrderTemp
SET           ESPGroup = vBM_BillOptionHeader.UDF_CODE
FROM        CurrentSalesOrderTemp INNER JOIN
                  vSO_SalesOrderHistoryDetail ON CurrentSalesOrderTemp.SONo = vSO_SalesOrderHistoryDetail.SalesOrderNo INNER JOIN
                  vBM_BillOptionHeader ON vSO_SalesOrderHistoryDetail.BillOption8 = vBM_BillOptionHeader.BillOption
WHERE     (vBM_BillOptionHeader.BillOptionCategory = '8')
END

Any help will be appreciated.
Thanks in advance
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:

Not considering indexes for now, I think you can combine the first 3 UPDATEs into a single UPDATE, which will perform better than 3 separate statements:


UPDATE    CSOT
SET           TempUnits = CASE WHEN BOH.BillOptionCategory = '1' THEN BOH.OptionDesc1 ELSE CSOT.TempUnits END,
              AD_Mode = CASE WHEN BOH.BillOptionCategory = '2' THEN BOH.OptionDesc1 ELSE CSOT.AD_Mode END,
              FiberCableCode = CASE WHEN BOH.BillOptionCategory = '3' THEN BOH.UDF_CODE ELSE CSOT.FiberCableCode END
FROM        dbo.CurrentSalesOrderTemp CSOT INNER JOIN
                  dbo.vSO_SalesOrderHistoryDetail SOHD ON CSOT.SONo = SOHD.SalesOrderNo INNER JOIN
                  dbo.vBM_BillOptionHeader BOH ON SOHD.BillOption2 = BOH.BillOption AND
WHERE     BOH.BillOptionCategory IN ('1', '2', '3')


Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
Check the datatype of BillOptionCategory. Is it truly a text as your query seems to indicatate?

Author

Commented:
Thanks a lot. The query work better than before.