Discussion :

[MM_13]

Bonjour,

Je viens vers vous pour une optimisation de code sous VBA Access.

J'ai un petit bout de code, mélangeant traitement d'enregistrement avec des Recordset et du SQL, qui me permet de mettre à jour un champ (IdListeFichierPalier) à partir d'un autre champ (N°) en fonction de critères provenant d'autres champs (Nom), (Type) et (Taille).
La table compte un peu plus de 390 000 lignes et malheureusement le temps de traitement est trèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèèès long, c'est pour cela que je vous demande de l'aide.

Je procède donc de la façon suivant pour le code, je ne sais pas si c'est la meilleure, je ne pense pas vu le temps de traitement :
- Je boucle sur tous les enregistrements de la table avec les recordsets
- Sur chaque enregistrement, j'identifie plusieurs critères :
-> ID que je dois mettre dans le champ (IdListeFichierPalier) si celui si répond à 3 critères
-> Critère 1 = (Nom)
-> Critère 2= (Type)
-> Critère 3= (Taille)
- Cette mise à jour de champ répondant au 3 critères se faire à partir d'une requête SQL

Donc ma question est, est-ce que l'on peut optimiser ce bout de code ou le long temps de traitement est dû au grand nombre d'enregistrement ?
Est-ce que le fait de mélanger traitement avec recordset et SQL n’alourdit pas le code ?

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
 
Set rstTblTmp1 = CurrentDb.OpenRecordset(TableTmp, dbOpenDynaset)
    rstTblTmp1.MoveFirst
    While (Not rstTblTmp1.EOF)
        If IsNull(rstTblTmp1.Fields(ColonneTmp)) And rstTblTmp1![Type] <> "Dossier de fichiers" Then
 
            ID = rstTblTmp1![N°]
            NomTmp = rstTblTmp1![Nom]
            TypeTmp = rstTblTmp1![Type]
            TailleTmp = rstTblTmp1![Taille]
 
            strSQL = "UPDATE [" & [TableTmp] & "] SET [" & [TableTmp] & "].[" & [ColonneTmp] & "] = " & ID & "  Where ([" & [TableTmp] & "]![Nom] =  '" & NomTmp & "' And [" & [TableTmp] & "]![Type] = '" & TypeTmp & "' And [" & [TableTmp] & "]![Taille] =  " & Replace(CDec(TailleTmp), ",", ".") & " );"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
 
        End If
 
        rstTblTmp1.MoveNext
    Wend
    rstTblTmp1.Close
    Set rstTblTmp1 = Nothing

Merci.

[Zekraoui_Jakani]

350.000 c'est pas énorme.

Astuces:

• Compacter la DB à sa clôture (peut se faire dans les options de la DB)
• Décomposer votre table (la plus grande) en en créant plusieurs tables ayant des clefs primaires d'identification
• Construisez une vraie DB relationnelle (voir relationship dans menu 'database tools")
• Vérifiez la capacité mémoire de votre PC.

[loufab]

Bonjour,

Tout d'abord 390 000 lignes ce n'est pas énorme.

Par contre le code n'est franchement pas penser pour être économe en temps. Faire un update Sql sur la même table qu'un recordset ouvert ce n'est pas très judicieux.

De plus l'update dans une boucle c'est très consommateur.

Ce que je ferais c'est de travailler sur le même recordset en utilisant des Findfirst (et/ou findnext si besoin) et Edit / Update.

Évidemment le code est un peu plus complexe à écrire que le update Sql.

Cordialement,

[MM_13]

Bonjour et merci pour vos réponses.

350.000 c'est pas énorme.

Astuces:

• Compacter la DB à sa clôture (peut se faire dans les options de la DB)
• Décomposer votre table (la plus grande) en en créant plusieurs tables ayant des clefs primaires d'identification
• Construisez une vraie DB relationnelle (voir relationship dans menu 'database tools")
• Vérifiez la capacité mémoire de votre PC.

Yes j'avais déjà mis l'option de compactage de la DB.
La capacité mémoire du PC ça correspond à quoi ? Comment je vérifie cela ?

Bonjour,

Tout d'abord 390 000 lignes ce n'est pas énorme.

Par contre le code n'est franchement pas penser pour être économe en temps. Faire un update Sql sur la même table qu'un recordset ouvert ce n'est pas très judicieux.

De plus l'update dans une boucle c'est très consommateur.

Ce que je ferais c'est de travailler sur le même recordset en utilisant des Findfirst (et/ou findnext si besoin) et Edit / Update.

Évidemment le code est un peu plus complexe à écrire que le update Sql.

Cordialement,

C'est là où je veux optimiser le code, je sais pas trop ce qui est le plus "rapide" entre le traitement avec recordset et l'utilisation de SQL.
J'ai plus la façon de fonctionner avec du vba sous Excel, là j'essaie de faire une petite DB sous Access et la façon de construire n'est pas la même j'ai l'impression.
Sous Excel pour faire ce type de manip j'aurais utilisé 2 boucles imbriquées avec des variables tableaux et j'ai voulu reproduire cela.
Au départ j'avais fait 2 boucles imbriquées avec 2 recordsets, 1 qui me permet de définir l'ID et les autres champs de critères et l'autre imbriquée dans cette première boucle pour mettre à jour les champs suite aux critères. Mais ça prenait encore plus de temps et je pensais qu'en utilisant le SQL ça irait plus vite.

Avec un Findfirst ça donnerait quelque chose comme ça ou je me trompe :

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
 
    Set rstTblTmp1 = CurrentDb.OpenRecordset(TableTmp, dbOpenDynaset)
    rstTblTmp1.MoveFirst
    While (Not rstTblTmp1.EOF)
        If IsNull(rstTblTmp1.Fields(ColonneTmp)) And rstTblTmp1![Type] <> "Dossier de fichiers" Then
 
            ID = rstTblTmp1![N°]
            NomTmp = rstTblTmp1![Nom]
            TypeTmp = rstTblTmp1![Type]
            TailleTmp = rstTblTmp1![Taille]
 
            Set rstTblTmp2 = CurrentDb.OpenRecordset(TableTmp, dbOpenDynaset)
            Critere = "[N°] LIKE " & Chr(34) & ID & Chr(34)
            rstTblTmp2.FindFirst Critere
            While (Not rstTblTmp2.EOF)
                 If rstTblTmp2![Nom] = NomTmp And rstTblTmp2![Taille] = TailleTmp And rstTblTmp2![Type] = TypeTmp Then
                      rstTblTmp2.Edit
                      rstTblTmp2![IdListeFichierGeneral] = ID
                      rstTblTmp2.Update
                 End If
                 rstTblTmp2.MoveNext
            Wend
            rstTblTmp2.Close
            Set rstTblTmp2 = Nothing
 
        End If
 
        rstTblTmp1.MoveNext
    Wend
    rstTblTmp1.Close
    Set rstTblTmp1 = Nothing

[Zekraoui_Jakani]

La remarque de Loufab au sujet du RecordSet est pertinente:

Si vous utilisez TableTmp comme source de votre formulaire et que vous le réutilisez dans rstTblTmp1 et dans rstTblTmp2 en même temps, je ne sais comment le moteur Access va gérer tout ça.
Peut-on avoir copie zipée de votre db (avec données fictives) pour voir ce que l'on peut faire ....

[loufab]

Bonjour,

C'est là où je veux optimiser le code, je sais pas trop ce qui est le plus "rapide" entre le traitement avec recordset et l'utilisation de SQL.

J'ai plus la façon de fonctionner avec du vba sous Excel, là j'essaie de faire une petite DB sous Access et la façon de construire n'est pas la même j'ai l'impression.

Sous Excel pour faire ce type de manip j'aurais utilisé 2 boucles imbriquées avec des variables tableaux et j'ai voulu reproduire cela.
Au départ j'avais fait 2 boucles imbriquées avec 2 recordsets, 1 qui me permet de définir l'ID et les autres champs de critères et l'autre imbriquée dans cette première boucle pour mettre à jour les champs suite aux critères. Mais ça prenait encore plus de temps et je pensais qu'en utilisant le SQL ça irait plus vite.

Dans ton cas le traitement full recordset pourrait améliorer les choses. Mais ce n'est pas tout le temps le cas. Si tu faisais un traitement full SQL à coup de requêtes oui ça irait plus vite.

Excel est un tableur et n'a pas de notion de base de données, donc utilise tes connaissances VBA mais ne tente pas une résolution en pensant EXCEL, tu ne produiras que des UAG difficilement maintenables.

Code :

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

Critere = "[N°] LIKE " & Chr(34) & ID & Chr(34)

ton N° c'est du texte ? Si c'est un entier utilise = et non like.

Cordialement

[MM_13]

La remarque de Loufab au sujet du RecordSet est pertinente:

Si vous utilisez TableTmp comme source de votre formulaire et que vous le réutilisez dans rstTblTmp1 et dans rstTblTmp2 en même temps, je ne sais comment le moteur Access va gérer tout ça.
Peut-on avoir copie zipée de votre db (avec données fictives) pour voir ce que l'on peut faire ....

Je vais lever des données et épurer la DB pour pouvoir l'envoyer.

Bonjour,



Dans ton cas le traitement full recordset pourrait améliorer les choses. Mais ce n'est pas tout le temps le cas. Si tu faisais un traitement full SQL à coup de requêtes oui ça irait plus vite.

Excel est un tableur et n'a pas de notion de base de données, donc utilise tes connaissances VBA mais ne tente pas une résolution en pensant EXCEL, tu ne produiras que des UAG difficilement maintenables.

Code :

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

Critere = "[N°] LIKE " & Chr(34) & ID & Chr(34)

ton N° c'est du texte ? Si c'est un entier utilise = et non like.

Cordialement

C'est bien un entier oui, j'ai modifié le "like".
C'est là où je bloque, je ne vois pas comment faire un traitement full SQL sans boucler sur chaque enregistrement pour récupérer le champ ID et le mettre à jour aux autres enregistrements répondant aux mêmes critères, c'est pour cela que j'avais fait un recordset puis du SQL imbriqué, si vous pouvez me donner une piste je suis preneur.

[loufab]

Le SQL c'est en effet un langage et des des concepts totalement différents de la programmation classique.

Commence par faire un SELECT des valeurs que tu souhaites "ajouter" (ceux que tu récupères dans ta boucle).
Quand c'est ok tu t'en sers comme source de l'update. Reste l'épineuse question de la liaison entre les valeurs à changer et les nouvelles (relation entre Update et Select). La requête promet d'être ardue pour un néophyte.