[ Optimisation ] Requête, parametrages BDD

**onipif** · 16/10/2006, 09h52

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 :

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 ?
Nom : pb_tps_execution.jpg
Affichages : 69
Taille : 227,4 Ko

Nom : pb_tps_execution.jpg
Affichages : 69
Taille : 227,4 Ko

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

**ZERS** · 16/10/2006, 10h10

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+

**onipif** · 16/10/2006, 10h37

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.

**ZERS** · 16/10/2006, 11h05

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.

**onipif** · 16/10/2006, 12h07

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

**ZERS** · 16/10/2006, 12h24

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 :

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

[ Optimisation ] Requête, parametrages BDD

MS SQL Server

Vue hybride

Discussions similaires

Partager

Partager