SQL Server performance issue with parameters

Question

There is a performance issue with a SQL query. This is my query

declare @siteId int,    
@totalCount int

 SELECT @totalCount = COUNT(DISTINCT pro.product_id)
    FROM product AS pro 
    INNER JOIN product_to_category AS proCat ON pro.product_id = proCat.product_id
    INNER JOIN product_to_vendor proVen ON  pro.product_id = proVen.product_id
WHERE pro.site_id = @siteId 
    AND pro.product_type <> 3
print @totalCount

It is take 6 seconds to execute..

when I remove parameters as follows

@totalCount int

SELECT @totalCount = COUNT(DISTINCT pro.product_id)
FROM product AS pro 
INNER JOIN product_to_category AS proCat ON pro.product_id = proCat.product_id
INNER JOIN product_to_vendor proVen ON  pro.product_id = proVen.product_id
WHERE pro.site_id = 28
AND pro.product_type <> 3
print @totalCount

and execute query again, it takes only 2 seconds.

is there a method to improve performance of an query like this? why it takes time to assign values to parameters? any comments....

Answer 1

This is not a parameter it is a variable.

SQL Server does not sniff the values of variables so it does not know at compile time that the value it will contain is 28

Add OPTION (RECOMPILE) to the end of your query to get it to recompile the statement after the variable's value has been assigned and it should generate a more suitable execution plan.