Optimisation de la Performance

**clemsy** · 23/05/2016, 20h48

Bonjour

Je me suis mis à Access il y a peu pour créer une application pour mon entreprise.
Cette application contient un planning crée à partir de ce tuto http://denishulo.developpez.com/tuto...ss/planningv2/.
Tout allait bien jusqu'à ce que je sois confronté à la performance.
J'ai crée un programme VBA qui permet de remplir les taches des employés sur l'année, ce qui donne une fois exécuté environ 40 000 enregistrements (200 employés*200 jour travaillés).
Une fois ces enregistrements créent, mon beau planning qui était si réactif auparavant met 10 à 15 secondes a effectuer un changement d'affichage (changement d'équipe ou de date) ce qui en soi est logique vu qu'il cherche dans les 40 000 enregistrements ceux qu'il doit afficher.
Pour décrire très vite le code, j'instancie un objet querydef qui va renseigner l'équipe voulu à une requête, le programme parcourt ensuite les résultats de la requête qui correspondent à toute les taches d'une équipe sur l'année et affiche celles qui sont comprises dans la période affichée.
J'ai au préalable regarder ce tuto http://loufab.developpez.com/tutorie...sation/#LIII-A pour essayer de d'optimiser au mieux ma base, mais rien à faire je l'ai compacté, j'ai fait l'analyse des performances ... mon planning est toujours aussi lent.
Je l'ai aussi compilé en acde mais le code VBA ne s’exécute plus ensuite.
Je reste assez dubitatif car j'ai déjà vu des applications Access de la même taille tourner sans problème.
Je me tourne donc vers vous, espérant que votre expérience saura m'apporter lumière car je me doute que le problème vient aussi de mon faible niveau.
Merci d'avance

Bien cordialement.

**User** · 23/05/2016, 21h31

Bonsoir,

C'est vrai que ça me semble un peu bizarre.

Peux-tu poster le code VBA qui met à jour le planning, ainsi que le SQL ou une image de la requête qui alimente le recordset.

Faudrait aussi regarder si tu as bien des index sur les champs intervenant dans les relations.

Cdlt,

**clemsy** · 24/05/2016, 07h57

Bonjour
voici le code :

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
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
Public Sub MajPlanning()
 
On Error GoTo Err_MajPlanning
 
Dim i As Integer, j As Integer, k As Integer
Dim NEmp As Integer
Dim sql1 As String
Dim AColor As Long, nc As Integer
Dim Col1 As Integer, Col2 As Integer
Dim RecPL As DAO.Recordset
Dim Tache As String
Dim Qry As DAO.QueryDef
 
 
 
 
 
 
Set Qry = CurrentDb.QueryDefs("R_Vacation2")
 
Qry.Parameters("[Formulaires]![fPlanning]![Equipe]") = Forms!fPlanning!Equipe
 
Set RecPL = Qry.OpenRecordset
 
InitPlanning
 
 
RecPL.MoveFirst
i = 1
j = 0
k = 0
   Do While Not (RecPL.EOF)
 
   If RecPL!fonction = "CME" Then
 
      obPlanning.DrawFieldText i, 1, RecPL!Nom_tech, 10, 0, 1, 6316128
 
    ElseIf RecPL!fonction = "EIR" Then
 
      obPlanningEIR.DrawFieldText j, 1, RecPL!Nom_tech, 10, 0, 1, 6316128
 
     ElseIf RecPL!fonction = "Manager" Then
 
      obPlanningManager.DrawFieldText k, 1, RecPL!Nom_tech, 10, 0, 1, 6316128
 
    End If
 
   NEmp = RecPL!mat
 
      Do While (RecPL!mat = NEmp)
 
         If Not IsNull(RecPL!Date_vac) Then
         AColor = Nz(RecPL!Couleur, 16777215)
 
            If (RecPL!Date_vac < DateDebut + 35) And (RecPL!Date_vac > DateDebut - 1) Then
 
                  Tache = RecPL!code
 
            Col1 = ConversionJourVersColonne(RecPL!Date_vac)
 
 
               If RecPL!fonction = "CME" Then
               With obPlanning
 
               .DrawRect i, Col1, i, Col1, AColor, 6316128, 1
               .DrawText i, Col1, i, Col1, Tache, 9, 1, 1, vbBlack, False
 
               End With
 
               ElseIf RecPL!fonction = "EIR" Then
 
                With obPlanningEIR
 
               .DrawRect j, Col1, j, Col1, AColor, 6316128, 1
               .DrawText j, Col1, j, Col1, Tache, 9, 1, 1, vbBlack, False
 
               End With
                ElseIf RecPL!fonction = "Manager" Then
 
                With obPlanningManager
 
               .DrawRect k, Col1, k, Col1, AColor, 6316128, 1
               .DrawText k, Col1, k, Col1, Tache, 9, 1, 1, vbBlack, False
 
               End With
               End If
            End If
 
         End If
 
      RecPL.MoveNext
 
         If (RecPL.EOF) Then
 
         Exit Do
         End If
 
      Loop
  If Not RecPL.EOF Then
    If RecPL!fonction = "CME" Then
        i = i + 1
    ElseIf RecPL!fonction = "EIR" Then
        j = j + 1
    ElseIf RecPL!fonction = "Manager" Then
        k = k + 1
    End If
    End If
   Loop
 
obHeader.KeepImage
obHeader.Refresh
 
obPlanningManager.KeepImage
obPlanningManager.Refresh
 
obPlanning.KeepImage
obPlanning.Refresh
 
obPlanningEIR.KeepImage
obPlanningEIR.Refresh
 
obTotal.KeepImage
obTotal.Refresh
' Libération des variables.
 
RecPL.Close
Set RecPL = Nothing
Qry.Close
Set Qry = Nothing
Exit_MajPlanning:
    Exit Sub
 
Err_MajPlanning:
    Set obHeader = Nothing
    Set obPlanningManager = Nothing
    Set obPlanning = Nothing
    Set obPlanningEIR = Nothing
    Set obTotal = Nothing
    MsgBox Err.description
    Resume Exit_MajPlanning
 
End Sub

Les objets obplanning sont des objets clplanning issu du module de classe du tuto et les méthodes drawrect et drawtext dessine dans l'image planning des rectangles et du texte.

voici la requête :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
SELECT Technicien.Fonction AS fonction, Technicien.Matricule AS mat, R_Vacation.*, Technicien.Nom_Technicien AS Nom_tech, Technicien.No_Equipe
FROM Technicien LEFT JOIN R_Vacation ON Technicien.Matricule = R_Vacation.Matricule
WHERE (((Technicien.No_Equipe)=[Formulaires]![fPlanning]![Equipe]))
ORDER BY Technicien.Fonction, Technicien.Nom_Technicien;

R_Vacation est une première requête dont voici le code :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
SELECT Vacation.Date_vac, Vacation.NOccupation, Occupation.Nom, Vacation.Matricule, Technicien.Nom_Technicien, Occupation.Couleur, Occupation.Code, Occupation.Travail, Vacation.NoSemaine, Technicien.Fonction, Equipe.Nom_Equipe
FROM ((Vacation INNER JOIN Technicien ON Vacation.Matricule = Technicien.Matricule) INNER JOIN Occupation ON Vacation.NOccupation = Occupation.NOccupation) INNER JOIN Equipe ON Technicien.No_Equipe = Equipe.No_Equipe
WHERE (((Technicien.Fonction)<>"Manager"));

**clemsy** · 24/05/2016, 08h40

Je n'avais effectivement pas indexé les champs, mais même après l'avoir fait même résultat ><.

**User** · 24/05/2016, 10h18

Bonjour,

Je penses qu'il faut déjà optimiser la requête R_Vacation :

Il faudrait déja simplifier la requête R_Vacation, en ne conservant que les champs utilisés dans Majplanning (Date_Vac, Code,Fonction,Couleur,Nom_tech) et éliminer si possible certaines jointures inutiles :

As-tu besoin de la table Technicien dans la requête R_Vacation étant donné que tu reprends cette table et ces champs dans la requête principale,

As-tu besoin par exemple de la table Equipe dans cette requête, si tu as déjà NoEquipe dans la requête n°1 ?

Ensuite ajouter un paramètre [Date_Debut] dans la requête (bien définr aussi le type du paramètre dans la requête :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
PARAMETERS [Date_Debut] DateTime;
...
WHERE ((Technicien.Fonction)<>"Manager") and (Date_vac < [Date_Debut] + 35) And (Date_vac > [Date_Debut] - 1);

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
PARAMETERS [Formulaires]![fPlanning]![Equipe] Integer, [Date_Debut] DateTime;
SELECT Technicien.Fonction AS fonction, Technicien.Matricule AS mat, R_Vacation.*, Technicien.Nom_Technicien AS Nom_tech, Technicien.No_Equipe
FROM Technicien LEFT JOIN R_Vacation ON Technicien.Matricule = R_Vacation.Matricule
WHERE (((Technicien.No_Equipe)=[Formulaires]![fPlanning]![Equipe]))
ORDER BY Technicien.Fonction, Technicien.Nom_Technicien;

Ensuite dans le code essayer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
Qry.Parameters("[Formulaires]![fPlanning]![Equipe]") = Forms!fPlanning!Equipe
Qry.Parameters("[Date_Debut]") = DateDebut

et éliminer le if sur les dates.

Voici pour les quelques pistes...

à+

**clemsy** · 24/05/2016, 14h49

Effectivement ca réduit considérablement la taille des résultats.
Cependant tu me confronte à un nouveau problème.
Sous quel format faut-il mettre une date en paramètre d'un objet querydef ?
J'ai essayé dd/mm/yyyy, mm/dd/yyyy, #dd/mm/yyyy#, #mm/dd/yyyy# et directement =DateDebut.
Rien à faire j'obtient toujours : "L'expression entrée en paramètre est à l'origine de l'erreur [Date_vac]"
ou "Erreur sur la convertion des données"

**User** · 24/05/2016, 15h56

Bonjour,

Normalement DateDebut doit être de type date, quand on utilise un paramètre on a pas besoin de formater les dates en us:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Dim DateDebut as date
...
Qry.Parameters("[Formulaires]![fPlanning]![Equipe]") = Forms!fPlanning!Equipe
Qry.Parameters("[Date_Debut]") = DateDebut
...

Cdlt,

**clemsy** · 25/05/2016, 08h46

Bonjour

C'est pourtant ce que j'ai fait au début.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
Public DateDebut As Date
 
Private Sub Form_Load()
...
DateDebut = Format(Now() - (Weekday(DateValue(Now()), vbMonday) - 1), "dd mmmm yyyy")
...
End Sub
 
Public Sub MAJPlanning()
...
Qry.Parameters("[Date_Debut]") = DateDebut
...
End Sub

Je la met en public car je l'utilise dans d'autres méthodes.

Du coup j'ai aussi essayé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
Public DateDebut As Date
...
Public Sub MAJPlanning()
Dim DateD as Date
DateD =DateDebut
...
Qry.Parameters("[Date_Debut]") = DateDebut
...
End Sub

mais je reçois toujours la même erreur, par contre la requête marche lorsque je rentre les paramètres manuellement.

Voila la requête que j'ai repris en fonction de mon besoin :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
PARAMETERS [Formulaires]![fPlanning]![Equipe] Long, [Date_Debut] DateTime;
SELECT Technicien.Fonction AS fonction, Technicien.Matricule AS mat, Technicien.Nom_Technicien AS Nom_tech, Technicien.No_Equipe, R_Vacation.Date_vac, R_Vacation.Couleur, R_Vacation.Code AS code
FROM Technicien LEFT JOIN R_Vacation ON Technicien.Matricule = R_Vacation.Matricule
WHERE (((Technicien.No_Equipe)=[Formulaires]![fPlanning]![Equipe]) AND ((R_Vacation.Date_vac)>[Date_Debut] And (R_Vacation.Date_vac)<([Date_Debut]+36)))
ORDER BY Technicien.Fonction, Technicien.Nom_Technicien;

Lorsque je fait MsgBox DateDebut, j'obtiens 23/05/2016
J' ai tester Qry.Parameters("[Date_Debut]") = Now() pour voir si cela venait de l'initialisation de DateDebut mais ca ne marche pas non plus
Cdlt

**User** · 25/05/2016, 10h58

bonjour,

vous n'avez pas besoin d'utiliser la fonction format dans lors du chargement du formulaire.

il se peut aussi qu''il y ai des valeurs nulles dans date_vac.

Cdlt,

**clemsy** · 25/05/2016, 11h16

Mea culpa
Le problème venait du fait que j'utilisais la requête dans une autre méthode ou je n'instanciait pas le paramètre date_vac.
Maintenant la requête marche parfaitement et mon planning à retrouver toute sa fluidité.
Merci beaucoup User pour votre aide et votre patience.

Bien cordialement

**User** · 25/05/2016, 11h38

De rien,

Peux-tu clore la discussion.

Cdlt,

Optimisation de la Performance [AC-2010]

VBA Access

Discussions similaires

Partager

Partager