sql execution latency when assign to a variable

Question

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

That's quite interesting. Assigning to a variable seems to disable the parameter embedding optimization that you get with `OPTION (RECOMPILE)`. You can change `dbo.tvw_AllProjectStructureParents_ChildView` to an inline TVF and pass a parameter in to avoid having to use `OPTION (RECOMPILE)` at all. — Martin Smith, May 30 '17 at 07:26
Is @i value same in both cases? How many times you've tested this behaviour? I think you have problem with your indexes. Sometimes query optimazer takes "bad" plans and it can be a coincidence — Mikhail Lobanov, May 30 '17 at 07:43

score 4 · Accepted Answer · answered May 30 '17 at 17:31

When you use OPTION (RECOMPILE) SQL Server can generally perform parameter embedding optimisation.

The plan it is compiling is single use so it can sniff the values of all variables and parameters and treat them as constants.

A trivial example showing the parameter embedding optimisation in action and the effect of assigning to a variable is below (actual execution plans not estimated).

DECLARE @A INT = 1, 
        @B INT = 2,
        @C INT;

SELECT TOP (1) number FROM master..spt_values WHERE @A > @B;
SELECT TOP (1) number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);
SELECT TOP (1) @C = number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);

The plans for this are below

Note the middle one does not even touch the table at all as SQL Server can deduce at compile time that @A > @B is not true. But plan 3 is back to including the table in the plan as the variable assignment evidently prevents the effect of OPTION (RECOMPILE) shown in plan 2.

(As an aside the third plan is not really 4-5 times as expensive as the first. Assigning to a variable also seems to suppress the usual row goal logic where the costs of the index scan would be scaled down to reflect the TOP 1)

In your good plan the @i value of 156567 is pushed right into the seek in the anchor leg of the recursive CTE, it returned 0 rows and so the recursive part had to do no work.

In your bad plan the recursive CTE gets fully materialised with 627,393 executions of the recursive sub tree and finally the predicate is applied on the resulting 627,393 rows (discarding all of them) at the end

I'm not sure why SQL Server can't push the predicate with a variable down. You haven't supplied the definitions of your tables - or the view with the recursive CTE. There is a similar issue with predicate pushing, views, and window functions though.

One solution would be to change the view to an inline table valued function that accepts a parameter for mainid and then add that in to the WHERE clause in the anchor part of the definition. Rather than relying on SQL Server to push the predicate down for you.

score -2 · Answer 2 · answered May 30 '17 at 07:58

The difference comes probably from SELECT TOP 1.

When you have only field, SQL Server will take only first row. When you have variable assignment SQL Server is fetching all results but use only the top one.

I checked on different queries and it is not always a case, but probably here SQL Server optimization fails because of complexity of views/tables.

You can try following workaround:

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

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

sql execution latency when assign to a variable

2 Answers2

Linked