August 9, 2012 at 4:13 am
hi All,
I am learning sql server optimization,
declare @p1 int
set @p1 = 1
SELECT ProductID, SalesOrderID--, LineNumber
FROM Sales.SalesOrderDetail
WHERE ProductID > 1
ORDER BY ProductID
SELECT ProductID, SalesOrderID--, LineNumber
FROM Sales.SalesOrderDetail
WHERE ProductID > @p1
ORDER BY ProductID
when we execute the above query, the second query (Parameterized query) is showing less query cost compared with the first query.
can anybody explain why there is a difference?
Thanks
🙂
August 9, 2012 at 4:42 am
I'm guessing that ProductID column is an INT data type?
So the first query is doing an implicit data conversion to INT ("CONVERT_IMPLICIT(int,[@1],0)") on the value, as 1 could be any Numeric Datatype.
Where as in the second query it doesnt need to do that conversion as it already knows that Parameter is an INT datatype.
I suspect if you change the declaration of the @P variable to tinyint you will get the conversion and so two equal plans.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
August 9, 2012 at 5:09 am
The relative cost is an estimate. In this case, the cost of the parameterised query depends on the relative distribution of whichever value happens to be fed in as the parameter value, so the estimated number of rows is indeterminate and is estimated based on statistics/the initial parameter used when the statement was first executed.
Where you have a static value, the estimated number of rows can be 100% accurate as it has explicit knowledge of it when the plan is being compiled, hence it estimates more accurately.
It does not mean that one is actually more efficient than the other - if you set statistics Time and IO on, or capture the cost in profiler, you should see that they're doing exactly the same thing (as they have otherwise identical plans)
August 9, 2012 at 5:09 am
Jason-299789 (8/9/2012)
I'm guessing that ProductID column is an INT data type?So the first query is doing an implicit data conversion to INT ("CONVERT_IMPLICIT(int,[@1],0)") on the value, as 1 could be any Numeric Datatype.
Where as in the second query it doesnt need to do that conversion as it already knows that Parameter is an INT datatype.
I suspect if you change the declaration of the @P variable to tinyint you will get the conversion and so two equal plans.
i have tried as you said, by changing the parameter to tinyint, but still it is giving the same difference.
🙂
August 9, 2012 at 5:10 am
SQL* (8/9/2012)
hi All,I am learning sql server optimization,
declare @p1 int
set @p1 = 1
SELECT ProductID, SalesOrderID--, LineNumber
FROM Sales.SalesOrderDetail
WHERE ProductID > 1
ORDER BY ProductID
SELECT ProductID, SalesOrderID--, LineNumber
FROM Sales.SalesOrderDetail
WHERE ProductID > @p1
ORDER BY ProductID
when we execute the above query, the second query (Parameterized query) is showing less query cost compared with the first query.
can anybody explain why there is a difference?
Thanks
Hello!
All the answers are in the execution plan. Look carfuly on the estimated rows and table cardinality.
In the first query the shuld be quite the same. That is because value 1 is first (or among the first) values, and sql server knows it. When you write a>1 optimizer "knows" that all the table would be selected.
In the second query, you use a parameter, and sql server has no idea until it start executing query what is value of this parameter. For SQL Server this parameter is unknown, that's why it could not estimate how many rows will be selected! That's why it makes a guess, the guess in case of inequality is 30% of rows. So optimizer estimates to select not all table (like in the first case) but only 30%, it is 70% cheaper. That is the reason why it thinks and displays so.
You may press "include actual execution plan" button in ssms, and add option(recompile) to the end of the second query, after that - look at the plan and you will see no difference. That is because when you set value to a parameter you forced optimizer to recompile a plan, and optimizer already knew parameter value and was able to make correct estimates.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply