Amélioration des performances lors de la commande par colonne d'une table jointe
J'ai une table parent qui contient une clé étrangère vers une table de recherche (exemple simplifié):
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
Dans ce cas, le Parent
tableau contient plus de 10 millions de lignes et le Lookup
tableau en contient environ 5 000. L' Parent
implémentation réelle a plusieurs références de clé étrangère à d'autres tables et chacune de ces colonnes peut contenir des valeurs NULL.
Les deux exemples de tables ont des index clusterisés uniques pour leurs Id
colonnes, Parent
un index non clusterisé pour LookupId
et Lookup
un index non clusterisé pour Name
.
J'exécute une requête paginée dans laquelle je souhaite inclure la valeur de recherche dans les résultats: -
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
Cela fonctionne rapidement, tout comme la commande par P.LookupId
.
Si, cependant, j'essaie de commander par Name
(ou même L.Id
), la requête s'exécute considérablement plus lentement:
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
Le plan de requête pour la deuxième requête est ici: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
D'autres questions apparemment liées semblent impliquer un classement par colonnes dans le premier tableau qui pourrait être résolu à l'aide d'un index approprié.
J'ai essayé de créer une vue indexée pour cette requête, cependant, SQL Server ne me permettra pas d'indexer la vue car elle contient un LEFT JOIN dont j'ai besoin car il LookupId
peut être NULL et si j'utilise un INNER JOIN, ces enregistrements seront exclus.
Existe-t-il un moyen d'optimiser cette situation?
ÉDITER
La réponse de Rob Farley (merci!) Est excellente et fonctionne parfaitement pour la question telle que je l'ai posée à l'origine, dans laquelle j'impliquais que je rejoignais une seule table.
Dans l'état actuel des choses, j'ai plusieurs tables de ce type et je n'ai pas été en mesure de réconcilier toutes celles qui utilisent INNER JOINs afin d'utiliser cette solution.
Pour le moment, j'ai contourné ce problème en ajoutant une ligne "NULL" aux tables de recherche afin que je puisse utiliser un INNER JOIN sans perdre aucune ligne sur la gauche.
Dans mon cas, j'utilise des uniqueidentifier
identités, donc je crée une vue indexée comme celle-ci:
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
J'ajoute ensuite une ligne à la Lookup
table avec une valeur de 00000000-0000-0000-0000-000000000000
for Id
afin qu'il y ait toujours une correspondance à droite de la jointure.
Je peux ensuite créer des index sur cette vue au besoin.
De plus, comme je n'utilise pas Enterprise, j'ai trouvé que je devais utiliser l' NOEXPAND
indice pour m'assurer que ces index sont utilisés:
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
Réponses
Commençons par réfléchir à cette première requête.
Vous vous joignez entre Parent et Lookup, mais c'est une jointure externe, donc les parents ne sont jamais supprimés des résultats. Je vais deviner que Lookup.Id est unique, donc aucun parent n'aura plusieurs recherches auxquelles il se joint.
Par conséquent, la 50000ème ligne dans Parent (triée par Parent.Id) sera la 50000ème ligne dans les résultats si nous n'avons pas la clause OFFSET.
Par conséquent, la requête peut dépasser les 50000 lignes pour le décalage, examiner les 50 lignes suivantes et l'utiliser pour joindre à la table de recherche. Peu importe si la jointure ne trouve rien, c'est une jointure externe gauche et elle retournera simplement NULL.
Si vous triez selon une colonne différente dans Parent et que celle-ci est indexée, elle peut dépasser ces 50000 lignes tout aussi rapidement.
Considérons maintenant la deuxième requête.
Vous voulez que les 50000 lignes que vous ignorez (par le décalage) soient les 50000 premières en fonction des résultats de la jointure. Ces 50000 lignes peuvent inclure certaines qui sont NULL, où la valeur Parent.LookupId n'existe pas dans la table de recherche. Même si vous avez un bon index sur Parent.LookupId, vous devrez probablement impliquer la plupart des lignes, car à moins que vous ne trouviez 50050 lignes qui ne se rejoignent pas correctement, vous devrez continuer. Même 50050 est bien plus que les 50 lignes que vous rejoignez dans la première requête.
Maintenant, si vous avez une clé étrangère en place, les choses peuvent être un peu différentes. Ensuite, le moteur SQL doit savoir que s'il a une valeur, alors Lookup.Name ne sera pas nul. Il pourrait donc théoriquement commencer par trouver ceux qui sont nuls, pour voir s'il y en a 50000. Mais c'est encore un peu exagéré, et il est peu probable que le moteur SQL produise un plan comme celui-ci.
Mais tu pourrais.
Donc, pour résoudre les performances de la deuxième requête, je ferais plusieurs choses.
Commencez par considérer ceux qui ne sont pas nuls. Cela signifie les lignes qui font partie d'une jointure interne. Vous pouvez créer une vue indexée à ce sujet, de sorte que vous puissiez avoir un index qui est dans l'ordre que vous voulez.
Mais vous aurez également besoin de ceux où Parent.LookupID est nul - sauf que pour ceux-ci, vous n'avez pas du tout besoin de la jointure.
Si vous effectuez un UNION ALL sur ces deux ensembles (et que vous incluez peut-être une colonne constante dans les deux, pour vous assurer que les lignes NULL apparaissent avant les NOT NULL dans votre ordre par), vous devriez pouvoir voir une amélioration.
Quelque chose comme ça:
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;
Espérons que votre plan inclura un opérateur de jointure de fusion (concaténation), de sorte qu'il extrait uniquement les lignes dont il a besoin à partir d'une analyse d'index sur la vue indexée (dans l'ordre du nom) et une recherche d'index sur le parent (pour les LookupID).