Slow Cross Apply Query Needs Optimization

Question

I have a stored procedure which takes about 4 min to complete. I added some logging and discovered that the reason it is slow is the Cross Apply queries, of which there are 5. Each one takes about 40 seconds to complete. I'm sure there are ways to improve the performance, which I'm hoping someone can help me with. Here is the query:

update @Data1
set Open = b.Open_Sum
from @Data1 a
cross apply (Select Count(*) as Open_Sum
from [Data] c
where (c.Sunday > a.Week or c.Sunday is Null)
and c.Project = a.Project
and c.Item = a.Item
and c.IPT = a.IPT
and c.Product = a.Product
and c.Center = a.Center
and c.Name = a.Name
and c.Project  in (select * from SplitParmList(@Project ))
and c.Product in (select * from SplitParmList(@Product  ))
and c.Item in  (select * from SplitParmList(@Item ))
and c.Area in  (select * from SplitParmList(@Area ))
and c.IPT in  (select * from SplitParmList(@IPT ))
and c.Name in  (select * from SplitParmList(@Name ))
and c.Center in  (select * from SplitParmList(@Center ))
and c.Effectivity  in  (select * from SplitParmList(@Effectivity))
and c.Planned in (select * from SplitParmList(@Planned))
and CURRENT = 'Y'
) as b

Query Explanation:

1. Populates a table variable (@Data1) with the aggregate count, in this case records still open
2. The SplitParmList is a function that parses a parameter passed into the SP. ie. ('a,b,c,d,e')
3. The Data table is indexed

I'm looking for suggestions that may help speed this query up.

As requested, here is the SplitParmList function:

CREATE FUNCTION [dbo].[SplitParmList] (@StringList VARCHAR(MAX))

RETURNS @TableList TABLE( StringLiteral VARCHAR(128))
BEGIN
    DECLARE @StartPointer INT, @EndPointer INT
    SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)
    WHILE (@StartPointer < LEN(@StringList) + 1) 
      BEGIN
        IF @EndPointer = 0 
        SET @EndPointer = LEN(@StringList) + 1
        INSERT INTO @TableList (StringLiteral)
        VALUES(LTRIM(RTRIM(SUBSTRING(@StringList, @StartPointer, @EndPointer - @StartPointer))))
        SET @StartPointer = @EndPointer + 1
        SET @EndPointer = CHARINDEX(',', @StringList, @StartPointer)
      END -- WHILE
    RETURN
END -- FUNCTION

Here is the index:

CREATE CLUSTERED INDEX [ClusteredIndex-20210222-092308] ON [dbo].[Data]
(
  [Name] ASC,
  [Center] ASC,
  [Project] ASC,
  [Effectivity] ASC,
  [Product] ASC,
  [Drawing] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [dbo]

Answer 1

You should use table-valued parameters instead of string-splitting.

First declare a table type, note that it should have a primary key. I usually keep a few standard one-column ones.

CREATE TYPE dbo.StringList (value varchar(100) PRIMARY KEY);

Then the subqueries get transfomred into (select value FROM @product) etc. You should also use more obvious table aliases, and use the correct alias on the first line.

Ensure the column in your table type matches the column name that you are querying, and that includes varchar lengths.

update d1
set Open = d.Open_Sum
from @Data1 d1
cross apply (
  Select Count(*) as Open_Sum
  from Data d
  where (d.Sunday > d1.Week or d.Sunday is Null)
  and d.Project = d1.Project
  and d.Item = d1.Item
  and d.IPT = d1.IPT
  and d.Product = d1.Product
  and d.Center = d1.Center
  and d.Name = d1.Name
  and d.Project  in (select value FROM @Project )
  and d.Product in (select value FROM @Product)
  and d.Item in (select value FROM @Item )
  and d.Area in (select value FROM @Area )
  and d.IPT in  (select value FROM @IPT )
  and d.Name in  (select value FROM @Name )
  and d.Center in  (select value FROM @Center )
  and d.Effectivity in (select value FROM @Effectivity)
  and d.Planned in (select value FROM @Planned)
  and d.CURRENT = 'Y'
) as d

You are also going to want to sort out the indexing on both Data and @Data1 but it's hard to say what you need without a query plan.

If you still want string-splitting, then just create table variables from the table types, and insert using the STRING_SPLIT function.