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

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

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:

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.

Bonjour,

Essayez ces requêtes :

Code:

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:

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

Structure TABLE1 :

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
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:

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:

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]

Citation:

Envoyé par aieeeuuuuu

Bonjour,

Essayez ces requêtes :

Code:

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:

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?

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

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...

Ok parfait.
Je garde la requête 2.

Merci ;)

Citation:

Envoyé par afrodje

Structure TABLE2 :

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

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:

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

Elles ressemblent à quoi vos valeurs pour A.numero_ligne où il y a des points et des virgules ?

Citation:

Envoyé par Sergejack

Elles ressemblent à quoi vos valeurs pour A.numero_ligne où il y a des points et des virgules ?

Numero de ligne au format JDE, c'est a dire :
1.000
2.000
3.000

Dans certains cas, cela peut être des virgules à la place des points.

Citation:

Envoyé par afrodje

Numero de ligne au format JDE, c'est a dire :
1.000
2.000
3.000

Dans certains cas, cela peut être des virgules à la place des points.

Et C.numero_ligne ressemble à

1000
2000
3000

?

Citation:

Envoyé par Sergejack

Et C.numero_ligne ressemble à

1000
2000
3000

?

Exactement.