Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

many thanks

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
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
    
Thanks Aaron, I will test this shortly, what is the best way to put the execution plans here? I included the whole code but attempted to highlight the problem line in case it had an relevance on the usage. I came to the conclusion by replacing the bitwise with a AND statement and thn looked at the execution plans which reduced by X10 –  DamagedGoods Jun 22 '12 at 11:31
    
Probably best to post to github or pastebin. I talked about this a bit in a recent blog post : dba.blogoverflow.com/2012/06/help-us-help-you –  Aaron Bertrand Jun 22 '12 at 12:19
    
attached are the TIME stats SQL Server Execution Times: CPU time = 16 ms, elapsed time = 66 ms. CPU time = 15 ms, elapsed time = 743 ms. Attached is the xml from the query plan gist.github.com/2987367.git I was using SQL Server sentry, I couldn't see how to attach the plan created to github, only xml as its in text? –  DamagedGoods Jun 25 '12 at 9:12
    
@Chris when I click the github link I get "HTTP 406 Not Acceptable." Any old file sharing service (that doesn't require registration) or download link will do. –  Aaron Bertrand Jun 25 '12 at 17:21
    
apologies, here is the link depositfiles.com/files/xq78xtkgv . I put this back into the sql sentry tool as its easier to follow –  DamagedGoods Jun 26 '12 at 15:02
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.