SQL Refactoring #2: Function use in a Where clause

We recently came across an old Stored Procedure where it had a query something like this -

SELECT *
FROM Product p 
JOIN CustomerProduct cp ON (cp.ProductID = p.ProductID)     
WHERE p.ProductID = dbo.GetProductIDByName('SomeProductName')

Notice that there is a function call in the WHERE clause. I ran a Profiler trace and here are the results of the 3 runs of this query -

We changed the Stored Procedure by declaring a local variable @productID to store the result of the function call, and use this instead in our SELECT query. -

DECLARE @productID int
SET @productID = dbo.GetProductIDByName('SomeProductName')  

SELECT *
FROM Product p 
JOIN CustomerProduct cp ON (cp.ProductID = p.ProductID)     
WHERE p.ProductID = @productID

Here are the Profiler results of 3 runs -

Notice that even though there is no remarkable improvement in the Duration, but there is huge improvement in the CPU and Logical Reads. This shows that in the previous case, SQL Server was probably making the function call for every row in the Product table.

In conclusion, Functions in the Where clause are bad. Introduce a local variable and store the results of the function call in this variable.