Index sur Sql Server 2005

Version imprimable

10/02/2009, 10h01
Naruto_kun

Index sur Sql Server 2005

Bonjour à tous,

je voulais savoir si il existait une option sur sql server 2005 qui nous définit les index à créer lorsque l'on lance un requête. Pourriez vous m'aider à ce propos?

Si cela n'existe pas, comment savoir sur quel champ créer un index. Il faut que je fasse de l'optimisation mais je ne sais pas par où commencer.

Merci de votre soutien
10/02/2009, 10h09
elsuket

Bonjour,

L'indexation d'une base de données est tatillon et nécessite un travail assez long de surveillance, de test de gain (SET STATISTICS IO, SET STAITSTICS TIME, ...), de lecture de plans de requête, ...

Avec SQL Server 2005 vous pouvez rechercher les indexes manquants, mais cette fonctionnalité ne constitue qu'un indicateur, c'est à vous de décider ensuite, en fonction de la structure de vos tables & vues, et des requêtes qui s'exécutent dessus, les indexes qui doivent être implémentés, ce qui doivent être rajoutés et ceux qui doivent être supprimés.

Un bon article également sur l'indexation, par SQLPro.

@++
10/02/2009, 11h39
Naruto_kun

Merci pour votre aide.
J'ai lu ce que vous m'avez envoyé et cela m'a l'air un peu compliqué.

Il faut que je crée des index sur une vue qui fait des uni de requetes complexes.
Je vous dit pas la compléxité de la chose.
Je cherche à quel endroit j'ai pu faire des jointures et je créer des index sur ces liens là.

J'espere quie cela est bien la bonne methode
10/02/2009, 11h41
Naruto_kun

Lorsque j'execute une requête sur sql server 2005, n'existe t il pas un moyen de savoir à quel endroit créer un index?
J'ai vu y fort longtemps un exemple mais je ne me souviens plus comment avait fait la personne en question.

Il a éxécuté sa requete et ensuite une aide sur l'endroit où créer les index et apparue. Est ce possible?
10/02/2009, 12h10
SQLpro

Vous pouvez utiliser DTA ou bien les dmv afin d'obtenir des renseignements sur la création des index. Mais ceci n'a d'intérêt qu'à deux conditions :
1) suffisamment de données (plusieurs dizaines de milliers de lignes)
2) suffisamment d'exécution de la requête en question.

Sinon l'indexation à priori reste la seule solution.

A +
10/02/2009, 13h28
elsuket

Citation:

Je cherche à quel endroit j'ai pu faire des jointures et je créer des index sur ces liens là.

C'est bon mais cela ne suffit pas.

Le mieux reste que vous analysiez le plan de requête des requêtes spécifiant vos vues (CTRL+L pour avoir le plan d'exécution estimé sous Management Studio)

@++ ;)
10/02/2009, 14h11
Naruto_kun

Citation:

Envoyé par elsuket

C'est bon mais cela ne suffit pas.

Le mieux reste que vous analysiez le plan de requête des requêtes spécifiant vos vues (CTRL+L pour avoir le plan d'exécution estimé sous Management Studio)

@++ ;)

Je veux bien mais j'agis à quel niveau. Où dois je regarder dans la plan D'execution? Honnetement, je suis débutant et j'essais de comprendre.

Merci pour votre aide
10/02/2009, 15h19
elsuket

Prenez la requête SELECT qui spécifie votre vue sous SQL Server Management Studio, en cliquant droit sur celle-ci puis en choisissant ''Générer un script de la vue en tant que"/"CREATE to"/"Nouvelle fenêtre de l'éditeur de requêtes"

Ensuite faites CTRL+L, vous obtiendrez ainsi le plan d'exécution estimé par SQL Server, c'est à dire ce que l'optimiseur pense qu'il va faire pour exécuter votre requête en utilisant un minimum de ressources.
Ce plan d'exéction se lit de haut en bas mais de droite à gauche.

Les icônes du plan d'exécution vous permettent de trouver assez facilement ce qui rend votre requête lente à l'exécution :

- TABLE SCAN
- CLUSTERED INDEX SCAN
- RID LOOKUP
- BOOKMARK LOOKUP
- KEY LOOKUP
- HASH MATCH
- ...

Vous devez poser les index sur vos tables en fonction des prédicats de recherche libellés dans les info-bulles lorsque vous passez la souris sur une des icônes du plan d'exécution.

D'autre part vous remarquerez que l'épaisseur des flèches est parfois plus petite au début du plan qu'à la fin : cela vous renseigne sur le cardinal de l'ensemble de données manipulé par chaque étape du plan d'exécution.

Veillez à ce que vos index contiennent des clés de faible largeur (entiers, dates), pas trop de colonnes.

Pour voir le gain obtenu, vous devez exécuter une première fois votre requête en la préfixant par SET STATISTICS IO ON (ce qui vous permettra de voir par table impliquées dans votre requête le nombre de pages lues : plus il est faible, plus rapide seront vos requêtes) et SET STATISTICS TIME ON ensuite (pour comparer les temps de compilation et d'exécution entre deux poses d'index).

Posez ensuite vos index, et regardez le nombre de pages lues : s'il baisse c'est bon, mais il faut vous assurer que SQL Server les utilise correctement, (pas de RID LOOKUP, BOOKMARK LOOKUP, KEY LOOKUP, ...) et seul le plan d'exécution peut vous le montrer.

Bref, vous avez du pain sur la planche.
N'hésitez pas à poster votre plan (une fois que vous l'avez, CTRL+S) et à le poster ici au besoin.

Bon courage,

@++ ;)

voici ma requete

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
 
select
  1 as S4_REPORT_ACTION_PLAN_ID
, 'A' + convert(char, a.S4_ACTIVITY_NUMBER) as S4_REPORT_ACTION_PLAN_NUMBER 
, convert(char(8),a.S4_REQUEST_DATE,112) as S4_REPORT_ACTION_PLAN_DEAD_LINE
, sourc_coun.RF_COUNTRY_CODE as S4_REPORT_ACTION_PLAN_SOURCING_COUNTRY
, sourc_coun.RF_COUNTRY_DESC as S4_REPORT_ACTION_PLAN_SOURCING_COUNTRY_DESC
, dest_coun.SAM_RF_COUNTRY_CODE as S4_REPORT_ACTION_PLAN_DEST_COUNTRY
, dest_coun.SAM_RF_COUNTRY_DESC as S4_REPORT_ACTION_PLAN_DEST_COUNTRY_DESC
, a.S4_SOURCING_REGIONAL_UNIT as S4_REPORT_ACTION_PLAN_SOURCING_REGIONAL_UNIT
, a.S4_SOURCING_REGION as S4_REPORT_ACTION_PLAN_SOURCING_REGION
, dest_ru.SAM_RF_REGIONAL_UNIT_REGION as S4_REPORT_ACTION_PLAN_DEST_REGION_DESC
, dest_ru.SAM_RF_REGIONAL_UNIT_REGION as S4_REPORT_ACTION_PLAN_DEST_REGION
, a.S4_DESTINATION_REGIONAL_UNIT as S4_REPORT_ACTION_PLAN_DEST_REGIONAL_UNIT
, dest_ru.SAM_RF_REGIONAL_UNIT_DESC as S4_REPORT_ACTION_PLAN_DEST_REGIONAL_UNIT_DESC
, p.S4_PROJECT_NAME as S4_REPORT_ACTION_PLAN_PROJECT_NAME
, isnull(p.S4_PROJECT_NAME,'') + ' - ' + convert(varchar(20),isnull(p.S4_PROJECT_NUMBER,0)) as S4_REPORT_ACTION_PLAN_PROJECT_NAME_NUMBER
, p.S4_PROJECT_NUMBER as S4_REPORT_ACTION_PLAN_PROJECT_NUMBER
, '' as S4_REPORT_ACTION_PLAN_STRATEGICAL_PROJECT
, p.S4_CUSTOMER as S4_REPORT_ACTION_PLAN_CUSTOMER
, a.S4_BUSINESS_DIVISION as S4_REPORT_ACTION_PLAN_BD
, a.S4_BUSINESS_GROUP as S4_REPORT_ACTION_PLAN_BG
, isnull(a.S4_SERVICE_BUSINESS_GROUP,'') + isnull(a.S4_SERVICE_BUSINESS_DIVISION,'') as S4_REPORT_ACTION_PLAN_SERVICE_BD_BG
, a.S4_ACTIVITY_DESCRIPTION as S4_REPORT_ACTION_PLAN_ACTION_DESC
, sfam.RF_ORG4_SUBFAM_DESC as S4_REPORT_ACTION_PLAN_SUB_FAMILY
, a.S4_FAMILY as S4_REPORT_ACTION_PLAN_FAMILY
, a.S4_COUNCIL as S4_REPORT_ACTION_PLAN_COUNCIL
, a.S4_NEGOTIATION_TYPE as S4_REPORT_ACTION_PLAN_NEGO_TYPE
, convert(char(8),a.S4_AXIS_PRICE_VALID_FROM_DATE,112) as S4_REPORT_ACTION_PLAN_PRICE_VALID_FROM
, round(isnull(a.S4_ACTIVITY_PROBABILITY_PERCENT,0) * isnull(a.S4_PROJECT_PROBABILITY_PERCENT,0) * 100,2) as S4_REPORT_ACTION_PLAN_TOTAL_PROBABILITY
, round(isnull(a.S4_NEGO_ESTIMATED_SUCCESS_RATE,0) * 100, 2)  as S4_REPORT_ACTION_PLAN_NEGO_RATE
, round(isnull(S4_ACTUAL_SAVING_PERCENT,0) * 100,2) as S4_REPORT_ACTION_PLAN_GPS_SAVING
, case when a.S4_BUDGET_PROVISION_FLAG = 'N' then 'NO' when a.S4_BUDGET_PROVISION_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_BUDGET_PROVISION_FLAG
, case when a.S4_RENEGOTIATION_FLAG = 'N' then 'NO' when a.S4_RENEGOTIATION_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_RENEGOTIATION_FLAG
, p.S4_PROJECT_ACTIVITY_GROSS_SPEND_EUR as S4_REPORT_ACTION_PLAN_GROSS_SPEND_EUR
, case when a.S4_AXIS_CONTRACT_NUMBER = '0' then '' else convert(varchar(20),a.S4_AXIS_CONTRACT_NUMBER) end as S4_REPORT_ACTION_PLAN_AXIS_CONTRACT_NUMBER
, List.FieldLabel as S4_REPORT_ACTION_PLAN_CRP_CATEGORY
, case when a.S4_SPOT_TEAM_FLAG = 'N' then 'NO' when a.S4_SPOT_TEAM_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_SPOT_TEAM_FLAG
, case when a.S4_SYNERGY_FLAG = 'N' then 'NO' when a.S4_SYNERGY_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_SYNERGY_FLAG
, case when a.S4_RECURRENT_FLAG = 'N' then 'NO' when a.S4_RECURRENT_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_RECURRENT_FLAG
, case when a.S4_LOCAL_ACTIVITY_FLAG = 'N' then 'NO' when a.S4_LOCAL_ACTIVITY_FLAG = 'Y' then 'YES' else '' end as S4_REPORT_ACTION_PLAN_LOCAL_ACTIVITY_FLAG
, 'Project Sourcing' as S4_REPORT_ACTION_PLAN_DOMAIN
, a.S4_RPIS_OFFICIAL_EUR as S4_REPORT_ACTION_PLAN_RPIS_OFFICIAL_EUR
, a.S4_SERVICE_BUSINESS_GROUP as S4_REPORT_ACTION_PLAN_SERVICE_BG
, a.S4_SERVICE_BUSINESS_DIVISION as S4_REPORT_ACTION_PLAN_SERVICE_BD
, a.S4_BUSINESS_UNIT as S4_REPORT_ACTION_PLAN_BUSINESS_UNIT
, '' as S4_REPORT_ACTION_PLAN_BLANK_1
, '' as S4_REPORT_ACTION_PLAN_BLANK_2
, '' as S4_REPORT_ACTION_PLAN_BLANK_3
, '' as S4_REPORT_ACTION_PLAN_BLANK_4
, '' as S4_REPORT_ACTION_PLAN_BLANK_5
, '' as S4_REPORT_ACTION_PLAN_BLANK_6
, '' as S4_REPORT_ACTION_PLAN_CARY_SAVING_Y1
, '' as S4_REPORT_ACTION_PLAN_CARY_APPLICATION
, '' as S4_REPORT_ACTION_PLAN_SPEND_TBD
, '' as S4_REPORT_ACTION_PLAN_SAVING_TBD
, '' as S4_REPORT_ACTION_PLAN_SYNERGY_SPEND
, '' as S4_REPORT_ACTION_PLAN_SYNERGY_SAVING
,  round(isnull(a.S4_ACTIVITY_PROBABILITY_PERCENT,0) * isnull(a.S4_PROJECT_PROBABILITY_PERCENT,0) * 100,2) as S4_REPORT_ACTION_PLAN_RISK
 
, round(convert(numeric(30,9), 
 
  IsNull(S4_ACTUAL_SPEND_TOTAL_EUR, 0)
  * IsNull(ys.S4_SHARE_PERCENT, 0)), 2)
 as S4_REPORT_ACTION_PLAN_ESTIMATED_TOTAL
 
, u_nego.RF_LAST_NAME + ' ' + u_nego.RF_FIRST_NAME as S4_REPORT_ACTION_PLAN_NEGOTIATOR
, ys.S4_YEAR as S4_REPORT_ACTION_PLAN_YEAR
, convert(varchar(10),a.S4_SNAPSHOT_NUMBER) as S4_REPORT_ACTION_PLAN_SNAPSHOT
, p.S4_PROJECT_PRIME_PPM as S4_REPORT_ACTION_PLAN_PRIME_PPM
from S4_ACTIVITY_SNAPSHOT a
INNER JOIN S4_PROJECT_SNAPSHOT p ON a.S4_PROJECT_ID = p.S4_PROJECT_ID and p.S4_LATEST_PROJECT_FLAG='Y' and p.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
LEFT OUTER JOIN S4_YEAR_SHARE_SNAPSHOT ys ON ys.S4_PARENT_TYPE_ACTIVITY='A' and a.S4_ACTIVITY_ID=ys.S4_PARENT_ID and ys.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER and ys.S4_ACTIVE='A' -- Added on 12-Oct-2008 
LEFT JOIN	RF_ORG4_SUBFAM sfam	ON sfam.RF_ORG4_SUBFAM_CODE= a.S4_SUB_FAMILY OR upper(sfam.RF_ORG4_SUBFAM_DESC) = upper(a.S4_SUB_FAMILY)
LEFT JOIN	RF_ORG3_FAMILY fam	ON fam.RF_ORG3_FAMILY_CODE = a.S4_FAMILY OR upper(fam.RF_ORG3_FAMILY_DESC) = upper(a.S4_FAMILY)
LEFT JOIN	RF_ORG2_COUNCIL cou	ON cou.RF_ORG2_COUNCIL_CODE = a.S4_COUNCIL OR upper(cou.RF_ORG2_COUNCIL_DESC) = upper(a.S4_COUNCIL)
LEFT JOIN	SAM_RF_REGIONAL_UNIT dest_ru	ON dest_ru.SAM_RF_REGIONAL_UNIT_CODE = a.S4_DESTINATION_REGIONAL_UNIT
LEFT JOIN	SAM_RF_COUNTRY dest_coun	ON dest_coun.SAM_RF_COUNTRY_CODE  = a.S4_DESTINATION_COUNTRY OR upper(a.S4_DESTINATION_COUNTRY) = upper(dest_coun.SAM_RF_COUNTRY_DESC)
LEFT JOIN	RF_PURCH_SUB_AREA sourc_ru	ON sourc_ru.RF_PURCH_SUB_AREA_CODE = a.S4_SOURCING_REGIONAL_UNIT
LEFT JOIN	RF_COUNTRY sourc_coun	ON sourc_coun.RF_COUNTRY_CODE  = a.S4_SOURCING_COUNTRY OR upper(a.S4_SOURCING_COUNTRY) = upper(sourc_coun.RF_COUNTRY_DESC)
LEFT JOIN	RF_USER u_nego ON u_nego.RF_USER_CN = a.S4_NEGOTIATOR
LEFT JOIN 	SAM_GEN_STATIC_LIST List ON List.FieldId = 'ActivityType'  and List.FieldValue = a.S4_ACTIVITY_TYPE
-- Modified on 12-Oct-2008 - BEGIN
LEFT JOIN	S4_QUOTATION_SNAPSHOT quo	ON quo.S4_PARENT_ID = a.S4_ACTIVITY_ID AND quo.S4_PARENT_TYPE_ACTIVITY = 'A' AND quo.S4_QUOTATION_ROUND=1 AND quo.S4_QUOTE_COMPLETE = 'Y' and quo.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
-- Modified on 12-Oct-2008 - END
where a.S4_LATEST_ACTIVITY_FLAG='Y'
AND a.S4_PHASE_FLAG = 2
AND a.S4_ACTIVITY_STATUS not in ('D','H')

i l ya 35000 lignes dans la table S4_ACTIVITY_SNAPSHOT.
Ou dois je mettre les index???

10/02/2009, 18h21
elsuket

Donnez-nous la structure de vos tables ainsi que le plan de requête SVP.
Je vais essayer de vous dire où les mettre et pourquoi ...

@++ ;)

Bonsoir,

Je viens de prendre le temps de votre requête (désolé mais je n'avais pas trop le temps tout à l'heure, elle est tout de même longue :D).

Je me suis permis de la retoucher un peu :

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
134
135
SELECT 1 AS S4_REPORT_ACTION_PLAN_ID
	'A' + CONVERT(CHAR, a.S4_ACTIVITY_NUMBER) AS S4_REPORT_ACTION_PLAN_NUMBER,
	CONVERT(CHAR(8),a.S4_REQUEST_DATE,112) AS S4_REPORT_ACTION_PLAN_DEAD_LINE,
	sourc_coun.RF_COUNTRY_CODE AS S4_REPORT_ACTION_PLAN_SOURCING_COUNTRY,
	sourc_coun.RF_COUNTRY_DESC AS S4_REPORT_ACTION_PLAN_SOURCING_COUNTRY_DESC,
	dest_coun.SAM_RF_COUNTRY_CODE AS S4_REPORT_ACTION_PLAN_DEST_COUNTRY,
	dest_coun.SAM_RF_COUNTRY_DESC AS S4_REPORT_ACTION_PLAN_DEST_COUNTRY_DESC,
	a.S4_SOURCING_REGIONAL_UNIT AS S4_REPORT_ACTION_PLAN_SOURCING_REGIONAL_UNIT,
	a.S4_SOURCING_REGION AS S4_REPORT_ACTION_PLAN_SOURCING_REGION,
	dest_ru.SAM_RF_REGIONAL_UNIT_REGION AS S4_REPORT_ACTION_PLAN_DEST_REGION_DESC,
	dest_ru.SAM_RF_REGIONAL_UNIT_REGION AS S4_REPORT_ACTION_PLAN_DEST_REGION,
	a.S4_DESTINATION_REGIONAL_UNIT AS S4_REPORT_ACTION_PLAN_DEST_REGIONAL_UNIT,
	dest_ru.SAM_RF_REGIONAL_UNIT_DESC AS S4_REPORT_ACTION_PLAN_DEST_REGIONAL_UNIT_DESC,
	p.S4_PROJECT_NAME AS S4_REPORT_ACTION_PLAN_PROJECT_NAME,
	ISNULL(p.S4_PROJECT_NAME,'') + ' - ' + CONVERT(VARCHAR(20), ISNULL(p.S4_PROJECT_NUMBER,0)) AS S4_REPORT_ACTION_PLAN_PROJECT_NAME_NUMBER,
	p.S4_PROJECT_NUMBER AS S4_REPORT_ACTION_PLAN_PROJECT_NUMBER,
	'' AS S4_REPORT_ACTION_PLAN_STRATEGICAL_PROJECT,
	p.S4_CUSTOMER AS S4_REPORT_ACTION_PLAN_CUSTOMER,
	a.S4_BUSINESS_DIVISION AS S4_REPORT_ACTION_PLAN_BD,
	a.S4_BUSINESS_GROUP AS S4_REPORT_ACTION_PLAN_BG,
	ISNULL(a.S4_SERVICE_BUSINESS_GROUP,'') + ISNULL(a.S4_SERVICE_BUSINESS_DIVISION,'') AS S4_REPORT_ACTION_PLAN_SERVICE_BD_BG,
	a.S4_ACTIVITY_DESCRIPTION AS S4_REPORT_ACTION_PLAN_ACTION_DESC,
	sfam.RF_ORG4_SUBFAM_DESC AS S4_REPORT_ACTION_PLAN_SUB_FAMILY,
	a.S4_FAMILY AS S4_REPORT_ACTION_PLAN_FAMILY,
	a.S4_COUNCIL AS S4_REPORT_ACTION_PLAN_COUNCIL,
	a.S4_NEGOTIATION_TYPE AS S4_REPORT_ACTION_PLAN_NEGO_TYPE,
	CONVERT(CHAR(8), a.S4_AXIS_PRICE_VALID_FROM_DATE,112) AS S4_REPORT_ACTION_PLAN_PRICE_VALID_FROM,
	ROUND(ISNULL(a.S4_ACTIVITY_PROBABILITY_PERCENT,0) * ISNULL(a.S4_PROJECT_PROBABILITY_PERCENT,0) * 100,2) AS S4_REPORT_ACTION_PLAN_TOTAL_PROBABILITY
	ROUND(ISNULL(a.S4_NEGO_ESTIMATED_SUCCESS_RATE,0) * 100, 2) AS S4_REPORT_ACTION_PLAN_NEGO_RATE,
	ROUND(ISNULL(S4_ACTUAL_SAVING_PERCENT,0) * 100,2) AS S4_REPORT_ACTION_PLAN_GPS_SAVING,
	CASE a.S4_BUDGET_PROVISION_FLAG
		WHEN 'N' THEN 'NO'
		WHEN 'Y' THEN 'YES'
		ELSE ''
	END AS S4_REPORT_ACTION_PLAN_BUDGET_PROVISION_FLAG,
	CASE a.S4_RENEGOTIATION_FLAG
		WHEN 'N' THEN 'NO'
		WHEN 'Y' THEN 'YES'
		ELSE ''
	END AS S4_REPORT_ACTION_PLAN_RENEGOTIATION_FLAG
	p.S4_PROJECT_ACTIVITY_GROSS_SPEND_EUR AS S4_REPORT_ACTION_PLAN_GROSS_SPEND_EUR
	CASE a.S4_AXIS_CONTRACT_NUMBER
		WHEN  = '0' THEN ''
		ELSE CONVERT(VARCHAR(20), a.S4_AXIS_CONTRACT_NUMBER)
	END AS S4_REPORT_ACTION_PLAN_AXIS_CONTRACT_NUMBER,
	List.FieldLabel AS S4_REPORT_ACTION_PLAN_CRP_CATEGORY
	CASE a.S4_SPOT_TEAM_FLAG
		WHEN 'N' THEN 'NO'
		WHEN 'Y' THEN 'YES'
		ELSE '' END
	AS S4_REPORT_ACTION_PLAN_SPOT_TEAM_FLAG,
	CASE a.S4_SYNERGY_FLAG
		WHEN 'N' THEN 'NO'
		WHEN 'Y' THEN 'YES'
		ELSE ''
	END AS S4_REPORT_ACTION_PLAN_SYNERGY_FLAG,
	CASE a.S4_RECURRENT_FLAG
		WHEN 'N' THEN 'NO'
		WHEN 'Y' THEN 'YES'
		ELSE ''
	END AS S4_REPORT_ACTION_PLAN_RECURRENT_FLAG,
	CASE a.S4_LOCAL_ACTIVITY_FLAG
		WHEN 'N' THEN 'NO'
		WHEN 'Y' THEN 'YES'
		ELSE ''
	END AS S4_REPORT_ACTION_PLAN_LOCAL_ACTIVITY_FLAG,
	'Project Sourcing' AS S4_REPORT_ACTION_PLAN_DOMAIN,
	a.S4_RPIS_OFFICIAL_EUR AS S4_REPORT_ACTION_PLAN_RPIS_OFFICIAL_EUR,
	a.S4_SERVICE_BUSINESS_GROUP AS S4_REPORT_ACTION_PLAN_SERVICE_BG,
	a.S4_SERVICE_BUSINESS_DIVISION AS S4_REPORT_ACTION_PLAN_SERVICE_BD,
	a.S4_BUSINESS_UNIT AS S4_REPORT_ACTION_PLAN_BUSINESS_UNIT,
	'' AS S4_REPORT_ACTION_PLAN_BLANK_1,
	'' AS S4_REPORT_ACTION_PLAN_BLANK_2,
	'' AS S4_REPORT_ACTION_PLAN_BLANK_3,
	'' AS S4_REPORT_ACTION_PLAN_BLANK_4,
	'' AS S4_REPORT_ACTION_PLAN_BLANK_5,
	'' AS S4_REPORT_ACTION_PLAN_BLANK_6,
	'' AS S4_REPORT_ACTION_PLAN_CARY_SAVING_Y1,
	'' AS S4_REPORT_ACTION_PLAN_CARY_APPLICATION,
	'' AS S4_REPORT_ACTION_PLAN_SPEND_TBD,
	'' AS S4_REPORT_ACTION_PLAN_SAVING_TBD,
	'' AS S4_REPORT_ACTION_PLAN_SYNERGY_SPEND,
	'' AS S4_REPORT_ACTION_PLAN_SYNERGY_SAVING,
	ROUND(ISNULL(a.S4_ACTIVITY_PROBABILITY_PERCENT,0) * ISNULL(a.S4_PROJECT_PROBABILITY_PERCENT,0) * 100, 2) AS S4_REPORT_ACTION_PLAN_RISK,
	ROUND(CONVERT(NUMERIC(30,9),
	ISNULL(S4_ACTUAL_SPEND_TOTAL_EUR, 0) * ISNULL(ys.S4_SHARE_PERCENT, 0)), 2) AS S4_REPORT_ACTION_PLAN_ESTIMATED_TOTAL,
	u_nego.RF_LAST_NAME + ' ' + u_nego.RF_FIRST_NAME AS S4_REPORT_ACTION_PLAN_NEGOTIATOR,
	ys.S4_YEAR AS S4_REPORT_ACTION_PLAN_YEAR,
	CONVERT(VARCHAR(10), a.S4_SNAPSHOT_NUMBER) AS S4_REPORT_ACTION_PLAN_SNAPSHOT,
	p.S4_PROJECT_PRIME_PPM AS S4_REPORT_ACTION_PLAN_PRIME_PPM
FROM S4_ACTIVITY_SNAPSHOT a
INNER JOIN S4_PROJECT_SNAPSHOT p
	ON a.S4_PROJECT_ID = p.S4_PROJECT_ID
	AND p.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
LEFT JOIN S4_YEAR_SHARE_SNAPSHOT ys	
	ON a.S4_ACTIVITY_ID = ys.S4_PARENT_ID
	AND ys.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
LEFT JOIN RF_ORG4_SUBFAM sfam -- Added on 12-Oct-2008 
	ON sfam.RF_ORG4_SUBFAM_CODE = a.S4_SUB_FAMILY
	OR UPPER(sfam.RF_ORG4_SUBFAM_DESC) = UPPER(a.S4_SUB_FAMILY)
LEFT JOIN RF_ORG3_FAMILY fam 
	ON fam.RF_ORG3_FAMILY_CODE = a.S4_FAMILY
	OR UPPER(fam.RF_ORG3_FAMILY_DESC) = UPPER(a.S4_FAMILY)
LEFT JOIN RF_ORG2_COUNCIL cou 
	ON cou.RF_ORG2_COUNCIL_CODE = a.S4_COUNCIL
	OR UPPER(cou.RF_ORG2_COUNCIL_DESC) = UPPER(a.S4_COUNCIL)
LEFT JOIN SAM_RF_REGIONAL_UNIT dest_ru
	ON dest_ru.SAM_RF_REGIONAL_UNIT_CODE = a.S4_DESTINATION_REGIONAL_UNIT
LEFT JOIN SAM_RF_COUNTRY dest_coun
	ON dest_coun.SAM_RF_COUNTRY_CODE = a.S4_DESTINATION_COUNTRY 
	OR UPPER(a.S4_DESTINATION_COUNTRY) = UPPER(dest_coun.SAM_RF_COUNTRY_DESC)
LEFT JOIN RF_PURCH_SUB_AREA sourc_ru
	ON sourc_ru.RF_PURCH_SUB_AREA_CODE = a.S4_SOURCING_REGIONAL_UNIT
LEFT JOIN RF_COUNTRY sourc_coun
	ON sourc_coun.RF_COUNTRY_CODE = a.S4_SOURCING_COUNTRY
	OR UPPER(a.S4_SOURCING_COUNTRY) = UPPER(sourc_coun.RF_COUNTRY_DESC)
LEFT JOIN RF_USER u_nego
	ON u_nego.RF_USER_CN = a.S4_NEGOTIATOR
LEFT JOIN SAM_GEN_STATIC_LIST List
	AND List.FieldValue = a.S4_ACTIVITY_TYPE
-- Modified on 12-Oct-2008 - BEGIN
LEFT JOIN S4_QUOTATION_SNAPSHOT quo
	ON quo.S4_PARENT_ID = a.S4_ACTIVITY_ID	
	AND quo.S4_SNAPSHOT_NUMBER = a.S4_SNAPSHOT_NUMBER
-- Modified on 12-Oct-2008 - END
WHERE a.S4_LATEST_ACTIVITY_FLAG = 'Y'
AND a.S4_PHASE_FLAG = 2
AND a.S4_ACTIVITY_STATUS NOT IN ('D','H')
AND p.S4_LATEST_PROJECT_FLAG = 'Y'
AND ys.S4_PARENT_TYPE_ACTIVITY = 'A'
AND ys.S4_ACTIVE = 'A'
AND List.FieldId = 'ActivityType'
AND quo.S4_PARENT_TYPE_ACTIVITY = 'A'
AND quo.S4_QUOTATION_ROUND = 1
AND quo.S4_QUOTE_COMPLETE = 'Y'

Quelques petits défauts :
- Vous avez par endroits mélangé les "filtres", que l'on doit spécifier dans une clause WHERE / AND, et les jointures, comme par exemple dans la ligne suivante :

Code:

INNER JOIN S4_PROJECT_SNAPSHOT p ON a.S4_PROJECT_ID = p.S4_PROJECT_ID AND p.S4_LATEST_PROJECT_FLAG='Y'

La partie p.S4_LATEST_PROJECT_FLAG='Y' n'est pas une jointure, c'est pourquoi je l'ai déplacée dans la clause WHERE.
Cela ne change rien à votre traitement mais facilite le travail du compilateur, qui recherche dans une clause JOIN des jointures, et dans un WHERE des contraintes.

- Vous avez spécifié des jointures sur des chaînes de caractère, comme par exemple dans :

Code:

UPPER(sfam.RF_ORG4_SUBFAM_DESC) = UPPER(a.S4_SUB_FAMILY)

Peut-être que vous n'avez pas le choix, mais vous est-il possible d'utiliser plutôt pour vos jointures des colonnes de type entier ?
Sinon, est-ce que les colonnes sfam.RF_ORG4_SUBFAM_DESC et a.S4_SUB_FAMILY sont de type CHAR, c'est à dire de longueur fixe ?
Ou alors peuvent-ils l'être à un ou deux caractères près ?

Voici classés par ordre décroissant de performance les types des colonnes à utiliser pour une jointure :

- colonnes numériques
- colonnes de type caractère de longueur fixe
- colonnes de type caractère de longueur variable

De même l'utilisation de la fonction UPPER empêche toute optimisation.
Ne pouvez-vous pas stocker ces chaînes directement en majuscules (ou en minuscules?
Et si tel est le cas pour l'ensemble de vos données de type chaîne de caractères, utilisez alors une collation forte (sensible à la casse et aux accents, ou encore mieux : binaire)
De façon générale évitez d'utiliser des données de type caractère si ce n'est pas pour des libellés.

- Vous avez spécifié certaines jointures avec l'opérateur OR. Vous verrez ici que ce n'est pas non plus optimisable.
Peut-on les remplacer par un AND, ou une autre expression de la requête ?

- Vous utilisez 'Y', 'N' à la place d'une colonne de type BIT (0, 1, NULL).
Changez vers ce type si vous n'avez pas plus que ces 3 états à gérer.
Sinon faites une table de libellés de tous les statuts avec une clé primaire auto-incrémentée, puis réalisez la mise à jour.

- Vous pouvez spécifier vos CASE, si ceux-ci ne portent que sur une seule colonne, de la façon suivante :

Code:

1
2
3
4
5
CASE maColonne
	WHEN uneValeur THEN uneAutreValeur
	WHEN uneNouvelleValeur THEN uneAutreNouvelleValeur
	ELSE
END

Plutôt que :

Code:

1
2
3
4
5
6
 
CASE
	WHEN maColonne = uneValeur THEN uneAutreValeur
	WHEN maColonne = uneNouvelleValeur THEN uneAutreNouvelleValeur
	ELSE
END

Fonctionnellement cela remplit le même office, au niveau performance la première version est un peu plus optimisée, et aussi c'est un peu plus lisible ;)

Un petit test :

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
CREATE TABLE maTable
(
	val INT
)
GO
 
WITH
	CTE AS
	(
			SELECT 1 AS Val
		UNION ALL
			SELECT Val + 1
			FROM CTE
			WHERE Val <= 10000
	)	
INSERT INTO maTable
SELECT Val
FROM CTE
OPTION (MAXRECURSION 11000)
GO
 
SET STATISTICS TIME ON
GO
 
SELECT CASE val
			WHEN 1 THEN 'Un'
			WHEN 2 THEN 'Deux'
			ELSE 'PLUS'
		END
FROM dbo.maTable
 
PRINT '========================='
 
SELECT CASE 
			WHEN val = 1 THEN 'Un'
			WHEN val = 2 THEN 'Deux'
			ELSE 'PLUS'
		END
FROM dbo.maTable

Citation:

Temps d'analyse et de compilation de SQL Server :
, Temps UC = 0*ms, temps écoulé = 1*ms.

SQL Server \endash Temps d'exécution*:
, Temps UC = 0*ms, temps écoulé = 152*ms.
=========================

SQL Server \endash Temps d'exécution*:
, Temps UC = 0*ms, temps écoulé = 1*ms.

SQL Server \endash Temps d'exécution*:
, Temps UC = 0*ms, temps écoulé = 174*ms.

Un peu plus de 20ms gagnées sur 10000 lignes d'une table à une seule colonne, c'est à dire un tout petit jeu de données sur un cas assez improbable, ce n'est pas négligeable ;)

Dans l'attente de la structure de vos tables et de votre plan de requête, @++ ;)

11/02/2009, 10h20
Naruto_kun

Je vous remercie beaucoup pour votre aide.
Le problème est que je ne peux toucher à la structure de la base. Je dois analyser et faire de l'optimisation.
Pourriez vous m'expliquez comment faire pour sauvegarder un plan d'execution avec SQL Server 2005 ? On me le propose mais il n'est pas accessible (option grisée)

Merci
11/02/2009, 14h32
Naruto_kun

Problème résolu en enlevant la partie OR dans les jointures.
Merci à tous
11/02/2009, 15h16
elsuket

Bonjour,

Si vous avez besoin de la partie OR de vos jointures, reprenez la requête que vous venez d'écrire et ajouter l'opérateur UNION avec la même requête mais avec vos jointures OR ;)

@++ ;)