Mejora del rendimiento al realizar pedidos por una columna de una tabla unida

Aug 17 2020

Tengo una tabla principal que contiene una clave externa para una tabla de búsqueda (ejemplo 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
)

En este caso, la Parenttabla tiene más de 10 millones de filas y la Lookuptabla tiene alrededor de 5000. La Parentimplementación real tiene varias referencias de clave externa a otras tablas y cada una de esas columnas puede contener NULL.

Ambas tablas de ejemplo tienen índices agrupados únicos para sus Idcolumnas, Parenttienen un índice no agrupado para LookupIdy Lookupun índice no agrupado para Name.

Estoy ejecutando una consulta paginada en la que quiero incluir el valor de búsqueda en los 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

Esto se ejecuta rápidamente, al igual que ordenar por P.LookupId.

Sin embargo, si intento ordenar por Name(o incluso L.Id), la consulta se ejecuta considerablemente más 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

El plan de consulta para la segunda consulta está aquí: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD

Otras preguntas aparentemente relacionadas parecen involucrar la ordenación por columnas en la primera tabla que podrían resolverse usando un índice apropiado.

Intenté crear una vista indexada para esta consulta, sin embargo, SQL Server no me permitirá indexar la vista porque contiene un LEFT JOIN que necesito porque LookupIdpuede ser NULL y si uso un INNER JOIN, esos registros serían excluidos.

¿Hay alguna forma de optimizar esta situación?

EDITAR

La respuesta de Rob Farley (¡gracias!) Es excelente y funciona perfectamente para la pregunta como la hice originalmente, en la que insinuaba que me unía a una sola mesa.

Tal como están las cosas, tengo varias tablas de este tipo y no pude conciliar todas las combinaciones con INNER JOIN para usar esa solución.

Por el momento, he solucionado esto agregando una fila "NULL" a las tablas de búsqueda para poder usar INNER JOIN sin perder ninguna fila a la izquierda.

En mi caso, uso uniqueidentifieridentidades, así que creo una vista 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

Luego agrego una fila a la Lookuptabla con un valor de 00000000-0000-0000-0000-000000000000para Idque siempre haya una coincidencia a la derecha de la combinación.

Luego puedo crear índices en esa vista según sea necesario.

Además, como no estoy usando Enterprise, descubrí que necesitaba usar la NOEXPANDsugerencia para asegurarme de que se usen esos índices:

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

Respuestas

10 RobFarley Aug 17 2020 at 11:12

Comencemos pensando en esa primera consulta.

Te estás uniendo entre Parent y Lookup, pero es una unión externa, por lo que los padres nunca se eliminan de los resultados. Voy a adivinar que Lookup.Id es único, por lo tanto, ningún padre va a tener múltiples búsquedas a las que se une.

Por lo tanto, la fila número 50000 en Parent (ordenada por Parent.Id) va a ser la fila número 50000 en los resultados si no tenemos la cláusula OFFSET.

Por lo tanto, la consulta puede pasar de las 50000 filas para el desplazamiento, mirar las siguientes 50 filas y usar esto para unirse a la tabla de búsqueda. No importa si la combinación no encuentra nada, es una combinación externa izquierda y solo devolverá NULL.

Si ordena por una columna diferente en Principal, y está indexada, puede pasar de esas 50000 filas con la misma rapidez.

Ahora consideremos la segunda consulta.

Desea que las 50000 filas que ignora (por el desplazamiento) sean las primeras 50000 según los resultados de la combinación. Esas 50000 filas pueden incluir algunas que son NULL, donde el valor Parent.LookupId no existe en la tabla de búsqueda. Incluso si tiene un buen índice en Parent.LookupId, probablemente necesitará involucrar a la mayoría de las filas, porque a menos que encuentre 50050 filas que no se unan correctamente, deberá continuar. Incluso 50050 es mucho más que las 50 filas a las que se une en la primera consulta.

Ahora, si tiene una clave externa en su lugar, las cosas podrían ser un poco diferentes. Entonces, el motor SQL debe saber que si tiene un valor, Lookup.Name no será nulo. Entonces, teóricamente, podría comenzar por encontrar los que son nulos, para ver si hay 50000 de ellos. Pero eso todavía es un poco exagerado, y es poco probable que el motor SQL produzca un plan como este.

Pero podrías.

Entonces, para resolver el rendimiento de la segunda consulta, haría algunas cosas.

Empiece por considerar los que no son nulos. Eso significa las filas que forman parte de una combinación interna. Puede hacer una vista indexada sobre esto, de modo que pueda tener un índice que esté en el orden que desee.

Pero también necesitará aquellos en los que Parent.LookupID es nulo, excepto que para estos, no necesita la unión en absoluto.

Si hace UNION ALL en estos dos conjuntos (y tal vez incluya una columna constante en ambos, para asegurarse de que las filas NULL aparezcan antes de las NOT NULL en su orden), debería poder ver alguna mejora.

Algo como esto:

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;

Es de esperar que su plan incluya un operador Merge Join (Concatenación), de modo que solo extraiga las filas que necesita de un Index Scan en la vista indexada (en orden de nombre) y una Index Seek on Parent (para los LookupIDs).