Sample Header Ad - 728x90

Slow Cross Apply Query Needs Optimization

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