Home Dashboard Directory Help
Search

Add Constants to TSQL for Better Query Plans by jhess001


Status: 

Closed
 as Won't Fix Help for as Won't Fix


8
0
Sign in
to vote
Type: Suggestion
ID: 533825
Opened: 2/15/2010 2:45:44 PM
Access Restriction: Public
Primary Feedback Item: 404252
0
Workaround(s)
view

Description

When I declare a local variable in a stored procedure and assign it a value that never changes and then restrict a SELECT query based on that variable, the performance is worse than if I hard-code the constant in the SELECT's WHERE statement.

Here's an example:

-- Create a table and populate it with some sparse data

create table SparseData (
    ID int not null identity(1, 1) ,
        constraint PK_SparseData_ID primary key ( ID ) ,
    ----------------------------------------------------
    IsProcessed bit not null default(0)
)

create index IX_SparseData_IsProcessed on SparseData( IsProcessed )
where IsProcessed = 1

declare @RowsToInsert int
set @RowsToInsert = 1000000
while (select count(*) from SparseData) < @RowsToInsert
begin
    insert into SparseData(IsProcessed) values(0)
end

-- Looking at the execution plans for the following, the estimated
-- rowcount differs

select * from SparseData where IsProcessed = 1

declare @cTrue bit
set @cTrue = 1
select * from SparseData where IsProcessed = @cTrue

This causes worse query plans to be generated when using local variables than when using constants. The problem is that using the variable makes the code more readable and harder to inject defects into than if I hard-code a constant throughout a proc.
Details
Sign in to post a comment.
Posted by Microsoft on 3/3/2010 at 11:40 AM
Hi,
I have resolved your request as duplicate of feedback item below:

https://connect.microsoft.com/SQLServer/feedback/details/404252/use-of-constants-in-sql?wa=wsignin1.0

Thanks
Umachandar, SQL Programmability Team
Posted by Microsoft on 3/1/2010 at 9:02 AM
Hi,

Thanks for your feedback. This is a well known issue and we're working to fix it by default at some point in the future. Consider for now as a workaround adding OPTION(RECOMPILE) to all the queries that use the variable. That will cause SQL Server to use the value during optimization ("sniff" it) rather than treating it as unknown and guessing the selectivity estimate. See http://msdn.microsoft.com/en-us/library/dd535534.aspx for more details.

Eric
Sign in to post a workaround.