The concatenation of two or more data sets is most commonly expressed in T-SQL using theUNION ALL clause. Dado que o otimizador de servidor SQL pode muitas vezes reordenar coisas como junções e agregados para melhorar o desempenho, é bastante razoável esperar que o servidor SQL também considere a reordenação de entradas de concatenação, onde isso daria uma vantagem. Por exemplo, o otimizador pode considerar os benefícios de reescrever A UNION ALL B como B UNION ALL A.

Na verdade, o otimizador do servidor SQL não faz isso. Mais precisamente, houve algum suporte limitado para a reordenação de entrada de concatenação no SQL Server releases até 2008 R2, mas isso foi removido no SQL Server 2012, e não ressurgiu desde então.

SQL Server 2008 R2

intuitivamente, a ordem de entradas de concatenação só importa se houver um objetivo de linha. Por padrão, o servidor SQL otimiza os planos de execução na base de que todas as linhas de qualificação serão devolvidas ao cliente. Quando um objetivo de linha está em vigor, o otimizador tenta encontrar um plano de execução que irá produzir as primeiras linhas rapidamente.

Linha metas podem ser definidas de várias formas, por exemplo usando TOPFAST n dica de consulta, ou usando EXISTS (que, por sua natureza, precisa encontrar mais de uma linha). Onde não há meta de linha (ou seja, o cliente requer todas as linhas), geralmente não importa em que ordem as entradas de concatenação são lidas: cada entrada será totalmente processada eventualmente, em qualquer caso.

O suporte limitado em versões até o SQL Server 2008 R2 aplica-se onde há um objetivo de exatamente uma linha. Nesta circunstância específica, o servidor SQL irá reordenar as entradas de concatenação com base no custo esperado.

isto não é feito durante a otimização baseada em custos (como seria de esperar), mas sim como uma reescrita de última hora da saída de otimização normal. Este arranjo tem a vantagem de não aumentar o espaço de busca de plano baseado em custos (potencialmente uma alternativa para cada possível reordenamento), enquanto ainda produzindo um plano que é otimizado para retornar a primeira linha rapidamente.

exemplos

os seguintes exemplos usam duas tabelas com conteúdo idêntico: um milhão de linhas de inteiros de um a um milhão. Uma tabela é um heap sem índices não obstruídos; a outra tem um índice agrupado único:

CREATE TABLE dbo.Expensive( Val bigint NOT NULL); CREATE TABLE dbo.Cheap( Val bigint NOT NULL, CONSTRAINT UNIQUE CLUSTERED (Val));GOINSERT dbo.Cheap WITH (TABLOCKX) (Val)SELECT TOP (1000000) Val = ROW_NUMBER() OVER (ORDER BY SV1.number)FROM master.dbo.spt_values AS SV1CROSS JOIN master.dbo.spt_values AS SV2ORDER BY ValOPTION (MAXDOP 1);GOINSERT dbo.Expensive WITH (TABLOCKX) (Val)SELECT C.ValFROM dbo.Cheap AS COPTION (MAXDOP 1);

Nenhuma Linha de Meta

a consulta A seguir procura as mesmas linhas em cada tabela, e retorna a concatenação de dois conjuntos:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005;

O plano de execução gerado pelo otimizador de consulta está:

The warning on the root operator is alerting us to the obvious missing index on the heap table. O Aviso sobre o operador de varredura de mesa é adicionado pelo sentinela um explorador de plano. Está a chamar a nossa atenção para o custo de E/S do predicado residual escondido dentro da digitalização.

a ordem das entradas para a concatenação não importa aqui, porque não estabelecemos uma meta de linha. Ambas as entradas serão totalmente lidas para retornar todas as linhas de resultado. Of interest (though this is not guaranteed) notice that the order of the inputs follows the textual order of the original query. Observe também que a ordem das linhas de resultado final também não é especificada, uma vez que não usamos uma cláusula de nível superior ORDER BY. Assumiremos que isso é deliberado e ordem final é inconsequente para a tarefa em questão.

Se invertermos a ordem escrita das tabelas na consulta assim:

SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 UNION ALL SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005;

O plano de execução segue a mudança, acessando o cluster tabela pela primeira vez (novamente, isso não é garantido):

Ambas as consultas podem ser esperados a ter as mesmas características de desempenho, pois eles têm as mesmas operações, apenas em uma ordem diferente.

com um objetivo de linha

claramente, a falta de indexação na tabela heap normalmente fará encontrar linhas específicas Mais caro, em comparação com a mesma operação na tabela agrupada. Se pedirmos ao otimizador um plano que retorne a primeira linha rapidamente, esperaremos que o servidor SQL reordene as entradas de concatenação para que a tabela de cluster barata seja consultada primeiro.

Usando a consulta que menciona a tabela heap primeiro, e usando uma dica rápida de 1 consulta para especificar o objetivo da linha:

SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.ValFROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005OPTION (FAST 1);

O plano de execução estimado produzido em uma instância do SQL Server 2008 R2 é:

Observe que a concatenação entradas foram reordenadas para reduzir o custo estimado de retornar a primeira linha. Note também que o índice em falta e os avisos residuais de E/S desapareceram. Nenhuma das questões é de consequência com esta forma de plano quando o objetivo é retornar uma única linha o mais rápido possível.

a mesma consulta executada no SQL Server 2016 (usando qualquer um dos modelos de estimativa da cardinalidade) é:

SQL Server 2016 não reordenou as entradas de concatenação. O aviso de I/O Plan Explorer voltou, mas infelizmente o optimizer não produziu um aviso de índice em falta desta vez (embora seja relevante).

reordenamento geral

conforme mencionado, a reescrita de pós-otimização de que as entradas de concatenação de gravadores só são eficazes para:

• o SQL Server 2008 R2 e versões anteriores
• Uma linha gol de exatamente um

Se nós realmente só querem uma linha retornada, ao invés de um plano otimizado para retornar a primeira linha rapidamente (mas que, em última análise, ainda retornar todas as linhas), podemos usar uma TOP cláusula com uma tabela derivada ou expressão de tabela comuns (CTE):

SELECT TOP (1) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UA;

No SQL Server 2008 R2 ou versões anteriores, esta produz o ideal reordenadas de entrada de plano de:

No SQL Server 2012, 2014 e 2016 pós-otimização de reordenação ocorre:

Se nós queremos mais do que uma linha retornada, por exemplo, usando o TOP (2), o desejado de reconfiguração, não será aplicado no SQL Server 2008 R2 mesmo se uma FAST 1 dica também é usado. Nessa situação, precisamos recorrer a truques como usar TOP com uma variável e um OPTIMIZE FOR dica:

DECLARE @TopRows bigint = 2; -- Number of rows actually needed SELECT TOP (@TopRows) UA.ValFROM( SELECT E.Val FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 UNION ALL SELECT C.Val FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005) AS UAOPTION (OPTIMIZE FOR (@TopRows = 1)); -- Just a hint

A dica de consulta é suficiente para definir uma linha de meta, enquanto que o valor de tempo de execução da variável garante o número desejado de linhas (2) é retornado.

O real plano de execução no SQL Server 2008 R2 é:

Ambas as linhas retornadas vêm de reordenada buscar entrada, e a Verificação de Tabela não é executada. O Plan Explorer mostra as contagens de linhas a vermelho porque a estimativa era para uma linha (devido à dica), enquanto duas linhas foram encontradas no tempo de execução.

sem união todos

esta questão também não se limita a consultas escritas explicitamente com UNION ALL. Outras construções como EXISTS e OR também podem resultar na introdução de um operador de concatenação, que pode sofrer com a falta de reordenamento de entrada. Houve uma pergunta recente sobre os administradores de banco de dados Stack Exchange com exatamente este problema. Transformando a consulta de que a questão de usar as nossas tabelas de exemplo:

SELECT CASE WHEN EXISTS ( SELECT 1 FROM dbo.Expensive AS E WHERE E.Val BETWEEN 751000 AND 751005 ) OR EXISTS ( SELECT 1 FROM dbo.Cheap AS C WHERE C.Val BETWEEN 751000 AND 751005 ) THEN 1 ELSE 0 END;

O plano de execução no SQL Server 2016 tem a tabela de heap na primeira entrada:

No SQL Server 2008 R2 a ordem das entradas é otimizado para reflectir a linha de meta do semi join:

No mais plano ideal, a pilha de digitalização nunca é executado.

Workarounds

em alguns casos, será evidente para o escritor de consulta que uma das entradas de concatenação será sempre mais barato de executar do que as outras. Se isso for verdade, é bastante válido reescrever a consulta para que as entradas de concatenação mais baratas apareçam primeiro em ordem escrita. Claro que isso significa que o escritor de consultas precisa estar ciente desta limitação de otimização, e preparado para confiar em comportamentos não documentados.

uma questão mais difícil surge quando o custo das entradas de concatenação varia com as circunstâncias, talvez dependendo dos valores dos parâmetros. Usando OPTION (RECOMPILE) não vai ajudar no servidor SQL 2012 ou mais tarde. Essa opção pode ajudar no SQL Server 2008 R2 ou antes, mas apenas se o requisito de meta de linha única também for cumprido.

Se houver preocupações sobre confiar no comportamento observado (as entradas do plano de concatenação da consulta coincidem com a ordem textual da consulta) um guia de plano pode ser usado para forçar a forma do plano. Quando diferentes ordens de entrada são ideais para diferentes circunstâncias, podem ser utilizados vários guias de plano, onde as condições podem ser codificadas com precisão antecipadamente. Mas isto não é ideal.

Final Thoughts

The SQL Server query optimizer does in fact contain a cost-based exploration rule, UNIAReorderInputs, which is capable of generating concatenation input order variations and exploring alternatives during cost-based optimization (not as a single-shot post-optimization rewrite).

esta regra não está activa de momento para uso geral. Tanto quanto eu posso dizer, ele só é ativado quando um guia de plano ou USE PLAN dica está presente. Isso permite que o motor force com sucesso um plano que foi gerado para uma consulta que se qualificou para a reordenação de entrada, mesmo quando a consulta atual não se qualifica.

meu senso é que esta regra de exploração é deliberadamente limitada a este uso, porque as consultas que se beneficiariam com a reordenação de entrada de concatenação como parte da otimização baseada em custos são consideradas não suficientemente comuns, ou talvez porque há uma preocupação de que o esforço extra não compensaria. A minha opinião pessoal é que a reordenação de entradas do operador de concatenação deve ser sempre explorada quando um objetivo de linha está em vigor.

também é uma pena que a (mais limitada) reescrita pós-otimização não é eficaz no SQL Server 2012 ou mais tarde. Isto pode ter sido devido a um bug sutil, mas eu não consegui encontrar nada sobre isso na documentação, base de conhecimento, ou no Connect. Eu adicionei um novo item de conexão aqui.

Update 9 August 2017: This is now fixed under trace flag 4199 for SQL Server 2014 and 2016, see KB 4023419:

FIX: A consulta com o UNION ALL e um objetivo de linha pode correr mais lentamente no servidor SQL 2014 ou versões posteriores quando é comparada com o servidor SQL 2008 R2