WHERE clause is not filtering in logical order

Question

I'm writing a general search Stored Procedure to search in a table based on many filters which user can select in the UI (using MS-SQL 2008).

Here is ther simplified version:

CREATE PROCEDURE SearchAll
    @FirstName NVARCHAR(MAX) = NULL,
    @LastName NVARCHAR(MAX) = NULL,
    @Age INT = NULL
AS
    SELECT * 
    FROM persons 
    WHERE 
        (@FirstName IS NULL OR FirstName = @firstname)
        AND (@LastName IS NULL OR LastName = @LastName)
        AND (@Age IS NULL OR Age = @Age)

It seems that if I pass NULL to @Age there'll be no performance cost. But, when I'm testing with huge amount of data, I have a great perfomance lost!

Here is the queries which are the same logicaly but VERY different practically:

DECLARE @FirstName NVARCHAR(MAX) = NULL
DECLARE @Age INT = 23
------------First slow------------
SELECT * 
FROM persons 
WHERE 
    (@FirstName IS NULL OR FirstName = @firstname)
    AND (@Age IS NULL OR Age = @Age)
------------Very fast------------
SELECT * 
FROM persons 
WHERE 
    Age = @Age

Did is miss a point?

I know that SQL engine finds the best match for indexes, and ... (before running the query),but it's obvious that: @FirstName IS NULL and there's no need to analyse anything.

I've also tested ISNULL function in the query (the same result).

score 5 · Accepted Answer · answered Mar 27 '12 at 07:06

5

Queries that contain this construct of @variable is null or @variable = column are a performance disaster. This is because SQL plans are created so they work for any value of the variable. For a lengthy discussion of the topic, the problems and possible solutions see Dynamic Search Conditions in T-SQL

answered Mar 27 '12 at 07:06

Remus Rusanu

288,378
40
442
569

How about using 'CASE WHEN' in WHERE clause for my variables? – Amir Pournasserian Mar 27 '12 at 07:15
1

Your issue is the access path, not operator short-circuit. CASE will help nothing. – Remus Rusanu Mar 27 '12 at 07:17

score 0 · Answer 2 · answered Mar 28 '12 at 09:27

The problem, as has already been mentioned, is that the query plan will be built to work with any value of the variables. You can circumvent this by building the query with only the paremeter required, as follows:

CREATE PROCEDURE SearchAll
    @FirstName NVARCHAR(MAX) = NULL,
    @LastName NVARCHAR(MAX) = NULL,
    @Age INT = NULL
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX), @has_where BIT
    SELECT @has_where = 0, @sql = 'SELECT * FROM persons '

    IF @FirstName IS NOT NULL
        SELECT @sql = @sql + 'WHERE FirstName = ''' + @FirstName + '''', @has_where = 1
    IF @LastName IS NOT NULL
        SELECT @sql = @sql + CASE WHEN @has_where = 0 THEN 'WHERE ' ELSE 'AND ' END + 'LastName = ''' + @LastName + '''', @has_where = 1
    IF @Age IS NOT NULL
        SELECT @sql = @sql + CASE WHEN @has_where = 0 THEN 'WHERE ' ELSE 'AND ' END + 'Age = ' + CAST(@Age AS VARCHAR), @has_where = 1

    EXEC sp_executesql @sql
END

WHERE clause is not filtering in logical order

2 Answers2