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.

SQL Refactoring #1: CONCAT_NULL_YIELDS_NULL

During the peak hours we have been seeing lot of blocking by one Stored Procedure. I looked into the master.dbo.sysprocesses table and it showed that this was getting blocked because it was waiting on an exclusive lock to recompile the Stored Procedure.

Why would this Stored Procedure get recompiled? I ran a trace with SP:Recompile switch on, and saw that it was recompiling on the statement

SET CONCAT_NULL_YIELDS_NULL OFF

What does CONCAT_NULL_YIELDS_NULL do? According to MSDN, this option controls whether concatenation results are treated as null or empty string values. When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'abc' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'abc' + NULL yields abc.

According to a KB articles, Troubleshooting stored procedure recompilation and SET CONCAT_NULL_YIELDS_NULL May Cause Stored Procedures to Recompile, I found that if you execute the SET statement to set the options to OFF, the stored procedure will be recompiled every time it runs, causing performance problems. The reason is that by default CONCAT_NULL_YIELDS_NULL is set to ON in the SQL Server, and changing the option in a Stored Procedure triggers the recompilation.

Therefore do not set CONCAT_NULL_YIELDS_NULL inside of the stored procedure. The recommended solution is to use the ISNULL function when concatenating strings.

For example, don't do this:

SELECT * FROM mytable WHERE name LIKE 'Xavier ' + @lastname + '%'

Instead do this:

SELECT * FROM mytable WHERE name LIKE 'Xavier ' + ISNULL(@lastname,'') + '%'