代码之家 › 专栏 › 技术社区 › Brian

高效地处理WHERE子句中的多个可选约束

tsql sql-server-2005

1

Brian · 技术社区 · 16 年前

这是 Slow Exists Check . 亚历克斯的建议很有效,并且成功地避免了代码重复,但最后我还是发现了第二个问题。考虑下面的例子(来自Alexkuznetsov)。在里面,我有两个分支来处理1个约束。如果我有2个可选的约束条件,我最终会得到4个分支。基本上,分支数随约束数呈指数增长。

另一方面,如果我使用多语句表值函数或者使用临时表,那么SQL查询优化器就不能帮助我,所以事情变得很慢。我对动态SQL有些不信任(我听说它也很慢)。

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

注: 我以前试过锁链 x is null or inpo = @inpo 一起,但这很慢。记住,当 inpo = @inpo 测试可以通过某种索引黑魔法来处理,零测试最终会针对表中的每一行进行评估。

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: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

5 回复 | 直到 16 年前

1

5

KM. 16 年前

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

2

1

A-K 16 年前

在这种情况下,我使用 sp_executesql 如Erland的文章所述: Using sp_executesql 每当使用动态SQL时,丢失权限可能是一个问题,因此我有一个用于单元测试的真实网络帐户,我将该帐户添加到实际角色,并在测试动态SQL时使用该真实帐户进行模拟,如下所述: Database Unit Testing: Impersonation

3

0

HardCode 16 年前

下面是一个粗略的例子。根据需要在查询中“starts with”或“contains”或完全匹配,修改WHERE子句中的LIKE语句。

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

0

Matt Howells 16 年前

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

显然这太慢了。有趣。

您考虑过代码生成吗?只有在必须直接维护的情况下,具有大量重复的冗长查询才是一个问题。

5

0

Alex Peck 16 年前

我知道你的问题可能纯粹是学术性的,但是如果你有真实的用例,你是否只考虑为最常见的场景提供优化的查询?