Discussion :

[cristophe0071983]

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

[StringBuilder]

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.

[SQLpro]

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 +

[SQLpro]

Exemple...

Préparation :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 +

[cristophe0071983]

merci bien