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