Início / dba / Perguntas / 126006
Accepted
boot4life
Asked: 2016-01-13 07:40:20 +0800 CST

Como usar o modo de lote com um UNPIVOT (uma junção de loop)?

  • 772

Tenho uma consulta no seguinte formulário:

SELECT ...
FROM ColumnstoreTable cs
CROSS APPLY (
    SELECT *
    FROM (VALUES
        ('A', cs.DataA)
        , ('B', cs.DataB)
        , ('C', cs.DataC)
    ) x(Col0, Col1)
) someValues

Isso pega cada linha de uma subconsulta suportada por Columnstore ( ColumnstoreTable) e multiplica essas linhas. Este é essencialmente um arquivo UNPIVOT. A consulta real é maior do que isso. Essa parte da consulta alimenta outro processamento.

O problema aqui é que isso CROSS APPLYé implementado como uma junção de loop, o que é uma escolha razoável. Infelizmente, junções de loop não suportam o modo de lote.

Essa parte da consulta é muito crítica para o desempenho e suspeito que executá-la em modo de lote pode ser muito benéfica para o desempenho.

Como posso reescrever esta consulta para não sair do modo de lote?

Eu tentei usar uma tabela temporária em vez de VALUES, mas isso não mudou o fato de que não há condição de junção de igualdade para junção de hash.

sql-server sql-server-2014

1 respostas

  1. Best Answer

    Uma abordagem pode ser usar uma tabela #temp para os valores e também introduzir uma coluna equijunta fictícia para permitir uma junção de hash. Por exemplo:

    -- Create a #temp table with a dummy column to match the hash join
-- and the actual column you want
CREATE TABLE #values (dummy INT NOT NULL, Col0 CHAR(1) NOT NULL)
INSERT INTO #values (dummy, Col0)
VALUES (0, 'A'),
        (0, 'B'),
        (0, 'C')
GO

-- A similar query, but with a dummy equijoin condition to allow for a hash join
SELECT v.Col0, 
    CASE v.Col0 
        WHEN 'A' THEN cs.DataA 
        WHEN 'B' THEN cs.DataB 
        WHEN 'C' THEN cs.DataC
        END AS Col1
FROM ColumnstoreTable cs
JOIN #values v
    -- Join your dummy column to any numeric column on the columnstore,
    -- multiplying that column by 0 to ensure a match to all #values
    ON v.dummy = cs.DataA * 0

    Desempenho e plano de consulta

    Essa abordagem produz um plano de consulta como o seguinte, e a correspondência de hash é executada no modo de lote:

    insira a descrição da imagem aqui

    Se eu substituir a SELECTinstrução por uma SUMda CASEinstrução para evitar ter que transmitir todas essas linhas para o console e, em seguida, executar a consulta em uma tabela columnstore de linha real de 100 MM que tenho por aí, vejo um desempenho razoavelmente bom para gerar os 300 MM necessários linhas:

    CPU time = 33803 ms, elapsed time = 4363 ms.

    E o plano real mostra uma boa paralelização da junção de hash.

    insira a descrição da imagem aqui

    Notas sobre a paralelização de hash join quando todas as linhas têm o mesmo valor

    O desempenho desta consulta depende fortemente de cada thread no lado da sonda da junção ter acesso à tabela de hash completa (em oposição a uma versão particionada por hash, que mapearia todas as linhas para um único thread, dado que existe apenas um valor distinto para a dummycoluna).

    Felizmente, isso é verdade neste caso (como podemos ver pela falta de um Parallelismoperador no lado da sonda) e deve ser verdadeiro porque o modo em lote cria uma única tabela de hash que é compartilhada entre os encadeamentos. Portanto, cada thread pode obter suas linhas Columnstore Index Scane combiná-las com essa tabela de hash compartilhada. No SQL Server 2012, essa funcionalidade era muito menos previsível porque um derramamento fazia com que o operador reiniciasse no modo Row, perdendo o benefício do modo batch e também exigindo um Repartition Streamsoperador no lado da sonda da junção, o que causaria a distorção do thread nesse caso . Permitir que os derramamentos permaneçam no modo de lote é uma grande melhoria no SQL Server 2014.

    Que eu saiba, o modo de linha não tem esse recurso de tabela de hash compartilhada. No entanto, em alguns casos, normalmente com uma estimativa de menos de 100 linhas no lado da compilação, o SQL Server criará uma cópia separada da tabela de hash para cada thread (identificável pela Distribute Streamsliderança na junção de hash). Isso pode ser muito poderoso, mas é muito menos confiável do que o modo em lote, pois depende de suas estimativas de cardinalidade e o SQL Server está tentando avaliar os benefícios em relação ao custo de criar uma cópia completa da tabela de hash para cada thread.

    UNION ALL: uma alternativa mais simples

    Paul White apontou que outra opção, potencialmente mais simples, seria usar UNION ALLpara combinar as linhas para cada valor. Esta é provavelmente a sua melhor aposta, assumindo que é fácil para você construir este SQL dinamicamente. Por exemplo:

    SELECT 'A' AS Col0, c.DataA AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'B' AS Col0, c.DataB AS Col1
FROM ColumnstoreTable c
UNION ALL
SELECT 'C' AS Col0, c.DataC AS Col1
FROM ColumnstoreTable c

    Isso também gera um plano capaz de utilizar o modo de lote e oferece desempenho ainda melhor do que a resposta original. (Embora em ambos os casos o desempenho seja rápido o suficiente para que qualquer seleção ou gravação de dados em uma tabela rapidamente se torne o gargalo.) A UNION ALLabordagem também evita jogos como multiplicar por 0. Às vezes, é melhor pensar de forma simples!

    CPU time = 8673 ms, elapsed time = 4270 ms.

    insira a descrição da imagem aqui

    • 12

relate perguntas