Most efficient way to filter by a text-based list?

SQL Server Administration
2

Any function against the column will kill performance. You have to clean up the list prior to executing the split. Roughly something like below. Try running this command first, then doing the split without any functions, as shown.

Also, for best overall performance, uniquely cluster the #product table on the "id" column (if there really is a temp table in use: if that's just a stand-in for the real non-temp table, then please ignore these comments). It may not help, but it could, and it certainly won't hurt.

DECLARE @skuList NVARCHAR(MAX)	= 'AARCONFETTI,97-9F6J-H7MU,1592,...'

/*remove any spaces in @skuList, ok assuming no SKU has an embedded space (seems
  right to me); but, if any SKU has embedded space, we'll have to change the code!*/
SELECT @skuList = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(LTRIM(RTRIM(@skuList)), SPACE(33), SPACE(1)), SPACE(17), 
    SPACE(1)), SPACE(9), SPACE(1)), SPACE(5), SPACE(1)), SPACE(3), SPACE(1)), 
    SPACE(2), SPACE(1)), SPACE(2), SPACE(1)), SPACE(1), '')

SELECT 
	[bp].*
FROM #product bp
FULL JOIN [MyDB].[dbo].[DelimitedSplit8K](@skuList,',') asl
	ON [asl].[item] = [bp].[ID]
WHERE 1=1
	AND [asl].[Item] IS NOT NULL
1 Like