Melhorar o desempenho ao ordenar por uma coluna de uma tabela unida

Aug 17 2020

Eu tenho uma tabela pai que contém uma chave estrangeira para uma tabela de pesquisa (exemplo simplificado):

CREATE TABLE [dbo].[Parent] (
    [Id] [uniqueidentifier] NOT NULL,
    [LookupId] [uniqueidentifier] NULL
)

CREATE TABLE [dbo].[Lookup] (
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](64) NOT NULL
)

Nesse caso, a Parenttabela tem mais de 10 milhões de linhas e Lookupcerca de 5.000. A Parentimplementação real tem várias referências de chave estrangeira para outras tabelas e cada uma dessas colunas pode conter NULLs.

Ambas as tabelas de exemplo têm índices de cluster exclusivos para suas Idcolunas, Parenttem um índice não clusterizado para LookupIde Lookuptem um índice não clusterizado para Name.

Estou executando uma consulta paginada onde desejo incluir o valor de pesquisa nos resultados: -

SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id 
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

Isso é executado rapidamente, assim como o pedido por P.LookupId.

Se, no entanto, eu tentar ordenar por Name(ou até L.Id), a consulta será executada de forma consideravelmente mais lenta:

SELECT
    P.Id,
    L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id 
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY

O plano de consulta para a segunda consulta está aqui: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD

Outras questões aparentemente relacionadas parecem envolver a ordenação por colunas na primeira tabela que pode ser resolvida usando um índice apropriado.

Tentei criar uma exibição indexada para esta consulta, no entanto, o SQL Server não me permite indexar a exibição porque ela contém um LEFT JOIN que eu exijo porque LookupIdpode ser NULL e se eu usar um INNER JOIN esses registros seriam excluídos.

Existe uma maneira de otimizar essa situação?

EDITAR

A resposta de Rob Farley (obrigado!) É ótima e funciona perfeitamente para a pergunta que eu fiz originalmente, na qual sugeri que estava entrando em uma única mesa.

Do jeito que está, eu tenho várias dessas tabelas e não consegui reconciliar todas usando INNER JOINs para usar essa solução.

No momento, resolvi isso adicionando uma linha "NULL" às tabelas de pesquisa para que eu possa usar um INNER JOIN sem perder nenhuma linha à esquerda.

No meu caso, uso uniqueidentifieridentidades, então crio uma visualização indexada como esta:

CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
    P.Id,
    L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id

Em seguida, adiciono uma linha à Lookuptabela com um valor de 00000000-0000-0000-0000-000000000000para Idque haja sempre uma correspondência à direita da junção.

Posso então criar índices nessa exibição conforme necessário.

Além disso, como não estou usando o Enterprise, descobri que precisava usar a NOEXPANDdica para garantir que esses índices sejam usados:

SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Respostas

10 RobFarley Aug 17 2020 at 11:12

Vamos começar pensando sobre a primeira consulta.

Você está ingressando entre Parent e Lookup, mas é uma associação externa, portanto, os Pais nunca são removidos dos resultados. Vou supor que Lookup.Id é exclusivo, portanto, nenhum pai terá vários Lookups aos quais se unirá.

Portanto, a 50000ª linha em Parent (ordenada por Parent.Id) será a 50000ª linha nos resultados se não tivermos a cláusula OFFSET.

Portanto, a consulta pode ir além das 50000 linhas para o deslocamento, examinar as próximas 50 linhas e usar isso para se unir à tabela Lookup. Não importa se a junção não encontrar nada, é uma junção externa esquerda e retornará apenas NULL.

Se você ordenar por uma coluna diferente no Parent, e ela estiver indexada, ela poderá passar dessas 50000 linhas com a mesma rapidez.

Agora vamos considerar a segunda consulta.

Você deseja que as 50.000 linhas que você ignora (pelo deslocamento) sejam as primeiras 50.000 com base nos resultados da junção. Essas 50000 linhas podem incluir algumas que são NULL, onde o valor Parent.LookupId não existe na tabela de pesquisa. Mesmo se você tiver um bom índice em Parent.LookupId, provavelmente precisará envolver a maioria das linhas, porque a menos que encontre 50050 linhas que não se juntem com sucesso, você precisará continuar. Mesmo 50050 é muito mais do que as 50 linhas que você junta na primeira consulta.

Agora, se você tiver uma chave estrangeira, as coisas podem ser um pouco diferentes. Então, o mecanismo SQL deve saber que, se tiver algum valor, Lookup.Name não será nulo. Portanto, poderia teoricamente começar encontrando aqueles que são nulos, para ver se há 50.000 deles. Mas isso ainda é um pouco forçado, e é improvável que o mecanismo SQL produza um plano como esse.

Mas você poderia.

Portanto, para resolver o desempenho da segunda consulta, eu faria algumas coisas.

Comece considerando aqueles que não são nulos. Isso significa as linhas que fazem parte de uma junção interna. Você pode fazer uma visualização indexada sobre isso, de modo que você possa ter um índice que esteja na ordem que você deseja.

Mas você também precisará daqueles em que Parent.LookupID é nulo - exceto que, para esses, você não precisa de nenhuma junção.

Se você fizer um UNION ALL entre esses dois conjuntos (e talvez incluir uma coluna constante em ambos, para ter certeza de que as linhas NULL apareçam antes das NOT NULL em seu pedido), você poderá ver algumas melhorias.

Algo assim:

SELECT ID, Name
FROM 
(
  SELECT i.ID, i.Name, 2 as SetNumber
  FROM dbo.MyIndexedView i
  UNION ALL
  SELECT p.ID, NULL, 1 as SetNumber
  FROM dbo.Parent p
  WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;

Esperançosamente, seu plano incluirá um operador Merge Join (Concatenation), de forma que ele apenas extraia as linhas que precisa de uma Index Scan na exibição indexada (na ordem do nome) e uma Index Seek on Parent (para os LookupIDs).