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

programmerist
programmerist used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Developer
Commented:
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

Author

Commented:
Thank you ste%an. You are awesome with your great answers. You shot my issue from 12.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial