sql-server - 重写 T-SQL 按位逻辑

Question

如何重写此 T-SQL 代码以产生相同的结果

   SELECT   ACC.Title,
            ACC.AdvertiserHierarchyId,
            1 AS Counter
    FROM    admanAdvertiserHierarchy_tbl ACC
    JOIN    dbo.admanAdvertiserObjectType_tbl AOT ON AOT.AdvertiserObjectTypeId = ACC.AdvertiserObjectTypeId
              WHERE  (EXISTS
                         (SELECT 1
                          FROM    dbo.admanAdvertiserHierarchy_tbl CAMP
                          JOIN    dbo.admanAdvertiserAdGroup_tbl AG ON CAMP.AdvertiserHierarchyId = AG.AdvertiserHierarchyId
                          JOIN    dbo.admanAdvertiserCreative_tbl AC ON AC.AdvertiserAdGroupId = AG.AdvertiserAdGroupId
                          AND     CAMP.ParentAdvertiserHierarchyId = ACC.AdvertiserHierarchyId
                          WHERE   CAMP.ERROR = 0
                                  AND AC.Dirty & 7 > 0
                                  AND AC.ERROR = 0
                                  AND AG.ERROR = 0 ))

它阻止优化器有效地使用索引。
试图达到以下结果

    Title                               AdvertiserHierarchyId       Counter
    trcom65@travelrepublic.co.uk        15908                       1
    paul570@travelrepublic.co.uk        37887                       1
    es88@travelrepublic.co.uk           37383                       1
    it004@travelrepublic.co.uk          27006                       1
    011                                 10526                       1
    013                                 10528                       1
    033                                 12013                       1
    062                                 17380                       1
    076                                 20505                       1

这是脏 tinyint 列的计数

    Dirty   total
    0       36340607
    1       117569
    2       873553
    3       59

链接到静态原因表

    DirtyReasonId   Title
    0               Nothing
    1               Overnight Engine
    2               End To End
    3               Overnight And End To End
    4               Pause Resume
    5               Overnight Engine and Paused
    6               Overnight Engine E2E and Paused
    7               All Three

Answer 1

如果您专门询问 BITWISE AND 运算符的使用，我相信您是正确的，并且 SQL Server 不太可能将其视为 sargable，至少，不会将 Dirty 作为前导列的索引。

您仅显示使用中的最低两位(Dirty 的最大值为 3)，但您正在测试最低三位。

所以，AC.Dirty > 0将返回等效结果，因为 3 是 Dirty 的最大值。但是有可能设置其他(高阶)位，例如 Dirty 可以设置为 8。因此，如果目的是仅检查最低的三位，那么我们需要确保我们只测试三个最低位。这个表达式可以做到这一点，其中一个谓词是 sargable:

( AC.Dirty > 0 AND AC.Dirty % 8 > 0 )

这基本上首先测试 AC.Dirty 中是否有任何位设置，然后检查是否设置了最后三位中的任何一个。 (我们使用 MODULO 除法运算符返回 AC.Dirty 除以 8 的余数，这当然会返回 0 到 7 之间的整数值。如果我们得到零，那么我们知道低三位被设置，否则我们知道至少有一个位被设置。

需要说明的是: AC.Dirty > 0 上的谓词是多余的。如果您想确保数据库至少可以考虑使用带有 Dirty 的现有索引，则将其包含在此处。作为领先的专栏。

我会提到另一个要考虑的选项是在表达式上添加一个持久化的 COMPUTED COLUMN，并在其上创建一个索引。但这对于您在这里需要的东西似乎有点矫枉过正。

如果您特别询问如何在表 admanAdvertiserCreative_tbl 上使用索引(AC)，那么您最好的候选人可能会覆盖 (AdvertiserAdGroupId, Error, Dirty) 上的索引。 .

下面的 SQL 重写应该返回等效的结果，也许具有更好的性能(取决于您的数据分布、索引等)

基本上，将 EXISTS(相关子查询)替换为 JOIN 到子查询。子查询返回 CAMP.ParentAdvertiserHierarchyId 的不同值，这是您引用以关联子查询的列。

这可能会或可能不会使用任何索引，具体取决于可用的索引。 (它可能在主键上有聚集唯一索引，在外键上有非聚集索引，这应该有助于连接性能。)

未经测试:

SELECT  ACC.Title,
        ACC.AdvertiserHierarchyId,
        1 AS Counter
FROM    admanAdvertiserHierarchy_tbl ACC
JOIN    dbo.admanAdvertiserObjectType_tbl AOT 
        ON AOT.AdvertiserObjectTypeId = ACC.AdvertiserObjectTypeId
JOIN   (SELECT    CAMP.ParentAdvertiserHierarchyId
          FROM    dbo.admanAdvertiserHierarchy_tbl CAMP
          JOIN    dbo.admanAdvertiserAdGroup_tbl AG 
            ON    CAMP.AdvertiserHierarchyId = AG.AdvertiserHierarchyId
          JOIN    dbo.admanAdvertiserCreative_tbl AC 
            ON    AC.AdvertiserAdGroupId = AG.AdvertiserAdGroupId
         WHERE    CAMP.ERROR = 0
           AND    ( AC.Dirty > 0 AND AC.Dirty % 8 > 0 )
           AND    AC.ERROR = 0
           AND    AG.ERROR = 0 )
         GROUP BY CAMP.ParentAdvertiserHierarchyId
       ) c
  ON c.ParentAdvertiserHierarchyId = ACC.AdvertiserHierarchyId