Performance: multiple conditional WHERE clauses, or dynamic query?

Question

I have the following problem. I have to do a query over a very large table with several filters. These filters aren't mandatory: that is, sometimes the query will have to use a certain filter (say, over ColumnA) and sometimes it won't. These filters are passed as parameters to a stored procedure (for instance, @filterA, for ColumnA) .

Suppose the possible filters are over ColumnA, ColumnB, ColumnC and ColumnD (and the values to filter them over are @filterA, ..., @filterD). If I don't need to apply a particular filter, the parameters are passed as NULL. Currently, I'm thinking about doing something like this using "conditional" WHERE clauses:

SELECT *
FROM LargeTable
WHERE (@filterA IS NULL OR ColumnA = @filterA)
  AND (@filterB IS NULL OR ColumnB = @filterB)
  AND (@filterC IS NULL OR ColumnC = @filterC)
  AND (@filterD IS NULL OR ColumnD = @filterD)

This is one possibility, but I wonder how performant it is, given that I would be applying all those WHERE clauses in every case, and the table is quite large.

Would it be preferable to use a dynamic query, where I'd apply a particular WHERE clause only if I actually have to use it? Something like this:

DECLARE @query varchar(MAX)
SET @query = 'SELECT * FROM LargeTable WHERE ';
IF @filterA IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'ColumnA = ', @filterA)
END
IF @filterB IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'AND ColumnB = ', @filterB')
END
IF @filterC IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'AND ColumnC = ', @filterC')
END
IF @filterD IS NOT NULL
BEGIN
   SET @query = CONCAT(@query, 'AND ColumnD = ', @filterD')
END

EXEC sp_executesql @query 

What's the better option? How do the WHERE clauses affect performance here when I'm basically doing "WHERE true"? Does the dynamic query carry a performance penalty?

Thanks

Answer 1

What's the better option?

In your case , I would prefer dynamic SQL.

How do the WHERE clauses affect performance here when I'm basically doing WHERE true?

All conditions in your where clause would be evaluated for each and every row irrespective of whether you supply parameters or not.

Does the dynamic query carry a performance penalty?

If you have indexes on the filtration columns then your dynamic perform better.