XL 2016 Performance et manipulation avec d’un variant

Tradman

XLDnaute Nouveau
Bonjour à tous,
J’ai un onglet avec 800 000 lignes et 162 colonnes sur lesquels je boucle pour faire plusieurs traitements. Pour gagner en efficacité, je charge le tout dans un array et je fais le traitement. Jusque là tout va bien et c’est même plutôt performant. Par contre lorsque je veux replacer cet array dans ma feuille, j’ai un message d’erreur comme quoi la mémoire est insuffisante.

Dim vData as variant

vData = range(« A1:DH800000 »).value2

Boucle sur le variant et traitement des données

Range.(« A1:DH800000 »).value2 = vData -> Erreur car mémoire insuffisante

J’ai trois questions :
1-) Est-ce que le fait d’avoir une machine plus puissante réglerait le problème ?
2-) Est-ce qu’il y a un moyen de faire une boucle pour replacer les données par tranches de 100000 lignes par exemple ?
3-) Est-ce que je suis rendu à un point où j’aurais besoin d’un autre outil plus performant?

Pour la deuxième partie du titre, est-il possible d’effectuer des opérations (Tri, déplacement de lignes, etc...) dans un variant (Mon vData dans mon exemple)? Comme vous pouvez l’imaginer, c’est un enjeu de performance pour moi. Mes tests avec un plus petit jeux de données m’a montré que je double presque ma performance en chargeant le tout dans un variant (~35 min de traitements plutôt que 60 min si je fais les manipulations directement sur la feuille Excel)

Merci beaucoup de votre aide !
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Voili !

1618064702188.png

Merci pour ton test. :)

Ton proc est cadencé à une fréquence plus élevée (4 Ghz) que le mien (3,6 Ghz) soit environ une différence de 10%.
Si on augmente de 10% ta durée, on arrive aux alentours de 84 s soit mon temps d'exécution.
Ça serait donc plus une différence de fréquence que d’architecture.
 
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @fanch55 :),
Salut à tous,
@mapomme , pour info
Le premier test sur le portable de ma femme datant de 2013
Le 2ème sur mon fixe tout refait à neuf de cette année ....
32 gb de mémoires, y'a pas photo
Merci pour les tests. Une petite bombe ta bécane 🚀. Deux fois plus rapide que ma bouse.

J'espère pour toi que ton épouse ne fréquente pas XLD car si elle compare tes résultats aux siens, il va y avoir du grabuge au dîner de ce soir.:eek: Et tu ne peux même pas te rattraper en lui offrant un bon resto!
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Pour les opérations sur un variant, c’est que dans mon traitement, j’aimerais pouvoir déplacer un ligne et/ou faire des classements et j’aimerais savoir comment m’y prendre sur un variant. Mes données sont des une série d’évènements que doit faire une personne. J’ai une personne par ligne et je dois traiter les déplacements chronologiquement. Une fois que j’ai traité le premier événement de la première personne, je dois la déplacé au moment où chronologiquement je devrai traiterm
N'ayant pas de fichier pour comprendre tes traitements, je me demande si un traitement en partie par excel (notamment les tris ou ordonnancement des lignes) ne serait pas plus rapide sur la feuille excel du genre :
  • on lit le tableau des données
  • le traitement construit un tableau à une seule colonne avec le rang que chaque ligne devrait avoir
  • on aboute cette colonne au tableau sur la feuille
  • puis on trie sur cette dernière colonne
D'abord initialiser puis réordonner.

=> Je passe de 84 s à 14 s.​
 

Pièces jointes

  • Tradman- via 1 col & tri- v1.xlsm
    19.8 KB · Affichages: 11
Dernière édition:

fanch55

XLDnaute Barbatruc
Merci pour les tests. Une petite bombe ta bécane 🚀. Deux fois plus rapide que ma bouse.
Je me demande si Excel en 64 bits ne serait pas plus rapide,
du fait de pouvoir profiter d'une plus grande mémoire ... 🤔

J'espère pour toi que ton épouse ne fréquente pas XLD car si elle compare tes résultats aux siens, il va y avoir du grabuge au dîner de ce soir.:eek: Et tu ne peux même pas te rattraper en lui offrant un bon resto!
Je ne vais rien lui dire sinon elle va réclamer plus de mémoires dans la queue de la casserole .... 🤪
 
Bonjour @fanch55

Pour rester cohérent en comparaison, c'était un test sous Excel 365 32 bits que j'avais fait pour @mapomme et mon proc est en fait un I7 4790K et pas un I7 4690K (erratum).
Voila le résultat du même test sur la même config sous Excel 365 64 bit, on gagne environ 15% de rapidité de traitement entre Excel 32 et Excel 64 bit. En utilisation mémoire, je n'ai jamais dépassé les 11 Go utilisés que ce soit en 32 ou en 64 bit ni les 20 % d'utilisation processeur avec un turbo qui est monté à 4.38 Ghz soit quasiment le max de 4,4 donc on n'utilise pas tous les coeurs pour le calcul. Elle tient encore la route ma config vieille de 6 ans (début 2015) :). Par contre, j'ai vu des sollicitations de mon disque système qui est un Ssd Sata à 500m/s en lecture et écriture, certainement des fichiers temporaires.
fanch55, ce ne serait pas un Nvme ton disque système par hasard ?
ça, ça pourrait jouer aussi !
Et aussi pour @mapomme si son disque système est un disque dur magnétique ou un ssd moins rapide.

Bien cordialement, @+
Sans titre.png
 
Dernière édition:

juvaxe

XLDnaute Occasionnel
Bonjour

Le recours à une (vraie ?) Base de Données ne serait-il pas la réponse à votre problème ?

Par base de données, j'entends base de données SQL type SQL Server (Microsoft), MySql (Oracle) ... qui sont organisées pour la gestion des volumes et qui créent des index pour être performantes dans la recherche et le tri. Excel est très bien interfacé avec ces systèmes.

Cdt
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Le recours à une (vraie ?) Base de Données ne serait-il pas la réponse à votre problème ?
Bonjour juvaxe :) ,
Tu as raison. Quand on atteint 800 000 lignes et beaucoup de colonnes, le mieux est de passer par un SGBD.
Mais quand on ne connait pas, ça peut rebuter.
Un pis-aller est peut-être MS-ACCESS (pour ceux qui ont la bonne version de Office) qui dispose d'un VBA comparable à Excel; ça pourrait faire la transition en douceur :).
 

fanch55

XLDnaute Barbatruc
Bonjour @fanch55
fanch55, ce ne serait pas un Nvme ton disque système par hasard ?
Tout à fait c'est un nvme, j'ai fait le test également en supprimant le fichier d'échange pour obliger W10 à n'utiliser que la mémoire, je n'ai gagné que 2 sd .
Avec ou sans fichier d'échange, il y a quand même une grosse activité "disque",
Excel doit utiliser des fichiers Temp, ce sont les seuls qui vont sur mon disque HD en dehors de mes données.
Le recours à une (vraie ?) Base de Données ne serait-il pas la réponse à votre problème ?

Par base de données, j'entends base de données SQL type SQL Server (Microsoft), MySql (Oracle) ...
800000 lignes et 162 colonnes, il est vrai qu'il vaudrait mieux se tourner vers une Bd .
S'ils sont bien référencés nativement dans les Odbc , Sql Server ou Mysql obligent quand même à avoir un serveur, cad un pc hébergeur tournant en permanence sur un réseau et éventuellement un gestionnaire de base , le fichier Excel quant à lui ne serait plus autoporteur .
Il existe Sqlite également, le fichier est stocké dans un fichier d'extension .bd ( comme Linux ou Google ) mais il faut installer le driver Odbc spécifique Sqlite 3 ( en 32 et 64 bits ) .
 

juvaxe

XLDnaute Occasionnel
Bonjour

Pour mon usage personnel (familial), Office 2010 et SQLEXPRESS sont installés sur le même PC.

Il est vrai qu'il devient nécessaire d'avoir les deux éléments (Excel et données) disponibles pour pouvoir fonctionner.
Lorsqu'on veut se déplacer, il faut alors faire une copie des deux et les remettre en ligne dans un autre environnement. C'est simple à faire.

J'ai fait mon premier message car on voit quelques fois passer des "méga-classeurs" qui ont été commencés petitement, sans prendre garde, et qui par la suite, au fil des développements, deviennent des monstres à gérer.

MS_ACCCESS est sans aucun doute plus facile à installer.

Cdt
 

Discussions similaires