Avatar of SALA DONATELLA
SALA DONATELLA
 asked on

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
Microsoft SQL ServerSQL

Avatar of undefined
Last Comment
SALA DONATELLA

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anders Ebro (Microsoft MVP)

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

ASKER
Thanks a lot. The query work better than before.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23