Comparaison entre left outer join et not in

bonjour

j'ai lit l'article de SQLpro qui concerne la partie optimisation http://sqlpro.developpez.com/cours/optimiser/

j'était devant la règle ou la conseil n10 éviter le sous requêtes et remplacer le par une jointure externe donc je doit remplacer le not in avec une jointure externe left outer join

en faisant un petit test je viens de trouver le contraire c'est que le not in est plus optimiser que le lefet outer join

pour info le colonne de jointure est un clé primaire dans les deux table donc un index cluster est présent

donc est ce que je laisse mais requétes avec un not in ou je le remplace par une jointure externe

merci pour vos conseil

On remarque le même genre d'étrangeté avec EXIST et IN : en soit, ça "devrait faire pareil ou presque", et "normalement le EXISTS devrait être plus rapide".

Dans les faits, le IN / NOT IN est très souvent le plus rapide.
Cependant, comme le prouve votre lot, 49% vs 51% on ne peut pas franchement parler d'un résultat probant.

Quant aux 10ms vs 310ms, à nouveau, ce sont des lantences tellement petites qu'elles peuvent s'expliquer par d'autres raisons (disque occupé par une autre ressource pendant une fraction de seconde, latence réseau, etc.)

Quand on passe de 30 minutes à 20 secondes, là, oui, on peut réellement déterminer qu'une méthode est plus optimisée que l'autre. Après, sur des durée inférieures à 1s et sur des ratios aussi faibles, privilégier avant tout :
- La lisibilité
- La simplicité
- La logique

En gros : si une requête "au premier jet, répondant littéralement à la règle énoncée" dure 51% d'un lot comparée à la requête réécrite incompréhensible qui dure 49%, autant conserver la première. Elle sera plus facile à maintenir.

Attention aussi à ne pas se baser sur le plan estimé, mais bien sur le plan réel.
Et idem, ne pas se baser sur des jeux de test, mais bien sur des données réelles dans des conditions réelles.

En effet, si par exemple le LEFT JOIN est "légèrement plus lent" mais bouffe 80% de RAM en moins que le "NOT IN", alors il n'y a pas une hésitation : en PROD, le NOT IN sera de facto moins performant (ou inversement)

Enfin, l'article en question n'est plus tout jeune, et se base sur les performances (et limitations) de l'époque.

Par exemple, sous Oracle, en version 8, on était limité en nombre de lignes dans un NOT IN : par conséquent il était inexploitable dès qu'on avait beaucoup de données. Cette limitation n'est plus vraie.

ATTENTION : les unités minimales de cycle de temps de SQL Server sont de 31,25 ms pour le CPU. Toute mesure en dessous n'a aucune signification précise. (SELECT @@TIMETICKS)
Si vos métriques sont proche de ces valeurs il convient de les ré exécuter plusieurs fois et d'en tirer une moyenne (personnellement je fais 10 exécution et je retire les valeurs extrêmes).

AUTRE CHOSE : un plan de requête est fonction des "circonstances" dont la majeure partie sont les statistiques et la mineure les contraintes.
Avec une distribution des données toute autre, vous pouvez obtenir une différence significative des plans... De même si l'on rajoute ou retire des contraintes !

A +

Exemple...

Préparation :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

USE tempdb; GO   CREATE TABLE T1 (C1 INT) GO   INSERT INTO T1 VALUES (0) GO 1000   INSERT INTO T1 VALUES (1) GO 1000   INSERT INTO T1 SELECT T1.C1 FROM T1 CROSS JOIN T1 AS T2 WHERE T1.C1 = 0 GO     CREATE TABLE T2 (C1 INT) GO   INSERT INTO T2 VALUES (1) GO 100   INSERT INTO T2 VALUES (0) GO 1000

---

Requête NOT IN :

Code:

---

1 2 3 4 5

SELECT * FROM T1 WHERE C1 NOT IN (SELECT * FROM T2 WHERE C1 <> 1)

---

Cout : 182,559

Requête NOT EXISTS :

Code:

---

1 2 3 4 5 6

SELECT * FROM T1 WHERE NOT EXISTS(SELECT * FROM T2 WHERE C1 <> 1 AND T1.C1 = T2.C1)

---

Cout : 16,9342

Semi anti jointure gauche :

Code:

---

1 2 3 4

SELECT T1.* FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1 AND T2.C1 <> 0 WHERE T2.C1 IS NULL;

---

Cout : 2961,75

Indexation : CREATE INDEX X ON T1 (C1)
Cout NOT IN : 175,355
Cout NOT EXISTS : 5,54608
Cout SAJG : 2878,38

Indexation : CREATE INDEX X2 ON T2 (C1)
Cout NOT IN : 214,892
Cout NOT EXISTS : 5,51978
Cout SAJG : 2878,37

Si vous modifiez les trois requêtes pour filtrer sur 0 à la place de 1 :
Cout NOT IN : 215,089
Cout NOT EXISTS : 5,71685
Cout SAJG : 13,7087

Autrement dit, commencez par avoir un nombre de lignes réellement significatif. Là j'en ai 2 millions dans l'une des tables....

A +

merci bien