SQL Server - 使用聚集索引时如何存储数据页

Question

DamagedGoods

Asked: 2012-06-21 02:17:35 +0800 CST2012-06-21 02:17:35 +0800 CST 2012-06-21 02:17:35 +0800 CST

影响性能的位运算符

772

我最近看到一些存储过程包含导致优化器问题的 BITWISE 运算符。我从执行计划中知道这些是问题的原因，但我不能 100% 确定原因。这是因为短路不能用作程序语言吗？然后使用 SQL 服务器同时评估WHERE子句中的所有内容并被迫扫描整个表？

附件是proc内部的一段代码，我为测试做了一些修改。如何重写它以删除 BITWISE？

DECLARE
@AdvertiserId INT, 
@DirtyReason INT

SET @advertiserid = 3
SET @dirtyreason = 7

BEGIN


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

   --DROP TABLE #temparraytable;

CREATE TABLE #tempArrayTable (advertiserHierarchyId int)
    INSERT INTO #tempArrayTable (advertiserHierarchyId)
    (SELECT convert(int,str) FROM dbo.SplitString('14167',','))

CREATE INDEX #ix_tempArrayTable ON #tempArrayTable  (advertiserHierarchyId)

        SELECT DISTINCT MA.ContentNetwork,MA.DestinationURL, MA.MasterAdGroupId, AA.Title, MA.ShortTitle, ma.LinkedObjectId, ma.LinkedObjectTypeId, MA.MasterHierarchyId, 
                AA.AdvertiserAdGroupId, AA.AdvertiserId, AA.AdvertiserHierarchyId, AdvertiserAdGroupCode, AA.AdvertiserHierarchyId, AA.Dirty, 
                AA.Deleted, AA.Paused, MA.MatchTypeId,
                lob.MaxCostPerClick AS 'LinkedObjectROIMAXCPC'                     
          FROM admanMasterAdGroup_tbl MA  
    INNER JOIN admanAdvertiserAdGroup_tbl AA  ON MA.MasterAdGroupId = AA.MasterAdGroupId 
        INNER JOIN admanAdvertiserCreative_tbl AC ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId 
        INNER JOIN admanAdvertiserHierarchy_tbl AH ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId 
        INNER JOIN #tempArrayTable tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
    LEFT JOIN admanLinkedObjectROIBid_tbl lob ON lob.LinkedObjectId = ma.LinkedObjectId AND lob.LinkedObjectTypeId = ma.LinkedObjectTypeId AND lob.AdvertiserId = @AdvertiserId
        WHERE AA.AdvertiserId = @AdvertiserId 
          **AND (AC.Dirty & @dirtyReason > 0)**
      AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL) 
          AND AA.AdvertiserAdGroupCode IS NOT NULL 
          AND AA.Error = 0 
          AND AC.Error = 0 

        SELECT  MCH.Headline,MCH.Duplicate,MCH.MasterCreativeHeadlineId, MCB.MasterCreativeBodyId, 
                ACI.AdvertiserCreativeInstanceId,ACI.ActualLine1,ACI.ActualLine2,ACI.ActualHeadline,ACI.ActualDisplayURL,ACI.ActualDestinationURL, MCU.MasterCreativeURLId, MCH.Rank,  
                MCB.Line1, 
                MCB.Line2, AA.MasterAdGroupId, AdvertiserCreativeId, 
                AA.AdvertiserAdGroupId, AC.Dirty, AC.Deleted, AC.Paused, MCU.DisplayURL,AA.AdvertiserId, AC.AdvertiserCreativeCode,  
                MCH.CreativeHeadlineTemplateId, MCB.CreativeBodyTemplateId, MCU.CreativeURLTemplateId, CBT.CreativeTemplateGroupId  
           FROM admanAdvertiserCreative_tbl AC  
     INNER JOIN admanMasterCreativeHeadline_tbl MCH  ON AC.MasterCreativeHeadlineId = MCH.MasterCreativeHeadlineId 
     INNER JOIN admanAdvertiserCreativeInstance_tbl ACI  ON AC.AdvertiserCreativeInstanceId = ACI.AdvertiserCreativeInstanceId 
     INNER JOIN admanMasterCreativeBody_tbl MCB  ON MCB.MasterCreativeBodyId = AC.MasterCreativeBodyId 
     INNER JOIN admanMasterCreativeURL_tbl MCU  ON AC.MasterCreativeURLId = MCU.MasterCreativeURLId 
     INNER JOIN admanAdvertiserAdGroup_tbl AA  ON AC.AdvertiserAdGroupId = AA.AdvertiserAdGroupId 
     INNER JOIN admanAdvertiserHierarchy_tbl AH  ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId 
     INNER JOIN admanCreativeBodyTemplate_tbl CBT  ON CBT.CreativeBodyTemplateId = MCB.CreativeBodyTemplateId 
     INNER JOIN #tempArrayTable tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
          WHERE AA.AdvertiserId = @AdvertiserId 
           **AND (AC.Dirty & @dirtyreason > 0)**  
           AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL) 
           AND AA.AdvertiserAdGroupCode IS NOT NULL 
           AND AA.Error = 0 
           AND AC.Error = 0 
      ORDER BY AC.Deleted desc, AC.AdvertiserAdGroupId,AC.AdvertiserCreativeId asc 

END;

BEGIN

DROP TABLE #temparraytable2;
CREATE TABLE #tempArrayTable2 (advertiserHierarchyId int)
INSERT INTO #tempArrayTable2 (advertiserHierarchyId)
    (SELECT convert(int,str) FROM dbo.SplitString('14167',','))
CREATE INDEX #ix_tempArrayTable ON #tempArrayTable2 (advertiserHierarchyId)

        SELECT DISTINCT MA.ContentNetwork,MA.DestinationURL, MA.MasterAdGroupId, AA.Title, MA.ShortTitle, ma.LinkedObjectId, ma.LinkedObjectTypeId, MA.MasterHierarchyId, 
                AA.AdvertiserAdGroupId, AA.AdvertiserId, AA.AdvertiserHierarchyId, AdvertiserAdGroupCode, AA.AdvertiserHierarchyId, AA.Dirty, 
                AA.Deleted, AA.Paused, MA.MatchTypeId,
                lob.MaxCostPerClick AS 'LinkedObjectROIMAXCPC'                     
          FROM admanMasterAdGroup_tbl MA  
    INNER JOIN admanAdvertiserAdGroup_tbl AA  ON MA.MasterAdGroupId = AA.MasterAdGroupId 
        INNER JOIN admanAdvertiserCreative_tbl AC ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId 
        INNER JOIN admanAdvertiserHierarchy_tbl AH ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId 
        INNER JOIN #tempArrayTable2 tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
    LEFT JOIN admanLinkedObjectROIBid_tbl lob ON lob.LinkedObjectId = ma.LinkedObjectId AND lob.LinkedObjectTypeId = ma.LinkedObjectTypeId AND lob.AdvertiserId = @AdvertiserId
        WHERE AA.AdvertiserId = @AdvertiserId 
          **AND (AC.Dirty > 0 AND @dirtyReason > 0)**
      AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL) 
          AND AA.AdvertiserAdGroupCode IS NOT NULL 
          AND AA.Error = 0 
          AND AC.Error = 0 

        SELECT  MCH.Headline,MCH.Duplicate,MCH.MasterCreativeHeadlineId, MCB.MasterCreativeBodyId, 
                ACI.AdvertiserCreativeInstanceId,ACI.ActualLine1,ACI.ActualLine2,ACI.ActualHeadline,ACI.ActualDisplayURL,ACI.ActualDestinationURL, MCU.MasterCreativeURLId, MCH.Rank,  
                MCB.Line1, 
                MCB.Line2, AA.MasterAdGroupId, AdvertiserCreativeId, 
                AA.AdvertiserAdGroupId, AC.Dirty, AC.Deleted, AC.Paused, MCU.DisplayURL,AA.AdvertiserId, AC.AdvertiserCreativeCode,  
                MCH.CreativeHeadlineTemplateId, MCB.CreativeBodyTemplateId, MCU.CreativeURLTemplateId, CBT.CreativeTemplateGroupId  
           FROM admanAdvertiserCreative_tbl AC  
     INNER JOIN admanMasterCreativeHeadline_tbl MCH  ON AC.MasterCreativeHeadlineId = MCH.MasterCreativeHeadlineId 
     INNER JOIN admanAdvertiserCreativeInstance_tbl ACI  ON AC.AdvertiserCreativeInstanceId = ACI.AdvertiserCreativeInstanceId 
     INNER JOIN admanMasterCreativeBody_tbl MCB  ON MCB.MasterCreativeBodyId = AC.MasterCreativeBodyId 
     INNER JOIN admanMasterCreativeURL_tbl MCU  ON AC.MasterCreativeURLId = MCU.MasterCreativeURLId 
     INNER JOIN admanAdvertiserAdGroup_tbl AA  ON AC.AdvertiserAdGroupId = AA.AdvertiserAdGroupId 
     INNER JOIN admanAdvertiserHierarchy_tbl AH  ON AA.AdvertiserHierarchyId = AH.AdvertiserHierarchyId 
     INNER JOIN admanCreativeBodyTemplate_tbl CBT  ON CBT.CreativeBodyTemplateId = MCB.CreativeBodyTemplateId 
     INNER JOIN #tempArrayTable2 tat ON tat.advertiserHierarchyId = AA.AdvertiserHierarchyId
          WHERE AA.AdvertiserId = @AdvertiserId 
           **AND (AC.Dirty > 0 AND @dirtyreason > 0)**
           AND NOT (AC.Deleted = 1 AND AC.AdvertiserCreativeCode IS NULL) 
           AND AA.AdvertiserAdGroupCode IS NOT NULL 
           AND AA.Error = 0 
           AND AC.Error = 0 
      ORDER BY AC.Deleted desc, AC.AdvertiserAdGroupId,AC.AdvertiserCreativeId asc 

END

1 个回答

Voted

Aaron Bertrand · Answer 1 · 2012-06-21T04:56:03+08:00

短路并不总是像您在 SQL Server 中所期望的那样起作用。您对 where 子句的评估顺序几乎没有发言权；即使您首先在子查询或 CTE 中执行此过滤，优化器仍可能以您不期望的顺序对其进行评估。我见过的唯一两个可能克服这个问题的选择：

(a) 将中间过滤结果转储到#temp 表。

(b) 尝试将按位运算符添加到 ON 条件，而不是 WHERE 子句，并使用 FORCE ORDER 确保按照您编写它们的顺序对连接进行评估。我不明白你的两个查询之间的区别，我认为这个问题包含很多不必要的代码，但我会尝试这种变化：

FROM 
  dbo.admanAdvertiserAdGroup_tbl AS AA 
INNER JOIN 
  dbo.admanAdvertiserCreative_tbl AS AC 
    ON AA.AdvertiserAdGroupId = AC.AdvertiserAdGroupId 
    AND (AC.Dirty & @dirtyReason > 0)
INNER JOIN 
  dbo.admanMasterAdGroup_tbl AS MA  
    ON MA.MasterAdGroupId = AA.MasterAdGroupId 
...
WHERE AA.AdvertiserId = @AdvertiserId 
...
OPTION (FORCE ORDER);

（我还冒昧地添加dbo.了您应该始终使用的表引用，以及AS为了可读性。对不起，我对此有点谨慎。）

显然，没有保证，但值得一试。这是假设您关于短路的结论甚至是正确的：您是如何得出这个结论的？你能展示一下执行计划吗？是什么导致你把短路作为可能的罪魁祸首？大部分查询是否处理了太多理论上应该被按位运算符过滤掉的行？

编辑

我写过关于这种情况的博客：http ://www.sqlperformance.com/2012/08/t-sql-queries/dry-principle

影响性能的位运算符

如何查看 Oracle 中的数据库列表？

mysql innodb_buffer_pool_size 应该有多大？

列出指定表的所有列

从 .frm 和 .ibd 文件恢复表？

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

如何选择每组的第一行？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

影响性能的位运算符

1 个回答

相关问题