Início / dba / Perguntas / 183486
Accepted
Nate
Asked: 2017-08-15 14:19:31 +0800 CST

SELECT COUNT(*) em uma visualização é ordens de magnitude mais lenta que SELECT * na mesma visualização

  • 772

A vista

CREATE VIEW [dbo].[vProductList]
WITH SCHEMABINDING
AS 

SELECT
     p.[Id]
    ,p.[Name]
    ,price.[Value] as CalculatedPrice
    ,orders.[Value] as OrdersWithThisProduct
FROM 
    products as p 
    INNER JOIN productMetadata as price ON p.Id = price.ProductId AND price.MetaId = 1
    INNER JOIN productMetadata as orders ON p.Id = orders.ProductId AND orders.MetaId = 2

Para simplificar, suponha que productMetadatatem colunas ProductId, MetaId, Valuecom ~ 87 milhões de linhas e cerca de 400 mil linhas na productstabela.

As consultas gerais nesta visão funcionam perfeitamente:

SELECT * FROM vProductList WHERE CalculatedPrice > 500

A consulta resulta em 2-4 segundos (por uma VPN e remota, então estou bem com isso).

Alterar o acima para uma contagem é igualmente rápido:

SELECT COUNT(*) from vProductList WHERE CalculatedPrice > 500

é executado quase ao mesmo tempo que a seleção bruta, com a qual novamente estou bem. Existem cerca de 10 mil produtos que atendem a esse critério.

Eu me deparei com dois casos separados onde as coisas ficam realmente estranhas e levam PARA SEMPRE.

Primeiro

Fazendo uma consulta em uma das colunas da tabela base na exibição:

SELECT * FROM vProductList WHERE Name = 'Hammer'

Essa consulta demora um pouco para ser executada (20 a 30 segundos) e retorna cerca de 30 mil resultados; no entanto, uma pequena alteração na consulta:

SELECT COUNT(*) FROM vProductList WHERE Name = 'Hammer'

leva treze MINUTOS para retornar uma contagem informando ~30k .

Segundo

Fazendo uma WHERE INsubconsulta

SELECT * FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

Essa consulta retorna ~300k linhas e leva dois minutos para retornar (muito desse tempo é simplesmente gasto baixando os dados no SSMS, acredito); no entanto, alterar isso para um SELECT COUNT(*)resulta em uma consulta que leva vinte minutos.

SELECT COUNT(*) FROM vProductList WHERE Id IN (SELECT ProductId FROM TableThatHasFKToProductId and ColumnInTable = 'Yes')

Por que é que SELECT *é mais rápido do que SELECT COUNT?

Estou usando o tempo total de execução fornecido pelo SSMS para todos os horários listados aqui.

Planos de execução

Plano paraSELECT 1 FROM v WHERE IN (...)

Plano paraSELECT COUNT(0) FROM v WHERE IN (...)

Nota: Eu tentei usar o PasteThePlan, mas ele continuava dizendo que o plano era xml inválido.

performance view

1 respostas

  1. Best Answer

    Dos planos de execução fornecidos, para o COUNTcaso do otimizador escolher uma estratégia de agregação local/global em torno da junção final:

    Junção final

    Infelizmente, o otimizador superestima a eficácia da agregação local. Ele estima 136 linhas conduzindo a junção de loops aninhados, enquanto 366.115 são encontradas em tempo de execução.

    Estimativas

    As pesquisas de índice 366.115 podem não ser um grande problema para uma instância local do SQL Server, mas as estatísticas de espera incluídas no plano mostram as limitações de E/S (e possivelmente memória) de sua configuração atual do Banco de Dados SQL do Azure:

    Estatísticas de espera

    O plano para SELECT 1mostra uma estratégia exclusivamente de junção de hash e mesclagem, que produz melhores resultados neste caso com os recursos de memória e/ou E/S muito limitados.

    Você pode ver um melhor desempenho para a primeira consulta com uma OPTION (HASH JOIN, MERGE JOIN)dica, mas o problema fundamental é a baixa estimativa de distribuição de cardinalidade/dados impulsionada pelo grande número de junções.

    Não se deixe enganar pelas porcentagens de custo mostradas em relação a cada operadora de plano - esses números são atualmente derivados da estimativa de custo do otimizador (usando um modelo abstrato). Os números não refletem as condições de tempo de execução ou custos.

    Grandes desvios entre as contagens de linhas estimadas e reais geralmente podem levar a problemas. Isso é especialmente verdadeiro para uma subestimação que faz com que o otimizador escolha uma estratégia que não seja bem dimensionada em uma configuração de hardware específica.

    • 5

relate perguntas