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 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]
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 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]
Asked by FlyFish
(109 rep)
May 11, 2023, 11:24 AM
Last activity: Jul 16, 2025, 10:06 PM
Last activity: Jul 16, 2025, 10:06 PM