Para uma consulta moderadamente complexa que estou tentando otimizar, notei que a remoção da TOP ncláusula altera o plano de execução. Eu teria imaginado que, quando uma consulta inclui TOP no mecanismo de banco de dados, executaria a consulta ignorando a TOPcláusula e, no final, apenas reduziria o resultado definido para o número n de linhas solicitadas. O plano de execução gráfico parece indicar que este é o caso -- TOPé a "última" etapa. Mas parece que há mais coisas acontecendo.
Minha pergunta é: como (e por que) uma cláusula TOP n afeta o plano de execução de uma consulta?
Aqui está uma versão simplificada do que está acontecendo no meu caso:
A consulta está correspondendo linhas de duas tabelas, A e B.
Sem a TOPcláusula, o otimizador estima que haverá 19.000 linhas da tabela A e 46.000 linhas da tabela B. O número real de linhas retornadas é 16.000 para A e 13.000 para B. Uma correspondência de hash é usada para unir esses dois conjuntos de resultados para um total de 69 linhas (então uma classificação é aplicada). Esta consulta acontece muito rapidamente.
Quando adiciono TOP 1001o otimizador não usa uma correspondência de hash; em vez disso, ele primeiro classifica os resultados da tabela A (mesma estimativa/real de 19k/16k) e faz um loop aninhado na tabela B. O número estimado de linhas para a tabela B agora é 1, e o estranho é que TOP nafeta diretamente o número estimado de execuções (busca de índice) contra B - parece ser sempre 2n+1 ou, no meu caso, 2003. Essa estimativa muda de acordo se eu mudar TOP n. Obviamente, como essa é uma junção aninhada, o número real de execuções é 16k (o número de linhas da tabela A) e isso torna a consulta mais lenta.
A consulta tem uma ORDER BYcláusula. Adicionar TOPalterações onde no plano esse tipo ocorre, mas estou mais preocupado em como isso afeta o número de execuções de buscas de índice em relação à tabela B.
O cenário real é um pouco mais complexo, mas captura a ideia/comportamento básico. Ambas as tabelas são pesquisadas usando buscas de índice. Esta é a edição SQL Server 2008 R2 Enterprise.