Início / dba / Perguntas / 19344
Accepted
Martin Smith
Asked: 2012-06-16 13:53:55 +0800 CST

Como verificar com eficiência EXISTS em várias colunas?

  • 772

Este é um problema com o qual me deparo periodicamente e ainda não encontrei uma boa solução.

Supondo a seguinte estrutura de tabela

CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)

e o requisito é determinar se uma das colunas anuláveis B​​ou Crealmente contém algum NULLvalor (e, em caso afirmativo, qual(is)).

Suponha também que a tabela contém milhões de linhas (e que não há estatísticas de coluna disponíveis que possam ser espiadas, pois estou interessado em uma solução mais genérica para essa classe de consultas).

Posso pensar em algumas maneiras de abordar isso, mas todas têm pontos fracos.

Duas EXISTSdeclarações separadas. Isso teria a vantagem de permitir que as consultas parem de varrer antecipadamente assim que um NULLfor encontrado. Mas se ambas as colunas de fato não contiverem nenhum NULLs, resultarão em duas varreduras completas.

Consulta agregada única 

SELECT 
    MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T

Isso pode processar as duas colunas ao mesmo tempo, portanto, o pior caso é uma varredura completa. A desvantagem é que, mesmo que encontre um NULLem ambas as colunas muito cedo, a consulta ainda acabará varrendo todo o restante da tabela.

Variáveis ​​do usuário

Eu posso pensar em uma terceira maneira de fazer isso

BEGIN TRY
DECLARE @B INT, @C INT, @D INT

SELECT 
    @B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
    @C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
    /*Divide by zero error if both @B and @C are 1.
    Might happen next row as no guarantee of order of
    assignments*/
    @D = 1 / (2 - (@B + @C))
FROM T  
OPTION (MAXDOP 1)       
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
    BEGIN
    SELECT 'B,C both contain NULLs'
    RETURN;
    END
ELSE
    RETURN;
END CATCH

SELECT ISNULL(@B,0),
       ISNULL(@C,0)

mas isso não é adequado para código de produção, pois o comportamento correto para uma consulta de concatenação agregada é indefinido. e encerrar a varredura lançando um erro é uma solução horrível de qualquer maneira.

Existe outra opção que combine os pontos fortes das abordagens acima?

Editar

Apenas para atualizar isso com os resultados que recebo em termos de leituras das respostas enviadas até agora (usando os dados de teste do @ypercube)

+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          | 2 * EXISTS | CASE | Kejser  |  Kejser  |        Kejser        | ypercube |       8kb        |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
|          |            |      |         | MAXDOP 1 | HASH GROUP, MAXDOP 1 |          |                  |
| No Nulls |      15208 | 7604 |    8343 | 7604     | 7604                 |    15208 | 8346 (8343+3)    |
| One Null |       7613 | 7604 |    8343 | 7604     | 7604                 |     7620 | 7630 (25+7602+3) |
| Two Null |         23 | 7604 |    8343 | 7604     | 7604                 |       30 | 30 (18+12)       |
+----------+------------+------+---------+----------+----------------------+----------+------------------+

Para a resposta de @Thomas, mudei TOP 3para TOP 2potencialmente permitir que ela saísse mais cedo. Eu tenho um plano paralelo por padrão para essa resposta, então também tentei com uma MAXDOP 1dica para tornar o número de leituras mais comparável aos outros planos. Fiquei um pouco surpreso com os resultados, pois no meu teste anterior eu havia visto esse curto-circuito na consulta sem ler a tabela inteira.

O plano para meus dados de teste que curtos-circuitos está abaixo

Curto circuitos

O plano para os dados do ypercube é

Sem curto-circuito

Portanto, ele adiciona um operador de classificação de bloqueio ao plano. Eu também tentei com a HASH GROUPdica, mas isso ainda acaba lendo todas as linhas

Sem curto-circuito

Portanto, a chave parece ser obter um hash match (flow distinct)operador para permitir que esse plano entre em curto-circuito, pois as outras alternativas bloquearão e consumirão todas as linhas de qualquer maneira. Não acho que haja uma dica para forçar isso especificamente, mas aparentemente "em geral, o otimizador escolhe um Flow Distinct onde determina que são necessárias menos linhas de saída do que valores distintos no conjunto de entrada". .

Os dados do @ypercube têm apenas 1 linha em cada coluna com NULLvalores (cardinalidade da tabela = 30300) e as linhas estimadas que entram e saem do operador são ambas 1. Ao tornar o predicado um pouco mais opaco para o otimizador, gerou um plano com o operador Flow Distinct.

SELECT TOP 2 *
FROM (SELECT DISTINCT 
        CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT

Editar 2

Um último ajuste que me ocorreu é que a consulta acima ainda pode acabar processando mais linhas do que o necessário caso a primeira linha que encontrar com a NULLtenha NULLs na coluna Be no C. Ele continuará digitalizando em vez de sair imediatamente. Uma maneira de evitar isso seria desarticular as linhas à medida que são digitalizadas. Então, minha correção final para a resposta de Thomas Kejser está abaixo

SELECT DISTINCT TOP 2 NullExists
FROM test T 
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
                   (CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL

Provavelmente seria melhor que o predicado fosse, WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULLmas contra os dados de teste anteriores que não me dê um plano com um Flow Distinct, enquanto o NullExists IS NOT NULLoutro faz (plano abaixo).

Não articulada

sql-server performance

6 respostas

  1. Best Answer

    Que tal:

    SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS B
        , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS C
  FROM T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT
    • 20

  2. As I understand the question, you want to know whether a null exists in any of the columns values as opposed to actually returning the rows in which either B or C is null. If that is the case, then why not:

    Select Top 1 'B as nulls' As Col
From T
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T
Where T.C Is Null

    On my test rig with SQL 2008 R2 and one million rows, I got the following results in ms from the Client Statistics tab:

    Kejser                          2907,2875,2829,3576,3103
ypercube                        2454,1738,1743,1765,2305
OP single aggregate solution    (stopped after 120,000 ms) Wouldn't even finish
My solution                     1619,1564,1665,1675,1674

    If you add the nolock hint, the results are even faster:

    Select Top 1 'B as nulls' As Col
From T With(Nolock)
Where T.B Is Null
Union All
Select Top 1 'C as nulls'
From T With(Nolock)
Where T.C Is Null

My solution (with nolock)       42,70,94,138,120

    For reference I used Red-gate's SQL Generator to generate the data. Out of my one million rows, 9,886 rows had a null B value and 10,019 had a null C value.

    In this series of tests, every row in column B has a value:

    Kejser                          245200  Scan count 1, logical reads 367259, physical reads 858, read-ahead reads 367278
                                250540  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367280

ypercube(1)                     249137  Scan count 2, logical reads 367276, physical reads 850, read-ahead reads 367278
                                248276  Scan count 2, logical reads 367276, physical reads 869, read-ahead reads 368765

My solution                     250348  Scan count 2, logical reads 367276, physical reads 858, read-ahead reads 367278
                                250327  Scan count 2, logical reads 367276, physical reads 854, read-ahead reads 367278

    Before each test (both sets) I ran CHECKPOINT and DBCC DROPCLEANBUFFERS.

    Aqui estão os resultados quando não há nulos na tabela. Observe que as 2 soluções existentes fornecidas pelo ypercube são quase idênticas às minhas em termos de leituras e tempo de execução. Eu (nós) acreditamos que isso se deve às vantagens da edição Enterprise/Developer usando o Advanced Scanning . Se você estava usando apenas a edição Standard ou inferior, a solução de Kejser pode muito bem ser a solução mais rápida.

    Kejser                          248875  Scan count 1, logical reads 367259, physical reads 860, read-ahead reads 367290

ypercube(1)                     243349  Scan count 2, logical reads 367265, physical reads 851, read-ahead reads 367278
                                242729  Scan count 2, logical reads 367265, physical reads 858, read-ahead reads 367276
                                242531  Scan count 2, logical reads 367265, physical reads 855, read-ahead reads 367278

My solution                     243094  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
                                243444  Scan count 2, logical reads 367265, physical reads 857, read-ahead reads 367278
    • 6

  3. Testado em SQL-Fiddle nas versões: 2008 r2 e 2012 com 30K linhas.

    • A EXISTSconsulta mostra um grande benefício em eficiência quando encontra Nulos antecipadamente - o que é esperado.
    • Obtenho melhor desempenho com a EXISTSconsulta - em todos os casos em 2012, o que não consigo explicar.
    • Em 2008R2, quando não há Nulls, é mais lento que as outras 2 consultas. Quanto mais cedo ele encontrar os Nulls, mais rápido será e quando ambas as colunas tiverem nulls cedo, é muito mais rápido que as outras 2 consultas.
    • A consulta de Thomas Kejser parece ter um desempenho um pouco melhor, mas constantemente melhor em 2012 e pior em 2008R2, em comparação com a CASEconsulta de Martin.
    • A versão 2012 parece ter um desempenho muito melhor. Pode ter a ver com as configurações dos servidores SQL-Fiddle e não apenas com melhorias no otimizador.

    Dúvidas e horários. Horários onde foi feito:

    • 1º sem nulos
    • 2º com coluna Btendo um NULLem um pequeno id.
    • 3º com ambas as colunas tendo uma NULLcada em pequenas ids.

    Aqui vamos nós (há um problema com os planos, vou tentar novamente mais tarde. Siga os links por enquanto):

    Consulta com 2 subconsultas EXISTS

    SELECT 
      CASE WHEN EXISTS (SELECT * FROM test WHERE b IS NULL)
             THEN 1 ELSE 0 
      END AS B,
      CASE WHEN EXISTS (SELECT * FROM test WHERE c IS NULL)
             THEN 1 ELSE 0 
      END AS C ;

-------------------------------------
Times in ms (2008R2): 1344 - 596 -  1  
Times in ms   (2012):   26 -  14 -  2

    Consulta agregada única de Martin Smith

    SELECT 
    MAX(CASE WHEN b IS NULL THEN 1 ELSE 0 END) AS B,
    MAX(CASE WHEN c IS NULL THEN 1 ELSE 0 END) AS C
FROM test ;

--------------------------------------
Times in ms (2008R2):  558 - 553 - 516  
Times in ms   (2012):   37 -  35 -  36

    A pergunta de Thomas Kejser

    SELECT TOP 3 *
FROM (SELECT DISTINCT 
        CASE WHEN B IS NULL THEN NULL ELSE 'foo' END AS b
      , CASE WHEN C IS NULL THEN NULL ELSE 'bar' END AS c
  FROM test T 
  WHERE 
    (B IS NULL AND C IS NOT NULL) 
    OR (B IS NOT NULL AND C IS NULL) 
    OR (B IS NULL AND C IS NULL)
) AS DT ;

--------------------------------------
Times in ms (2008R2):  859 - 705 - 668  
Times in ms   (2012):   24 -  19 -  18

    Minha sugestão (1)

    WITH tmp1 AS
  ( SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id 
  ) 

  SELECT 
      tmp1.*, 
      NULL AS id2, NULL AS b2, NULL AS c2
  FROM tmp1
UNION ALL
  SELECT *
  FROM
    ( SELECT TOP (1)
          tmp1.id, tmp1.b, tmp1.c,
          test.id AS id2, test.b AS b2, test.c AS c2 
      FROM test
        CROSS JOIN tmp1
      WHERE test.id >= tmp1.id
        AND ( test.b IS NULL AND tmp1.c IS NULL
           OR tmp1.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id
    ) AS x ;

--------------------------------------
Times in ms (2008R2): 1089 - 572 -  16   
Times in ms   (2012):   28 -  15 -   1

    Ele precisa de algum polimento na saída, mas a eficiência é semelhante à EXISTSconsulta. Eu pensei que seria melhor quando não houvesse nulos, mas os testes mostram que não.

    Sugestão (2)

    Tentando simplificar a lógica:

    CREATE TABLE tmp
( id INT
, b CHAR(1000)
, c CHAR(1000)
) ;

DELETE  FROM tmp ;

INSERT INTO tmp 
    SELECT TOP (1) 
        id, b, c
    FROM test
    WHERE b IS NULL OR c IS NULL
    ORDER BY id  ; 

INSERT INTO tmp 
    SELECT TOP (1)
        test.id, test.b, test.c 
      FROM test
        JOIN tmp 
          ON test.id >= tmp.id
      WHERE ( test.b IS NULL AND tmp.c IS NULL
           OR tmp.b IS NULL AND test.c IS NULL
            )
      ORDER BY test.id ;

SELECT *
FROM tmp ;

    Parece ter um desempenho melhor em 2008R2 do que a sugestão anterior, mas pior em 2012 (talvez o 2º INSERTpossa ser reescrito usando IF, como a resposta de @8kb):

    ------------------------------------------
Times in ms (2008R2): 416+6 - 1+127 -  1+1   
Times in ms   (2012):  14+1 - 0+27  -  0+29
    • 4

  4. As IFdeclarações são permitidas?

    Isso deve permitir que você confirme a existência de B ou C em uma passagem pela tabela:

    DECLARE 
  @A INT, 
  @B CHAR(10), 
  @C CHAR(10)

SET @B = 'X'
SET @C = 'X'

SELECT TOP 1 
  @A = A, 
  @B = B, 
  @C = C
FROM T 
WHERE B IS NULL OR C IS NULL 

IF @@ROWCOUNT = 0 
BEGIN 
  SELECT 'No nulls'
  RETURN
END

IF @B IS NULL AND @C IS NULL
BEGIN
  SELECT 'Both null'
  RETURN
END 

IF @B IS NULL 
BEGIN
  SELECT TOP 1 
    @C = C
  FROM T
  WHERE A > @A
  AND C IS NULL

  IF @B IS NULL AND @C IS NULL 
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'B is null'
    RETURN
  END
END

IF @C IS NULL 
BEGIN
  SELECT TOP 1 
    @B = B
  FROM T 
  WHERE A > @A
  AND B IS NULL

  IF @C IS NULL AND @B IS NULL
  BEGIN
    SELECT 'Both null'
    RETURN
  END
  ELSE
  BEGIN
    SELECT 'C is null'
    RETURN
  END
END
    • 4

  5. Quando você usa EXISTS, o SQL Server sabe que você está fazendo uma verificação de existência. Quando encontra o primeiro valor correspondente, retorna TRUE e para de procurar.

    quando você concatenar 2 colunas e se alguma for nula o resultado será nulo

    por exemplo

    null + 'a' = null

    então verifique este código

    IF EXISTS (SELECT 1 FROM T WHERE B+C is null)
SELECT Top 1 ISNULL(B,'B ') + ISNULL(C,'C') as [Nullcolumn] FROM T WHERE B+C is null
    • 0

  6. Que tal:

    select 
    exists(T.B is null) as 'B is null',
    exists(T.C is null) as 'C is null'
from T;

    Se isso funcionar (não testei), resultaria em uma tabela de uma linha com 2 colunas, cada uma TRUE ou FALSE. Não testei a eficiência.

    • -3

relate perguntas