Eu tenho uma consulta que não funciona bem e o otimizador varre todos os 108.864 itens da tabela ExternFile inutilmente ao buscar apenas 30 linhas. Eu simplifiquei o problema para seguir a seleção da exibição.
A consulta da camada superior se parece com:
SELECT
realtyId,
ServerPath,
OfferState,
CategoryId,
ParentCategoryId
FROM FSTest
WHERE OfferState <= 3 AND (CategoryId = 2 OR ParentCategoryId = 2)
ORDER BY Ranking DESC
OFFSET 2970 ROWS FETCH NEXT 30 ROWS ONLY
A visualização subjacente é a seguinte:
CREATE View [dbo].[FSTest] AS
SELECT
realty.Id as realtyId,
realty.OfferState,
realty.Ranking,
Category.Id as CategoryId,
Category.ParentCategoryId,
ExternFileOuterApply.ServerPath as ServerPath,
ExternFileOuterApply.Height as ImageHeight,
ExternFileOuterApply.Width as ImageWidth
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
OUTER APPLY (SELECT TOP (1) ServerPath, Height,Width FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFileOuterApply
O problema é o OUTER APPLY. Eu esperaria que a consulta ignorasse todas as 2.970 linhas e executasse OUTER APPLY apenas para 30 linhas contidas no resultado. Mas o OUTER APPLY está verificando toda a tabela ExternFile (108864 linhas). Por quê?
Está de alguma forma conectado com o índice temporário no spool de índice (veja a imagem abaixo)? O que há de errado com a consulta?
Sua varredura está sendo executada uma única vez, para preencher um spool de índice. Este carretel é chamado 3032 vezes. Se você criar um índice que se adapte melhor, poderá evitar o spool e provavelmente também não digitalizará mais.
Editar: Mas acho que o que você deseja é que o TOP mais à esquerda esteja à direita do loop aninhado. Isso também é comum com pesquisas de chave, que geralmente aparecem à direita dos principais operadores quando você preferiria que estivessem à esquerda. Aqui, o Spool é bem barato de consultar, então não há muita vantagem em tê-lo de lado. Com um índice melhor, você provavelmente poderia convencê-lo a não fazer o Spool e, portanto, pode ver um benefício real em empurrar o Topo mais para a direita.
Outra edição: presumivelmente, se você deixou de fora ServerPath, o Nested Loop desaparece completamente, pois essa tabela não é mais necessária. Mas tenho certeza que você está tentando fazer funcionar sem alterar muito as consultas.