I need to run a query that filters by arbitrary parameters supplied in a comma separated list. I use DelimitedSplit8K.
Currently, I am doing something like this. Though it is on a substantially larger dataset.
It works, but when the comma-separated list supplied is greater than 5 parameters it really slows things down. When it is greater than 20 parameters, it is nearly unusable.
Assuming we're talking about millions of rows, is there ever a way to filter by a comma-separated-list efficiently?
Would you change anything about the query itself?
I use TVFs which inherit from other TVFs. Is there a performance difference between passing the raw @skuList from the parent TVF to the child & letting the child TVF filter VS running the child TVF without filters & letting the parent filter at the end?
-- Prepare Temporary table with dummy data
CREATE TABLE #product (id nvarchar(100), dateadded datetime default(getdate()));
INSERT INTO #product (id) VALUES
-- Query data DECLARE @skuList NVARCHAR(MAX) = 'AARCONFETTI,97-9F6J-H7MU,1592' SELECT [bp].* FROM #product bp FULL JOIN [MyDB].[dbo].[DelimitedSplit8K](@skuList,',') asl ON LTRIM(RTRIM([asl].[item])) = LTRIM(RTRIM([bp].[ID])) WHERE 1=1 AND [asl].[Item] IS NOT NULL