Sql-server – Bitwise operators affecting performance

sql servert-sql

I have recently seen some stored procs coming my way that contain BITWISE operators that are causing the optimizer problems. I know from playing with execution plans that these are the cause of the issue, but I'm not 100% sure the reasoning. Is this because of short circuiting not functioning as procedural languages. With SQL server then evaluates everything in the WHERE clause at the same time and is forced to scan the whole table?

Attached is a snippet of code from inside the proc, I have amended slightly for testing. How can I rewrite this to remove the 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

Best Answer

Short circuiting does not always function the way you might expect in SQL Server. You really have very little say in what order your where clauses are evaluated; even if you do this filtering in a subquery or CTE first, the optimizer still might evaluate it in an order you don't expect. The only two options I've seen to possibly overcome this:

(a) dump the intermediate filtered results to a #temp table.

(b) try adding the bitwise operator to the ON condition, instead of the WHERE clause, and use FORCE ORDER to ensure that the joins are evaluated in the order that you write them. I don't understand the differences between your two queries, and I think the question contains a lot of unnecessary code, but I would try this variation:

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);

(I've also taken the liberty to add dbo. to your table references, which you should always be using, and AS for readability. Sorry, I am a little meticulous about this.)

No guarantees, obviously, but worth a try. This is assuming that your conclusion about short circuiting is even correct: how did you come to this conclusion? Can you show the execution plans and what led you to short circuiting as a possible culprit? Is the bulk of the query dealing with way too many rows that theoretically should have been filtered out by the bitwise operator?

EDIT

I've blogged about this scenario: http://www.sqlperformance.com/2012/08/t-sql-queries/dry-principle

Related Question