Query Plan different when using variables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Plan different when using variables

Hi All, Below is a Query from one of my SPs DECLARE @intransitInStockpot varchar(15) DECLARE @intransitOutStockpot varchar(15) SET @intransitInStockpot = ‘INTRANSIT_IN’
SET @intransitOutStockpot = ‘INTRANSIT_OUT’ — Get the Stockpot_Name,Stockpot_code, Stockpot_type
SELECT
[Stockpot_Name], –Stockpot Name
[Stockpot_code], –Stockpot Code
[Stockpot_type] –Stockpot Type
FROM
[tblStockPots]
WHERE
[Stockpot_type] = @intransitInStockpot –Intransit In
OR
[Stockpot_type] = @intransitInStockpot –Intransit out The Query Plan to execute the above shows 2 Index Seeks, 1 Concatenation and 1 Sort/distinct operation But if I change the Query to include actual values (shown below), then it does only 1 Index seek to return the results DECLARE @intransitInStockpot varchar(15)
DECLARE @intransitOutStockpot varchar(15) SET @intransitInStockpot = ‘INTRANSIT_IN’
SET @intransitOutStockpot = ‘INTRANSIT_OUT’ — Get the Stockpot_Name,Stockpot_code, Stockpot_type
SELECT
[Stockpot_Name],–Stockpot Name
[Stockpot_code], –Stockpot Code
[Stockpot_type] –Stockpot Type
FROM
[tblStockPots]
WHERE
[Stockpot_type] = ‘INTRANSIT_IN’ –Intransit In
OR
[Stockpot_type] = ‘INTRANSIT_OUT’ –Intransit out Any help to understand this would be great!
when a variable is used, the SQL Server optimizer uses the average number of rows per distinct value of that column in estimating the cost,
when a specific value is used, SQL Server will try to use more relevent estimates for that value, look up DBCC SHOW_STATISTICS(table,index) then run it for the above table & relevent index
When local variable value is used as part of where cluse or join condition, query optimizer doesn’t try to figure out which value will be used. Without knowing values it can’t use statistics, so it uses ‘magic numbers’ instead (and statistics for other conditions where values are known) which sometimes result in a less then optimal execution plan.
Uhm, ‘magic numbers’ is term used by Oracle. For explanation what does that mean on mssql server see Joe’s post. I think I’ll stop posting here, I start to mix terminology and often I have to ask myself if solution I remember is applicable on Oracle or MSSQL Server.
Stats show that Density to be 0 which is not surprising given that the table has only 30 rows.
]]>