Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Variable vs String Equivalent


T-SQL Variable vs String Equivalent

Author
Message
sqlpadawan_1
sqlpadawan_1
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 1027
Got a question with some coding standards. There are developers declaring a variable to a constant value and using that in multiple places in a stored proc rather that using the string equivalent.

For example:


DECLARE @Yes VARCHAR(3) = 'Yes'

SELECT [pk_value]
,[field_value]
FROM [tempdb].[dbo].[table7]
WHERE [field_value] = @Yes;



rather than:


SELECT [pk_value]
,[field_value]
FROM [tempdb].[dbo].[table7]
WHERE [field_value] = 'Yes';



I setup a quick and dirty example and ended up with the example below, the same execution plan was generated, but query using the variable was slower, using more CPU.



Is there any scenario out there where a variable would be faster than the string equivalent?

Thanks,
Kevin
j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
Hi SQL Padawan,

I coded up your example to explain the difference between estimated execution plans and actual I/O statistics.

Both of your statements use the idx_field_value in the query plan.

In my example below, I could not get the query analyzer to use the this index due to the amount of data in the table (2 records).

It performs a full table scan (FTS).


Here are some things to note.

1 - The plan with a constant will probably be re-used. This is due to the fact that we know what the value is.

On the other hand, this may lead to parameter sniffing.


2 - The plan with the variable will be recompiled every time. This is due to the fact it might change.

This is even more likely if the input parameter is set to a local variable before executing. And the local variable is used in the where clause.


Please see Grant Fritchey query plan e-book from simple talk.


I usually look at time and I/O when running a query. The below TSQL with turn these counters on in the query window.

-- Show time & i/o
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO


I usually clear the buffers and plan cache in test so that stored plans do not skew the results.


-- Remove clean buffers & clear plan cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO


My simple execution using two records show that the non-parameter solution is 1 ms quicker than the one with a variable.

Both have the same page scans (logical and physical).

Please redo clearing the buffers and cache and post the results for I/O.

If they are almost the same, there is really not difference between the two constructs other than possibly removing parameter sniffing.

Sincerely

John

PS: I hope this post helps you out!

w00t



--
-- Sample code
--

-- Create temp table
create table tempdb.dbo.table7
(
[pk_value] int,
[field_value] varchar(10)
);

-- Create NC index
create nonclustered index idx_field_value on tempdb.dbo.table7 ([field_value]);

-- Insert two rows
insert into tempdb.dbo.table7 values
(1, 'Yes'),
(2, 'No');

-- Update the stats
update statistics tempdb.dbo.table7;
go

-- Show the data
select * from tempdb.dbo.table7

-- Show time & i/o
SET STATISTICS TIME ON
SET STATISTICS IO ON
GO

-- Remove clean buffers & clear plan cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- Declare local variable
declare @Yes varchar(3) = 'Yes'

-- First way
select
[pk_value]
,[field_value]
from tempdb.dbo.table7
where [field_value] = @Yes;


/*
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 18 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'table7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 14 ms.


*/


-- Remove clean buffers & clear plan cache
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

-- Second way
select
[pk_value]
,[field_value]
FROM tempdb.dbo.table7
WHERE [field_value] = 'Yes';


/*

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 22 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'table7'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 13 ms.

*/

John Miner
Crafty DBA
www.craftydba.com
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
j.miner (4/8/2013)
2 - The plan with the variable will be recompiled every time. This is due to the fact it might change.

This is even more likely if the input parameter is set to a local variable before executing. And the local variable is used in the where clause.


Not true.

Variables do not cause recompilation (nor do constants or parameters). The plan will be cached and reused just as any other query, in fact, the caching will be exactly the same as the version of the query with the constant in it.

What causes recompilation:
The OPTION (RECOMPILE) hint (actually causes compile, not recompile)
The WITH RECOMPILE procedure option (actually causes compile, not recompile)
A change in statistics
A modification to an object that the query is dependent on.
Rebuilding an index on a table the query uses.
Clearing the plan cache (actually causes compile, not recompile)

See http://sqlinthewild.co.za/index.php/2011/08/16/compiles-and-recompiles/ for what causes compiles and recompiles.


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


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
Basically the difference between the two comes down to parameter sniffing. At compile time the optimiser can see the value of the constant and can compile a query plan optimal for that value. This is called parameter sniffing. It is a Good Thing (most of the time).

When using a variable, the optimiser cannot sniff the value at compile time (the variable has no value at compile time) and hence the optimiser cannot use the value of the variable to generate a plan optimal for that value. It generates a more generic plan. In some cases this generic plan will be less optimal than the plan compiled with parameter sniffing.

The other side of the story however is that a generic plan may be better for reuse than an optimal plan, if the number of rows that the query affects can change radically between multiple executions. This is because in both cases the plans will be cached and reused.

Have a read through these, they're not as detailed as they could be, very old posts. If you want more information, google "Grant Frichey" "parameter sniffing", you'll find some of his articles on the subject.
http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/
http://sqlinthewild.co.za/index.php/2008/05/22/parameter-sniffing-pt-3/


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


JackIntheBox
JackIntheBox
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 1031
A great article that could be added to this behavior is from Erland SommarsKog. Also as John mentioned, the above query may have been added by developers to avoid Parameter sniffing.

http://www.sommarskog.se/query-plan-mysteries.html
sqlpadawan_1
sqlpadawan_1
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 1027
Thanks all for the replies.

To clarify a couple of points. The variable is being used as a constant in the code. @Yes will always equal 'Yes'. Rather than have a type-o, the developer will declare @Yes at the top of the code and use it rather than typing 'Yes' multiple times in the code. Apply this logic to a more complex example, and hopefully you get the idea.

Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?

The query in question can run multiple times a second, I was simply looking at the performance of using @Yes vs 'Yes', a quick and dirty adhoc looking at the cost per batch looks like the variable option is more expensive.


Here's the entire script to recreate the results:

/*

USE tempdb
GO

IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[table7]')
AND type IN (N'U')
)
DROP TABLE [dbo].[table7]
GO

CREATE TABLE dbo.table7 (
pk_value INT IDENTITY(1, 1) PRIMARY KEY
,field_value VARCHAR(255)
);
GO

INSERT INTO table7 (field_value)
VALUES ('Yes');
GO 5000

INSERT INTO table7 (field_value)
VALUES ('No');
GO 15000

IF EXISTS (
SELECT *
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[dbo].[table7]')
AND NAME = N'IX_field_value'
)
DROP INDEX [IX_field_value] ON [dbo].[table7]
WITH (ONLINE = OFF)
GO

CREATE NONCLUSTERED INDEX [IX_field_value] ON [dbo].[table7] ([field_value] ASC)
WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,SORT_IN_TEMPDB = OFF
,IGNORE_DUP_KEY = OFF
,DROP_EXISTING = OFF
,ONLINE = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
GO

*/

/*

USE tempdb
GO

--Include Actual Execution Plan

DECLARE @Yes VARCHAR(3) = 'Yes'

SELECT [pk_value]
,[field_value]
FROM [tempdb].[dbo].[table7]
WHERE [field_value] = @Yes;
GO

SELECT [pk_value]
,[field_value]
FROM [tempdb].[dbo].[table7]
WHERE [field_value] = 'Yes';
GO

*/


GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47299 Visits: 44392
sqlpadawan_1 (4/8/2013)
To clarify a couple of points. The variable is being used as a constant in the code. @Yes will always equal 'Yes'. Rather than have a type-o, the developer will declare @Yes at the top of the code and use it rather than typing 'Yes' multiple times in the code. Apply this logic to a more complex example, and hopefully you get the idea.


That is going to harm performance. I suggest you head that one off as soon as possible, get them using string literals, not variables

Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?


Correct on the caching and reuse. Parameter sniffing won't come into effect because the optimiser can't sniff the value of variables, this will result in generic plans which are likely to perform worse than an optimal plan would. See the second of the links I posted.


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


j.miner
j.miner
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 358
Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?


If the value never changes in your where clause, you have some really boring code?

:-P


Again, it all depends upon performance. That is why I suggested looking at run time and i/o. If you can live with a generic plan that uses variables, why not?

John Miner
Crafty DBA
www.craftydba.com
sqlpadawan_1
sqlpadawan_1
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 1027
Thank you all for taking time to reply, I appreciate all of the input.
sqlpadawan_1
sqlpadawan_1
SSC-Enthusiastic
SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)SSC-Enthusiastic (102 reputation)

Group: General Forum Members
Points: 102 Visits: 1027
j.miner (4/8/2013)
Maybe I'm misunderstanding param sniffing, but isn't the optimizer going to generate a plan and keep it until something changes (index rebuilt\reorg, stats update, table records added\deleted, etc...), and then generate a new plan based on the updated objects? Where the variable value never changes, param sniffing won't come into play here?


If the value never changes in your where clause, you have some really boring code?

:-P


Again, it all depends upon performance. That is why I suggested looking at run time and i/o. If you can live with a generic plan that uses variables, why not?


Isn't all code boring after it's been written and running in production?

Imagine a SELECT in a proc that returns active widgets, the WHERE is always going to be the same. So do you code your where clause 'WHERE Active = 'YES'' or WHERE Active = @Yes? If I can consistently prove that the variable code uses more CPU and is slower, than in my mind, it's an open and shut case. What does the variable declaration buy you? I'm either lazy or efficient (probably lazy), but in my mind this just clouds the code.

I typically do look at IO and runtime (I <3 my profiler more than the query io \ runtime measures), but I also find it helpful to run both and look at the cost relative to the batch.
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