Difference in query cost for parameterized query---?

  • 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

    🙂

  • 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

  • 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)

  • 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.

    🙂

  • 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.


    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply