XML path running slow

Question

Post reply

XML path running slow

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
January 12, 2024 at 4:12 pm

#4360856

This is the sub query being used in the join and it's timing out. Any suggestion to rewrite to make it faster would be of big help. Plan attached as well.
SELECT pr.col1, LEFT(pr.Name,Len(pr.Name)-1) ProgramName
FROM (SELECT DISTINCT pr2.col1,
( SELECT
CASE
WHEN pr1.ProgramType='a' THEN 'b'
WHEN pr1.ProgramType='c' THEN 'd'
WHEN pr1.ProgramType='e' THEN 'f'
WHEN pr1.ProgramType='g' THEN 'h'
ELSE pr1.ProgramType
END + ';' AS [text()]
FROM SYN.Table1 pr1
WHERE pr1.col1 = pr2.col1
AND pr1.col2IS NULL
ORDER BY pr1.col1
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') ProgramName
FROM SYN.Table2 pr2
WHERE pr2.col2 IS NULL
) pr

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

Don't see a plan, but you could try this, in case SQL's having to do a full scan of SYN.table1 now:

CREATE NONCLUSTERED INDEX [table1__IX_col2] ON SYN.table1 (col2, col1) INCLUDE (ProgramType);

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

And possibly likewise for SYN.Table2:

CREATE NONCLUSTERED INDEX [table2__IX_col2] ON SYN.table2 (col2, col1) ;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 3

Since you are using SQL Server 2019 you can use STRING_AGG

SELECT DISTINCT 
       pr2.col1,
       ISNULL(STRING_AGG(CASE 
                             WHEN pr1.ProgramType = 'a' THEN 'b'
                             WHEN pr1.ProgramType = 'c' THEN 'd'
                             WHEN pr1.ProgramType = 'e' THEN 'f'
                             WHEN pr1.ProgramType = 'g' THEN 'h'
                             ELSE pr1.ProgramType
                         END, ';'
                        ) WITHIN GROUP (ORDER BY pr1.col1), '' ) AS ProgramName
  FROM SYN.Table2 pr2
  LEFT JOIN SYN.Table1 pr1 
         ON pr1.col1 = pr2.col1 
        AND pr1.col2 IS NULL
 WHERE pr2.col2 IS NULL
 GROUP BY pr2.col1
;