SQL Server Performance Forum – Threads Archive

Specifying parameters more than once

In some article on this site (unfortunately I can’t remember exactly where i read it), i read something along the line that it’s better to specify as many parameters as possible in a query to help the optimizer make the best possible plan. For example, if I have the following query SELECT cu.firstname, oo.orderno
FROM order oo
INNER JOIN customer cu
ON cu.customer_id = oo.customer_id
AND cu.customer_id = 526
WHERE oo.status = 10 Would it be better to also specify the customer_id constant on order like SELECT cu.firstname, oo.orderno
FROM order oo
INNER JOIN customer cu
ON cu.customer_id = oo.customer_id
AND cu.customer_id = 526
WHERE oo.status = 10
AND oo.customer_id = 526 ? /Linus —
http://anticAPSLOCK.com

Well, actually there’s an easy way to find this out. Run both statements and compare the execution plans. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] ———————– –Frank http://www.insidesql.de</a> ———————–

Well, in the simple testcases I’ve tried there hasnt been a difference in plans. In simle queries like this, perhaps what I should do is try with the constant first on one table then the other, and then chose the one version of the query with the best plan/result. However, on more complex queries it might not be that simple /l —
http://anticAPSLOCK.com

Well, I have seen execution plans proving that (in case of that specific query) query optimizer propagated parameter values the way you did. I don’t know if qo is always doing it and if complexity of queries affects it or not.

]]>