[ Optimisation ] Requête, parametrages BDD

Version imprimable

1 pièce(s) jointe(s)

Bonjour,

Suite à un changement de serveur de base de données (auparavant sous VAX, et migration vers SQLServer 8), une application met des temps de réponses hallucinant pour afficher des graphiques.

L'application et la migration n'ayant pas été effectué par moi même...je peux seulement dire que sous système VAX, ça tournait pas mal, genre quelque secondes pour afficher les résultats, alors que maintenant ça demande pas loin de 5 minutes !
En fait, il faut effectuer 21 calculs en fonction de valeurs d'un tableau.

Ce que j'ai effectué pour ciblé le problème :
- Mise en place d'un affichage du temps d'execution pour chaque requête ( de 5 à 15s * 21 calcul )
- Nombre de block lu très élevé.

D'après vous, que puis-je faire pour améliorer la situation :
- Faire des procédures stockées avec paramètre ?
- Améliorer la base de données au point de vue administration ? (en se penchant sur quoi ?)
- ? ? ?

Voici le code concerné :
Ce qu'il faut en retenir dans cette tartine :
la requête du genre :

Citation:

1/
Select sum(C100),count(*) as nb,5 as GRP FROM DONNEES where voie='A1' and jour>20060926134946 and jour <=20060926135146 union Select sum(C100),count(*) as nb,4 as GRP FROM DONNEES where voie='A1' and jour>20060926134746 and jour <=20060926134946 union Select sum(C100),count(*) as nb,3 as GRP FROM DONNEES where voie='A1' and jour>20060926134546 and jour <=20060926134746 union Select sum(C100),count(*) as nb,2 as GRP FROM DONNEES where voie='A1' and jour>20060926134346 and jour <=20060926134546 union Select sum(C100),count(*) as nb,1 as GRP FROM DONNEES where voie='A1' and jour>20060926134146 and jour <=20060926134346
2 /
et les temps d'execution sur les 21 boucles énoncées précédemment.
3 /
Je joins une images du générateur de profils qui montre les temps d'execution, etc. et il y a des Audit Logout qui prennent beaucoup de temps, si vous avez une idée là dessus ?
Pièce jointe 5713

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
 
For Num_Calcul = Debut_Calcul To Fin_Calcul
 
  MySql = ""
 
  If (Num_Calcul <> Affichage_Compteur) Then
    If (Tableau_Calculs(P_Voie, Num_Calcul).Nom_Calcul = "") Then GoTo Next_Calcul
  End If
 
 
    ' Déclaration pour test sur temps d'execution
    Dim Init As Long ' Avant l'execution
    Dim Final As Long ' Après l'execution
    Dim TpsExec As String ' temps en ms d'execution
 
    Init = GetTickCount
 
  For i = 0 To 10 Step 5
    For j = 0 To 4
      SDateDebut = CStr(Convert_DateHeure(DateAdd("n", -((j + i) + 1) * Duree_Barre, Date_Enreg_Dern_Selec), DateTimePC_Vers_DateTimeRDB))
      SDateFin = CStr(Convert_DateHeure(DateAdd("n", -(j + i) * Duree_Barre, Date_Enreg_Dern_Selec), DateTimePC_Vers_DateTimeRDB))
 
      If (Num_Calcul <> Affichage_Compteur) Then
 
        If (Tableau_Calculs(P_Voie, Num_Calcul).Denominateur <> "") Then
          MySql = MySql & "Select sum(" & Tableau_Calculs(P_Voie, Num_Calcul).Nominateur & _
                  "),sum(" & Tableau_Calculs(P_Voie, Num_Calcul).Denominateur & _
                  "),count(*) as nb," & MAX_BARRE - (j + i) & " as GRP "
 
          If (Num_Calcul = Pertes_Totales) Then
              MySql = MySql & ",sum(" & tableau_cpt_entree_ligne(P_Voie) & ") as c " ' as cpt_entree_ligne "
          End If
        Else
          MySql = MySql & "Select Sum(" & Tableau_Calculs(P_Voie, Num_Calcul).Nominateur & "), " & _
                  "Count(*) as nb," & MAX_BARRE - (j + i) & " as GRP "
        End If
 
        MySql = MySql & "FROM DONNEES where voie='" & SVoie & "' and jour>" & SDateDebut & " and jour <=" & SDateFin & " union "
 
      Else
        If (Num_Compteur = 0) Then Num_Compteur = 100
        MySql = MySql & "Select sum(C" & Format(Num_Compteur, "0") & "),count(*) as nb," & _
                MAX_BARRE - (j + i) & " as GRP FROM DONNEES where voie='" & SVoie & _
                "' and jour>" & SDateDebut & " and jour <=" & SDateFin & " union "
      End If
 
    Next j
 
    MySql = Mid(MySql, 1, Len(MySql) - 6)
 
    ' Modification type de curseur pour optimisation, par DFR le 19/09/06
    'REC.Open MySql, BASE_VISUALISATION, adOpenStatic, adLockReadOnly
    REC.Open MySql, BASE_VISUALISATION, adOpenForwardOnly, adLockReadOnly
 
    While Not REC.EOF
 
      If IsNull(REC(0)) Then Nominateur = 0 Else Nominateur = REC(0)
 
      If Num_Calcul = Affichage_Compteur Then
        Resultats_Voie(REC(2), Num_Calcul).Valeur = Nominateur
        Resultats_Voie(REC(2), Num_Calcul).Nb_Enreg = REC(1)
        Resultats_Voie(REC(2), Num_Calcul).Denominateur = 1
        Resultats_Voie(REC(2), Num_Calcul).Nominateur = Nominateur
      Else
 
        If (Tableau_Calculs(P_Voie, Num_Calcul).Denominateur <> "") Then
          If IsNull(REC(1)) Then Denominateur = 0 Else Denominateur = REC(1)
 
          Resultats_Voie(REC(3), Num_Calcul).Nominateur = Nominateur
          Resultats_Voie(REC(3), Num_Calcul).Denominateur = Denominateur
          Resultats_Voie(REC(3), Num_Calcul).Nb_Enreg = REC(2)
 
          If Denominateur <> 0 Then
            If (Num_Calcul = Pertes_Totales) Then
                Resultats_Voie(REC(3), Num_Calcul).Valeur = 100 * (1 - (Nominateur / Denominateur))
            Else
                Resultats_Voie(REC(3), Num_Calcul).Valeur = 100 * Nominateur / Denominateur
            End If
          Else
            If (Num_Calcul = Pertes_Totales) Then
                If IsNull(REC(1)) Then
                    Resultats_Voie(REC(3), Num_Calcul).Valeur = 0
                Else
                    Resultats_Voie(REC(3), Num_Calcul).Valeur = 100
                End If
 
                If REC(4) = 0 Then Resultats_Voie(REC(3), Num_Calcul).Valeur = 0
 
            Else
                Resultats_Voie(REC(3), Num_Calcul).Valeur = 0
            End If
 
          End If
 
        Else
          Resultats_Voie(REC(2), Num_Calcul).Nominateur = Nominateur
          Resultats_Voie(REC(2), Num_Calcul).Nb_Enreg = REC(1)
          Resultats_Voie(REC(2), Num_Calcul).Valeur = 0
        End If
 
      End If
 
      REC.MoveNext
    Wend
    REC.Close: MySql = ""
 
  Next i
 
' Fin de 0 à 21 on fait.
 
  ' Fin de test de temps d'execution
  Final = GetTickCount
  '' Affichage pour test / optimisation, par DFR
  'MsgBox (Str(Final - Init) & " ms")

Merci bien pour votre aide,
Damien.

16/10/2006, 10h10
ZERS

Bonjour,

quelques questions
1-Est-ce que le fait que les intervales de temps soient tous de 2mns est un pur hasard, ou bien est-ce que le calcul qui te permet d'avoir les 21 périodes te donne tojours un interval fixe (du genre toutes les X minutes entre date debut et datede fin)?

2- A quoi sert le GRP ?

A+
16/10/2006, 10h37
onipif

Merci de ton aide :)

Oui, l'intervalle de temps de 2mins corresonds en fait à l'intervalle d'echantillonnage (prise de valeurs toutes les 2 minutes). (tu le retrouves quand dans la requête que j'ai énoncé ? pas dans le code ? )

Et concernant, GRP, visiblement c'est pour "groupe", et sert d'identifiant.
16/10/2006, 11h05
ZERS

Citation:

Envoyé par onipif

Merci de ton aide :)

Oui, l'intervalle de temps de 2mins corresonds en fait à l'intervalle d'echantillonnage (prise de valeurs toutes les 2 minutes). (tu le retrouves quand dans la requête que j'ai énoncé ? pas dans le code ? )

Et concernant, GRP, visiblement c'est pour "groupe", et sert d'identifiant.

Je regardais la reqête SQL uniquement, j'ai pas trop compris le code en dessous car ca fait appel à des fonctions dont nous n'avons pas la définition. Mais je comprends donc que l'interval de temps est fixe.
16/10/2006, 12h07
onipif

Ouep je me doute bien que le code ne servent pas vraiment...mais bon, pour support.

Sinon, moi je chercherai plutôt à m'orienter point de vue optimisation dans la façon de faire, et niveau administration de la base aussi (mais là je ne m'y connais pas de trop).

re,

j'ai essayé de faire une simulation de mon côté en replacant les 'union' par des jointures avec une table qui contient tes intervales de temps, table qui a été crée automatiquement, à partir d'une date de début, date de fin et un intervalle fixe.

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
 
create table INTERVALES (
DateDebut datetime null,
DateFin datetime null,
GRP int null)
 
declare @i int
declare @t int
set @i = 1
set @t = 0
 
declare @date datetime
declare @datefin datetime
 
set @date = '2006-06-29 13:00:02.000'
set @datefin = '2006-06-29 14:00:02.000'
while (dateadd(mi, @t, @date) <=@datefin )
begin
	insert into INTERVALES
	SELECT dateadd(mi,  @t, @date), dateadd(mi,  @t + 2, @date), @i
	set @i = @i+1
	set @t = @t + 2 --2mns
end
 
--remplissage tables DONNEES
declare @date datetime
declare @datefin datetime
 
set @t = 0
 
set @date = '2006-06-29 13:00:02.000'
set @datefin = '2006-06-29 14:00:02.000'
while (dateadd(ss, @t, @date) <=@datefin )
begin
	insert into DONNEES
	SELECT 100 + @t,
		dateadd(ss,  @t, @date),
		'A1'
	set @t = @t + 30 --30s
end
 
 
 
--CODE DE regroupement
select sum(D1.C100), count(D1.jour), I.GRP
FROM DONNEES D1 INNER JOIN INTERVALES I
ON D1.JOUR <I.DateFin
AND D1.JOUR >=I.DateDebut
WHERE D1.VOIE = 'A1'
group by I.GRP
 
 
--resultat -- PS : le count donne toujours 4 car j'ai remplis en auto par tranche de 30s ;-)
 
580	4	1
1060	4	2
1540	4	3
2020	4	4
2500	4	5
2980	4	6
3460	4	7
3940	4	8
4420	4	9
4900	4	10
5380	4	11
5860	4	12
6340	4	13
6820	4	14
7300	4	15
7780	4	16
8260	4	17
8740	4	18
9220	4	19
9700	4	20
10180	4	21
10660	4	22
11140	4	23
11620	4	24
12100	4	25
12580	4	26
13060	4	27
13540	4	28
14020	4	29
14500	4	30
3700	1	31

Sur une table de 50 intervales, et une table DONNEES de 1200 lignes, entre ta requete et la mienne, y'a pas de différence notable. Essaie de voir chez toi si ca fait avancer le schmilblick ;)

Si ce n'est pas le cas, mes connaissances ne sont pas assez suffisantes pour t'aider un peu plus...:(

Cdlt

16/10/2006, 12h58
onipif

Merci bien, c'est super sympa de ta part de t'être penché sur mon problème.

Actuellement, je n'ai pas le temps pour faire ce test, mais je vais essayer d'en trouver ;-) Je ne pense pas moi aussi que ça change grand chose, mais c'est à tester !

Merci encore.
16/10/2006, 13h08
ZERS

Citation:

Envoyé par onipif

Merci bien, c'est super sympa de ta part de t'être penché sur mon problème.

De rien.

Citation:

Envoyé par onipif

Actuellement, je n'ai pas le temps pour faire ce test, mais je vais essayer d'en trouver ;-)

Essaie quand même de trouver le temps de le faire et assez rapidement. On n'est jamais à l'abri d'une réussite :lol:

L'optimisation cela commence par simplifier ses requêtes...

Votre requête avec 5 union est stupide. Une simple jointure avec une table de tranche de temps suffira :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 CREATE TABLE T_TRANCHES (NUM INT, MINUTE INT) INSERT INTO T_TRANCHES VALUES (1, 0) INSERT INTO T_TRANCHES VALUES (2, 2) INSERT INTO T_TRANCHES VALUES (3, 4) INSERT INTO T_TRANCHES VALUES (4, 6) INSERT INTO T_TRANCHES VALUES (5, 8) SELECT SUM(C100), COUNT(*) AS NB, T.NUM AS GRP FROM DONNEES D CROSS JOIN T_TRANCHES T WHERE VOIE = 'A1' AND JOUR > DATEADD(m, MINUTE, '20060926 13:41:46') AND JOUR <= DATEADD(m, MINUTE, '20060926 13:43:46')
Si j'ai bien compris vous analysez vos données toutes les 2 minutes..

A mon avis vos boubles imbriquées en VB peuvent être réduites de la même façon. Au finish une seule requête devrait être la solution de votre problème.

Autre chose, vous n'avez pas posté la définition de votre table. SI cell-ci contient de nombreuses colonnes pensez à faire un index couvrant très sélectif.
Par exemple (JOUR, VOIE, C100)

A +

27/10/2006, 15h44
onipif

Merci de ta réponse SQLPro.

En fait, je n'ai plus de problème maintenant, l'application se met bien à jour sans le moindre problème ! Sauf que je n'ai rien changé de mon côté lol . Aucun changement du côté serveur, aucun reboot du serveur entre temps (de toute façon, le problème était apparant depuis le début d'année...donc des reboot il y en a eu)...
Bref maintenant cette requête s'execute instantanément alors que je ne l'ai pas modifié...et le nombre de blocs lues pour retourner le résultat est passé à même pas 2000 ... alors qu'il était d'environ 500.000 avant !

Donc la je suis stupéfait !!! En ce moment, j'étais entrain de rédiger les différents points aussi bien developpement logiciel que configuration et optimisation du server SQL et de la base en cause (avec moyen d'auditer,etc.)... ben je le rédige...mais ça ne va pas servir de suite du coup :aie:

Sinon simplement pour info, en fait je ne peux pas casser la structure de la base de données...la base de données contient un gros nombres de valeurs mis à jour régulierement (2') et supprimer au bout de 10 jours ...et 107 compteurs..donc impossible de définir des index ou d'effectuer des jointures, etc. ( dumoins je pense que l'on va perdre en temps d'enregistrement et de lecture)

Je vous remercie bien en tout cas !
Et je vous tiendrai au courant si ça vous interesse,
Bon WE,
Damien.