How can I reduce costly- performance problem in my update stored procedure?

Hi;

How can I improve performance issue ? I created below stored procedure but if I check costly stored procedure by using sql query to detect them. I realized that below query needs improvement to fix performance issue.Can you help me please?

USE [FFSNext]
GO
/****** Object:  StoredProcedure [WMS].[UpdateQuantityBySalesOrderLineIds]    Script Date: 6/12/2019 11:55:39 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [WMS].[UpdateQuantityBySalesOrderLineIds] 
(
	@SalesOrderLineIds VARCHAR(MAX)
)
AS
BEGIN
	SET NOCOUNT ON;

	UPDATE	PLL
	SET		Quantity = SOL.Quantity
	FROM	WMS.PickListLine PLL

	JOIN	WMS.SalesOrderLine SOL ON SOL.Id = PLL.SalesOrderLineId
	JOIN	PIM.Product SOLP ON SOLP.Id = SOL.ProductId

	WHERE	SOLP.HasAssembly = 0 AND PLL.SalesOrderLineId IN
	(
		SELECT	CAST([value] AS INT)
		FROM	STRING_SPLIT(@SalesOrderLineIds, ',')
	)
	

	UPDATE	PLL
	SET		Quantity = (SOL.Quantity * PLLP.Count)
	FROM	WMS.PickListLine PLL

	JOIN	WMS.SalesOrderLine SOL ON SOL.Id = PLL.SalesOrderLineId
	JOIN	PIM.Product SOLP ON SOLP.Id = SOL.ProductId
	JOIN	PIM.ProductAssembly PLLP ON PLLP.AssemblyProductId = PLL.ProductId

	WHERE	SOLP.HasAssembly = 1 AND PLL.SalesOrderLineId IN
	(
		SELECT	CAST([value] AS INT)
		FROM	STRING_SPLIT(@SalesOrderLineIds, ',')
	)
END

Open in new window

programmeristAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
hmm, don't do things twice.

1. Split the parameter once into a table variable.
2. Check the indices.
3. Check your model. I'm not sure whether you need to test against PIM.Product. But when you have to, then try using an EXISTS() instead. Sometimes it performs better.
4. It's an UPDATE. Sometimes writing data is slow. Take a look at your IO subsystem. Especially as your modifying the entire table.
5. Check your model again, do you really need two UPDATE's?
6. Check your model again, how are the cardinalities? Especially between Product and ProductAssembly? You may work on a too large intermediate result set.

The last point raises an architectural question: Why is this procedure necessary? How often is it executed? Can it be offloaded to triggers to keep the data in sync? So that you need to run it only once a day to be sure?

E.g. think about

DECLARE @ID TABLE (
    ID INT NOT NULL PRIMARY KEY
);

INSERT INTO @ID ( ID )
            SELECT CAST([value] AS INT)
            FROM   STRING_SPLIT(@SalesOrderLineIds, ',');

UPDATE PLL
SET    Quantity = SOL.Quantity * ISNULL(PLLP.Count, 1)
FROM   WMS.PickListLine PLL
       INNER JOIN @ID ID ON ID.ID = PLL.SalesOrderLineId
       INNER JOIN WMS.SalesOrderLine SOL ON SOL.Id = PLL.SalesOrderLineId
       INNER JOIN PIM.Product SOLP ON SOLP.Id = SOL.ProductId
       LEFT JOIN PIM.ProductAssembly PLLP ON PLLP.AssemblyProductId = PLL.ProductId;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
programmeristAuthor Commented:
Thank you ste%an. You are awesome with your great answers. You shot my issue from 12.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL SERVER 2017

From novice to tech pro — start learning today.