Início / dba / Perguntas / 84577
Accepted
Shaul Behr
Asked: 2014-12-09 01:05:51 +0800 CST

Otimização de índice em uma exibição usando uma junção externa completa

  • 772

Estrutura da tabela:

Foo             FooChild                Bar
---             --------                ---
ID              ID                      ID
Date            FooID                   Date
GroupID         UserID                  UserID
Notes           Amount                  GroupID
                                        IsComplete
  • Footem um índice único em Date+GroupID
  • FooChildtem um FK para Foo e um índice exclusivo em FooID+ UserID, que incluiAmount
  • Bartem um índice exclusivo em ++ Date, que incluiUserIDGroupIDIsComplete

Agora preciso criar um relatório mostrando a soma de todos os valores de FooChild junto com a contagem de barras completas para qualquer intervalo de datas. Os usuários também querem poder ver as estatísticas por grupo ou por usuário. Este parece ser um ótimo lugar para escrever uma visão:

create view vFooBar as
select f.Date, f.GroupID, fc.UserID, fc.Amount, b.IsComplete
from Foo f join FooChild fc on fc.FooID = f.ID
left join Bar b on f.Date = b.Date and f.GroupID = b.GroupID and fc.UserID = b.UserID
union
select b.Date, b.GroupID, b.UserID, x.Amount, b.IsComplete
from Bar b left join 
    (select f.Date, f.GroupID, fc.UserID, fc.Amount
    from Foo f join FooChild fc on fc.FooID = f.ID) x
on x.Date = b.Date and x.GroupID = b.GroupID and x.UserID = b.UserID

( É por isso que escrevi a visão dessa maneira.)

Agora posso facilmente escrever consultas como esta:

select UserID, sum(Amount) FooAmount, sum(cast(IsCompleted as int)) CompletedBars
from vFooBar
where Date between @fromDate and @toDate
group by UserID

Mas há um obstáculo aqui. Assim que o intervalo de datas começa a ficar relativamente grande, o plano de execução fica todo em forma de pêra. Ele usa o índice de data em Foo, mas em vez de usar o FooIDíndice em FooChild, ele faz uma varredura de índice clusterizado e, em seguida, uma correspondência de hash FooIDpara se juntar aos resultados de Foo. E faz isso duas vezes no plano geral; Eu estou supondo uma vez para cada agregado. E isso realmente dói.

Entendo que usar o índice que criei FooChildpode não ser eficiente, pois os valores de FooIDpara uma determinada data podem ser discretos, embora normalmente sejam inseridos aproximadamente na mesma ordem.

Eu poderia desnormalizar e adicionar Datee GroupIDà tabela FooChild, indexar essas colunas e tenho certeza de que isso melhoraria muito o desempenho. Mas simplesmente não parece certo.

Alguma outra ideia?

index sql-server-2012

1 respostas

  1. Best Answer

    O otimizador faz escolhas com base em estimativas de custo. O modelo de custo é genérico e nem sempre pode escolher planos ideais para seu hardware específico, e suas suposições podem nem sempre ser válidas para suas circunstâncias.

    Nesse caso, o otimizador avalia uma junção de hash como a opção mais barata em relação a loops aninhados quando o número estimado de linhas a serem unidas é grande. Se você tiver certeza de que uma junção de loops aninhados sempre será preferível a uma junção de hash, considere (e teste!) Forçar uma busca em vez de uma varredura da FooChildtabela na exibição:

    SELECT 
    f.TheDate, 
    f.GroupID, 
    fc.UserID, 
    fc.Amount, 
    b.IsComplete
FROM dbo.Foo AS f 
JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
    ON fc.FooID = f.ID
LEFT JOIN dbo.Bar AS b 
    ON f.TheDate = b.TheDate 
    AND f.GroupID = b.GroupID 
    AND fc.UserID = b.UserID
UNION
SELECT 
    b.TheDate, 
    b.GroupID, 
    b.UserID, 
    x.Amount, 
    b.IsComplete
FROM dbo.Bar AS b 
LEFT JOIN 
(
    SELECT 
        f.TheDate, 
        f.GroupID, 
        fc.UserID, 
        fc.Amount
    FROM dbo.Foo AS f 
    JOIN dbo.FooChild AS fc WITH (FORCESEEK) -- New hint
        ON fc.FooID = f.ID
) AS x
    ON x.TheDate = b.TheDate 
    AND x.GroupID = b.GroupID 
    AND x.UserID = b.UserID;

    Observação: embora essa transformação da junção completa original seja válida, dadas as restrições de exclusividade atuais em suas tabelas, revise a resposta à sua pergunta anterior e considere reescrever a junção completa conforme sugerido em minha edição.

    • 3

relate perguntas