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 12»»

T-SQL Variable vs String Equivalent Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 99, Visits: 744
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

Post #1439835
Posted Monday, April 8, 2013 11:37 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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!





--
-- 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
Post #1439958
Posted Monday, April 8, 2013 11:56 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 @ 8:19 AM
Points: 40,208, Visits: 36,617
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 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 #1439964
Posted Monday, April 8, 2013 12:02 PM


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 @ 8:19 AM
Points: 40,208, Visits: 36,617
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 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 #1439966
Posted Monday, April 8, 2013 12:06 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 54, Visits: 1,016
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
Post #1439967
Posted Monday, April 8, 2013 12:31 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 99, Visits: 744
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

*/

Post #1439975
Posted Monday, April 8, 2013 12:51 PM


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 @ 8:19 AM
Points: 40,208, Visits: 36,617
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 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 #1439983
Posted Monday, April 8, 2013 1:06 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:08 PM
Points: 80, Visits: 351
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?




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
Post #1439991
Posted Monday, April 8, 2013 1:18 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 99, Visits: 744
Thank you all for taking time to reply, I appreciate all of the input.
Post #1439994
Posted Monday, April 8, 2013 1:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 6:09 AM
Points: 99, Visits: 744
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?




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.
Post #1440002
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse