SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

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

Operadores bit a bit afetando o desempenho

772

Recentemente, vi alguns procedimentos armazenados vindo em minha direção que contêm operadores BITWISE que estão causando problemas ao otimizador. Sei, por brincar com os planos de execução, que essa é a causa do problema, mas não tenho 100% de certeza do raciocínio. Isso ocorre porque o curto-circuito não funciona como linguagens procedurais. Com o SQL server então avalia tudo na WHEREclausula ao mesmo tempo e é forçado a escanear a tabela inteira?

Em anexo está um trecho de código de dentro do proc, alterei um pouco para teste. Como posso reescrever isso para remover o 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 respostas

Voted

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

O curto-circuito nem sempre funciona da maneira esperada no SQL Server. Você realmente tem muito pouco a dizer em que ordem suas cláusulas where são avaliadas; mesmo se você fizer essa filtragem em uma subconsulta ou CTE primeiro, o otimizador ainda poderá avaliá-la em uma ordem inesperada. As duas únicas opções que vi para possivelmente superar isso:

(a) despejar os resultados filtrados intermediários em uma tabela #temp.

(b) tente adicionar o operador bit a bit à condição ON, em vez da cláusula WHERE, e use FORCE ORDER para garantir que as junções sejam avaliadas na ordem em que você as escreve. Não entendo as diferenças entre suas duas consultas e acho que a pergunta contém muito código desnecessário, mas tentaria esta variação:

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

(Também tomei a liberdade de adicionar dbo.referências à sua tabela, que você sempre deve usar e ASpara facilitar a leitura. Desculpe, sou um pouco meticuloso quanto a isso.)

Sem garantias, obviamente, mas vale a pena tentar. Isso pressupõe que sua conclusão sobre curto-circuito esteja correta: como você chegou a essa conclusão? Você pode mostrar os planos de execução e o que o levou ao curto-circuito como um possível culpado? A maior parte da consulta está lidando com muitas linhas que teoricamente deveriam ter sido filtradas pelo operador bit a bit?

EDITAR

Eu escrevi sobre este cenário: http://www.sqlperformance.com/2012/08/t-sql-queries/dry-principle

Operadores bit a bit afetando o desempenho

Como ver a lista de bancos de dados no Oracle?

Quão grande deve ser o mysql innodb_buffer_pool_size?

Listar todas as colunas de uma tabela especificada

restaurar a tabela do arquivo .frm e .ibd?

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Como selecionar a primeira linha de cada grupo?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

Operadores bit a bit afetando o desempenho

1 respostas

relate perguntas