[Transact-SQL] Optimisation d'une procédure stockée

Bonjour, dans le cadre d'une création d'une application web pour la génération d'un planing, j'ai créé une procédure stockée qui, à partir de ce que je lui donne en paramètre, me renvoit tous les RDV de la journée demandée :

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 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go   -- ============================================= -- Author: Shinn -- Create date: 13/06/2007 -- Description: Procédure stockée qui récupère le -- planning du jour -- ============================================= ALTER PROCEDURE [dbo].[getPlanning] -- Paramètres @date datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;   -- Insert statements for procedure here SELECT distinct   max (case (s.id) when 1 then s.secteur end) [Z1],   max (case (s.id) when 1 then ( case (p.heure) when 10 then p.lieu end ) end) [Z1_10_L], max (case (s.id) when 1 then ( case (p.heure) when 10 then p.client end ) end) [Z1_10_C],   max (case (s.id) when 1 then ( case (p.heure) when 11 then p.lieu end ) end) [Z1_11_L], max (case (s.id) when 1 then ( case (p.heure) when 11 then p.client end ) end) [Z1_11_C],   max (case (s.id) when 1 then ( case (p.heure) when 12 then p.lieu end ) end) [Z1_12_L], max (case (s.id) when 1 then ( case (p.heure) when 12 then p.client end ) end) [Z1_12_C],   max (case (s.id) when 1 then ( case (p.heure) when 13 then p.lieu end ) end) [Z1_13_L], max (case (s.id) when 1 then ( case (p.heure) when 13 then p.client end ) end) [Z1_13_C],   max (case (s.id) when 1 then ( case (p.heure) when 14 then p.lieu end ) end) [Z1_14_L], max (case (s.id) when 1 then ( case (p.heure) when 14 then p.client end ) end) [Z1_14_C],   max (case (s.id) when 1 then ( case (p.heure) when 15 then p.lieu end ) end) [Z1_15_L], max (case (s.id) when 1 then ( case (p.heure) when 15 then p.client end ) end) [Z1_15_C],   max (case (s.id) when 1 then ( case (p.heure) when 16 then p.lieu end ) end) [Z1_16_L], max (case (s.id) when 1 then ( case (p.heure) when 16 then p.client end ) end) [Z1_16_C],   max (case (s.id) when 1 then ( case (p.heure) when 17 then p.lieu end ) end) [Z1_17_L], max (case (s.id) when 1 then ( case (p.heure) when 17 then p.client end ) end) [Z1_17_C],   max (case (s.id) when 1 then ( case (p.heure) when 18 then p.lieu end ) end) [Z1_18_L], max (case (s.id) when 1 then ( case (p.heure) when 18 then p.client end ) end) [Z1_18_C],         max (case (s.id) when 2 then s.secteur end) [Z2],   max (case (s.id) when 2 then ( case (p.heure) when 10 then p.lieu end ) end) [Z2_10_L], max (case (s.id) when 2 then ( case (p.heure) when 10 then p.client end ) end) [Z2_10_C],   max (case (s.id) when 2 then ( case (p.heure) when 11 then p.lieu end ) end) [Z2_11_L], max (case (s.id) when 2 then ( case (p.heure) when 11 then p.client end ) end) [Z2_11_C],   max (case (s.id) when 2 then ( case (p.heure) when 12 then p.lieu end ) end) [Z2_12_L], max (case (s.id) when 2 then ( case (p.heure) when 12 then p.client end ) end) [Z2_12_C],   max (case (s.id) when 2 then ( case (p.heure) when 13 then p.lieu end ) end) [Z2_13_L], max (case (s.id) when 2 then ( case (p.heure) when 13 then p.client end ) end) [Z2_13_C],   max (case (s.id) when 2 then ( case (p.heure) when 14 then p.lieu end ) end) [Z2_14_L], max (case (s.id) when 2 then ( case (p.heure) when 14 then p.client end ) end) [Z2_14_C],   max (case (s.id) when 2 then ( case (p.heure) when 15 then p.lieu end ) end) [Z2_15_L], max (case (s.id) when 2 then ( case (p.heure) when 15 then p.client end ) end) [Z2_15_C],   max (case (s.id) when 2 then ( case (p.heure) when 16 then p.lieu end ) end) [Z2_16_L], max (case (s.id) when 2 then ( case (p.heure) when 16 then p.client end ) end) [Z2_16_C],   max (case (s.id) when 2 then ( case (p.heure) when 17 then p.lieu end ) end) [Z2_17_L], max (case (s.id) when 2 then ( case (p.heure) when 17 then p.client end ) end) [Z2_17_C],   max (case (s.id) when 2 then ( case (p.heure) when 18 then p.lieu end ) end) [Z2_18_L], max (case (s.id) when 2 then ( case (p.heure) when 18 then p.client end ) end) [Z2_18_C],     max (case (s.id) when 3 then s.secteur end) [Z3],   max (case (s.id) when 3 then ( case (p.heure) when 10 then p.lieu end ) end) [Z3_10_L], max (case (s.id) when 3 then ( case (p.heure) when 10 then p.client end ) end) [Z3_10_C],   max (case (s.id) when 3 then ( case (p.heure) when 11 then p.lieu end ) end) [Z3_11_L], max (case (s.id) when 3 then ( case (p.heure) when 11 then p.client end ) end) [Z3_11_C],   max (case (s.id) when 3 then ( case (p.heure) when 12 then p.lieu end ) end) [Z3_12_L], max (case (s.id) when 3 then ( case (p.heure) when 12 then p.client end ) end) [Z3_12_C],   max (case (s.id) when 3 then ( case (p.heure) when 13 then p.lieu end ) end) [Z3_13_L], max (case (s.id) when 3 then ( case (p.heure) when 13 then p.client end ) end) [Z3_13_C],   max (case (s.id) when 3 then ( case (p.heure) when 14 then p.lieu end ) end) [Z3_14_L], max (case (s.id) when 3 then ( case (p.heure) when 14 then p.client end ) end) [Z3_14_C],   max (case (s.id) when 3 then ( case (p.heure) when 15 then p.lieu end ) end) [Z3_15_L], max (case (s.id) when 3 then ( case (p.heure) when 15 then p.client end ) end) [Z3_15_C],   max (case (s.id) when 3 then ( case (p.heure) when 16 then p.lieu end ) end) [Z3_16_L], max (case (s.id) when 3 then ( case (p.heure) when 16 then p.client end ) end) [Z3_16_C],   max (case (s.id) when 3 then ( case (p.heure) when 17 then p.lieu end ) end) [Z3_17_L], max (case (s.id) when 3 then ( case (p.heure) when 17 then p.client end ) end) [Z3_17_C],   max (case (s.id) when 3 then ( case (p.heure) when 18 then p.lieu end ) end) [Z3_18_L], max (case (s.id) when 3 then ( case (p.heure) when 18 then p.client end ) end) [Z3_18_C],   max (case (s.id) when 4 then s.secteur end) [Z4],   max (case (s.id) when 4 then ( case (p.heure) when 10 then p.lieu end ) end) [Z4_10_L], max (case (s.id) when 4 then ( case (p.heure) when 10 then p.client end ) end) [Z4_10_C],   max (case (s.id) when 4 then ( case (p.heure) when 11 then p.lieu end ) end) [Z4_11_L], max (case (s.id) when 4 then ( case (p.heure) when 11 then p.client end ) end) [Z4_11_C],   max (case (s.id) when 4 then ( case (p.heure) when 12 then p.lieu end ) end) [Z4_12_L], max (case (s.id) when 4 then ( case (p.heure) when 12 then p.client end ) end) [Z4_12_C],   max (case (s.id) when 4 then ( case (p.heure) when 13 then p.lieu end ) end) [Z4_13_L], max (case (s.id) when 4 then ( case (p.heure) when 13 then p.client end ) end) [Z4_13_C],   max (case (s.id) when 4 then ( case (p.heure) when 14 then p.lieu end ) end) [Z4_14_L], max (case (s.id) when 4 then ( case (p.heure) when 14 then p.client end ) end) [Z4_14_C],   max (case (s.id) when 4 then ( case (p.heure) when 15 then p.lieu end ) end) [Z4_15_L], max (case (s.id) when 4 then ( case (p.heure) when 15 then p.client end ) end) [Z4_15_C],   max (case (s.id) when 4 then ( case (p.heure) when 16 then p.lieu end ) end) [Z4_16_L], max (case (s.id) when 4 then ( case (p.heure) when 16 then p.client end ) end) [Z4_16_C],   max (case (s.id) when 4 then ( case (p.heure) when 17 then p.lieu end ) end) [Z4_17_L], max (case (s.id) when 4 then ( case (p.heure) when 17 then p.client end ) end) [Z4_17_C],   max (case (s.id) when 4 then ( case (p.heure) when 18 then p.lieu end ) end) [Z4_18_L], max (case (s.id) when 4 then ( case (p.heure) when 18 then p.client end ) end) [Z4_18_C]     FROM dbo.Planning p, dbo.Secteur s, dbo.Utilisateur u WHERE s.id *= p.fk_secteur AND u.id *= p.fk_utilisateur AND p.date = @date END

---

Mon problème est que cette procédure est lente.

C'est surement du aux multiples case dans le SELECT.

Seulement, je dois récupérer l'intégralité des évènement de la journée pour que mon application puisse les traiter.

Y'a t il une façon plus propre de faire cette procédure ?
Comment devrais je l'optimiser ?


Je vous remercie beaucoup par avance, et attends vos réponses.


Shinn77

lorsque tu écris

Citation:

---

FROM dbo.Planning p, dbo.Secteur s, dbo.Utilisateur u
WHERE s.id *= p.fk_secteur
AND u.id *= p.fk_utilisateur
AND p.date = @date

---

tu remontes toutes les infos des 3 tables et ensuite tu réduis par le where
fais d'abord des join

Code:

---

1 2 3 4 5

from dbo.Planning p join dbo.Secteur s on s.id = p.fk_secteur dbo.Utilisateur u on u.id = p.fk_utilisateur WHERE p.date = @date

---

comme ca il remontera moins d'infos avant de chercher sur la date

SQL pro a fait un bon article:
http://sqlpro.developpez.com/cours/sqlaz/jointures/

as tu des index sur fk_secteur et fk_utilisateur ?

sorry :oops:
j'ai oublié un join

Code:

---

1 2 3 4

FROM dbo.Planning p JOIN dbo.Secteur s ON s.id = p.fk_secteur JOIN dbo.Utilisateur u ON u.id = p.fk_utilisateur WHERE p.date = @date

---

Okay

Merci j suis en train de tenter de faire ça.

(Petit problème de LEFT/RIGHT/INNER Join, il me ressort pas les même résultats, mais j'y travaille)

Par contre y'a t il encore de l'optimisation à faire ? Par exemple par rapport à tous ses cases ?

Merci

Shinn77

a mon avis c'est des INNER qu'il te faut !

le secteur de Planning doit exister dans Secteur
et
l'utilisateur de Planning doit exister dans Utilisateur

Pour l'optimisation des case, je ne vois pas grand chose (sauf si tu es sous 2005) ton pb est que tu mets en "colonnes" des données en "lignes"

Okay merci de tes conseils.

Là je suis en train de créer dynamiquement ma requête.
Les JOIN passent sans problème.

Par contre, je ne sais pas vraiment ou mettre mes index...

Tu me suggères quoi ??

Merci Serge

tu peux mettre un index sur planning

Code:

---

1 2	create index ton_idx on planning (fk_secteur,fk_utilisateur,date)

---

pour secteur et utilisateur, il le faut sur ID (mais il doit y être en PK: primary key)

Okay merci beaucoup Serge.

Ma procédure stockée est plus ou moins optimisée, du moins beaucoup plus qu'au départ grâce à la création de l'index et la jointure dans le FROM.

Voici ma procédure modifiée :

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

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go   -- ============================================= -- Author: Shinn -- Create date: 13/06/2007 -- Description: Procédure stockée qui récupère le -- planning du jour -- ============================================= ALTER PROCEDURE [dbo].[getPlanning] -- Add the parameters for the stored procedure here @date datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;   -- Zone Minimum DECLARE @ZoneMIN int;   -- Zone Maximum DECLARE @ZoneMAX int;   -- Heure DECLARE @Heure int;   -- Correspond à notre partie dynamique de la requete DECLARE @forSQL varchar(8000);   -- Correspond à la requete dynamique qui utilisera @forSQL DECLARE @query varchar(8000);   -- Initialisation des variables SET @ZoneMIN = 1; SET @Heure = 10;   -- On initialise @forSQL qui est notre variable qui contiendra notre partie de requête dynamique SET @forSQL = '';   -- On récupère le nombre de Zone et on le met dans la variable @ZoneMAX SELECT @ZoneMAX = count(id) FROM secteur;   -- On construit notre partie de requête dynamiquement SELECT @forSQL = @forSQL + '''' + s.secteur + ''' [Z' + cast(s.id as char(1)) + '], ' FROM secteur s GROUP BY s.id, s.secteur;   WHILE (@ZoneMIN <= @ZoneMAX) BEGIN WHILE (@Heure <= 18) BEGIN SET @forSQL = @forSQL + 'max (case (s.id) when ' + cast(@ZoneMIN as nvarchar(2)) + ' then ('+ 'case (p.heure) when ' + cast(@Heure as nvarchar(2)) + ' then p.lieu + ''/'' + p.client end )'+ 'end) [Z' + cast(@ZoneMIN as nvarchar(2)) + '_' + cast(@Heure as nvarchar(2)) + '],'; SET @Heure = @Heure + 1; END SET @Heure = 10; SET @ZoneMIN = @ZoneMIN + 1; END   -- Supprime la virgule finale SET @forSQL = SUBSTRING(@forSQL, 1, LEN(@forSQL) - 1)   -- On remplit notre variable @query qui contiendra la requête à exécuter SET @query = 'SELECT ' + @forSQL + ' FROM Planning p LEFT JOIN Secteur s ON s.id = p.fk_secteur WHERE p.date = ''' + cast(@date as nvarchar(20)) + '''';   -- On exécute la requête EXEC (@query); END

---

Si tu vois autre chose à modifier ou à optimiser n'hésites pas à m'en faire part Serge.


Merci beaucoup en tout cas ^^

Shinn77