SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Optimizer and value of local variable.


Query Optimizer and value of local variable.

Author
Message
Shcherbunov Neil
Shcherbunov Neil
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 278
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228151 Visits: 46339
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Shcherbunov Neil
Shcherbunov Neil
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 278
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search