October 7, 2012 at 8:59 am
Let's take code example from this article (part Parameters and Variables):
CREATE PROCEDURE List_orders_1 AS
SELECT * FROM Orders WHERE OrderDate > '20000101'
go
...
CREATE PROCEDURE List_orders_3 @fromdate datetime AS
DECLARE @fromdate_copy datetime
SELECT @fromdate_copy = @fromdate
SELECT * FROM Orders WHERE OrderDate > @fromdate_copy
go
...
...
...
EXEC List_orders_1
...
EXEC List_orders_3 '20000101'
Yes, I understand that List_orders_1 much-much better, than List_orders_3. It's also clear for me why it's so - in the last case optimizer just ignore local variable @fromdate_copy and use very averaged statistic instead of "good form" statistic. It's all clear to me.
But WHY optimizer ignore local variables - that is the question!? WHY? Can't optimizer just "un-wrap" variable @fromdate_copy and "get" it's value '20000101'? Why not? You can say: "at the moment of compiling of List_orders_3 optimizer just don't know the value of @fromdate_copy". It's reasonable, so let's re-write the code:
DECLARE @fromdate_copy datetime
SET @fromdate_copy = '20000101'
SELECT * FROM Orders WHERE OrderDate > @fromdate_copy
Now what? Now optimizer for sure know the value of @fromdate_copy, it compile last batch as whole, all 3 commands at once. But it again ignore this value! And this moment is truly hard to me - why not to do standard parameter sniffing in the last batch?
October 7, 2012 at 11:45 am
No, it doesn't know the value, nor can it with the code you posted.
The optimiser does not and can not execute queries (that's what the query execution engine does). So at the point of compile (and compilation for the batch takes place before any of the statements in the batch get executed), that variable has no value and it does not get a value until execution time (which is after all the queries have been compiled). The optimiser can't execute that SET command and see what the value is, because it does not execute any SQL.
You can force a recompile (send the query back to the optimiser after part of the batch has been executed) with the recompile hint.
DECLARE @fromdate_copy datetime
SET @fromdate_copy = '20000101'
SELECT * FROM Orders WHERE OrderDate > @fromdate_copy OPTION (RECOMPILE)
That means that part way through execution, after the variable has a value, the query will be sent back to the optimiser to be recompiled, and since at that point the variable has a value, the optimiser can optimise based on that value.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2012 at 10:10 am
It was just outstanding explanation! Now the whole picture is much clearer for me, I see where my mistake was.
A thousand thanks for your help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply