Discussion :

[clemsy]

Bonjour

J'ai crée un code VBA qui permet de générer environ 40 000 enregistrements dans une table.
Il marche sans soucis. Mon problème est qu'il est excessivement lent (environ 10 minutes ).
Son fonctionnement est simple :

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
 
For i=1 to ...
 ...
 For j=1 to ...
  ...
  For k=1 to ...
   ...
 
    record.AddNew
   ...
  Next
  ...
 Next
 ...
Next

Agrémenté de quelque if et résultats de requête.

Je voulais donc savoir si il était possible de l’exécuter en background car il fige l'application pendant son exécution, ou si il existe une méthode moins lente pour un nombre aussi gros d'enregistrements.

Bien cordialement

[EddiGordo]

Agrémenté de quelque if et résultats de requête.

Et c'est quoi justement les if et les requêtes ?
Je demande ça parce qu'il est peut-être possible de passer par des requêtes ajout pour faire ce que tu demandes...

Par contre, niveau performances, 40 000 enregistrements avec access ne t'attends pas à des miracles non plus hein

[marot_r]

Bonjour.

10 mn pour 40 000 ... personnellement je traite cela en environ 5 secondes sur mon poste qui n'est pas des plus récents.

Vérifie que tu n'ouvres pas et ne ferme pas le recordset à chaque ajout d'enregistrement.

Sinon tu peux mettre l'instruction DoEvents avant chaque Next, cela permettra à ta machine d'aller faire autre chose.

Attention toutefois au DoEvents car il peut ralentir ton traitement (dans mon cas je suis passé d'environ 3h à quelques secondes après sa suppression).

A+

[clemsy]

Voici le code entier

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
Private Sub appli_Click()
On Error GoTo Err_appli
Dim RS As Recordset, Rech As Recordset, record As Recordset
Dim SQL As String, SQL2 As String
Dim DateC As Date
Dim cycle As Integer
 
 
DateC = CDate(DateValue(Me!DateD) - (Weekday(DateValue(Me!DateD), vbMonday) - 1))
 
DateFin = CDate(Me!DateF)
If DateC < DateFin - nbrSemaine * 7 Then
cycle = ((DateFin - DateC) \ 7 + 1) \ nbrSemaine
 
Set record = CurrentDb.OpenRecordset("Vacation", dbOpenDynaset)
For n = 1 To cycle
    For i = 1 To DCount("No_Equipe", "Equipe")
 
        SQL = "Select * " & _
       "FROM [VacationHoraire] " & _
       "Where [No_Equipe]=" & i & ""
 
       Set RS = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
 
 
       SQL2 = "Select * " & _
       "FROM [Technicien] " & _
       "Where [No_Equipe]=" & i & "" & _
       " Order By No_Equipe ;"
 
        Set Rech = CurrentDb.OpenRecordset(SQL2, dbOpenSnapshot)
 
        If Rech.EOF Then
 
        End If
       Rech.MoveFirst
        If Not RS.EOF Then
 
       For j = 1 To DCount("Matricule", "Technicien", "No_Equipe = " & i)
 
        RS.MoveFirst
 
        For k = 1 To DCount("Date_vac", "VacationHoraire", "No_Equipe = " & i)
 
            With record
 
                .FindFirst "Date_vac = " & FDateUs(CDate(DateC + RS!Date_vac - 1 + (n - 1) * 7 * nbrSemaine)) & " And Matricule ='" & Rech!Matricule & "'"
 
            If Not (record.NoMatch) Then
 
                .Edit
                ![NOccupation] = RS!NOccupation
 
            Else
 
                .AddNew
                ![Date_vac] = CDate(DateC + RS!Date_vac - 1 + (n - 1) * 7 * nbrSemaine)
                ![Matricule] = Rech!Matricule
                ![NOccupation] = RS!NOccupation
                ![NoSemaine] = Format(DateC + RS!Date_vac - 1 + (n - 1) * 7 * nbrSemaine - 1, "ww") - 1
 
            End If
            .Update
            End With
 
 
 
            RS.MoveNext
            Next
        Rech.MoveNext
       Next
       End If
    Next
Next
RS.Close
Set RS = Nothing
Rech.Close
Set Rech = Nothing
record.Close
Set record = Nothing
DoCmd.Close
Else
MsgBox "Dates incorectes, au minimum un cycle doit être réalisé."
End If
Exit_appli:
    Exit Sub
 
Err_appli:
    MsgBox Err.description
    Resume Exit_appli
End Sub

Lorsque que je met une période d'un an cela fait environ 40 000 enregistrements

[User]

Bonjour,

Il y a énormément de choses a faire pour optimiser ton code :

La 1ère chose, c'est de remplacer les dcount par des recordset.

Au lieu de parcourir des indices de boucles, tu parcours des recordset.

Par exemple, un while sur un recordset pour les techiniciens lui-même dans une while pour les équipes.. :

Tu parcours les équipes dans un jeu d'enregistrement et pour chaque équipe, tu parcours dans une autre boucle les techniciens.

Quelque chose comme celà:

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
 
...
Set rsEquipes=db.OpenRecordSet("select * from Equipe order by No_Equipe;",dbopensnapshot)
Set Rech=db.OpenRecordSet("select * from Technicien order by No_Equipe,Matricule;",dbopensnapshot)
Set RS=db.OpenRecordSet("select * from VacationHoraire order by No_Equipe, Date_vac;",dbopensnapshot)
 
For n = 1 To cycle
 
   Do until rsEquipes.eof
 
    nEquipe=rsEquipes!No_Equipe     
    pointer=RS.Bookmark
 
       Do while nEquipe=Rech!No_Equipe  
 
         RS.Bookmark = pointer ' début
 
         Do while nEquipe=RS!No_Equipe
           ...
           RS.MoveNext
           if RS.eof then exit do
        Loop
 
        Rech.MoveNext
        if Rech.eof then exit do
 
       loop
 
    rsEquipes.MoveNext
 
   loop
 
Next n
 
...

Il faut reprendre ton code, petit à petit avec cette idée...

Cdlt,

[marot_r]

À l'œil j'essaierai de faire une jointure entre équipe et technicien et je travaillerai sur le résultat (équipe <-> Technicien), plutôt que de chercher l'équipe puis les techniciens de l'équipe.

Je ne suis pas certain mais on peut peut-être pousser la chose en faisant aussi une jointure avec VacationHorair.

Au lieu d'ouvrir plusieurs recordsets, tu travaillerais sur 1 seul recordset (plus gros) qui contiendrai tout.

Il me semble qu'il serait aussi possible de faire une requête de modification et une requête d'ajout qui ferai cela en une instruction SQL au lieu de parcours VBA qui sont généralement moins rapides.

A+

[User]

À l'œil j'essaierai de faire une jointure entre équipe et technicien et je travaillerai sur le résultat (équipe <-> Technicien), plutôt que de chercher l'équipe puis les techniciens de l'équipe.

Je ne suis pas certain mais on peut peut-être pousser la chose en faisant aussi une jointure avec VacationHorair.

Au lieu d'ouvrir plusieurs recordsets, tu travaillerais sur 1 seul recordset (plus gros) qui contiendrai tout.

Il me semble qu'il serait aussi possible de faire une requête de modification et une requête d'ajout qui ferai cela en une instruction SQL au lieu de parcours VBA qui sont généralement moins rapides.

A+

Tu as peut-être raison, car ce qui me fait un peu peur dans toutes ces boucles c'est le FindFirst à la fin dans la dernière boucle ...

[clemsy]

Merci à tous pour vos réponses.

J'ai appliqué les conseils de User, cela prend toujours du temps mais le délai a considérablement diminué.
Je vais maintenant essayer d'appliquer ceux de marot_r voir si j'arrive a obtenir un temps de délai plus court.

[clemsy]

Finalement, j'ai fait plus simple.
J'ai gardé la méthode de User et me suis séparé du FindFirst.
Je l'utilisait pour trouver les enregistrements qui existaient déjà afin de les remplacer et ainsi éviter les doublons.
Du coup à la place j'exécute une requête, au début du programme, qui supprime tous les enregistrements compris entre DateD et DateF.
Résultat l'exécution du programme se fait maintenant en 5 secondes, soit 60 fois plus vite.

Merci à tous pour vos conseils, mention spéciale à User qui m'aide déjà depuis 3 discussions.

Note à moi-même et à tout autre débutant qui tomberait sur cette discussion :
Éviter DCount, FindFirst et autres méthodes du même style dans les boucles.
Privilégier plutôt les requêtes.