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))
    DECLARE @StartPointer INT, @EndPointer INT
    SELECT @StartPointer = 1, @EndPointer = CHARINDEX(',', @StringList)
    WHILE (@StartPointer < LEN(@StringList) + 1) 
        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

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
  • What is SplitParmList? Can you include the definition of the function? Why aren't you using STRING_SPLIT or (probably better) a table type parameter?
    – Thom A
    May 11 at 11:25
  • "The Data table is indexed" Without knowing the definition of the index(es) this doesn't mean a lot; a single clustered index on Effectivity is unlikely to help, for example. Can you add their definitions for the question to please.
    – Thom A
    May 11 at 11:27
  • 2
    That function is going to be terrible for performance. Multi-line table value functions are known to perform poorly as are WHILE loops, you are using both and have multiple calls to the function; that would bring any well written query to a very slow slog. Why aren't you using STRING_SPLIT or a table type parameter, as mentioned?
    – Thom A
    May 11 at 11:57
  • Is that your only index? That doesn't look like a good Clustered Index candidate to me; I doubt that all those columns will be always ascending which could mean you suffer fragmentation pretty quickly. What nonclustered indexes do you have?
    – Thom A
    May 11 at 11:57
  • @Larnu I wasn't aware of STRING_SPLIT. This function was created a long time ago, and we have just be reusing it. As for indexes, that is the only one on the table.
    – FlyFish
    May 11 at 12:06

1 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.

  • I tried table variables and it increased processing time. I'm confused about using TVPs. Are these persistent in the database? Would I need one created for every input parameter? How would I populate them from the input parameters?
    – FlyFish
    May 11 at 15:40
  • You only need one table type for every possible data type, for example StringList and NStringList and IntList. You need to pass them through from your client application as table valued parameters May 11 at 15:41
  • If I understand you, I need to 1. Create the TVP's in the database. 2. Add a declaration of the TVP to the SP (ie. @projects StringList). 3.Pass a table to the StringList typed parameter (i.e. I'm using Angular so, projects: this.criteria.Project != '' ? this.criteria.Project : 'null',). Is this going to be any faster than using table variables populated from the Split_String?
    – FlyFish
    May 11 at 15:50
  • Yes you understand the steps correct (but the TVP is the parameter, whose type is a table type). Probably faster, but depends how good the Angular SQL Server driver is. Even if you use table variables, make sure they are indexed (you can just define them like this DECLARE @Project dbo.StringList;) May 11 at 15:55
  • Thanks. I saw your other comment above. I need to do some more testing.
    – FlyFish
    May 11 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.