Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Optimizer and value of local variable. Expand / Collapse
Author
Message
Posted Sunday, October 7, 2012 8:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:31 AM
Points: 57, 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?
Post #1369537
Posted Sunday, October 7, 2012 11:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:36 AM
Points: 42,960, Visits: 36,114
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 2008, MVP
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

Post #1369549
Posted Monday, October 8, 2012 10:10 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:31 AM
Points: 57, 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!
Post #1369926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse