Discussion :

[Akanath]

Bonjour,

J'ai pris une procédure stockée en cours et j'ai besoin de l'optimiser avec des jointures, j'ai un peu de mal, pouvez vous m'aider svp !

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
	SELECT  DISTINCT  top 10
		 t.INT_ID_Task,
		t.INT_ID_TaskParent,
		t.INT_ID_Project,
		t.INT_ID_CategoryTask,
		t.INT_ID_Leader,
		t.VCH_Name,
		CAST(t.TXT_Designation AS varchar(5000)),
		CAST(t.TXT_Comments1 AS varchar(5000)),
		CAST(t.TXT_Comments2 AS varchar(5000)),
		CAST(t.TXT_Comments3 AS varchar(5000)),
		CAST(t.TXT_Comments4 AS varchar(5000)),
		CAST(t.TXT_Comments5 AS varchar(5000)),
		CAST(t.TXT_Comments6 AS varchar(5000)),
		CAST(t.TXT_Comments7 AS varchar(5000)),
		CAST(t.TXT_Comments8 AS varchar(5000)),
		CAST(t.TXT_Comments9 AS varchar(5000)),
		CAST(t.TXT_Comments10 AS varchar(5000)),
		t.INT_ID_Priority,
		t.FLT_Percent_Achieved,
		t.DAT_Begin_Date,
		t.DAT_End_Date,
		t.INT_ID_Creator,
		t.INT_ID_Updater,
		t.DAT_Creation_Date,
		t.DAT_Update_Date
			FROM TBL_Task AS t,TBL_Project AS p,TBL_Task AS m,TBL_Portfolio AS port,TBL_UserPortfolio AS up
					WHERE t.BIT_Deleted=0
						AND ( t.DAT_End_date < getdate())
						AND 
						( 
							(
								 (
									(t.INT_ID_Leader = @CurrentUser) 
									OR (p.INT_ID_Leader = @CurrentUser)
									OR (p.INT_ID_Portfolio = port.INT_ID_Portfolio AND port.INT_ID_Manager = @CurrentUser)
									OR (p.INT_ID_Portfolio = up.INT_Id_Portfolio AND up.INT_ID_User = @CurrentUser)
								) 
								AND (t.INT_ID_Project=p.INT_ID_Project) 
								AND (p.INT_ID_Status=3) 
							)
 
							OR 
							( 
								( 
									(t.INT_ID_Leader = @CurrentUser) 
									OR (p.INT_ID_Leader = @CurrentUser) 
									OR (p.INT_ID_Portfolio = port.INT_ID_Portfolio AND port.INT_ID_Manager = @CurrentUser) 
									OR (p.INT_ID_Portfolio = up.INT_Id_Portfolio AND up.INT_ID_User = @CurrentUser)
									OR (m.INT_ID_Leader = @CurrentUser) 
								)
								AND (t.INT_ID_TaskParent = m.INT_ID_Task) 
								AND (m.INT_ID_Project = p.INT_ID_Project) 
								AND (p.INT_ID_Status=3) )
						)
 
					ORDER BY t.DAT_End_Date DESC

[Akanath]

Une solution : Changer le FROM part :

FROM TBL_Task AS t
left join TBL_Task AS m
ON (m.INT_ID_Task = t.INT_ID_TaskParent or m.INT_ID_Task = t.INT_ID_Task)
left join TBL_Project AS p
ON p.INT_ID_Project = m.INT_ID_Project
left join TBL_Portfolio AS port
ON port.INT_ID_PortFolio = p.INT_ID_PortFolio
left join TBL_UserPortfolio AS up
ON up.INT_ID_PortFolio = port.INT_ID_PortFolio

Serais ce meilleur ?!!!