Delay sql execution on variable assignment
The following request will run 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 in the assignment to the variable (really, this line:) @x = AncestorId
. when deleting a job, it speeds up to about 15 milliseconds! I solved it by inserting the result into a temporary table, but I think this is a bad way.
Can anyone help me on what is the source of the problem ?!
PS
bad Execution plan (22 s ): https://www.brentozar.com/pastetheplan/?id=Sy6a4c9bW
good execution plan (20 ms ): https://www.brentozar.com/pastetheplan/?id=Byg8Hc5ZZ
source to share
When using OPTION (RECOMPILE)
SQL Server, can usually perform parameter injection optimization .
The plan it compiles is one-time, so it can sniff the values of all variables and parameters and treat them as constants.
A trivial example showing the optimization of parameter injection in action and the effect of variable assignment below (actual execution plans are not evaluated).
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 that the middle part doesn't even touch the table at all, as SQL Server might infer at compile time that it @A > @B
doesn't true
. But Plan 3 reverts to including the table in the plan, because the variable assignment clearly prevents the effect OPTION (RECOMPILE)
shown in Plan 2.
(As an aside, the third plan is not 4-5 times more expensive than the first. Variable assignment also seems to overwhelm the usual row chaining logic where the overhead of an index scan would be reduced to reflect TOP 1
)
In your good plan, the value @i
156567
is put right into the search in the anchor part of the recursive CTE, it returns 0 rows, and so the recursive part shouldn't work.
In your bad plan, the recursive CTE materializes completely with 627,393 recursive subtree execution, and finally the predicate is applied to 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 variable down. You have not provided your table definitions - or the view with recursive CTE. There is a similar problem with predicate functions, views and windows though .
One solution would be to change the view to a table-valued inline function that takes a parameter for mainid and then add that to the clause WHERE
in the anchor part of the definition. Instead of relying on SQL Server for the predicate to outperform you.
source to share
The difference is probably from SELECT TOP 1.
If you only have a field, SQL Server will only take the first row. When you have a variable assignment, SQL Server retrieves all results, but only uses the top one.
I have tested different queries and this is not always the case, but probably the SQL Server optimizations failed here due to the complexity of the views / tables.
You can try the 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)
source to share