Discussion :

[casavba]

Bonjour,

J'ai un souci de performance au niveau d'une base de données.
En effet, je parcours une table (700 000 lignes) par le biais d'un recordset et en fonction de certaines conditions je mets à jour un ou plusieurs champs.

Le problème et que cette opération prend énormèment de temps.

Avez-vous une solution pour optimiser l'execution?

Merci pour votre aide

Voici un exemple de code que j'utilise :

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
 
Dim oRst as DAO.Recordset
Dim oDb as DAO.Database
Set oDb=CurrentDb
Set oRst=oDb.OpenRecordset("TblCommande",dbOpenTable)
 
While Not oRst.EOF
 
'--------------------------
Ici je mets des Conditions en fonction desquels un ou plusieurs champs de la table seront mis à jour
'---------------------------
 
 
 
  'Passe en mode modification
  oRst.Edit
  'A  oRst.Fields("Champ9").Value=(Champ1 * champ2)^2/champ4
 
'Mettre à Jour
  oRst.Update
  'Passe au suivant
  oRst.MoveNext
Wend
 
'Libération des objets
oRst.Close
oDb.Close
Set oRst=Nothing
Set oDb=Nothing

[Invité]

Bonjour

Au lieu de mettre ta table dans l'OpenRecordset, utilise une requête SQL qui comporte déjà les critères des conditions (clause Where).

Ainsi ton recordset ne travaillera que sur ces lignes et non toute la table, cela devrait aller beaucoup plus vite.

Philippe

[casavba]

Bonjour,

Merci Philippe pour ton aide.

Malheureusement, le problème de lenteur persiste.

J'ai mis un exemple de code utilisé.

En effet,

TABLE_BRUTE : la table de départ qui contient toutes les informations nécessaires (700 000 lignes).

ChpCritere : les critères (noms de champs de la TABLE_BRUTE)

Le code s'execute normalement sans erreur mais la durée de traitement avoisine 3H ce qui constitue vraiment un handicap.

Ma question, est ce possible d'optimiser le code ci-dessous? si oui comment?

Si la démarche que j'ai suivie est fausse, merci de me faire part de vos suggestions.

Merci

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
Sub DB_TREAT(ByVal Path_BD As String, ByVal TABLE_BRUTE As String, _
                  ParamArray ChpCritere() As Variant)
 
 
 
Dim DB As DAO.Database
Dim Rst As DAO.Recordset
Dim RstTMP As DAO.Recordset
Dim intColIndex As Long
Dim strSql As String
Dim strSqlTMP As String
Dim strWhere As String
Dim strSelect As String
'Dim strUpdate As String
Dim v As Long
 
 
Set DB = DAO.OpenDatabase(Path_BD, False, False)
 
For v = LBound(ChpCritere) To UBound(ChpCritere)
    strSelect = strSelect & ChpCritere(v) & ", "
Next v
 
strSelect = Mid(strSelect, 1, Len(strSelect) - Len(", "))
 
strSql = "SELECT DISTINCT " & strSelect & " FROM " & TABLE_BRUTE
 
Set Rst = DB.OpenRecordset(strSql, DAO.dbOpenSnapshot)
 
 
 With Rst
    While Not .EOF
        strWhere = ""
        For i = 0 To .Fields.Count - 1
            strWhere = strWhere & .Fields(i).Name & "=" & "'" & Rst(i).Value & "'" & " AND "
        Next
 
        strSqlTMP = "SELECT * FROM " & TABLE_BRUTE & " WHERE " & Mid(strWhere, 1, Len(strWhere) - Len(" AND "))
        Set RstTMP = DB.OpenRecordset(strSqlTMP, DAO.dbOpenSnapshot)
 
            '
            ' nom de champs
            For intColIndex = 0 To RstTMP.Fields.Count - 1
 
                Worksheets("wSheet").Range("A2").Offset(-1, intColIndex).Value = RstTMP.Fields(intColIndex).Name
 
            Next intColIndex
 
                Worksheets("wSheet").Range("A2").CopyFromRecordset RstTMP
 
            RstTMP.Close
            Set RstTMP = Nothing
 
            '----  Traitement Xls -----
             ' Quelques traitments
            '----------------------------
 
        End If
 
         '--- Supprimer les enregist de la table -----
         DB.Execute "DELETE * FROM " & TABLE_BRUTE & " WHERE " & Mid(strWhere, 1, Len(strWhere) - Len(" AND "))
         '---------------------------------------
 
        .MoveNext
 
    Wend
 
'--- Nettoyage et libération mémoire
.Close
End With
Set Rst = Nothing
 
'---------------------
DB.Close
 
Set DB = Nothing
 
End Sub

[Domi2]

Bonjour,

Tes tables sont dans la même base de données que l'application ou sont-elles liées ?

Domi2

[casavba]

les tables sont dans la même base de données.

[Invité]

Bonjour

Dans ton premier message tu n'avais pas dit que tu écrivais dans Excel

Philippe

[casavba]

Oui, avec des besoins ultérieures. <effectuer des calculs dans Excel est devenu inévitable.

[Domi2]

Bonjour,

Déjà, je ne sais pas si cela à une incidence, mais j'essaierais de remplacer Set Rst = DB.OpenRecordset(strSql, DAO.dbOpenSnapshot) par Set Rst = DB.OpenRecordset(strSql).

Par défaut, le type sera dbOpenTable, je serais curieux de savoir si cela a une influence sur la vitesse d'exécution sur une table de cette importance.

Ensuite, pour voir, je retirerais toute la partie Excel et je ferais un test, de façon à déterminer si c'est cette partie qui est chronophage.

Domi2

[marsouin_89]

Bonsoir,

Autre idée : réalise toute ta procédure dans une table temporaire et exporte là ensuite dans un fichier Excel.

Cordialement

Christophe

[casavba]

Bonjour,

Déjà, je ne sais pas si cela à une incidence, mais j'essaierais de remplacer Set Rst = DB.OpenRecordset(strSql, DAO.dbOpenSnapshot) par Set Rst = DB.OpenRecordset(strSql).

Par défaut, le type sera dbOpenTable, je serais curieux de savoir si cela a une influence sur la vitesse d'exécution sur une table de cette importance.

Ensuite, pour voir, je retirerais toute la partie Excel et je ferais un test, de façon à déterminer si c'est cette partie qui est chronophage.

J'ai testé en enlevant le

Code :

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

.dbOpenSnapshot

---> celà n'a pas d'impact en termes de durée de traitement

Aussi, la partie

Code :

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

Excel

dure 15mn sur une durée total de 2h55mn
Donc je pense que l'essentiel de l'effort doit se faire au niveau du code que j'ai posté.
J'aimerais savoir s'il y a une autre démarche que celle que j'ai proposé?

Merci por votre aide

[zoopsys]

Bonjour,

Personnellement, pour proposer une autre solution, il faudrait comprendre le fonctionnel de ton code...

si ton premier select te remonte 700 000 lignes :

A la ligne 38 tu exécutes donc 700 000 select, où tu vas parcourir X champs
puis tu exécutes 700 000 requête de suppression (à par si ta clause where te remonte plus d'une ligne), çà peut effectivement prendre beaucoup de temps...

De plus ta base est en locale ou sur un réseau ?

[loufab]

Bonjour,
Techniquement je ne vois pas ce qui peut être optimisé, à part le Join à la place de la boucle sur ChpCritere.

Par contre d'un point de vue conceptuel, je n'arrive pas à comprendre la démarche.

J'ai fait un test avec un échantillon de données simple. Et ce que ça me remmène me laisse à penser que tu pourrais t'en sortir beaucoup mieux avec du SQL pur.
Voici mon échantillon vraiment bidon :

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
 
Id	Id_Client	NumeroFacture	Montant
1      6	       1	                102.00 €
5	6	       1	                107.00 €
6	6	       1	                102.00 €
7	7	       2                	105.00 €
8	7	       2	                108.00 €
9	7	       2	                104.00 €
10	8	       3	                107.00 €
11	8	       3			104.00 €
12	9	       4	                100.00 €
13	9	       4	                109.00 €
14	9	       4	                110.00 €
15	9	       5	                101.00 €
16	9	       5	                104.00 €

Voici ce que j'obtiens avec le code :

Code :

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

1
2
3
4
5
6
7
 
Id	Id_Client	NumeroFacture	Montant
1      6               1                      102 
7      7               2                      105 
10    8               3                      107 
12    9               4                      100 
15    9               5                      101

Et maintenant la même chose avec 1 seule requête :

Code :

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

1
2
3
4
5
6
7
 
Id	        Id_Client	 NumeroFacture	Montant
1	        6	         1	                102.00 €
7	        7	         2	                105.00 €
10	        8	         3	                107.00 €
12	        9	         4	                100.00 €
15	        9       	 5               	101.00 €

On peut donc en déduire, sauf erreur de ma part, que cette simple requête sera plus rapide.

Code :

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

1
2
3
SELECT Min(tFactures.Id) AS MinDeId, tFactures.Id_Client, tFactures.NumeroFacture, First(tFactures.Montant) AS PremierDeMontant
FROM tFactures
GROUP BY tFactures.Id_Client, tFactures.NumeroFacture;

Comme je le dis souvent, 99% d'un problème se solutionne via SQL. Les 1% restant sont soit le fait d'un problème très complexe, soit d'une mauvaise conception du MCD. Avec une seule table je ne pense pas que l'on soit dans les 1% restant.

Cordialement,

[casavba]

Bonjour,

Par contre d'un point de vue conceptuel, je n'arrive pas à comprendre la démarche.

Le concept est de rapatrier des données dans Excel en fonction de critères bien définies et ensuite de faire des calculs complexes (Simplexe ... (Fonction quadratique) )

Loufab, je te remercie pour la réponse Sql mais celle-ci ne répond pas au besoin.

Par conséquent, je vais mettre cette discussion en Délestage vu que le problème de performance ne peut être résolu. [MS Access et le VBA sont limités pour ce genre de traitement. Peut-être basculé vers du C++ ou C#)

Merci à tout le monde pour la contribution.

Cordialement,

[zoopsys]

As-tu identifié quelle(s) ligne(s) de code était consommatrice en temps ?

[Shanur]

SQL est quelque peu inadapté pour ce genre de traitement !

Je vous conseille d'indexer la table : un index par combinaison des critères de selection.

Via l'index, vous pouvez vous positionnez dans la table avec un Seek

Une boucle testant les critères de sélection et dans la boucle vous faites vos mise à jour.

Cela aura pour effet de limiter les accès à la table au nombre d'enregistrements correspondant aux critères de sélection.

Addenda : L'indexation peut être limitée à un seul critère, le plus restrictif (une date, par exemple), les autres critères pouvant être testés dans la boucle. Le but est de limiter les enregistrements lus.