主页 / dba / 问题 / 133712
Accepted
JohnG
Asked: 2016-03-30 10:03:30 +0800 CST

a=0 and b=0 and ... z=0 vs a+b+c+d=0 的表现

  • 772

这是一个简单的问题，我似乎找不到答案。

在性能方面，如果我有一个WHERE子句a=0 and b=0 and ... z=0，如果我用 替换那个条件，我会获得任何性能a+b+...+z=0吗？

换句话说，通过替换以下内容是否有任何性能提升

Select * 
From MyTable 
Where A=0 and B=0 and C=0 and D=0...

和

Select * 
From MyTable 
Where A+B+C+D=0...

我知道它可以依赖于索引，但为了这个目的，我们只说不存在索引。算术运算符 (+) 的性能是否优于“OR”或“AND”逻辑运算符？

我的印象是，加法比使用 AND 或 OR 的多个条件执行得更好。

试验结果

在 420 万行的表上

返回行其中 A=0 B=0 和 C=0 -> 351748 行

加法 (A+B+C=0) 耗时 5 秒，而逻辑条件 A=0 和 B=0 和 C=0 耗时 11 秒。

另一方面

返回行其中 A<>0 B<>0 或 C<>0 -> 3829750 行 58 秒

返回行 其中 F65+ F67+f64<>0 -> 3829750 行 57 秒

对于 OR，似乎没有显着差异。

我同意 gbn：

如果 A 为 -1 且 B 为 1，则 A+B=0 但 A=0 且 B= 0 为假

并使用 AMtwo：

ABS(A)+ABS(B)+ABS(C)+ABS(D)... 即使您只期望正值，如果该列接受负值，您应该假设您可能会遇到一个

结果非常令人印象深刻，正如我所想，加法似乎比逻辑运算符快得多。

A = 浮动，B = 货币和 C = 浮动。使用的查询如图所示。就我而言，都是正数。没有索引。在我看来，加法比逻辑条件更快是合乎逻辑的！

sql-server performance

5 个回答

  1. Best Answer

    在您的问题中，您详细说明了您准备的一些测试，您“证明”添加选项比比较离散列更快。我怀疑您的测试方法可能在几个方面存在缺陷。

    首先，您需要确保您没有测试 SQL Server Management Studio（或您使用的任何客户端）。例如，如果您SELECT *从具有 300 万行的表中运行 a，那么您主要是在测试 SSMS 从 SQL Server 中提取行并将它们呈现在屏幕上的能力。你最好使用类似这样SELECT COUNT(1)的东西，它不需要通过网络拉动数百万行，并将它们呈现在屏幕上。

    其次，您需要了解 SQL Server 的数据缓存。通常，我们测试从存储中读取数据并从冷缓存（即 SQL Server 的缓冲区为空）中处理该数据的速度。有时，使用热缓存进行所有测试是有意义的，但您需要明确地考虑到这一点来进行测试。

    对于冷缓存测试，您需要在每次运行测试之前运行CHECKPOINT。DBCC DROPCLEANBUFFERS

    对于您在问题中询问的测试，我创建了以下测试平台：

    IF COALESCE(OBJECT_ID('tempdb..#SomeTest'), 0) <> 0
BEGIN
    DROP TABLE #SomeTest;
END
CREATE TABLE #SomeTest
(
    TestID INT NOT NULL
        PRIMARY KEY 
        IDENTITY(1,1)
    , A INT NOT NULL
    , B FLOAT NOT NULL
    , C MONEY NOT NULL
    , D BIGINT NOT NULL
);

INSERT INTO #SomeTest (A, B, C, D)
SELECT o1.object_id, o2.object_id, o3.object_id, o4.object_id
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

SELECT COUNT(1) 
FROM #SomeTest;

    这会在我的机器上返回 260,144,641 的计数。

    为了测试“加法”方法，我运行：

    CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE (st.A + st.B + st.C + st.D) = 0;
GO
SET STATISTICS IO, TIME OFF;

    消息选项卡显示：

    表“#SomeTest”。扫描计数 3，逻辑读取 1322661，物理读取 0，预读读取 1313877，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    SQL Server 执行时间：CPU 时间 = 49047 毫秒，经过时间 = 173451 毫秒。

    对于“离散列”测试：

    CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE st.A = 0
    AND st.B = 0
    AND st.C = 0
    AND st.D = 0;
GO

SET STATISTICS IO, TIME OFF;

    再次，从消息选项卡：

    表“#SomeTest”。扫描计数 3，逻辑读取 1322661，物理读取 0，预读读取 1322661，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    SQL Server 执行时间：CPU 时间 = 8938 毫秒，经过时间 = 162581 毫秒。

    从上面的统计数据中你可以看到第二个变体，离散列与 0 相比，经过的时间减少了大约 10 秒，CPU 时间减少了大约 6 倍。我上面测试中的长时间持续时间主要是从磁盘读取大量行的结果。如果将行数减少到 300 万，您会看到比率保持大致相同，但经过的时间明显下降，因为磁盘 I/O 的影响要小得多。

    使用“加法”方法：

    表“#SomeTest”。扫描计数 3，逻辑读取 15255，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    SQL Server 执行时间：CPU 时间 = 499 毫秒，经过时间 = 256 毫秒。

    使用“离散列”方法：

    表“#SomeTest”。扫描计数 3，逻辑读取 15255，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    SQL Server 执行时间：CPU 时间 = 94 毫秒，经过时间 = 53 毫秒。

    什么会对这个测试产生非常大的影响？适当的索引，例如：

    CREATE INDEX IX_SomeTest ON #SomeTest(A, B, C, D);

    “加法”方法：

    表“#SomeTest”。扫描计数 3，逻辑读取 14235，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    SQL Server 执行时间：CPU 时间 = 546 毫秒，经过时间 = 314 毫秒。

    “离散列”方法：

    表“#SomeTest”。扫描计数 1，逻辑读取 3，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。

    SQL Server 执行时间：CPU 时间 = 0 毫秒，经过时间 = 0 毫秒。

    每个查询的执行计划（上面的索引就地）很能说明问题。

    "addition" 方法，它必须执行整个索引的扫描：

    在此处输入图像描述

    和“离散列”方法，它可以查找索引的第一行，其中前导索引列 ,A为零：

    在此处输入图像描述

    • 47

  2. 假设您在 A、B、C 和 D 上有一个索引。也可以过滤。

    这更有可能使用索引然后添加。

    Where A=0 and B=0 and C=0 and D=0

    在其他新闻中，如果 A 为 -1 而 B 为 1，A+B=0则为真但A=0 and B=0为假。

    • 24

  3. （请注意，此答案是在问题中提到的任何测试之前提交的：问题的文本在测试结果部分的上方结束。）

    我猜想AND最好使用单独的条件，因为如果其中一个不等于 0，优化器更有可能使操作短路，而无需先进行计算。

    尽管如此，由于这是一个性能问题，您应该首先设置一个测试以确定您的硬件的答案。报告这些结果，显示您的测试代码，并要求其他人检查它以确保它是一个好的测试。可能还有其他你没有考虑过的因素值得考虑。

    • 7

  4. 一些一般性的推理，如果您手头没有任何索引，我认为您选择两种解决方案中的哪一种并不重要，两者都会表现不佳。另一方面，如果您在谓词中的一个或多个列上有索引，则第一个可能会比第二个执行得更好，因为第二个可能无法利用索引。

    析取 (OR) 通常比连词 (AND) 执行得更差，但即使您有析取的查询，我也会把钱放在第一个上。

    • 3

  5. 这是一个简单的问题

    不它不是。这个（某种）问题日复一日地困扰着许多 DBA 和软件开发人员，而且几乎是微不足道的。

    我似乎找不到答案。

    是的，你不会。至少不是一个普遍的答案。首先，这在很大程度上取决于您使用的是哪个 RDBMS（好吧，您使用的是，但仍然如此）。当您从 RDBMS 的一个版本转到下一个版本时，它甚至可能会发生变化。

    然后，它可能取决于任何数量的其他小细节，例如您的数据库如何存储数据，如果您有子选择/连接会混淆计划优化器的问题等。优化器可能会给您不同的执行计划，具体取决于关于你有多少行......

    进行真实世界的测试通常是解决此类问题的唯一有用方法。此外，像这样的“神秘”优化所获得的任何收益通常都会被索引的明智选择吞噬十倍，所以在真正排除使用索引之前，我不会花太多时间在它上面。

    • 2

相关问题