sql execution latency when assign to a variable

Viewed 130

The following query will be ran in about 22 seconds:

DECLARE @i INT, @x INT
SET    @i = 156567

SELECT 
TOP 1
    @x = AncestorId
FROM 
    dbo.tvw_AllProjectStructureParents_ChildView a
WHERE 
    ProjectStructureId = @i AND
        a.NodeTypeCode = 42 AND
        a.AncestorTypeDiffLevel = 1
OPTION (RECOMPILE)

The problem is with variable assignment (indeed this line: @x = AncestorId). when removing the assignment, it speeds up to about 15 miliseconds! I solved it with inserting the result to a temp table but I think it is a bad way.

Can anyone help me what the source of problem is?!

P.S.

bad Execution plan (22s) : https://www.brentozar.com/pastetheplan/?id=Sy6a4c9bW

good execution plan (20ms) :https://www.brentozar.com/pastetheplan/?id=Byg8Hc5ZZ

2 Answers
Related