SQL查询执行快捷方式OR逻辑？

Question

我有三个表：

SmallTable
   (id int, flag1 bit, flag2 bit)
JoinTable
   (SmallTableID int, BigTableID int)
BigTable
   (id int, text1 nvarchar(100), otherstuff...)

SmallTable 最多有几十条记录。 BigTable 有几百万，实际上是一个将该数据库中的表与同一服务器上另一个数据库中的表 UNIONS 的视图。

这是连接逻辑：

SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE
    (s.flag1=1 OR b.text1 NOT LIKE 'pattern1%')
    AND (s.flag2=1 OR b.text1 <> 'value1')

平均连接大小是几千个结果。显示的所有内容均已编入索引。

对于大多数 SmallTable 记录，flag1 和 flag2 设置为 1，因此实际上甚至不需要访问BigTable.text1 上的索引，但 SQL Server 无论如何都会这样做，从而导致昂贵的索引扫描和嵌套循环。

有没有更好的方法来提示 SQL Server，如果 flag1 和 flag2 都设置为 1，它甚至不需要查找在text1？

实际上，如果我可以在这些情况下完全避免连接到 BigTable（JoinTable 是托管的，因此这不会产生问题），这将使这个关键查询更快。

Answer 1

SQL 布尔计算不保证运算符短路。请参阅 关于 SQL Server 布尔运算符短路 一个清晰的例子展示了假设操作符短路如何导致正确性问题和运行时错误。

另一方面，我的链接中的示例显示了 SQL Server 的作用：提供 SQL 可以使用的访问路径。因此，与所有 SQL 性能问题和疑问一样，真正的问题不在于 SQL 文本的表达方式，而在于存储的设计。 IE。查询优化器可以使用哪些索引来满足您的查询？

Answer 2

不幸的是，我不相信 SQL Server 会短路这样的情况。

所以我建议做两个查询并将它们联合在一起。第一个查询使用 s.flag1=1 和 s.flag2=1 WHERE 条件，第二个查询使用 s.flag1<>1 和 s.flag2>>1 条件连接到 BigTable。

关于此事的这篇文章值得一读，其中包括以下要点：

...SQL Server 不这样做
短路就像它所做的那样
其他编程语言和
你无能为力
到。

更新：
这篇文章也是这是一本有趣的读物，包含一些关于该主题的好链接，包括与 SQL Server 查询处理器团队的开发经理的 technet 聊天，其中简要提到优化器确实允许短路评估。我从各种文章中得到的总体印象是“是的，优化器可以发现短路的机会，但你不应该依赖它，也不能强迫它”。因此，我认为 UNION 方法可能是您最好的选择。如果它没有提出一个利用捷径机会的计划，那么这将取决于基于成本的优化器认为它找到了一个不这样做的合理计划（这将取决于索引、统计信息等） 。

Answer 3

它并不优雅，但它应该可以工作......

SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE
    (s.flag1 = 1 and s.flag2 = 1) OR 
    (
       (s.flag1=1 OR b.text1 NOT LIKE 'pattern1%')
       AND (s.flag2=1 OR b.text1 <> 'value1')
    )

Answer 4

SQL Server 通常会获取子查询提示（尽管可以随意丢弃它）：

SELECT      * 
FROM        (
            SELECT * FROM SmallTable where flag1 <> 1 or flag2 <> 1
            ) s
INNER JOIN  JoinTable j ON j.SmallTableID = s.ID
...

Answer 5

不知道如果没有测试数据，这是否会更快...但听起来可能会

SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE
    (s.flag1=1) AND (s.flag2=1)
 UNION ALL
 SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE
    (s.flag1=0 AND b.text1 NOT LIKE 'pattern1%')
    AND (s.flag2=0 AND b.text1 <> 'value1')

发生请让我知道发生了什么

此外，您可以通过仅返回此查询的唯一 id 然后使用结果来加快速度以获得所有其余数据。

编辑

这样的东西？

SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE
    (s.flag1=1) AND (s.flag2=1)
 UNION ALL
 SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE EXISTS
    (SELECT 1 from BigTable b
     WHERE   
    (s.flag1=0 AND b.text1 NOT LIKE 'pattern1%')
    AND (s.flag2=0 AND b.text1 <> 'value1')
)

Answer 6

希望这能起作用 - 小心围绕 aggregates 的 case 语句中的快捷逻辑，但是......

SELECT * FROM
    SmallTable s
    INNER JOIN JoinTable j ON j.SmallTableID = s.ID
    INNER JOIN BigTable b ON b.ID = j.BigTableID
WHERE 1=case when (s.flag1 = 1 and s.flag2 = 1) then 1
when (
       (s.flag1=1 OR b.text1 NOT LIKE 'pattern1%')
       AND (s.flag2=1 OR b.text1 <> 'value1')
    ) then 1
else 0 end