0

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.

Query:

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:

enter image description here

Execution plan on QA:

enter image description here

Is there a way to know why plans are different on both server, and how to force QA to follow same plan as DEV?

  • 3
    Different data... different statistics... – Dale K May 28 at 1:19
  • @DaleK it's different plan to compute scale, not just different statistics, see the highlighted box in images – Sadiq Khoja May 28 at 1:25
  • 3
    Yeah, but data and statistics are what SQL Server used to build the execution plan... so with different data and different statistics you can indeed get a completely different plan. – Dale K May 28 at 1:27
  • 1
    as DaleK said, the tables and their stats will be different, and this is how the execution plan is built. how many rows do you have in the Items table on each environment? assuming the indexes are the same, the statistics and the row count will create the tipping point between index scans and seeks etc ... – Rodney Ellis May 28 at 2:00
  • 1
    Also: your question text says you changed nvarchar(max) to nvarchar(1024) but the code you posted uses varchar(1024). This could lead to loss of information because varchar data types cannot represent same range of characters as nvarchar data types. – AlwaysLearning May 28 at 3:09
1

Solved the problem by altering the index on parent_id

CREATE NONCLUSTERED INDEX [IX_Parent_Id] ON [Items] ([Parent_Id]) INCLUDE (Name) WITH DROP_EXISTING
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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