Quais são as diferentes maneiras de substituir ISNULL() em uma cláusula WHERE que usa apenas valores literais?

Seção de resposta

Existem várias maneiras de reescrever isso usando diferentes construções T-SQL. Analisaremos os prós e os contras e faremos uma comparação geral abaixo.

Primeiro : UsandoOR

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

O uso ORnos dá um plano de busca mais eficiente, que lê o número exato de linhas que precisamos, mas adiciona o que o mundo técnico chama a whole mess of malarkeyao plano de consulta.

Observe também que o Seek é executado duas vezes aqui, o que realmente deve ser mais óbvio do operador gráfico:

Table 'Users'. Scan count 2, logical reads 8233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 469 ms,  elapsed time = 473 ms.

Segundo : Usar tabelas derivadas com UNION ALL Nossa consulta também pode ser reescrita assim

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

Isso produz o mesmo tipo de plano, com muito menos malandragem e um grau mais aparente de honestidade sobre quantas vezes o índice foi buscado (procurado?)

Ele faz a mesma quantidade de leituras (8233) que a ORconsulta, mas reduz cerca de 100ms de tempo de CPU.

CPU time = 313 ms,  elapsed time = 315 ms.

No entanto, você precisa ter muito cuidado aqui, porque se esse plano tentar ficar paralelo, as duas COUNToperações separadas serão serializadas, porque cada uma delas é considerada uma agregação escalar global. Se forçarmos um plano paralelo usando Trace Flag 8649, o problema se torna óbvio.

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)
OPTION(QUERYTRACEON 8649);

Isso pode ser evitado alterando um pouco nossa consulta.

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

Agora, ambos os nós que executam um Seek são totalmente paralelizados até atingirmos o operador de concatenação.

Para o que vale a pena, a versão totalmente paralela tem alguns bons benefícios. Ao custo de cerca de 100 leituras adicionais e cerca de 90 ms de tempo de CPU adicional, o tempo decorrido diminui para 93 ms.

Table 'Users'. Scan count 12, logical reads 8317, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 500 ms,  elapsed time = 93 ms.

E o CROSS APPLY? Nenhuma resposta está completa sem a magia do CROSS APPLY!

Infelizmente, temos mais problemas com o COUNT.

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

Esse plano é horrível. Este é o tipo de plano com o qual você acaba quando aparece por último no Dia de São Patrício. Embora bem paralelo, por algum motivo está escaneando o PK/CX. Ai credo. O plano tem um custo de 2198 dólares de consulta.

Table 'Users'. Scan count 7, logical reads 31676233, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 29532 ms,  elapsed time = 5828 ms.

O que é uma escolha estranha, porque se o forçarmos a usar o índice não clusterizado, o custo cairá significativamente para 1.798 dólares de consulta.

SELECT SUM(Records)
FROM dbo.Users AS u 
CROSS APPLY 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u2 WITH (INDEX(ix_Id_Age))
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

Ei, procura! Dá uma olhada lá. Observe também que com a magia do CROSS APPLY, não precisamos fazer nada bobo para ter um plano quase totalmente paralelo.

Table 'Users'. Scan count 5277838, logical reads 31685303, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 27625 ms,  elapsed time = 4909 ms.

A aplicação cruzada acaba se saindo melhor sem as COUNTcoisas lá dentro.

SELECT SUM(Records)
FROM dbo.Users AS u
CROSS APPLY 
(
    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id
    AND u2.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u2
    WHERE u2.Id = u.Id 
    AND u2.Age IS NULL
) x (Records);

O plano parece bom, mas as leituras e a CPU não são uma melhoria.

Table 'Users'. Scan count 20, logical reads 17564, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 4844 ms,  elapsed time = 863 ms.

Reescrever a aplicação cruzada para ser uma junção derivada resulta exatamente no mesmo tudo. Não vou postar novamente o plano de consulta e as informações de estatísticas - eles realmente não mudaram.

SELECT COUNT(u.Id)
FROM dbo.Users AS u
JOIN 
(
    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT u.Id
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x ON x.Id = u.Id;

Álgebra Relacional : Para ser completo e evitar que Joe Celko assombre meus sonhos, precisamos pelo menos tentar algumas coisas relacionais estranhas. Aqui vai nada!

Uma tentativa comINTERSECT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   INTERSECT
                   SELECT u.Age WHERE u.Age IS NOT NULL );

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 1094 ms,  elapsed time = 1090 ms.

E aqui está uma tentativa comEXCEPT

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE NOT EXISTS ( SELECT u.Age WHERE u.Age >= 18
                   EXCEPT
                   SELECT u.Age WHERE u.Age IS NULL);

Table 'Users'. Scan count 7, logical reads 9247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2126 ms,  elapsed time = 376 ms.

Pode haver outras maneiras de escrever isso, mas deixarei isso para as pessoas que talvez usem EXCEPTe com INTERSECTmais frequência do que eu.

Se você realmente precisa de uma contagem , eu uso COUNTem minhas consultas como um atalho (leia: às vezes sou muito preguiçoso para criar cenários mais complicados). Se você precisar apenas de uma contagem, poderá usar uma CASEexpressão para fazer praticamente a mesma coisa.

SELECT SUM(CASE WHEN u.Age < 18 THEN 1
                WHEN u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

SELECT SUM(CASE WHEN u.Age < 18 OR u.Age IS NULL THEN 1
                ELSE 0 END) 
FROM dbo.Users AS u

These both get the same plan and have the same CPU and read characteristics.

Table 'Users'. Scan count 1, logical reads 9157, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 719 ms,  elapsed time = 719 ms.

The winner? In my tests, the forced parallel plan with SUM over a derived table performed the best. And yeah, many of these queries could have been assisted by adding a couple filtered indexes to account for both predicates, but I wanted to leave some experimentation to others.

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

Thanks!

I wasn't game to restore a 110 GB database for just one table so I created my own data. The age distributions should match what's on Stack Overflow but obviously the table itself won't match. I don't think that it's too much of an issue because the queries are going to hit indexes anyway. I'm testing on a 4 CPU computer with SQL Server 2016 SP1. One thing to note is that for queries that finish this quickly it's important not to include the actual execution plan. That can slow things down quite a bit.

I started by going through some of the solutions in Erik's excellent answer. For this one:

SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

I got the following results from sys.dm_exec_sessions over 10 trials (the query naturally went parallel for me):

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     3532 ║                975 ║         60830 ║
╚══════════╩════════════════════╩═══════════════╝

The query that worked better for Erik actually performed worse on my machine:

SELECT SUM(Records)
FROM 
(
    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT 1
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records)   
OPTION(QUERYTRACEON 8649);

Results from 10 trials:

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     5704 ║               1636 ║         60850 ║
╚══════════╩════════════════════╩═══════════════╝

I'm not immediately able to explain why it's that bad, but it's not clear why we want to force nearly every operator in the query plan to go parallel. In the original plan we have a serial zone that finds all rows with AGE < 18. There are only a few thousand rows. On my machine I get 9 logical reads for that part of the query and 9 ms of reported CPU time and elapsed time. There's also a serial zone for the global aggregate for the rows with AGE IS NULL but that only processes one row per DOP. On my machine this is just four rows.

My takeaway is that it's most important to optimize the part of the query that finds rows with a NULL for Age because there are millions of those rows. I wasn't able to create an index with less pages that covered the data than a simple page-compressed one on the column. I assume that there's a minimum index size per row or that a lot of the index space cannot be avoided with the tricks that I tried. So if we're stuck with about the same number of logical reads to get the data then the only way to make it faster is to make the query more parallel, but this needs to be done in a different way than Erik's query that used TF 8649. In the query above we have a ratio of 3.62 for CPU time to elapsed time which is pretty good. The ideal would be a ratio of 4.0 on my machine.

One possible area of improvement is to divide the work more evenly among threads. In the screenshot below we can see that one of my CPUs decided to take a little break:

Index scan is one of the few operators that can be implemented in parallel and we can't do anything about how the rows are distributed to threads. There's an element of chance to it as well but pretty consistently I saw one underworked thread. One way to work around this is to do parallelism the hard way: on the inner part of a nested loop join. Anything on the inner part of a nested loop will be implemented in a serial way but many serial threads can run concurrently. As long as we get a favorable parallel distribution method (such as round robin), we can control exactly how many rows are sent to each thread.

I'm running queries with DOP 4 so I need to evenly divide the NULL rows in the table into four buckets. One way to do this is to create a bunch of indexes on computed columns:

ALTER TABLE dbo.Users
ADD Compute_bucket_0 AS (CASE WHEN Age IS NULL AND Id % 4 = 0 THEN 1 ELSE NULL END),
Compute_bucket_1 AS (CASE WHEN Age IS NULL AND Id % 4 = 1 THEN 1 ELSE NULL END),
Compute_bucket_2 AS (CASE WHEN Age IS NULL AND Id % 4 = 2 THEN 1 ELSE NULL END),
Compute_bucket_3 AS (CASE WHEN Age IS NULL AND Id % 4 = 3 THEN 1 ELSE NULL END);

CREATE INDEX IX_Compute_bucket_0 ON dbo.Users (Compute_bucket_0) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_1 ON dbo.Users (Compute_bucket_1) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_2 ON dbo.Users (Compute_bucket_2) WITH (DATA_COMPRESSION = PAGE);
CREATE INDEX IX_Compute_bucket_3 ON dbo.Users (Compute_bucket_3) WITH (DATA_COMPRESSION = PAGE);

I'm not quite sure why four separate indexes is a little faster than one index but that's one what I found in my testing.

To get a parallel nested loop plan I'm going to use the undocumented trace flag 8649. I'm also going to write the code a little strangely to encourage the optimizer not to process more rows than necessary. Below is one implementation which appears to work well:

SELECT SUM(t.cnt) + (SELECT COUNT(*) FROM dbo.Users AS u WHERE u.Age < 18)
FROM 
(VALUES (0), (1), (2), (3)) v(x)
CROSS APPLY 
(
    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_0 = CASE WHEN v.x = 0 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_1 = CASE WHEN v.x = 1 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_2 = CASE WHEN v.x = 2 THEN 1 ELSE NULL END

    UNION ALL

    SELECT COUNT(*) cnt 
    FROM dbo.Users 
    WHERE Compute_bucket_3 = CASE WHEN v.x = 3 THEN 1 ELSE NULL END
) t
OPTION (QUERYTRACEON 8649);

The results from ten trials:

╔══════════╦════════════════════╦═══════════════╗
║ cpu_time ║ total_elapsed_time ║ logical_reads ║
╠══════════╬════════════════════╬═══════════════╣
║     3093 ║                803 ║         62008 ║
╚══════════╩════════════════════╩═══════════════╝

Com essa consulta, temos uma taxa de CPU para tempo decorrido de 3,85! Reduzimos 17 ms do tempo de execução e foram necessárias apenas 4 colunas e índices computados para fazer isso! Cada encadeamento processa muito próximo do mesmo número de linhas em geral porque cada índice tem muito próximo do mesmo número de linhas e cada encadeamento verifica apenas um índice:

Em uma nota final, também podemos apertar o botão fácil e adicionar um CCI não clusterizado à Agecoluna:

CREATE NONCLUSTERED COLUMNSTORE INDEX X_NCCI ON dbo.Users (Age);

A seguinte consulta termina em 3 ms na minha máquina:

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18 OR u.Age IS NULL;

Isso vai ser difícil de vencer.

Although I don't have a local copy of the Stack Overflow database, I was able to try out a couple of queries. My thought was to get a count of users from a system catalog view (as opposed to directly getting a count of rows from the underlying table). Then get a count of rows that do (or maybe do not) match Erik's criteria, and do some simple math.

I used the Stack Exchange Data Explorer (Along with SET STATISTICS TIME ON; and SET STATISTICS IO ON;) to test the queries. For a point of reference, here are some queries and the CPU/IO statistics:

QUERY 1

--Erik's query From initial question.
SELECT COUNT(*)
FROM dbo.Users AS u
WHERE ISNULL(u.Age, 17) < 18;

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Users'. Scan count 17, logical reads 201567, physical reads 0, read-ahead reads 2740, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1829 ms, elapsed time = 296 ms.

QUERY 2

--Erik's "OR" query.
SELECT COUNT(*)
FROM dbo.Users AS u
WHERE u.Age < 18
OR u.Age IS NULL;

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Users'. Scan count 17, logical reads 201567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 2500 ms, elapsed time = 147 ms.

QUERY 3

--Erik's derived tables/UNION ALL query.
SELECT SUM(Records)
FROM 
(
    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age < 18

    UNION ALL

    SELECT COUNT(Id)
    FROM dbo.Users AS u
    WHERE u.Age IS NULL
) x (Records);

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Users'. Scan count 34, logical reads 403134, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 3156 ms, elapsed time = 215 ms.

1st Attempt

This was slower than all of Erik's queries I listed here...at least in terms of elapsed time.

SELECT SUM(p.Rows)  -
  (
    SELECT COUNT(*)
    FROM dbo.Users AS u
    WHERE u.Age >= 18
  ) 
FROM sys.objects o
JOIN sys.partitions p
    ON p.object_id = o.object_id
WHERE p.index_id < 2
AND o.name = 'Users'
AND SCHEMA_NAME(o.schema_id) = 'dbo'
GROUP BY o.schema_id, o.name

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. (1 row(s) returned)

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysrowsets'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'Users'. Scan count 1, logical reads 201567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 593 ms, elapsed time = 598 ms.

2nd Attempt

Here I opted for a variable to store the total number of users (instead of a sub-query). The scan count increased from 1 to 17 compared to the 1st attempt. Logical reads stayed the same. However, elapsed time dropped considerably.

DECLARE @Total INT;

SELECT @Total = SUM(p.Rows)
FROM sys.objects o
JOIN sys.partitions p
    ON p.object_id = o.object_id
WHERE p.index_id < 2
AND o.name = 'Users'
AND SCHEMA_NAME(o.schema_id) = 'dbo'
GROUP BY o.schema_id, o.name

SELECT @Total - COUNT(*)
FROM dbo.Users AS u
WHERE u.Age >= 18

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms. Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysrowsets'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (1 row(s) returned)

Table 'Users'. Scan count 17, logical reads 201567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1471 ms, elapsed time = 98 ms.

Other Notes: DBCC TRACEON is not permitted on Stack Exchange Data Explorer, as noted below:

User 'STACKEXCHANGE\svc_sede' does not have permission to run DBCC TRACEON.

Use variables?

declare @int1 int = ( select count(*) from table_1 where bb <= 1 )
declare @int2 int = ( select count(*) from table_1 where bb is null )
select @int1 + @int2;

Per the comment can skip the variables

SELECT (select count(*) from table_1 where bb <= 1) 
     + (select count(*) from table_1 where bb is null);

Uma solução trivial é calcular count(*) - count (idade >= 18):

SELECT
    (SELECT COUNT(*) FROM Users) -
    (SELECT COUNT(*) FROM Users WHERE Age >= 18);

Ou:

SELECT COUNT(*)
     - COUNT(CASE WHEN Age >= 18)
FROM Users;

Resultados aqui

Bem usandoSET ANSI_NULLS OFF;

SET ANSI_NULLS OFF; 
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT COUNT(*)
FROM dbo.Users AS u
WHERE age=NULL or age<18

Table 'Users'. Scan count 17, logical reads 201567

 SQL Server Execution Times:
 CPU time = 2344 ms,  elapsed time = 166 ms.

Isso é algo que surgiu na minha mente. Apenas executei isso em https://data.stackexchange.com

Mas não tão eficiente quanto @blitz_erik embora