Performance pour copier des données

[informatixo]

Bonjour le forum,

En voulant aider un XLDnaute j'en suis arrivé à me poser certaines questions mais avant tout voici le lien vers le fil d'où tout à commencé.

Ma question repose sur la copie.

Est-il plus efficace (en terme de rapidité d'exécution de la copie de données) d'effectuer une copie en utilisant :

• Un ".copy Destination:=..."
• Un ".copy" puis un ".Paste"
• Un feuille2.cellule.valeur = feuille1.cellule.valeur au sein d'une boucle en fonction de l'incrémentation

???

Oui je sais, ce doit être la chaleur qui me travaille 😛 pour poser des questions existencielles de ce type mais quand il y a beaucoup de données en jeu, je pense que ce peut être intéressant de le savoir.

Mais qu'est-ce qu'il dit 😱 (lol)

Qu'en penses-tu cher forum ?

Amitiés XLDiennes

 

[informatixo]

Re : Performance pour copier des données

Bonjour le forum,

Personne n'a une petite idée ?

A plus

 

[Hervé]

Re : Performance pour copier des données

bonjour 🙂

personnellement je n'en ai pas la moindre idée, utilisant rarement ce type de syntaxe pour copier des cellules.

le mieux ne serait-il pas de tester ?

salut

 

[Bricofire]

Re : Performance pour copier des données

Bonjour informatixo, Hervé 😉

Ben je suis du même avis qu'Hervé 🙂 ,

Sur un tableau de 58000 lignes at 4 colonnes de données :

Sub Brico1()
Dim TP1 As Date
Dim TP2 As Date
TP1 = Timer
Range("A1").CurrentRegion.Copy Destination:=Sheets(2).Range("A1")
TP2 = Timer
MsgBox " fait en " & TP2 - TP1 & "secondes"
End Sub
Sub Brico2()
Dim TP1 As Date
Dim TP2 As Date
TP1 = Timer
Range("A1").CurrentRegion.Copy
Sheets(2).Select
Range("A1").Select
ActiveSheet.Paste
TP2 = Timer
MsgBox " fait en " & TP2 - TP1 & "secondes"
End Sub
Sub Brico3()
Dim Cell As Range
Dim TP1 As Date
Dim TP2 As Date
TP1 = Timer
For Each Cell In Range("A1").CurrentRegion
Sheets(2).Range(Cell.Address) = Cell
Next Cell
TP2 = Timer
MsgBox " fait en " & TP2 - TP1 & "secondes"
End Sub

Résultats : tests multiples
Brico1 : de 2.36 à 2.39 sec
Brico2 : de 2.56 à 2.58 sec
Brico 3 : de 29 à 31 sec.. là il fallait s'y attendre, pouvait pas y avoir photo...

Bon WE à tous,

Brico

 

[informatixo]

Re : Performance pour copier des données

Re,

Et bien merci à vous 2 pour ces tests. J'en retire donc en conclusion que le copy avec destination est le meilleur.

Merci beaucoup et à plus sur d'autre fils

 

[JeanMarie]

Re : Performance pour copier des données

Bonjour

Dans son post, informatixo parle dans sa troisième possibilité d'utiliser ce type d'instruction, feuille2.cellule.valeur = feuille1.cellule.valeur, cela ne fait pas appel à une boucle, contrairement à la sub Brico3.

Peux-tu faire le test avec ce code

Sub Brico4()
Dim Cell As Range
Dim TP1 As Date
Dim TP2 As Date
Dim Adr As String
Adr = Range("A1").CurrentRegion.Address
TP1 = Timer
Sheets(2).Range(Adr).Value = Range(Adr).Value
TP2 = Timer
MsgBox " fait en " & TP2 - TP1 & "secondes"
End Sub

J'ai mis dans une variable l'adresse de la plage utilisée, pour ne prendre en compte que le temps de recopie des valeurs (chez moi, l'intruction de la plage utilisée prend 0,05 secondes).

Je pense qu'il faudrait mettre aussi une suppresion du contenu du presse-papier dans le cas d'un copy. Quand pensez-vous ?

Nota : Chez moi, la Sub Brico2, ne fonctionne pas.

@+Jean-Marie

 

[myDearFriend!]

Re : Performance pour copier des données

Bonjour informatixo, Bricofire, Hervé, le Forum,

Juste une petite remarque en passant...
Tout d'abord, bravo à Brico pour ces tests tout à fait intéressants, cela dit, le 3ème n'est pas vraiment comparable aux 2 premiers car, si je ne me trompe, cette 3ème façon de faire ne récupère que la valeur des cellules et non l'ensemble de leurs propriétés (Format, etc...).
Tout dépend donc de ce qu'on souhaite copier ou non, à vrai dire...
Et s'il faut le redire à nouveau ici, si l'objectif est de récupérer uniquement les valeur, alors pas besoin de boucle pour ça :

Sheets(2).Range("A1:D58000").Value = Sheets(1).Range("A1:D58000").Value

suffit amplement.

Cordialement,

EDITION :
Un peu en retard sur le coup... Salut Jean-Marie ! Je ne t'ai pas vu arriver.
On est parti sur la même analyse... 😉

EDITION 2 :
J'ajoute que si on parle de rapidité d'exécution, il convient aussi de penser à la désactivation du calcul automatique (Application.Calculation) et de l'affichage écran (Application.ScreenUpdating) avant traitement, sans oublier, bien sûr leur réactivation à l'issue de l'opération...

 

[informatixo]

Re : Performance pour copier des données

Re,

Merci beaucoup pour ces réponses supplémentaires.

myDearFriend! tu as bien fais de le préciser car j'ai fais une omission et je parle effectivement de la copie de la cellule et de ses propriétés j'ai oublié de le dire.

En fait jusqu'à présent, je faisais une boucle où je copier la valeur comme indiqué en 3 et puis j'appliquais les changements à la cellule (encadrement, alignement, ajustement de la colonne au texte, etc.).

En attendant qu'une âme charmante veuille bien faire des tests supplémentaires pour les comparer je m'en vais faire les miens de mon côté.

PS : je connaissais le Application.ScreenUpdating mais pas le Application.Calculation. En quoi on gagne du temps à le désactiver ?

Merci beaucoup et à plus

 

[Bricofire]

Re : Performance pour copier des données

Re,.. JeanMarie, Didier_mDF, 😉

OK, (on s'amuse bien, plus on est de fous....) voici les test supplémentaires, au passage on en profite pour inover et faire une suggestion à Billou, intrduire la notion d'Alias pour les noms de macro 🙂

Un nouveau venu en Tête !!

[COLOR=blue][B]Sub Brico4() 'Alias JeanMarie1 [/B]
Dim Cell As Range
Dim TP1 As Date
Dim TP2 As Date
Dim Adr As String[/COLOR]
[COLOR=blue]Adr = Range("A1").CurrentRegion.Address
TP1 = Timer
Sheets(2).Range(Adr).Value = Range(Adr).Value
TP2 = Timer
MsgBox " fait en " & TP2 - TP1 & "secondes"
End Sub[/COLOR]
[COLOR=blue][B]Sub Brico5() 'Alias Didier_mDF1 [/B]
Dim Cell As Range
Dim TP1 As Date
Dim TP2 As Date[/COLOR]
[COLOR=blue]TP1 = Timer
Sheets(2).Range("A1:D58061").Value = Sheets(1).Range("A1:D58061").Value
TP2 = Timer
MsgBox " fait en " & TP2 - TP1 & "secondes"
End Sub[/COLOR]

Résultats : tests multiples
Brico4() 'Alias JeanMarie1 : de 0.82 à 0.85 sec
Sub Brico5() 'Alias Didier_mDF1 : de 0.92 à 0.95 sec

Précision : le tableau a en fait 58061 lignes et ne comporte que des données non calculées et sans mise en forme (une base qui me sert pour ce genre de choses de 40 à 58000 lignes + macro de tri aléatoire)

Didier :
- dans ce cas est-ce que le Calcul auto intervient ou non ?
- De mêm pour le scrennup, vu qu'on ne selectionne pas ou peu ?
- Quand tu mets le calcul en manul par code, le fait de le remettre en auto lance le calcul ou il faut insérer un calculate entre deux ?

Reste à savoir pourquoi sur les différents lancements on obtient des écarts non négligeables, mais j'ai rien invalidé pour le faire, les ordres de grandeurs étant assez espacés pour voir le plus rapide...

PS : pour Brico 3, j'avais mal interprété la chose 😕

Bon WE,

Brico

Edition : JeanMarie, pour Brico2, n'est-ce pas dû à la syntaxe par rapport au Mac ?

 

[myDearFriend!]

Re : Performance pour copier des données

Bonsoir le fil,

Pour tenter de répondre aux questions posées (d'ailleurs, j'en profite pour vous informer que je ne suis pas plus que vous spécialiste sur le sujet !), je viens de procéder aux tests suivants :

En feuille 1, une plage de données A1😀58000
En colonne 1 : la valeur 1 dans chaque cellule (A1:A58000 = 1)
En colonne 2 : la même chose avec la valeur 2
En colonne 3 : la même chose avec la valeur 3
En colonne 4 : la même chose avec la valeur 4

En feuille 2 : sur la plage G1:G58000, une simple formule = A1 + A2 (tirée jusqu'en bas)

Chaque test est lancé 5 fois consécutivement et je note les temps mini et maxi obtenus...

[SIZE=2]Sub Test1()
[COLOR=navy]Dim [/COLOR]TP1[COLOR=navy] As Single[/COLOR]
    TP1 = Timer
    Sheets(2).Range("A1:D58000").Value = Sheets(1).Range("A1:D58000").Value
    MsgBox " fait en " & Timer - TP1 & "secondes"
    Sheets(2).Range("A1:D58000").ClearContents
[COLOR=navy]End[/COLOR] Sub[/SIZE]

[SIZE=2]Sub Test2()
[COLOR=navy]Dim [/COLOR]TP1[COLOR=navy] As Single[/COLOR]
    TP1 = Timer
    [COLOR=navy]With[/COLOR] Application
        .ScreenUpdating = [COLOR=navy]False[/COLOR]
        Sheets(2).Range("A1:D58000").Value = Sheets(1).Range("A1:D58000").Value
        MsgBox " fait en " & Timer - TP1 & "secondes"
        .ScreenUpdating = [COLOR=navy]True[/COLOR]
    [COLOR=navy]End With[/COLOR]
    Sheets(2).Range("A1:D58000").ClearContents
[COLOR=navy]End[/COLOR] Sub[/SIZE]

[SIZE=2]Sub Test3()
[COLOR=navy]Dim [/COLOR]TP1[COLOR=navy] As Single[/COLOR]
    TP1 = Timer
    [COLOR=navy]With[/COLOR] Application
        .Calculation = xlCalculationManual
        Sheets(2).Range("A1:D58000").Value = Sheets(1).Range("A1:D58000").Value
        MsgBox " fait en " & Timer - TP1 & "secondes"
        .Calculation = xlCalculationAutomatic
    [COLOR=navy]End With[/COLOR]
    Sheets(2).Range("A1:D58000").ClearContents
[COLOR=navy]End[/COLOR] Sub[/SIZE]

[SIZE=2]Sub Test4()
[COLOR=navy]Dim [/COLOR]TP1[COLOR=navy] As Single[/COLOR]
    TP1 = Timer
    [COLOR=navy]With[/COLOR] Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = [COLOR=navy]False[/COLOR]
        Sheets(2).Range("A1:D58000").Value = Sheets(1).Range("A1:D58000").Value
        MsgBox " fait en " & Timer - TP1 & "secondes"
        .ScreenUpdating = [COLOR=navy]True[/COLOR]
        .Calculation = xlCalculationAutomatic
    [COLOR=navy]End With[/COLOR]
    Sheets(2).Range("A1:D58000").ClearContents
[COLOR=navy]End[/COLOR] Sub[/SIZE]

J'ai obtenu (Windows XP et Excel 2003) :

1. Test1 : entre 0.9531 et 0.9689
2. Test2 : entre 0.9370 et 0.9532
3. Test3 : entre 0.9370 et 0.9381
4. Test4 : entre 0.9221 et 0.9379

Pour ma part, même si on peut observer un gain, je note que ce dernier reste infime. En toute sincérité, je pense que les résultats sont trop aléatoires pour en tirer une conclusion quelconque. J'observe notamment que pour chacun des tests lancés, c'est le premier lancement qui obtient le plus long temps de traitement...

Pour Bricofire : j'observe également qu'un re-calculate est inutile, le simple fait de réactiver le calcul automatique provoque d'office ce re-calculate (sur Excel 2003 en tout cas).

La seule chose dont je suis sûr (et c'était l'objet de mon intervention précédente), c'est que pour récupérer les valeurs seules, il faut oublier les boucles !

Cordialement,

 

[Dan]

Re : Performance pour copier des données

Bonjour à tous,

Très intéressant ce fil.
J'ai également effectué quelques tests proposés et notamment le test de Jean Marie qui ne me donne pas les même résultats en matière de rapidité. J'obtiens en effet des temps plus longs.

Assez curieusement, si j'enregistre le fichier puis je réexécute la macro, la première fois le temps est le plus court, les 4 fois suivantes le temps est identique.

Etant sur la même plateforme que Jean Marie (mais processeur plus lent) je pense que le temps est aussi lié au processeur et à la RAM installée.

Le test le plus rapide est celui de la macro "Test4" de mdF.

Bonne journée à tous.

 

[Robert]

Re : Performance pour copier des données

Bonjour le fil, bonjour le forum,

Pas besoin de test pour se rendre compte que ce forum est absolunique. Je reviens juste de chez le chapelier avec mon panier rempli à ras-bord. Permettez messieurs que je vous les tire à bout-portant...

 

[JeanMarie]

Re : Performance pour copier des données

Bonjour tout le monde

Je n'avais pas répondu, mes tests réalisés étaient sur plate-forme PC, pour garder la notion de rapidité, avec les codes "Brico" j'obtiens des temps de 0,15 ; 0,25 ; 19,55 ; et 0,25, temps en secondes, et la sysntaxe de la sub Brico3 ne passait pas.

Celeron à 1,5 Ghz 512 Mo, XP et office 2000, (xla active : mDF_Calendrier v3.0, Eurotools & NaviguerFile)

Test1 = 0,28125 ; 0,2382813 ; 0,25 ; 0,25 : 0,25
Test2 = 0,28125 ; 0,2382813 ; 0,2382813 ; 0,2382813 : 0,2382813
Test3 = 0,2421875 ; 0,2382813 ; 0,2617188 ; 0,25 ; 0,25
Test4 = 0,2421875 ; 0,2421875 ; 0,25 ; 0,2382813 ; 0,25

PS : Vous pouvez récupérer le fichier réalisé suivant les indications fournies par Didier ici

@+Jean-Marie

 

[Bricofire]

Re : Performance pour copier des données

Re bonjour à tous, 🙂

Bon, pour faciliter les choses, la base de test étant à l'étroit dans 50 Ko, j'ai créé un fichier test dont il suffit de lancer la macro "ConstructionDonnées" pour avoir un fichier de 60000 lignes sur 4 colonnes avec les col 2,3,4 qui ne comportent que des formules...
Les autres modules reprennent les procédures de ce fil, les lignes clear que Didier_mDF avait eu la bonne idée d'ajouter étant désactivées au profit d'un bouton Cleaner général.
Par rapport à nos soucis de rapidité, à l'étude il semble que :

Le calcul auto n'intervient pas :

- Logiquement, finalement quand on fait des copies par valeur, vu que les formules ne sont pas transférées, ce qui rend l'approche avec les .value différentes selon ce qu'on souhaite.
- Qu'en faisant joujou avec le module d (calcul auto non rétabli), il semble apparaître que d'origine sous Excel le calcul manuel ne concerne que les changements de valeur, si on entre une nouvelle formule sur une feuille sous calcul manuel, le calcul s'effectue, par contre si on change une des données dans une case, c'est gelé, il faut faire F9... Donc dans notre cas de figure, ça n'a aucune influence..

PS : Oups, je viens de voir que JeanMarie avait mis un lien vers un fichier, bah ça fait une compilation, j'avais repris les codes sur les fil...😉

PS 2 : JeanMarie, tu avais raison;;; j'ai mais un Application.CutCopyMode = False avec le bouton, mais cela vide-t-il efficacement le presse-Papier ?

Edition : Brico3 chez moi passe, mais Euh.. peut prendre plus de 4 minutes... Mis en commentaire (désactivé) dans le classeur joint.

Bonne journée à tous,

Brico

 

[informatixo]

Re : Performance pour copier des données

Bonjour à tous,

Je suis enthousiasmé de voir qu'autant de personnes réagissent et s'impliquent dans ce fil. Je tiens à vous remercier pour les tests que vous avez fait et les réponses que vous m'avez apporté.

Ce n'aurais pas été juste, moi qui est lancé le fil, d'attendre les résultats sans ne rien faire. Donc me voilà, un peu en retard certes, mais je viens de finir mon classeur de test à mon tour et je vous fais part de mes résultats.

J'ai repris les données de bricofire de A1 à D60000 pour essayer de garder une certaine homogénéité et j'ai ajouté des bordures aux cellules ainsi qu'une couleur d'intérieur.

Mon classeur possède 3 feuilles, la première possède tous les boutons essentiels ("Effacer feuille" sinon 1,7 MO de fichier (lol), "Construire les données" pour obtenir le tableau duquel je suis parti et "lancer les tests" qui permet de tout calculer sans rien faire), la deuxième me sert de feuille tampon (c'est elle qui reçoit les données) et la troisième sert à afficher les résultats.

Je suis parti sur un postulat de 2 copies différentes (avec les propriétés des cellules (2 premières instructions cités ci-après) et sans (les 2 dernières)) en utilisant 4 instructions "... Copy Destination:= ...", "Copy ... Paste", "Copy ... PasteSpecial" et "...Value = ...Value".

J'ai aussi suivi la démarche de myDearFriend!, c'est-à-dire que pour chaque instruction j'ai alterné ScreenUpdating et Calculation d'activés, puis un de désactivé, puis l'autre et puis les 2 de désactivés

Cela me donne au total 16 tests qui sont réitérés 5 fois chacun ce qui me donne 80 lignes de résultats exprimées en seconde.

Vous verrez dans le classeur joint, ce sera plus parlant 😀

Pourriez-vous me faire part de vos remarques SVP ? C'est surtout pour voir si avec le même classeur mais chez vous (Pc et MAC), vous obtenez la même grille de résultats que moi (à un ordre de grandeur près) ou si je me suis totalement planté.

Regardez la grille des résultats (troisième feuille) avant de lancer les tests (feuille 1) sinon ils seront écrasés ! Je n'ai pas eu le temps de coder la sauvegarde de la troisième feuille en CSV ou autre (désolé manque de temps 😱)

Je vous remercie tous beaucoup et à demain