Início / dba / Perguntas / 188457
Accepted
Steven Hibble
Asked: 2017-10-14 14:08:02 +0800 CST

Como sugerir junção de muitos para muitos no SQL Server?

  • 772

Eu tenho 3 tabelas "grandes" que se unem em um par de colunas (ambas ints).

  • Table1 tem aproximadamente 200 milhões de linhas
  • A Tabela2 tem aproximadamente 1,5 milhão de linhas
  • Table3 tem aproximadamente 6 milhões de linhas

Cada tabela tem um índice clusterizado em Key1, Key2e, em seguida, mais uma coluna. Key1tem baixa cardinalidade e é muito assimétrica. É sempre referenciado na WHEREcláusula. Key2nunca é mencionado na WHEREcláusula. Cada junção é muitos-para-muitos.

O problema é com a estimativa de cardinalidade. A estimativa de saída de cada junção fica menor em vez de maior . Isso resulta em estimativas finais de centenas baixas quando o resultado real está na casa dos milhões.

Existe alguma maneira de eu orientar o CE para fazer estimativas melhores?

SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;

Soluções que tentei:

  • Criando estatísticas de várias colunas em Key1,Key2
  • Criando toneladas de estatísticas filtradas Key1(isso ajuda bastante, mas acabo com milhares de estatísticas criadas pelo usuário no banco de dados.)

Plano de execução mascarado (desculpe pelo mascaramento ruim)

No caso que estou vendo, o resultado tem 9 milhões de linhas. O novo CE estima 180 linhas; o CE legado estima 6100 linhas.

Aqui está um exemplo reproduzível:

DROP TABLE IF EXISTS #Table1, #Table2, #Table3;
CREATE TABLE #Table1 (Key1 INT NOT NULL, Key2 INT NOT NULL, T1Key3 INT NOT NULL, CONSTRAINT pk_t1 PRIMARY KEY CLUSTERED (Key1, Key2, T1Key3));
CREATE TABLE #Table2 (Key1 INT NOT NULL, Key2 INT NOT NULL, T2Key3 INT NOT NULL, CONSTRAINT pk_t2 PRIMARY KEY CLUSTERED (Key1, Key2, T2Key3));
CREATE TABLE #Table3 (Key1 INT NOT NULL, Key2 INT NOT NULL, T3Key3 INT NOT NULL, CONSTRAINT pk_t3 PRIMARY KEY CLUSTERED (Key1, Key2, T3Key3));

-- Table1 
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2),
     DataSize (Key1, NumberOfRows)
     AS (SELECT 1, 2000 UNION
         SELECT 2, 10000 UNION
         SELECT 3, 25000 UNION
         SELECT 4, 50000 UNION
         SELECT 5, 200000)
INSERT INTO #Table1
SELECT Key1
     , Key2 = ROW_NUMBER() OVER (PARTITION BY Key1, T1Key3 ORDER BY Number)
     , T1Key3
FROM DataSize
     CROSS APPLY (SELECT TOP(NumberOfRows) 
                         Number
                       , T1Key3 = Number%(Key1*Key1) + 1 
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smaller number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table2
SELECT DISTINCT 
       Key1
     , Key2
     , T2Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1*10) 
                         T2Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;

-- Table2 (same Key1, Key2 values; smallest number of distinct third Key)
WITH Numbers
     AS (SELECT TOP (1000000) Number = ROW_NUMBER() OVER(ORDER BY t1.number)
         FROM master..spt_values t1
              CROSS JOIN master..spt_values t2)
INSERT INTO #Table3
SELECT DISTINCT 
       Key1
     , Key2
     , T3Key3
FROM #Table1
     CROSS APPLY (SELECT TOP (Key1) 
                         T3Key3 = Number
                  FROM Numbers
                  ORDER BY Number) size;


DROP TABLE IF EXISTS #a;
SELECT col = 1 
INTO #a
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
WHERE t1.Key1 = 1;

DROP TABLE IF EXISTS #b;
SELECT col = 1 
INTO #b
FROM #Table1 t1
     JOIN #Table2 t2
       ON t1.Key1 = t2.Key1
          AND t1.Key2 = t2.Key2
     JOIN #Table3 t3
       ON t1.Key1 = t3.Key1
          AND t1.Key2 = t3.Key2
WHERE t1.Key1 = 1;
sql-server join

3 respostas

  1. Best Answer

    Só para ficar claro, o otimizador já sabe que é uma junção de muitos para muitos. Se você forçar junções de mesclagem e observar um plano estimado, poderá ver uma propriedade para o operador de junção que informa se a junção pode ser de muitos para muitos. O problema que você precisa resolver aqui é aumentar as estimativas de cardinalidade, presumivelmente para obter um plano de consulta mais eficiente para a parte da consulta que você deixou de fora.

    A primeira coisa que eu tentaria é colocar os resultados da junção de Object3e Object5em uma tabela temporária. Para o plano que você postou, é apenas uma única coluna em 51393 linhas, portanto, dificilmente deve ocupar espaço no tempdb. Você pode reunir estatísticas completas na tabela temporária e isso por si só pode ser suficiente para obter uma estimativa de cardinalidade final precisa o suficiente. Reunir estatísticas completas Object1também pode ajudar. As estimativas de cardinalidade geralmente pioram à medida que você passa de um plano da direita para a esquerda.

    Se isso não funcionar, você pode tentar a ENABLE_QUERY_OPTIMIZER_HOTFIXESdica de consulta se ainda não a tiver ativado no banco de dados ou no nível do servidor. A Microsoft bloqueia as correções de desempenho que afetam o plano para o SQL Server 2016 por trás dessa configuração. Alguns deles estão relacionados a estimativas de cardinalidade, então talvez você tenha sorte e uma das correções ajude com sua consulta. Você também pode tentar usar o estimador de cardinalidade herdado com uma FORCE_LEGACY_CARDINALITY_ESTIMATIONdica de consulta. Certos conjuntos de dados podem obter melhores estimativas com o CE legado.

    Como último recurso, você pode aumentar manualmente a estimativa de cardinalidade por qualquer fator que desejar usando a MANY()função de Adam Machanic. Eu falo sobre isso em outra resposta , mas parece que o link está morto. Se estiver interessado, posso tentar desenterrar algo.

    • 5

  2. As estatísticas do SQL Server contêm apenas um histograma para a coluna principal do objeto de estatísticas. Portanto, você pode criar estatísticas filtradas que fornecem um histograma de valores para Key2, mas apenas entre as linhas com Key1 = 1. A criação dessas estatísticas filtradas em cada tabela corrige as estimativas e leva ao comportamento esperado para a consulta de teste: cada nova junção não afeta a estimativa de cardinalidade final (confirmada no SQL 2016 SP1 e no SQL 2017).

    -- Note: Add "WITH FULLSCAN" to each if you want a perfect 20,000 row estimate
CREATE STATISTICS st_#Table1 ON #Table1 (Key2) WHERE Key1 = 1
CREATE STATISTICS st_#Table2 ON #Table2 (Key2) WHERE Key1 = 1
CREATE STATISTICS st_#Table3 ON #Table3 (Key2) WHERE Key1 = 1

    Sem essas estatísticas filtradas, o SQL Server adotará uma abordagem mais baseada em heurística para estimar a cardinalidade de sua associação. O whitepaper a seguir contém boas descrições de alto nível de algumas das heurísticas que o SQL Server usa: Otimizando seus planos de consulta com o estimador de cardinalidade do SQL Server 2014 .

    Por exemplo, adicionar a USE HINT('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')dica à sua consulta alterará a heurística de contenção de junção para assumir alguma correlação (em vez de independência) entre o Key1predicado e o Key2predicado de junção, o que pode ser benéfico para sua consulta. Para a consulta de teste final, essa dica aumenta a estimativa de cardinalidade de 1,175para 7,551, mas ainda é um pouco tímida em relação à 20,000estimativa de linha correta produzida com as estatísticas filtradas.

    Outra abordagem que usamos em situações semelhantes é extrair o subconjunto relevante dos dados em tabelas #temp. Especialmente agora que as versões mais recentes do SQL Server não gravam mais tabelas #temp no disco , tivemos bons resultados com essa abordagem. Sua descrição de sua junção muitos-para-muitos implica que cada tabela #temp individual no seu caso seria relativamente pequena (ou pelo menos menor que o conjunto de resultados final), então essa abordagem pode valer a pena tentar.

    DROP TABLE IF EXISTS #Table1_extract, #Table2_extract, #Table3_extract, #c
-- Extract only the subset of rows that match the filter predicate
-- (Or better yet, extract only the subset of columns you need!)
SELECT * INTO #Table1_extract FROM #Table1 WHERE Key1 = 1
SELECT * INTO #Table2_extract FROM #Table2 WHERE Key1 = 1
SELECT * INTO #Table3_extract FROM #Table3 WHERE Key1 = 1
-- Now perform the join on those extracts, removing the filter predicate
SELECT col = 1
INTO #c 
FROM #Table1_extract t1
JOIN #Table2_extract t2
    ON t1.Key2 = t2.Key2
JOIN #Table3_extract t3
    ON t1.Key2 = t3.Key2
    • 2

  3. Um alcance. Nenhuma base real além de tentar.

    SELECT 1
FROM Table1 t1
     JOIN Table2 t2
       ON t1.Key2 = t2.Key2
      AND t1.Key1 = 1
      AND t2.Key1 = 1
     JOIN Table3 t3
       ON t2.Key2 = t3.Key2
      AND t3.Key1 = 1;
    • -1

relate perguntas