有效处理 Where 子句中的多个可选约束 [英] Efficiently Handling Multiple Optional Constraints in Where Clause

问题描述

这有点像慢速存在检查.Alex 的建议有效并成功避免了代码重复，但我仍然遇到第二个问题.考虑下面的例子(来自 AlexKuznetsov).在其中，我有两个分支来处理 1 个约束.如果我有 2 个可选约束，我最终会得到 4 个分支.基本上，分支的数量随着约束的数量呈指数增长.

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.

另一方面，如果我使用多语句表值函数或以其他方式使用临时表，SQL 查询优化器无法帮助我，所以事情变得缓慢.我对动态 SQL 有点不信任(而且我听说它也很慢).

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).

谁能提供有关如何在不添加大量 if 语句的情况下添加更多约束的建议?

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

注意: 我之前曾尝试将 x is null 或 inpo = @inpo 链接在一起，但这很慢.请记住，虽然 inpo = @inpo 测试可以通过某种索引黑魔法来处理，但无效测试最终会针对表中的每一行进行评估.

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

变体二:2 个约束:

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

推荐答案

这是最好的参考:http://www.sommarskog.se/dyn-search-2005.html

这篇关于有效处理 Where 子句中的多个可选约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！