Início / dba / Perguntas / 133712
Accepted
JohnG
Asked: 2016-03-30 10:03:30 +0800 CST

Desempenho de a=0 e b=0 e ... z=0 vs a+b+c+d=0

  • 772

Esta é uma pergunta simples para a qual não consigo encontrar a resposta.

Em termos de desempenho, se eu tiver uma WHEREcláusula como a=0 and b=0 and ... z=0, eu obteria algum desempenho se substituísse essa condição por a+b+...+z=0?

Em outras palavras, há algum ganho de desempenho substituindo o seguinte

Select * 
From MyTable 
Where A=0 and B=0 and C=0 and D=0...

Com

Select * 
From MyTable 
Where A+B+C+D=0...

Sei que pode depender de índices, mas, para esse fim, digamos que não existam índices. O operador aritmético (+) funciona melhor que um operador lógico "OU" ou "E"?

Tenho a impressão de que a adição funciona melhor do que várias condições com ANDs ou ORs.

Resultado dos testes

Em uma tabela de 4,2 milhões de linhas

Retornando linhas Onde A=0 B=0 e C=0 -> 351748 Linhas

A adição (A+B+C=0) levou 5 segundos, enquanto as condições lógicas A=0 e B=0 e C=0 levaram 11 segundos.

Por outro lado

Retornando linhas Onde A<>0 B<>0 ou C<>0 -> 3829750 Linhas 58 segundos

Retornando linhas Onde F65+ F67+f64<>0 -> 3829750 Linhas 57 segundos

Para o OR, parece que não há diferença significativa.

Concordo com gbn:

Se A é -1 e B é 1, A+B=0 mas A=0 e B= 0 é falso

e com AMtwo:

ABS(A)+ABS(B)+ABS(C)+ABS(D)... Mesmo que você espere apenas valores positivos, se a coluna aceitar valores negativos, você deve presumir que poderá encontrar um

Os resultados são muito impressionantes, como pensei, parece que a adição é muito mais rápida que os operadores lógicos.

A = Float, B = Dinheiro e C = Float. A consulta utilizada é a mostrada. No meu caso, todos são números positivos. Sem índices. É lógico em minha mente que a adição seja mais rápida do que as condições lógicas!

sql-server performance

5 respostas

  1. Best Answer

    Na sua pergunta, você detalha alguns testes que preparou onde "comprova" que a opção de adição é mais rápida do que comparar as colunas discretas. Eu suspeito que sua metodologia de teste pode ser falha de várias maneiras.

    Primeiro, você precisa garantir que não está testando o SQL Server Management Studio (ou qualquer cliente que esteja usando). Por exemplo, se você estiver executando um a SELECT *partir de uma tabela com 3 milhões de linhas, estará testando principalmente a capacidade do SSMS de extrair linhas do SQL Server e renderizá-las na tela. É muito melhor usar algo como SELECT COUNT(1)o que nega a necessidade de puxar milhões de linhas pela rede e renderizá-las na tela.

    Em segundo lugar, você precisa estar ciente do cache de dados do SQL Server. Normalmente, testamos a velocidade de leitura de dados do armazenamento e processamento desses dados de um cache frio (ou seja, os buffers do SQL Server estão vazios). Ocasionalmente, faz sentido fazer todos os seus testes com um cache quente, mas você precisa abordar seus testes explicitamente com isso em mente.

    Para um teste de cache frio, você precisa executar CHECKPOINTe DBCC DROPCLEANBUFFERSantes de cada execução do teste.

    Para o teste que você perguntou em sua pergunta, criei o seguinte test-bed:

    IF COALESCE(OBJECT_ID('tempdb..#SomeTest'), 0) <> 0
BEGIN
    DROP TABLE #SomeTest;
END
CREATE TABLE #SomeTest
(
    TestID INT NOT NULL
        PRIMARY KEY 
        IDENTITY(1,1)
    , A INT NOT NULL
    , B FLOAT NOT NULL
    , C MONEY NOT NULL
    , D BIGINT NOT NULL
);

INSERT INTO #SomeTest (A, B, C, D)
SELECT o1.object_id, o2.object_id, o3.object_id, o4.object_id
FROM sys.objects o1
    , sys.objects o2
    , sys.objects o3
    , sys.objects o4;

SELECT COUNT(1) 
FROM #SomeTest;

    Isso retorna uma contagem de 260.144.641 em minha máquina.

    Para testar o método "adição", eu executo:

    CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE (st.A + st.B + st.C + st.D) = 0;
GO
SET STATISTICS IO, TIME OFF;

    A guia de mensagens mostra:

    Tabela '#AlgumTeste'. Contagem de varredura 3, leituras lógicas 1322661, leituras físicas 0, leituras antecipadas 1313877, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server: tempo de CPU = 49047 ms, tempo decorrido = 173451 ms.

    Para o teste de "colunas discretas":

    CHECKPOINT 5;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;

SET STATISTICS IO, TIME ON;
GO
SELECT COUNT(1)
FROM #SomeTest st
WHERE st.A = 0
    AND st.B = 0
    AND st.C = 0
    AND st.D = 0;
GO

SET STATISTICS IO, TIME OFF;

    novamente, na guia de mensagens:

    Tabela '#AlgumTeste'. Contagem de varredura 3, leituras lógicas 1322661, leituras físicas 0, leituras antecipadas 1322661, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server: tempo de CPU = 8938 ms, tempo decorrido = 162581 ms.

    Nas estatísticas acima, você pode ver a segunda variante, com as colunas discretas comparadas a 0, o tempo decorrido é cerca de 10 segundos mais curto e o tempo da CPU é cerca de 6 vezes menor. As longas durações em meus testes acima são principalmente resultado da leitura de muitas linhas do disco. Se você diminuir o número de linhas para 3 milhões, verá que as taxas permanecem praticamente as mesmas, mas os tempos decorridos caem visivelmente, pois a E/S do disco tem muito menos efeito.

    Com o método "Adição":

    Tabela '#AlgumTeste'. Contagem de varredura 3, leituras lógicas 15255, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server: tempo de CPU = 499 ms, tempo decorrido = 256 ms.

    Com o método "colunas discretas":

    Tabela '#AlgumTeste'. Contagem de varredura 3, leituras lógicas 15255, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server: tempo de CPU = 94 ms, tempo decorrido = 53 ms.

    O que fará uma diferença realmente grande para este teste? Um índice apropriado, como:

    CREATE INDEX IX_SomeTest ON #SomeTest(A, B, C, D);

    O método de "adição":

    Tabela '#AlgumTeste'. Contagem de varredura 3, leituras lógicas 14235, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server: tempo de CPU = 546 ms, tempo decorrido = 314 ms.

    O método "colunas discretas":

    Tabela '#AlgumTeste'. Contagem de varredura 1, leituras lógicas 3, leituras físicas 0, leituras antecipadas 0, leituras lógicas lob 0, leituras físicas lob 0, leituras antecipadas lob 0.

    Tempos de execução do SQL Server: tempo de CPU = 0 ms, tempo decorrido = 0 ms.

    O plano de execução para cada consulta (com o índice acima no local) é bastante revelador.

    O método "adição", que deve realizar uma varredura de todo o índice:

    insira a descrição da imagem aqui

    e o método "colunas discretas", que pode buscar a primeira linha do índice onde a coluna do índice principal, A, é zero:

    insira a descrição da imagem aqui

    • 47

  2. Digamos que você tenha um índice em A, B, C e D. Também pode ser filtrado.

    É mais provável que use o índice do que a adição.

    Where A=0 and B=0 and C=0 and D=0

    Em outras notícias, Se A for -1 e B for 1, A+B=0é verdadeiro, mas A=0 and B=0é falso.

    • 24

  3. (Observe que esta resposta foi enviada antes de qualquer teste ser anotado na pergunta: o texto da pergunta terminava logo acima da seção de resultados do teste .)

    Eu acho que as ANDcondições separadas seriam preferidas, pois o otimizador teria mais probabilidade de causar um curto-circuito na operação se uma única delas não fosse igual a 0, sem a necessidade de fazer um cálculo primeiro.

    Ainda assim, como esta é uma questão de desempenho, você deve primeiro configurar um teste para determinar a resposta em seu hardware. Relate esses resultados, mostrando seu código de teste e peça a outras pessoas que o examinem para ter certeza de que foi um bom teste. Pode haver outros fatores dignos de consideração nos quais você não pensou.

    • 7

  4. Algum raciocínio geral, se você não tiver nenhum índice em mãos, não acho que fará muita diferença qual das duas soluções você escolherá, ambas terão um desempenho ruim. Se, por outro lado, você tiver um índice em uma ou mais colunas no predicado, o primeiro provavelmente terá um desempenho melhor que o segundo, já que o segundo provavelmente não poderá utilizar o(s) índice(s).

    As disjunções (OR) em geral têm um desempenho pior do que as conjunções (AND), mas mesmo que você tenha uma consulta com disjunções, apostarei na primeira.

    • 3

  5. Esta é uma pergunta simples

    Não não é. Esse (tipo de) pergunta é o que atormenta muitos DBAs e desenvolvedores de software todos os dias, e é quase trivial.

    para a qual não consigo encontrar a resposta.

    Sim, você não vai. Pelo menos não uma resposta geral. Em primeiro lugar, vai depender muito de qual RDBMS você está usando (OK, você está usando , mas ainda assim). Pode até mudar quando você passa de uma versão do seu RDBMS para outra.

    Então, pode depender de qualquer quantidade de outros pequenos detalhes, por exemplo, como seu banco de dados armazena os dados, se você tem sub-seleções/junções que confundem o problema para o otimizador de plano, etc. O otimizador pode fornecer diferentes planos de execução, dependendo em quantas linhas você tem...

    Fazer um teste do mundo real geralmente é a única maneira útil de resolver questões como essa. Além disso, quaisquer ganhos obtidos por otimizações "arcanas" como essa são geralmente engolidos dez vezes pela escolha inteligente de índices, então eu não me incomodaria em gastar muito tempo com isso, antes que o uso de índices fosse realmente descartado.

    • 2

relate perguntas