bits here and there ...: Performance improvement on sp_WHSOnHand / sp

These two stored procedure are generated by AX during synchronization (there're others as well, but not the focus of this post).

- sp_WHSOnHand

- sp_WHSOnHandWithDelta

In our environment (with certain amount of data), it tooks around 250ms to execute, although this number looks small, but when you have 100 of them being executed, that's 25,000ms (25 seconds), it is a different story then. A wait of 25s isn't something nice for the user.

Analyzing the query plan shows that it starts from InventDim, getting a large number of records and then nested loop to WHSInventReserve and so on. Looking at the thickness of the arrow next to InventDim, that's an indicator that a large number of rows are returned from that operator. That means it is operating that large number of rows against the WHSInventReserve.

This is the major bottleneck of the query.

We've tried update statistics and reindex and clear the cache to make sure it generate a new plan, but it still generate the same execution plan.

Due to this stored procedure has an ItemId provided, it should limit the number of rows from the WHSInventReserve, using this limited number of rows and nested loop against the InventDim would be much quicker, but somehow the optimizer didn't think so.

Bad query plan

To resolve this problem, I'm splitting this query into two parts.

First, filter the WHSInventReserve with the ItemId provided and insert it into a table variable.

Then join this table variable to the InventDim. Although the sum of the estimated subtree cost of the original query and the new 2-parts query is similar, but it runs much quicker than the original version.

Better query plan

With this new change, the stored procedure completed in 7ms instead of 250ms.

Original query join

New query join

The stored procedure should be changed from within AX at the 'WHSOnHandSPHelper' class. When synchronization is run, it regenerate this stored procedure. It also regenerate when stock dimension setup is changed.

Below is a sample of complete stored procedure code generated by the 'WHSOnHandSPHelper' class (Do take note, the selection field changed based on the stock dimension setup).

=================================================

CREATE PROCEDURE [dbo].[sp_WHSOnHand]

@PARTITION bigint,

@LEVEL int,

@DATAAREAID nvarchar (4),

@ITEMID nvarchar (20),

@INVENTSITEID nvarchar (10) = NULL,

@INVENTSITEIDLEVEL int = 1024,

@INVENTLOCATIONID nvarchar (10) = NULL,

@INVENTLOCATIONIDLEVEL int = 1024,

@WMSLOCATIONID nvarchar (10) = NULL,

@WMSLOCATIONIDLEVEL int = 1024,

@WMSPALLETID nvarchar (18) = NULL,

@WMSPALLETIDLEVEL int = 1024,

@INVENTSTATUSID nvarchar (10) = NULL,

@INVENTSTATUSIDLEVEL int = 1024,

@LICENSEPLATEID nvarchar (25) = NULL,

@LICENSEPLATEIDLEVEL int = 1024,

@INVENTBATCHID nvarchar (20) = NULL,

@INVENTBATCHIDLEVEL int = 1024,

@INVENTSERIALID nvarchar (20) = NULL,

@INVENTSERIALIDLEVEL int = 1024,

@INVENTPROFILEID_RU nvarchar (10) = NULL,

@INVENTPROFILEID_RULEVEL int = 1024,

@INVENTOWNERID_RU nvarchar (20) = NULL,

@INVENTOWNERID_RULEVEL int = 1024,

@INVENTGTDID_RU nvarchar (30) = NULL,

@INVENTGTDID_RULEVEL int = 1024,

@CONFIGID nvarchar (10) = NULL,

@INVENTSIZEID nvarchar (10) = NULL,

@INVENTCOLORID nvarchar (10) = NULL,

@INVENTSTYLEID nvarchar (10) = NULL,

-- Output parameters

@AVAILPHYSICAL numeric(32, 16) OUTPUT,

@AVAILORDERED numeric(32, 16) OUTPUT

-- Without nocount on the procedure will return number of affected rows in the result set which is

-- harmful to performance and requires us to adjust field retrieval when calling the stored procedure

SET NOCOUNT ON;

-- We need to supress warnings when trying to aggregate NULL columns. This is a valid business scenario

-- and happens e.g. for a non-LP controlled location with batch below item.

SET ANSI_WARNINGS OFF;

-- The @RESULT table variable is used to store intermediate quantities.

DECLARE @RESULT TABLE(

AVAILPHYSICALRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

AVAILORDEREDRESULT numeric(32, 16) NOT NULL, -- Aggregated minimum qtys from lower level in @RESULT

AVAILPHYSICALFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

AVAILORDEREDFOUND numeric(32, 16), -- Aggregated qty for current level in WHSInventReserve

HIERARCHYLEVEL int NOT NULL,

CONFIGID nvarchar (10) NULL,

INVENTSIZEID nvarchar (10) NULL,

INVENTCOLORID nvarchar (10) NULL,

INVENTSTYLEID nvarchar (10) NULL,

INVENTSITEID nvarchar (10) NULL,

INVENTLOCATIONID nvarchar (10) NULL,

WMSLOCATIONID nvarchar (10) NULL,

WMSPALLETID nvarchar (18) NULL,

INVENTSTATUSID nvarchar (10) NULL,

LICENSEPLATEID nvarchar (25) NULL,

INVENTBATCHID nvarchar (20) NULL,

INVENTSERIALID nvarchar (20) NULL,

INVENTPROFILEID_RU nvarchar (10) NULL,

INVENTOWNERID_RU nvarchar (20) NULL,

INVENTGTDID_RU nvarchar (30) NULL);

DECLARE @DIMLIST TABLE(

INVENTDIMID nvarchar (20) NULL,

AVAILPHYSICAL numeric(32, 16) NOT NULL,

AVAILORDERED numeric(32, 16) NOT NULL);

-- The algorithm of the stored procedure is as follows:

-- 1. Initialize the @RESULT table with aggregated qtys in WHSInventReserve for the level requested by user

-- 2. Traverse the hierarchy up and for each level:

-- 1) Aggregate qtys from previous level in @RESULT grouping by dimensions of current hierarchy level

-- 2) Aggregate qtys from current level in WHSInventReserve grouping by dimensions of current hierarchy level

-- 3) Select the minimum of the above qtys and insert into @RESULT

-- 3. When hierarchy level 0 is reached, return the sum of aggregated qtys in @RESULT

-- In the original query (the one after below query), it is joining InventDim and WHSInventReserve and filter records based on some criteria.

-- But in the execution plan, it doesn't seems to generate a good plan (even after UPDATE STATISTICS). In the original execution plan, it always

-- Get records from InventDim first, which returns lot of rows, then only join it to WHSInventReserve and continue filter from there.

-- If we makes it get records from WHSInventReserve first, filter it, then with lesser rows from here, join it to InventDim, it will be quicker.

-- So the optimization is to filter WHSInventResreve first, then use this result at the original query. In the test, this has reduce the execution

-- time from ~250ms to ~7ms

INSERT INTO @DIMLIST(INVENTDIMID, AVAILPHYSICAL, AVAILORDERED)

SELECT INVENTDIMID, AVAILPHYSICAL, AVAILORDERED

FROM WHSINVENTRESERVE WHSINVENTRESERVE

WHERE WHSINVENTRESERVE.ITEMID = @ITEMID

AND WHSINVENTRESERVE.PARTITION = @PARTITION

AND WHSINVENTRESERVE.DATAAREAID = @DATAAREAID

AND WHSINVENTRESERVE.HIERARCHYLEVEL = @LEVEL

-- Initialize the @RESULT variable.

-- Loads aggregated data for the lowest level requested by user.

INSERT INTO @RESULT (AVAILPHYSICALRESULT, AVAILORDEREDRESULT, AVAILPHYSICALFOUND, AVAILORDEREDFOUND, HIERARCHYLEVEL, CONFIGID, INVENTSIZEID, INVENTCOLORID, INVENTSTYLEID, INVENTSITEID, INVENTLOCATIONID, WMSLOCATIONID, WMSPALLETID, INVENTSTATUSID, LICENSEPLATEID, INVENTBATCHID, INVENTSERIALID, INVENTPROFILEID_RU, INVENTOWNERID_RU, INVENTGTDID_RU)

SELECT SUM(DIMLIST.AVAILPHYSICAL),

SUM(DIMLIST.AVAILORDERED),

NULL,

@LEVEL,

INVENTDIM.CONFIGID,

INVENTDIM.INVENTSIZEID,

INVENTDIM.INVENTCOLORID,

INVENTDIM.INVENTSTYLEID,

CASE WHEN @LEVEL + -1 >= @INVENTSITEIDLEVEL THEN INVENTDIM.INVENTSITEID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTLOCATIONIDLEVEL THEN INVENTDIM.INVENTLOCATIONID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @WMSLOCATIONIDLEVEL THEN INVENTDIM.WMSLOCATIONID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @WMSPALLETIDLEVEL THEN INVENTDIM.WMSPALLETID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTSTATUSIDLEVEL THEN INVENTDIM.INVENTSTATUSID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @LICENSEPLATEIDLEVEL THEN INVENTDIM.LICENSEPLATEID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTBATCHIDLEVEL THEN INVENTDIM.INVENTBATCHID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTSERIALIDLEVEL THEN INVENTDIM.INVENTSERIALID ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTPROFILEID_RULEVEL THEN INVENTDIM.INVENTPROFILEID_RU ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTOWNERID_RULEVEL THEN INVENTDIM.INVENTOWNERID_RU ELSE NULL END,

CASE WHEN @LEVEL + -1 >= @INVENTGTDID_RULEVEL THEN INVENTDIM.INVENTGTDID_RU ELSE NULL END

FROM INVENTDIM INVENTDIM

JOIN @DIMLIST DIMLIST

ON INVENTDIM.INVENTDIMID = DIMLIST.INVENTDIMID

WHERE INVENTDIM.DATAAREAID = @DATAAREAID

AND INVENTDIM.PARTITION = @PARTITION

AND (@CONFIGID IS NULL OR INVENTDIM.CONFIGID = @CONFIGID)

AND (@INVENTSIZEID IS NULL OR INVENTDIM.INVENTSIZEID = @INVENTSIZEID)

AND (@INVENTCOLORID IS NULL OR INVENTDIM.INVENTCOLORID = @INVENTCOLORID)

AND (@INVENTSTYLEID IS NULL OR INVENTDIM.INVENTSTYLEID = @INVENTSTYLEID)

AND (@LEVEL < @INVENTSITEIDLEVEL OR @INVENTSITEID IS NULL OR @INVENTSITEID = INVENTDIM.INVENTSITEID)

AND (@LEVEL < @INVENTLOCATIONIDLEVEL OR @INVENTLOCATIONID IS NULL OR @INVENTLOCATIONID = INVENTDIM.INVENTLOCATIONID)

AND (@LEVEL < @WMSLOCATIONIDLEVEL OR @WMSLOCATIONID IS NULL OR @WMSLOCATIONID = INVENTDIM.WMSLOCATIONID)

AND (@LEVEL < @WMSPALLETIDLEVEL OR @WMSPALLETID IS NULL OR @WMSPALLETID = INVENTDIM.WMSPALLETID)

AND (@LEVEL < @INVENTSTATUSIDLEVEL OR @INVENTSTATUSID IS NULL OR @INVENTSTATUSID = INVENTDIM.INVENTSTATUSID)

AND (@LEVEL < @LICENSEPLATEIDLEVEL OR @LICENSEPLATEID IS NULL OR @LICENSEPLATEID = INVENTDIM.LICENSEPLATEID)

AND (@LEVEL < @INVENTBATCHIDLEVEL OR @INVENTBATCHID IS NULL OR @INVENTBATCHID = INVENTDIM.INVENTBATCHID)

AND (@LEVEL < @INVENTSERIALIDLEVEL OR @INVENTSERIALID IS NULL OR @INVENTSERIALID = INVENTDIM.INVENTSERIALID)

AND (@LEVEL < @INVENTPROFILEID_RULEVEL OR @INVENTPROFILEID_RU IS NULL OR @INVENTPROFILEID_RU = INVENTDIM.INVENTPROFILEID_RU)

AND (@LEVEL < @INVENTOWNERID_RULEVEL OR @INVENTOWNERID_RU IS NULL OR @INVENTOWNERID_RU = INVENTDIM.INVENTOWNERID_RU)

AND (@LEVEL < @INVENTGTDID_RULEVEL OR @INVENTGTDID_RU IS NULL OR @INVENTGTDID_RU = INVENTDIM.INVENTGTDID_RU)

GROUP BY