Performances de INNER JOIN vs LEFT JOIN dans SQL Server

Question

J'ai créé une commande SQL qui utilise INNER JOIN sur 9 tables, mais cette commande prend beaucoup de temps (plus de cinq minutes). Mon entourage m'a donc suggéré de remplacer INNER JOIN par LEFT JOIN car les performances de LEFT JOIN sont meilleures, malgré ce que je sais. Après ce changement, la vitesse de la requête s'est considérablement améliorée.

Je voudrais savoir pourquoi LEFT JOIN est plus rapide que INNER JOIN ?

Ma commande SQL ressemble à ce qui suit : SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D et ainsi de suite

Mise à jour : C'est un résumé de mon schéma.

FROM sidisaleshdrmly a -- NOT HAVE PK AND FK
    INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK
        ON a.CompanyCd = b.CompanyCd 
           AND a.SPRNo = b.SPRNo 
           AND a.SuffixNo = b.SuffixNo 
           AND a.dnno = b.dnno
    INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine
        ON a.CompanyCd = h.CompanyCd
           AND a.sprno = h.AcctSPRNo
    INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix
        ON c.CompanyCd = h.CompanyCd
           AND c.FSlipNo = h.FSlipNo 
           AND c.FSlipSuffix = h.FSlipSuffix 
    INNER JOIN coMappingExpParty d -- NO PK AND FK
        ON c.CompanyCd = d.CompanyCd
           AND c.CountryCd = d.CountryCd 
    INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd
        ON b.CompanyCd = e.CompanyCd
           AND b.ProductSalesCd = e.ProductSalesCd 
    LEFT JOIN coUOM i -- PK = UOMId
        ON h.UOMId = i.UOMId 
    INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd
        ON a.CompanyCd = j.CompanyCd
            AND b.BFStatus = j.BFStatus
            AND b.ProductSalesCd = j.ProductSalesCd
    INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd
        ON e.ProductGroup1Cd  = g1.ProductGroup1Cd
    INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd
        ON e.ProductGroup1Cd  = g2.ProductGroup1Cd

Answer 1

A LEFT JOIN n'est absolument pas plus rapide qu'un INNER JOIN . En fait, c'est plus lent ; par définition, une jointure externe ( LEFT JOIN o RIGHT JOIN ) doit faire tout le travail d'un INNER JOIN plus le travail supplémentaire de null-extension des résultats. On peut également s'attendre à ce qu'elle renvoie un plus grand nombre de lignes, ce qui augmente encore le temps d'exécution total, simplement en raison de la taille plus importante de l'ensemble de résultats.

(Et même si un LEFT JOIN étaient plus rapide dans spécifique en raison d'une confluence de facteurs difficiles à imaginer, elle n'est pas fonctionnellement équivalente à un système de gestion de la qualité. INNER JOIN Vous ne pouvez donc pas simplement remplacer toutes les instances de l'un par l'autre !)

Il est fort probable que vos problèmes de performance se situent ailleurs, comme le fait de ne pas avoir une clé candidate ou une clé étrangère correctement indexée. 9 tables à joindre, c'est beaucoup, donc le ralentissement peut se situer n'importe où. Si vous publiez votre schéma, nous pourrons peut-être vous fournir plus de détails.

---

Editar:

En réfléchissant plus avant à ce sujet, j'ai pu penser à une circonstance dans laquelle une LEFT JOIN pourrait être plus rapide qu'un INNER JOIN et c'est à ce moment-là :

• Certains de ces tableaux sont très petit (disons, moins de 10 rangs) ;
• Les tables n'ont pas suffisamment d'index pour couvrir la requête.

Prenons cet exemple :

CREATE TABLE #Test1
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')

CREATE TABLE #Test2
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')

SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name

SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name

DROP TABLE #Test1
DROP TABLE #Test2

Si vous l'exécutez et que vous visualisez le plan d'exécution, vous verrez que la fonction INNER JOIN La requête coûte en effet plus cher que la LEFT JOIN car il répond aux deux critères ci-dessus. C'est parce que le serveur SQL veut faire une correspondance de hachage pour le fichier INNER JOIN mais fait des boucles imbriquées pour le LEFT JOIN ; le premier est normalement beaucoup plus rapide, mais comme le nombre de rangées est si minuscule et il n'y a pas d'index à utiliser, l'opération de hachage s'avère être la partie la plus coûteuse de la requête.

Vous pouvez constater le même effet en écrivant un programme dans votre langage de programmation préféré pour effectuer un grand nombre de consultations sur une liste de 5 éléments, par rapport à une table de hachage de 5 éléments. En raison de sa taille, la version table de hachage est en fait plus lente. Mais si l'on passe à 50 éléments, ou à 5000 éléments, la version liste devient très lente, parce que c'est O(N) contre O(1) pour la table de hachage.

Mais changez cette requête pour qu'elle soit sur le ID au lieu de la colonne Name et vous verrez une histoire très différente. Dans ce cas, il y a des boucles imbriquées pour les deux requêtes, mais l'élément INNER JOIN est capable de remplacer l'un des balayages de l'index en grappe par une recherche - ce qui signifie que ce sera littéralement un ordre de grandeur plus rapide avec un grand nombre de lignes.

La conclusion est donc plus ou moins ce que j'ai mentionné plusieurs paragraphes plus haut ; il s'agit presque certainement d'un problème d'indexation ou de couverture d'index, éventuellement combiné à une ou plusieurs très petites tables. Ce sont les seules circonstances dans lesquelles SQL Server pourrait parfois choisir un plan d'exécution plus mauvais pour une INNER JOIN qu'un LEFT JOIN .

Answer 2

Il existe un scénario important qui peut conduire à ce qu'une jointure externe soit plus rapide qu'une jointure interne et qui n'a pas encore été abordé.

Lors de l'utilisation d'une jointure externe, l'optimiseur est toujours libre de supprimer la table jointe externe du plan d'exécution si les colonnes de la jointure sont les PK de la table externe et si aucune des colonnes de la table externe n'est référencée en dehors de la jointure externe elle-même. Par exemple SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY et B.KEY est le PK de B. Oracle (je crois que j'utilisais la version 10) et Sql Server (j'utilisais 2008 R2) suppriment la table B du plan d'exécution.

Il n'en va pas nécessairement de même pour une jointure interne : SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY peut ou non exiger B dans le plan d'exécution en fonction des contraintes existantes.

Si A.KEY est une clé étrangère annulable faisant référence à B.KEY, l'optimiseur ne peut pas supprimer B du plan car il doit confirmer qu'une ligne B existe pour chaque ligne A.

Si A.KEY est une clé étrangère obligatoire référençant B.KEY, l'optimiseur est libre de supprimer B du plan car les contraintes garantissent l'existence de la ligne. Mais ce n'est pas parce que l'optimiseur peut supprimer la table du plan qu'il le fera. SQL Server 2008 R2 ne supprime PAS B du plan. Oracle 10 supprime effectivement B du plan. Il est facile de voir comment la jointure externe sera plus performante que la jointure interne sur SQL Server dans ce cas.

Il s'agit d'un exemple trivial, qui n'est pas pratique pour une requête autonome. Pourquoi joindre à une table si ce n'est pas nécessaire ?

Mais cela pourrait être une considération très importante lors de la conception des vues. Il est fréquent qu'une vue "tout faire" soit construite, qui réunit tout ce dont un utilisateur peut avoir besoin en rapport avec une table centrale. (Surtout s'il y a des utilisateurs naïfs qui font des requêtes ad hoc et qui ne comprennent pas le modèle relationnel) La vue peut inclure toutes les colonnes pertinentes de plusieurs tables. Mais les utilisateurs finaux peuvent n'accéder qu'aux colonnes d'un sous-ensemble de tables dans la vue. Si les tables sont jointes avec des jointures externes, alors l'optimiseur peut (et le fait) supprimer les tables inutiles du plan.

Il est essentiel de s'assurer que la vue utilisant des jointures externes donne les bons résultats. Comme Aaronaught l'a dit - vous ne pouvez pas aveuglément substituer OUTER JOIN à INNER JOIN et espérer les mêmes résultats. Mais il y a des moments où cela peut être utile pour des raisons de performance lors de l'utilisation de vues.

Une dernière remarque - je n'ai pas testé l'impact sur les performances à la lumière de ce qui précède, mais en théorie, il semble que vous devriez pouvoir remplacer sans risque un INNER JOIN par un OUTER JOIN si vous ajoutez également la condition <FOREIGN_KEY> IS NOT NULL à la clause where.

Answer 3

Si tout fonctionne comme il se doit, cela ne devrait pas être le cas, MAIS nous savons tous que tout ne fonctionne pas comme il se doit, surtout lorsqu'il s'agit de l'optimiseur de requêtes, de la mise en cache du plan de requêtes et des statistiques.

Je vous suggérerais d'abord de reconstruire l'index et les statistiques, puis d'effacer le cache du plan de requête pour être sûr que cela ne perturbe pas les choses. Cependant, j'ai rencontré des problèmes même lorsque cela a été fait.

J'ai connu des cas où une jointure gauche était plus rapide qu'une jointure interne.

La raison sous-jacente est la suivante : Si vous avez deux tables et que vous faites une jointure sur une colonne avec un index (sur les deux tables). La jointure interne produira le même résultat, peu importe que vous boucliez sur les entrées de l'index de la table 1 et que vous les fassiez correspondre à l'index de la table 2, comme si vous faisiez l'inverse : Bouclez sur les entrées de l'index de la table 2 et faites correspondre avec l'index de la table 1. Le problème est que lorsque vous avez des statistiques trompeuses, l'optimiseur de requêtes utilisera les statistiques de l'index pour trouver la table avec le moins d'entrées correspondantes (en fonction de vos autres critères). Si vous avez deux tables contenant chacune 1 million d'entrées, vous avez 10 lignes correspondantes dans la table 1 et 100 000 dans la table 2. La meilleure solution serait d'effectuer un balayage d'index sur la table 1 et d'effectuer 10 correspondances dans la table 2. L'inverse serait un balayage d'index qui bouclerait sur 100000 lignes et essaierait de correspondre 100000 fois et seulement 10 fois. Donc, si les statistiques ne sont pas correctes, l'optimiseur peut choisir la mauvaise table et le mauvais index à parcourir en boucle.

Si l'optimiseur choisit d'optimiser la jointure gauche dans l'ordre où elle est écrite, elle sera plus performante que la jointure interne.

MAIS, l'optimiseur peut également optimiser une jointure gauche de manière sous-optimale en tant que semi jointure gauche. Pour qu'il choisisse celle que vous voulez, vous pouvez utiliser l'indication "forcer l'ordre".

Answer 4

Essayez les deux requêtes (celle avec jointure interne et gauche) avec OPTION (FORCE ORDER) à la fin et de poster les résultats. OPTION (FORCE ORDER) est une indication de requête qui force l'optimiseur à construire le plan d'exécution avec l'ordre de jointure que vous avez fourni dans la requête.

Si INNER JOIN commence à fonctionner aussi vite que LEFT JOIN c'est parce que :

• Dans une requête composée entièrement de INNER JOIN l'ordre de jointure n'a pas d'importance. Cela laisse la liberté à l'optimiseur de requêtes d'ordonner les jointures comme il l'entend, de sorte que le problème pourrait reposer sur l'optimiseur.
• Avec LEFT JOIN mais ce n'est pas le cas, car changer l'ordre de jointure modifiera les résultats de la requête. Cela signifie que le moteur doit suivre l'ordre de jointure que vous avez fourni dans la requête, qui peut être meilleur que l'ordre optimisé.

Je ne sais pas si cela répond à votre question, mais j'ai participé à un projet qui comportait des requêtes très complexes effectuant des calculs, ce qui a complètement perturbé l'optimiseur. Nous avions des cas où une FORCE ORDER réduirait le temps d'exécution d'une requête de 5 minutes à 10 secondes.

Answer 5

Il est plus probable que vos problèmes de performances soient dus au nombre de jointures que vous effectuez et au fait que les colonnes sur lesquelles vous effectuez les jointures possèdent ou non des index.

Dans le pire des cas, vous pourriez facilement effectuer 9 analyses de tables entières pour chaque jointure.