Início / dba / Perguntas / 224998
Accepted
Salman A
Asked: 2018-12-15 04:13:03 +0800 CST

Encontre grupos com exatamente um valor: COUNT(DISTINCT x) = 1 vs MIN(x) = MAX(x)

  • 772

Dados esses dados:

gid | val
1   | a
1   | a
1   | a
2   | b
3   | x
3   | y
3   | z

as seguintes consultas retornam grupos (gid) que contêm exatamente um valor distinto (val):

SELECT gid FROM t GROUP BY gid HAVING MIN(val) = MAX(val)
SELECT gid FROM t GROUP BY gid HAVING COUNT(DISTINCT val) = 1

As pessoas parecem sugerir que a primeira variante seria mais rápida (se supondo que existam índices apropriados, procurar MIN e MAX seria mais rápido do que contar todos os valores). Isso é um fato ou um mito.

sql-server performance

1 respostas

  1. Best Answer

    A versão curta é que você deve esperar MIN(val) = MAX(val)ser melhor para consultas rowstore em todos os casos e COUNT(DISTINCT val) = 1ser melhor para consultas columnstore quando valfor uma coluna de string.

    Coloquei 6,4 milhões de linhas em uma tabela para testar. Os dados têm uma distribuição de dados aproximadamente semelhante aos seus dados de amostra:

    DROP TABLE IF EXISTS dbo.t224998_2;

CREATE TABLE dbo.t224998_2 (
gid INT,
val VARCHAR(20)
);

INSERT INTO dbo.t224998_2 WITH (TABLOCK)
SELECT 
CASE WHEN q.RN % 7 <= 2 THEN q.RN - q.RN % 7
WHEN q.RN % 7 >= 4 THEN q.RN - q.RN % 7 + 4
ELSE q.RN END
, CHAR(65 + q.RN % 7)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 RN 
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;

    Aqui estão as primeiras 14 linhas da tabela:

    primeiras linhas

    Aqui estão os planos de consulta ao executar seu par de consultas sem nenhum índice:

    insira a descrição da imagem aqui

    A consulta com MINe MAXtem apenas um único operador de agregação de hash. A COUNT(DISTINCT)consulta tem dois. Para a segunda consulta, o operador mais à direita mantém apenas as linhas distintas e o operador mais à esquerda realiza a contagem. Não surpreendentemente, o DISTINCT queryé cerca de duas vezes mais lento.

    A criação do índice a seguir torna as duas consultas mais competitivas:

    CREATE INDEX IX1 ON dbo.t224998_2 (gid, val);

    Agora os planos estão assim:

    insira a descrição da imagem aqui

    Agora, a consulta distinta é cerca de 25% mais lenta. Ressalte-se aqui que para nenhum desses planos os MINe MAXvalores são "procurados". Você está consultando a tabela sem um filtro. O SQL Server fará a varredura de todas as linhas do índice ou da tabela. O índice é útil porque pode ser verificado na ordem de chave e permite que o agregado seja calculado com mais eficiência. Para a MIN(val) = MAX(val)consulta, a agregação de fluxo lê as linhas ordenadas e acompanha o valor mínimo e máximo visto para cada valor exclusivo de gid. Ele passa a linha para o próximo operador quando encontra um novo valor para gid. Em nenhum momento é realizada uma busca de índice para obter o valor mínimo ou máximo. Você pode escrever uma consulta para fazer isso, mas é um pouco complicado.

    A COUNT(DISTINCT)consulta novamente divide o trabalho em duas agregações. Ambos os agregados aproveitam a ordenação do índice. O mais à direita remove as linhas duplicadas e o mais à esquerda realiza a contagem.

    Se eu alterar a tabela para um columnstore sem índices não clusterizados, a segunda consulta se tornará a vencedora. Aqui estão os planos:

    insira a descrição da imagem aqui

    O MIN(val) = MAX(val)executa todo o trabalho agregado no modo de linha. Agregados que retornam colunas de string não são compatíveis com o modo em lote. Essa restrição é documentada pela Microsoft. COUNT(DISTINCT val)é suportado pelo modo de lote e, como resultado, todo o trabalho agregado é executado no modo de lote. Essa consulta é duas vezes mais rápida que a MIN(val) = MAX(val)opção.

    • 7

relate perguntas