Efficiently Handling Multiple Optional Constraints

问题:

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

回答1:

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

回答2:

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

回答3:

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

回答4:

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.

回答5:

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?