Sql Server Query Optimization

Question

I want to write a query in a stored proc with many filters but I want to avoid dynamic SQL.

Say my parameters are nullable (@filter1, @filter2, @filter3...). One way I might solve this is:

SELECT col1, col2, col3
FROM table
WHERE col1 = ISNULL(@filter1, col1)
AND col2 = ISNULL(@filter2, col2)
AND col3 = ISNULL(@filter3, col3)

The result of this would filter by the appropriate filters if not null. The question is: 1) Is this good a practice? 2) Will the optimizer optimize the col1 = col1 out or will this affect query performance?

Answer 1

ISNULL can hurt index usage so I wouldn't say it's ideal but if you need the functionality described above i'm not sure there is a way around it.

Can you look at your execution plan to see if the index you would expect to be used are being used?

Answer 2

1) Is this good a practice? 2) Will the optimizer optimize the col1 = col1 out or will this affect query performance?

Yes, it's a good practice.

Some RDBMSes will optimize it out, some won't. None will if you're calling it as a prepared statement.

Don't prematurely optimize; odds are, for most things, the difference in costs will be negligible, or if not, can be made negligible with appropriate indices.

Concentrate on writing code that clearly expresses what you're doing. In my opinion, this idiom is clear and concise.

Answer 3

Erland Sommarskog put together an excellent article on this type of problem. I strongly advise reading through it.

Answer 4

If you expect this table to grow to any substantial size this is not a good idea as the query optimizer will not cache the execution plan and the optimizer sucks at dealing with situations like this as it can't easily tell at compile time what the execution path will be.

You would be much better off just generating a query on the client side with the correct filters in the where clause instead of trying to write a single catch-all query.

Answer 5

In my experience (by running some benchmarks on large tables) the following:

(col1 = @filter or @filter IS NULL)

is much faster than:

col1 = ISNULL(@filter1, col1)

Answer 6

About optimizing the conditions: what you must realize is that a compiled plan has to satisfy any variable value. So when the plan is generated, SQL Server must create an access plan that works when @filter1 is NULL and also works when @filter1 is not NULL. The result is almost always a scan.

The articles linked by Tom H. go into this in much detail.