Efficiently Handling Multiple Optional Constraints in Where Clause

Question

This is somewhat of a sequel to Slow Exists Check. Alex's suggestion works and successfully avoids code repetition, but I still end up with a second issue. Consider the example below (From AlexKuznetsov). In it, I have two branches to handle 1 contraint. If I had 2 optional constraints, I would end up with 4 branches. Basically, the number of branches increases exponentially with the number of constraints.

On the other hand, if I use a Multi-Statement Table-valued function or otherwise use temporary tables, the SQL query optimizer is not able to assist me, so things become slow. I am somewhat distrustful of dynamic SQL (and I've heard it is slow, too).

Can anyone offer suggestions on how to add more constraints without adding lots of if statements?

Note: I have previously tried just chaining x is null or inpo = @inpo together, but this is very slow. Keep in mind that while the inpo = @inpo test can be handled via some sort of indexing black magic, the nullity test ends up being evaluated for every row in the table.

IF @inpo IS NULL BEGIN
  SELECT a,b,c 
    FROM dbo.ReuseMyQuery(@i1)
    ORDER BY c;
END ELSE BEGIN
  SELECT a,b,c 
    FROM dbo.ReuseMyQuery(@i1)
    WHERE inpo = @inpo
    ORDER BY c;
END

Variation Two: 2 constraints:

IF @inpo IS NULL BEGIN      
    IF @inpo2 IS NULL BEGIN
  SELECT a,b,c 
  FROM dbo.ReuseMyQuery(@i1)
  ORDER BY c;
 END ELSE BEGIN
  SELECT a,b,c 
  FROM dbo.ReuseMyQuery(@i1)
  WHERE inpo2 = @inpo2
  ORDER BY c;
 END
END ELSE BEGIN
    IF @inpo2 IS NULL BEGIN
  SELECT a,b,c 
  FROM dbo.ReuseMyQuery(@i1)
  WHERE inpo = @inpo
  ORDER BY c;
 END ELSE BEGIN
  SELECT a,b,c 
  FROM dbo.ReuseMyQuery(@i1)
  WHERE inpo = @inpo AND
        inpo2 = @inpo2
  ORDER BY c;
 END
END

Answer 1

Select blah from foo    
Where (@inpo1 is null or @inpo1 = inpo1)
and (@inpo2 is null or @inpo2 = inpo2)

Apparently this is too slow. Interesting.

Have you considered code generation? Lengthy queries with lots of duplication is only an issue if it has to be maintained directly.

Answer 2

Here's a rough example. Modify the LIKE statements in the WHERE clause depending if you want "starts with" or "contains" or an exact match in your query.

CREATE PROCEDURE dbo.test
@name    AS VARCHAR(50) = NULL,
@address1    AS VARCHAR(50) = NULL,
@address2    AS VARCHAR(50) = NULL,
@city    AS VARCHAR(50) = NULL,
@state   AS VARCHAR(50) = NULL,
@zip_code    AS VARCHAR(50) = NULL
AS

BEGIN

SELECT  [name],
            address1,
            address2,
            city,
            state,
            zip_code
FROM    my_table
WHERE   ([name] LIKE @name + '%' OR @name IS NULL)
            AND (address1 LIKE @address1 + '%' OR @address1 IS NULL)
            AND (address2 LIKE @address2 + '%' OR @address2 IS NULL)
            AND (city LIKE @city + '%' OR @city IS NULL)
            AND (state LIKE @state + '%' OR @state IS NULL)
            AND (zip_code LIKE @zip_code + '%' OR @zip_code IS NULL)
ORDER BY    [name]
END
GO

Answer 3

this is the best reference: http://www.sommarskog.se/dyn-search-2005.html

Answer 4

I realise your question may be purely academic, but if you have real world use cases have you considered only providing optimised queries for the most common scenarios?

Answer 5

In such cases I use sp_executesql as described in Erland's article: Using sp_executesql Whenever dynamic SQL is used, missing permissions may be a problem, so I have a real network account for unit testing, I add that account to the actual role, and I impersonate with that real account whenever I test dynamic SQL, as described here: Database Unit Testing: Impersonation