Temps exécution trop long sur 3 tables avec sous-requête

**afrodje** · 24/04/2014, 09h31

Bonjour,

J'ai une requête qui calcul le nombre de ligne en comparant 2 tables (avec une jointure sur une 3ème table) :

Code :

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

1
2
3
4
5
6
7
8
9
10
select count(*)
from dbo.TABLE1 A, DATAWAREHOUSE.Datawarehouse_Prod.dbo.TABLE2 B
where  numero_bp+replace(replace(numero_ligne,'.',''),',','') not in 
(
	select numero_bp+numero_ligne
	from dbo.TABLE3
)
and A.numero_ligne <> 'REPORT O'
and A.[Article] = B.IMLITM 
and B.IMLNTY IN ('S ', 'W ','OC')

En terme de répartition du nombre de lignes :
TABLE1 : 7 000
TABLE2 : 32 000
TABLE3 : 10 000

Le problème est le temps d’exécution de la requête, trop long environ 1 minute.
A mon avis, le faite de faire numero_bp+numero_ligne ralenti énormément.

Merci pour votre aide.

**aieeeuuuuu** · 24/04/2014, 10h22

Bonjour,

Essayez ces requêtes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT COUNT(*)
FROM		dbo.TABLE1 A 
INNER JOIN	DATAWAREHOUSE.Datawarehouse_Prod.dbo.TABLE2 B
	ON			A.[Article] = B.IMLITM 
LEFT JOIN	dbo.TABLE3 C
	ON			C.numero_bp = A.numero_bp 
	AND			REPLACE(REPLACE(A.numero_ligne,'.',''),',','') = C.numero_ligne
WHERE 
		A.numero_ligne <> 'REPORT O'
AND		B.IMLNTY IN ('S ', 'W ','OC')

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
 
SELECT COUNT(*)
FROM		dbo.TABLE1 A 
INNER JOIN	DATAWAREHOUSE.Datawarehouse_Prod.dbo.TABLE2 B
	ON			A.[Article] = B.IMLITM 
LEFT JOIN	dbo.TABLE3 C
	ON			C.numero_bp = A.numero_bp 
	AND			C.numero_ligne = REPLACE(REPLACE(A.numero_ligne,'.',''),',','') 
WHERE	NOT EXISTS(
	SELECT 1
	FROM	dbo.TABLE3 C
	WHERE 
				C.numero_bp = A.numero_bp 
	AND			C.numero_ligne = REPLACE(REPLACE(A.numero_ligne,'.',''),',','')
)	
AND		A.numero_ligne <> 'REPORT O'
AND		B.IMLNTY IN ('S ', 'W ','OC')

Il faudra prévoir les index pour les supporter.
Pouvez-vous nous donner la structure de vos tables et de vos index

**afrodje** · 24/04/2014, 10h41

Structure TABLE1 :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
CREATE TABLE [dbo].[TABLE1](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Numero_commande] [varchar](8) COLLATE French_CI_AS NULL,
	[Sufixe_Cde] [varchar](3) COLLATE French_CI_AS NULL,
	[Date_commande] [varchar](12) COLLATE French_CI_AS NOT NULL,
	[Date_promise] [varchar](12) COLLATE French_CI_AS NULL,
	[Type_commande] [varchar](2) COLLATE French_CI_AS NULL,
	[Livre_a_numero] [int] NULL,
	[Livre_a_nom] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse1] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse2] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse3] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_ville] [varchar](25) COLLATE French_CI_AS NULL,
	[Livre_a_code_postal] [varchar](12) COLLATE French_CI_AS NULL,
	[Livre_a_pays] [varchar](25) COLLATE French_CI_AS NULL,
	[Facture_a_numero] [int] NULL,
	[Facture_a_nom] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse1] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse2] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse3] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_ville] [varchar](25) COLLATE French_CI_AS NULL,
	[Facture_a_code_postal] [varchar](12) COLLATE French_CI_AS NULL,
	[Facture_a_pays] [varchar](25) COLLATE French_CI_AS NULL,
	[Numero_BL] [int] NULL,
	[Numero_BP] [int] NULL,
	[Prochain_statut] [int] NULL,
	[Dernier_statut] [int] NULL,
	[Magasin] [varchar](50) COLLATE French_CI_AS NULL,
	[Numero_ligne] [varchar](50) COLLATE French_CI_AS NULL,
	[Article] [varchar](50) COLLATE French_CI_AS NULL,
	[Designation] [varchar](30) COLLATE French_CI_AS NULL,
	[Code_ean_upc] [varchar](50) COLLATE French_CI_AS NULL,
	[Famille_produit] [varchar](25) COLLATE French_CI_AS NULL,
	[Emplacement] [varchar](50) COLLATE French_CI_AS NOT NULL,
	[Numero_lot] [varchar](50) COLLATE French_CI_AS NULL,
	[Quantite_a_expedie] [varchar](50) COLLATE French_CI_AS NULL,
	[Unite_mesure] [varchar](50) COLLATE French_CI_AS NULL,
	[poid_total] [varchar](50) COLLATE French_CI_AS NULL,
	[COLONNE1] [varchar](50) COLLATE French_CI_AS NULL,
	[COLONNE2] [varchar](50) COLLATE French_CI_AS NULL,
	[COLONNE3] [varchar](50) COLLATE French_CI_AS NULL,
	[COLONNE4] [varchar](50) COLLATE French_CI_AS NULL,
	[COLONNE5] [varchar](50) COLLATE French_CI_AS NULL,
	[Niveau] [varchar](2) COLLATE French_CI_AS NULL,
	[Allee] [varchar](2) COLLATE French_CI_AS NULL,
	[Colonne] [varchar](2) COLLATE French_CI_AS NULL,
	[Scolonne] [varchar](2) COLLATE French_CI_AS NULL,
	[Emplacement_T] [varchar](50) COLLATE French_CI_AS NULL,
	[Zone_Prepa] [varchar](50) COLLATE French_CI_AS NULL,
	[Kit_Sdcomm] [varchar](20) COLLATE French_CI_AS NULL,
	[Kit_Sdktln] [varchar](20) COLLATE French_CI_AS NULL,
 CONSTRAINT [PK_BP] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Structure TABLE2 :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
CREATE TABLE [dbo].TABLE2(
	[IMITM] [decimal](8, 0) NOT NULL,
	[IMLITM] [char](25) COLLATE French_CI_AS NOT NULL,
	[IMAITM] [char](25) COLLATE French_CI_AS NOT NULL,
	[IMDSC1] [char](30) COLLATE French_CI_AS NOT NULL,
	[IMDSC2] [char](30) COLLATE French_CI_AS NOT NULL,
	[IMSRTX] [char](30) COLLATE French_CI_AS NOT NULL,
	[IMALN] [char](30) COLLATE French_CI_AS NOT NULL,
	[IMSRP1] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMSRP2] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMSRP3] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMSRP4] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMSRP5] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMSRP6] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMSRP7] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMSRP8] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMSRP9] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMSRP0] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMPRP1] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMPRP2] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMPRP3] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMPRP4] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMPRP5] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMPRP6] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMPRP7] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMPRP8] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMPRP9] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMPRP0] [char](6) COLLATE French_CI_AS NOT NULL,
	[IMCDCD] [char](15) COLLATE French_CI_AS NOT NULL,
	[IMPDGR] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMDSGP] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMPRGR] [char](8) COLLATE French_CI_AS NOT NULL,
	[IMRPRC] [char](8) COLLATE French_CI_AS NOT NULL,
	[IMORPR] [char](8) COLLATE French_CI_AS NOT NULL,
	[IMBUYR] [decimal](8, 0) NOT NULL,
	[IMDRAW] [char](20) COLLATE French_CI_AS NOT NULL,
	[IMRVNO] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMDSZE] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMVCUD] [decimal](15, 0) NOT NULL,
	[IMCARS] [decimal](8, 0) NOT NULL,
	[IMCARP] [decimal](8, 0) NOT NULL,
	[IMSHCN] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMSHCM] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMUOM1] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUOM2] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUOM3] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUOM4] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUOM6] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUOM8] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUOM9] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUWUM] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUVM1] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMSUTM] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMUMVW] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMCYCL] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMGLPT] [char](4) COLLATE French_CI_AS NOT NULL,
	[IMPLEV] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMPPLV] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMCLEV] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMPRPO] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMCKAV] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMBPFG] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMSRCE] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMOT1Y] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMOT2Y] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMSTDP] [decimal](7, 0) NOT NULL,
	[IMFRMP] [decimal](7, 0) NOT NULL,
	[IMTHRP] [decimal](7, 0) NOT NULL,
	[IMSTDG] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMFRGD] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMTHGD] [char](3) COLLATE French_CI_AS NOT NULL,
	[IMCOTY] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMSTKT] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMLNTY] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMCONT] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMBACK] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMIFLA] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMTFLA] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMINMG] [char](10) COLLATE French_CI_AS NOT NULL,
	[IMABCS] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMABCM] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMABCI] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMOVR] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMWARR] [char](8) COLLATE French_CI_AS NOT NULL,
	[IMCMCG] [char](8) COLLATE French_CI_AS NOT NULL,
	[IMSRNR] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMPMTH] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMFIFO] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMLOTS] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMSLD] [decimal](6, 0) NOT NULL,
	[IMANPL] [decimal](8, 0) NOT NULL,
	[IMMPST] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMPCTM] [decimal](7, 0) NOT NULL,
	[IMMMPC] [decimal](7, 0) NOT NULL,
	[IMPTSC] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMSNS] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMLTLV] [decimal](5, 0) NOT NULL,
	[IMLTMF] [decimal](5, 0) NOT NULL,
	[IMLTCM] [decimal](5, 0) NOT NULL,
	[IMOPC] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMOPV] [decimal](15, 0) NOT NULL,
	[IMACQ] [decimal](15, 0) NOT NULL,
	[IMMLQ] [decimal](15, 0) NOT NULL,
	[IMLTPU] [decimal](5, 0) NOT NULL,
	[IMMPSP] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMMRPP] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMITC] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMORDW] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMMTF1] [decimal](5, 0) NOT NULL,
	[IMMTF2] [decimal](5, 0) NOT NULL,
	[IMMTF3] [decimal](5, 0) NOT NULL,
	[IMMTF4] [decimal](5, 0) NOT NULL,
	[IMMTF5] [decimal](5, 0) NOT NULL,
	[IMEXPD] [decimal](5, 0) NOT NULL,
	[IMDEFD] [decimal](5, 0) NOT NULL,
	[IMSFLT] [decimal](5, 0) NOT NULL,
	[IMMAKE] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMCOBY] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMLLX] [decimal](3, 0) NOT NULL,
	[IMCMGL] [char](1) COLLATE French_CI_AS NOT NULL,
	[IMCOMH] [decimal](3, 0) NOT NULL,
	[IMCSNN] [decimal](8, 0) NOT NULL,
	[IMURCD] [char](2) COLLATE French_CI_AS NOT NULL,
	[IMURDT] [decimal](6, 0) NOT NULL,
	[IMURAT] [decimal](15, 0) NOT NULL,
	[IMURAB] [decimal](8, 0) NOT NULL,
	[IMURRF] [char](15) COLLATE French_CI_AS NOT NULL,
	[IMUSER] [char](10) COLLATE French_CI_AS NOT NULL,
	[IMPID] [char](10) COLLATE French_CI_AS NOT NULL,
	[IMJOBN] [char](10) COLLATE French_CI_AS NOT NULL,
	[IMUPMJ] [decimal](6, 0) NOT NULL,
	[IMTDAY] [decimal](6, 0) NOT NULL
)

Structure TABLE3 :

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
CREATE TABLE [dbo].[TABLE3](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Numero_BP] [int] NULL,
	[Numero_ligne] [varchar](50) COLLATE French_CI_AS NULL,
	[Numero_BL] [int] NULL,
	[Prochain_statut] [int] NULL,
	[Dernier_statut] [int] NULL,
	[Numero_commande] [varchar](8) COLLATE French_CI_AS NULL,
	[Sufixe_Cde] [varchar](3) COLLATE French_CI_AS NULL,
	[Date_commande] [varchar](12) COLLATE French_CI_AS NULL,
	[Date_promise] [varchar](12) COLLATE French_CI_AS NULL,
	[Type_commande] [varchar](2) COLLATE French_CI_AS NULL,
	[Magasin] [varchar](50) COLLATE French_CI_AS NULL,
	[Article] [varchar](50) COLLATE French_CI_AS NULL,
	[Designation] [varchar](30) COLLATE French_CI_AS NULL,
	[Code_ean_upc] [varchar](50) COLLATE French_CI_AS NULL,
	[Famille_produit] [varchar](25) COLLATE French_CI_AS NULL,
	[Emplacement] [varchar](50) COLLATE French_CI_AS NULL,
	[Numero_lot] [varchar](50) COLLATE French_CI_AS NULL,
	[Quantite_a_expedie] [varchar](50) COLLATE French_CI_AS NULL,
	[Unite_mesure] [varchar](50) COLLATE French_CI_AS NULL,
	[poid_total] [varchar](50) COLLATE French_CI_AS NULL,
	[Niveau] [varchar](5) COLLATE French_CI_AS NULL,
	[Allee] [varchar](2) COLLATE French_CI_AS NULL,
	[Colonne] [varchar](2) COLLATE French_CI_AS NULL,
	[Scolonne] [varchar](2) COLLATE French_CI_AS NULL,
	[Emplacement_T] [varchar](50) COLLATE French_CI_AS NULL,
	[Zone_Prepa] [varchar](50) COLLATE French_CI_AS NULL,
	[Livre_a_numero] [int] NULL,
	[Livre_a_nom] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse1] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse2] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse3] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_adresse4] [varchar](40) COLLATE French_CI_AS NULL,
	[Livre_a_ville] [varchar](25) COLLATE French_CI_AS NULL,
	[Livre_a_code_postal] [varchar](12) COLLATE French_CI_AS NULL,
	[Livre_a_pays] [varchar](25) COLLATE French_CI_AS NULL,
	[Facture_a_nom] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse1] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse2] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse3] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_adresse4] [varchar](40) COLLATE French_CI_AS NULL,
	[Facture_a_ville] [varchar](25) COLLATE French_CI_AS NULL,
	[Facture_a_code_postal] [varchar](12) COLLATE French_CI_AS NULL,
	[Facture_a_pays] [varchar](25) COLLATE French_CI_AS NULL,
	[lot_PICK] [varchar](50) COLLATE French_CI_AS NULL,
	[emplacement_PICK] [varchar](50) COLLATE French_CI_AS NULL,
	[quantite_PICK] [int] NULL,
	[prelever_par] [varchar](50) COLLATE French_CI_AS NULL,
	[Kit_Sdcomm] [varchar](20) COLLATE French_CI_AS NULL,
	[Kit_Sdktln] [varchar](20) COLLATE French_CI_AS NULL,
	[remarque] [varchar](50) COLLATE French_CI_AS NULL,
	[heure_recup] [varchar](50) COLLATE French_CI_AS NULL,
	[nombre_serie_a_scanner] [int] NULL CONSTRAINT [DF_BP_detail_nombre_serie_a_scanner]  DEFAULT ((0)),
	[container_destination] [varchar](50) COLLATE French_CI_AS NULL,
	[MISSION] [varchar](50) COLLATE French_CI_AS NULL,
	[statut_PDA] [int] NULL CONSTRAINT [DF_BP_detail_statut_PDA]  DEFAULT ((560)),
	[type_ligne] [varchar](5) COLLATE French_CI_AS NULL,
 CONSTRAINT [PK_BP_detail] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

**afrodje** · 24/04/2014, 10h55

Envoyé par aieeeuuuuu

Bonjour,

Essayez ces requêtes :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT COUNT(*)
FROM		dbo.TABLE1 A 
INNER JOIN	DATAWAREHOUSE.Datawarehouse_Prod.dbo.TABLE2 B
	ON			A.[Article] = B.IMLITM 
LEFT JOIN	dbo.TABLE3 C
	ON			C.numero_bp = A.numero_bp 
	AND			REPLACE(REPLACE(A.numero_ligne,'.',''),',','') = C.numero_ligne
WHERE 
		A.numero_ligne <> 'REPORT O'
AND		B.IMLNTY IN ('S ', 'W ','OC')

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
 
SELECT COUNT(*)
FROM		dbo.TABLE1 A 
INNER JOIN	DATAWAREHOUSE.Datawarehouse_Prod.dbo.TABLE2 B
	ON			A.[Article] = B.IMLITM 
LEFT JOIN	dbo.TABLE3 C
	ON			C.numero_bp = A.numero_bp 
	AND			C.numero_ligne = REPLACE(REPLACE(A.numero_ligne,'.',''),',','') 
WHERE	NOT EXISTS(
	SELECT 1
	FROM	dbo.TABLE3 C
	WHERE 
				C.numero_bp = A.numero_bp 
	AND			C.numero_ligne = REPLACE(REPLACE(A.numero_ligne,'.',''),',','')
)	
AND		A.numero_ligne <> 'REPORT O'
AND		B.IMLNTY IN ('S ', 'W ','OC')

Il faudra prévoir les index pour les supporter.
Pouvez-vous nous donner la structure de vos tables et de vos index

La première ne remonte pas le bon résultat mais la deuxième me parait correct et surtout rapide.
Quel est l'avantage ici d'utiliser une jointure externe au lieu du jointure classique?

**aieeeuuuuu** · 24/04/2014, 11h10

J'ai en effet oublié un clause dans la première requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT COUNT(*)
FROM		dbo.TABLE1 A 
INNER JOIN	DATAWAREHOUSE.Datawarehouse_Prod.dbo.TABLE2 B
	ON			A.[Article] = B.IMLITM 
LEFT JOIN	dbo.TABLE3 C
	ON 			C.numero_bp = A.numero_bp 
	AND 			REPLACE(REPLACE(A.numero_ligne,'.',''),',','') = C.numero_ligne
WHERE 
		A.numero_ligne <> 'REPORT O'
AND		B.IMLNTY IN ('S ', 'W ','OC')
AND 		C.numero_bp IS NULL

Ce qui explique l’intérêt de la jointure externe : on ne garde au final que les lignes pour lesquelles la jointure ne donne rien...

**afrodje** · 24/04/2014, 11h35

Ok parfait.
Je garde la requête 2.

Merci

**aieeeuuuuu** · 24/04/2014, 11h36

Envoyé par afrodje

Structure TABLE2 :

Waou ! Vous avez eu un prix de gros sur les colonnes ?

Revoir la modélisation serait certainement une bonne chose...
Certains type impliqués dans les jointures ne sont pas les même entre les différentes tables...
Pas de clef étrangère déclarées... pas toutes les clef primaires non plus...
Vos requêtes ont en effet toutes les chances d'être lentes...

Vous pouvez néanmoins essayer de poser les index suivants :

Code :

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

1
2
3
4
5
6
7
8
9
 
CREATE INDEX IX_TABLE1 ON dbo.TABLE1(Article, numero_bp) INCLUDE(numero_ligne)
GO
 
CREATE INDEX IX_TABLE2 ON [dbo].TABLE2(IMLITM) WHERE IMLNTY IN ('S ', 'W ','OC')
GO
 
CREATE INDEX IX_TABLE3 ON dbo.TABLE3(numero_bp , numero_ligne)
GO