I have recursive CTE query, it was taking 30s to execute, after investigation I found out that a column (
Name) with datatype
nvarchar(MAX) was the problem so I changed it to
nvarchar(1024). After which execution time reduced to 2s on DEV env. However, when I perform same alter on QA environment I don't see any improvement. Strange thing is that both servers are generating different execution plan, both server has same configuration, same schema. In fact DEV has more data.
WITH Ancestor (Id, TreeLevel, Branch, Name) AS ( SELECT I.Id, 0 AS TreeLevel, CAST(I.Name AS VARCHAR(1024)) AS Branch, I.Name FROM dbo.Items AS I WHERE (I.Parent_Id IS NULL) UNION ALL SELECT D.Id, A.TreeLevel + 1 AS TreeLevel, CAST(A.Branch + ' -> ' + CAST(D.Name AS VARCHAR(1024)) AS VARCHAR(1024)) AS Branch, D.Name FROM dbo.Items AS D INNER JOIN Ancestor AS A ON A.Id = D.Parent_Id WHERE (D.Parent_Id IS NOT NULL) ) SELECT A.Id, A.TreeLevel, A.Branch, A.Name FROM Ancestor AS A
Execution plan on DEV:
Execution plan on QA:
Is there a way to know why plans are different on both server, and how to force QA to follow same plan as DEV?