Dadas as duas tabelas a seguir:
CREATE TABLE SalesLedger (
Id int PRIMARY KEY IDENTITY,
Date date NOT NULL,
Total decimal(38,18),
INDEX IX (Date, Total)
);
CREATE TABLE Purchases (
Id int PRIMARY KEY IDENTITY,
Date date NOT NULL,
Total decimal(38,18),
INDEX IX (Date, Total)
);
E a seguinte visão
CREATE VIEW ViewMetrics
AS
Select
Date,
'Sale' as Metric,
Total as Value
From SalesLedger
UNION ALL
Select
Date,
'Purchase' as Metric,
Total as Value
From Purchases;
A consulta a seguir usa um Concatenation Sortpar:
Select SUM(Value) as Sales, Date
from ViewMetrics
Group By Date;
Considerando que uma pequena reescrita dá obviamente mais desempenhoMerge Concatenation
SELECT SUM(Sales), Date
FROM (
Select SUM(Value) as Sales, Date
from ViewMetrics
Group By Metric, Date
) t
GROUP BY Date;
O compilador pode ver claramente que a visualização está particionada por Metric, como mostra esta consulta, não Sorté necessário:
Select SUM(Value) as Sales, Date
from ViewMetrics
where Metric = 'Sale'
Group By Date;
A questão é: por que a primeira consulta força a Sort, enquanto a segunda pode usar uma mais eficiente Merge Concatenation, visto que a Metriccoluna não possui WHEREpredicado em nenhum dos casos?
O compilador não deveria ser capaz de ver que a Mergefuncionaria, visto que os índices já estão classificados Datee o particionamento está ativado Metric? Ou se não consegue ver isso, por que GROUP BY Metric, Datede repente lhe dá essa habilidade?
Ainda mais estranho, como descobriu @MartinSmith, se não houver dados, o compilador usará o plano melhor, embora sem uma agregação intermediária em Metric, Date. db<>fiddle Por outro lado, a mesclagem sem agregação parcial é provavelmente mais lenta do que a classificação após a agregação parcial, porque há mais linhas para mesclar. A questão é por que não é possível fazer agregação parcial e mesclar ao mesmo tempo por padrão?
Suponho que haja alguma otimização específica para uma visualização particionada quando a agregação inclui o particionamento, porque nesse caso ela usa uma concatenação e quando uma ordem é necessária, ela usa a concatenação de mesclagem, consulte db<> fiddle . Isso ajuda quando você deseja agregar ainda mais, pois os dados agora já estão classificados na ordem correta. Mas se você não fizer a agregação intermediária não tem lógica que a aplique.
O otimizador do SQL Server tem duas maneiras principais de empurrar um agregado para baixo de uma união.
1. Empurrão Global
A primeira regra é
GbAggBelowUniAll. É uma transformação bastante simples que move a agregação para cada uma das entradas da união.Só pode fazer isso com segurança se a união for disjunta – isto é, se houver algo que torne cada entrada completamente independente, e esse fator aparecer na
GROUP BYcláusula.Não precisa ser um valor literal como no seu caso, mas precisa ser algo que o otimizador possa reconhecer como uma separação completa dos conjuntos, como intervalos não sobrepostos. Como parte da transformação, a parte constante da cláusula de agrupamento é removida.
Esta regra está envolvida na sua reescrita porque o atributo Metric é disjunto e está presente na especificação de agrupamento.
Um exemplo usando o banco de dados de amostra Adventure Works:
Com a
FORCE ORDERdica descomentada, o otimizador é impedido de mover o agregado:Sem a dica, o agregado de nível superior pode ser movido e copiado para cada entrada de união:
2. Agregação Local
A segunda transformação envolve algumas regras diferentes.
Primeiro,
GenLGAggdivide um agregado em duas partes, um agregado global e um agregado local . Por exemplo, umCOUNTagregado seria dividido numCOUNTagregado local e numSUMagregado global que soma todas as contribuições locais para chegar ao resultado correcto.A ideia geral é usada tanto em planos seriais quanto paralelos. Às vezes, o agregado local calcula um subtotal local para seu próprio thread, às vezes executa um subconjunto do trabalho abaixo de uma junção. Em qualquer caso, a ideia geral é a mesma: realizar alguma parte da tarefa global de agregação o mais cedo possível.
Como muitas explorações de otimizadores,
GenLGAggproduz uma ou mais alternativas que podem ser exploradas posteriormente por outras regras. Por exemplo, as novas agregações locais podem ser junções anteriores ou corresponder a uma visualização indexada.No seu caso, uma regra chamada
LocalAggBelowUniAllé empregada para mover o agregado local abaixo do arquivoUNION ALL. Isso é o que estava acontecendo com a consulta original.É importante ressaltar que o agregado local não é exatamente um agregado normal. Ele está executando apenas parte do cálculo e pode acabar em um dos muitos threads em um plano paralelo. Uma agregação global sempre é executada em um único thread para garantir resultados corretos.
Em um plano paralelo, um agregado local pode ser implementado fisicamente como um Hash Match Partial Aggregate. Este operador obtém apenas uma pequena concessão de memória fixa e nunca transborda para tempdb . Se ficar sem memória, ele simplesmente para de agregar. Os resultados ainda estarão corretos graças ao agregado global.
As ressalvas não se limitam a esta operadora física ou planos paralelos. Em geral, você deve pensar em um agregado local como sendo um pouco diferente do tipo normal que você escreve em SQL.
Isto é principalmente uma consequência dos detalhes de implementação e da necessidade de preservar a ligação entre os agregados locais e globais. Como muitas regras de exploração,
LocalAggBelowUniAllrepresenta uma reescrita de consulta que você mesmo pode executar, mas você não deve esperar que ela se comporte exatamente da mesma forma em todos os aspectos que a representação T-SQL mais próxima. Deve-se dizer também que o otimizador tem a vantagem de poder decidir dinamicamente qual reescrita usar com base nas estatísticas e metadados atuais. Isso geralmente não é verdade para uma reescrita manual.De qualquer forma, uma das consequências de um agregado local ser um pouco diferente é que ele não vem com uma garantia de exclusividade associada às suas chaves de agrupamento. Isso é importante em muitos casos, mas especialmente com o operador Merge Concatenation desejado.
Mesclar concatenação
Como o nome do operador do plano sugere, Merge Concatenation é apenas um operador Merge Join normal executado em um modo especial. Requer entrada classificada nas 'chaves de junção', embora a ordem de classificação exata necessária possa ser afetada pela lista de colunas de projeção, pelo requisito de ordem global e por quaisquer garantias de exclusividade disponíveis (veja a referência abaixo).
Um agregado normal que fornece garantias de exclusividade de chave de agrupamento pode permitir que a Merge Concatenation exija uma classificação menos onerosa do que é possível com a entrada de um agregado local.
Para sua consulta original, o otimizador considerou a alternativa Merge Concatenation, mas a agregação local significava que as classificações eram necessárias para satisfazer as propriedades de entrada exigidas:
As classificações extras e o custo mais alto da concatenação de mesclagem significaram que o otimizador escolheu a opção de plano mais barata com uma concatenação e uma única classificação. Como sempre, essas escolhas são baseadas no modelo de custo.
Outras notas
Com tabelas vazias, o otimizador custa mais barato a opção Merge Concatenation porque mescla apenas uma linha de cada entrada. O custo mais alto por linha de uma Concatenação de Mesclagem não compensa o custo da Classificação extra necessária no plano de Concatenação.
Sua consulta deve rejeitar nulos ou as tabelas base devem ter um explícito
NOT NULLno atributo Value . Isso simplificará o plano final (e tornará mais fácil qualquer correspondência futura de visualizações indexadas). Em particular, os Stream Aggregates não precisarão mais calcular umCOUNT_BIG(Total)agregado e os Compute Scalars não serão mais necessários.Você também deve usar prefixos de esquema e evitar palavras-chave como nomes de atributos.
Leitura adicional
cobre agregação local, parcial e global
para classificações de concatenação de mesclagem
Ambos escritos por mim.