[Résolu] VBA - Recherche de données sur grands volumes

[Greenlynx]

Bonjour à tous,

J'ai un petit souci avec la macro en PJ

C'est une macro de mapping basiquement, elle utilise deux fichiers en entrée:
- Un fichier "catalogue" qui contient toutes les données que l'on va chercher
- Un fichier "site" qui contient simplement les références à chercher dans le catalogue ci dessous.

La macro vérifie d'abord le fichier site (doublon dans les références etc) et elle ouvre ensuite le catalogue
Les références du fichier site sont parcourues une à une et les informations recherchées dans le catalogue.

Le problème étant la taille des catalogues, certains fichiers font plus de 500 000 lignes et dans ce cas de figure cela devient longgggggg, vraiment long.

Je ne suis pas un expert en VBA donc j'imagine que ma macro est très fortement améliorable.

Je suis preneur de vos conseils

D'avance merci à tous.

Greenlynx

 

[BOISGONTIER]

Re : VBA - Recherche de données sur grands volumes

Bonjour,

-Il faut éviter de travailler directement sur les cellules (transférer les champs dans des Arrays)
-Eventuellement, indexer les Arrays par des dictionnaires

-Temps de transfert A1:E500000 (5 colonnes) --> Array 1,20s
-Recherche dans une colonne d'Array --> 0,35 sec au lieu de 7sec pour une recherche dans le tableur

Comparaison Dictionnaire/Tableau/Find

http://boisgontierjacques.free.fr/fichiers/Cellules/RechercheTableauDico.xls

Indexation d'un tableau 2D par un dictionnaire

JB

 

[Greenlynx]

Re : VBA - Recherche de données sur grands volumes

Bonjour,

-Il faut éviter de travailler directement sur les cellules (transférer les champs dans des Arrays)
-Eventuellement, indexer les Arrays par des dictionnaires


Comparaison Dictionnaire/Tableau/Find

http://boisgontierjacques.free.fr/fichiers/Cellules/RechercheTableauDico.xls

JB

Bonjour,

Merci de votre retour, j'avais effectivement envisagé de travailler dans un Array pour les recherches mais n'est pas poussé plus loin en raison de la taille.
Sur un tableau de 500 000 lignes, j'ai besoin d'environ 8 colonnnes ce qui ferait un array a 2 dimensions de 500 000x 8.


C'est jouable a votre avis ?

Greenlynx

 

[BOISGONTIER]

Re : VBA - Recherche de données sur grands volumes

-Un produit comme Access serait + approprié

JB

 

[Greenlynx]

Re : VBA - Recherche de données sur grands volumes

-Un produit comme Access serait + approprié

JB

Bonjour,

Surement, je n'ai malheureusement uniquement accès à Excel sur ma station de travail, je dois donc faire avec

Greenlynx

 

[Greenlynx]

Re : VBA - Recherche de données sur grands volumes

Bonjour,
Suite à votre lien je pensais à une ébauche de solution.
- Faire un dico avec la colonne contenant les références dans le catalogue
- Faire toute mes recherches dans ce dico et stocker les index des références trouvées dans un array.
- libérer la mémoire du dictionnaire et en reconstruire un sur la colonne suivante qui m'intéresse.
- appeler les index contenu dans mon array pour récupérer les données qui m'intéresse et ainsi de suite jusqu'à la dernière colonne.

Ainsi seule une colonne à la fois est présente en mémoire et je ne travaille que sur dictionnaire.

Que pensez-vous de cette solution ? est elle viable ?

Merci

Greenlynx

 

[Greenlynx]

Re : VBA - Recherche de données sur grands volumes

Bonjour à tous,

Pour ceux que cela intéresse la version modifié avec utilisation des dictionnaires

Le temps d’exécution est maintenant d'environ 100 sec pour 500 000 lignes ce qui est acceptable pour moi

Je passe le sujet en résolu

Bonne journée

Greenlynx

 

[BOISGONTIER]

Re : [Résolu] VBA - Recherche de données sur grands volumes

Bonjour,

Pour créer le dictionnaire, il serait peut être plus rapide de transférer le champ dans un tableau et de créer le dictionnaire à partir du tableau.

-Temps de transfert A1:E500000 (5 colonnes) --> Array 1,20s
-Recherche dans une colonne d'Array --> 0,35 sec au lieu de 7sec pour une recherche dans le tableur

JB

 

[BOISGONTIER]

Re : VBA - Recherche de données sur grands volumes

Bonjour,

Pour créer le dictionnaire, il serait peut être plus rapide de transférer le champ dans un tableau et de créer le dictionnaire à partir du tableau.

-Temps de transfert A1:E500000 (5 colonnes) --> Array 1,20s
-Recherche dans une colonne d'Array --> 0,35 sec au lieu de 7sec pour une recherche dans le tableur (rapport 20)

JB

 

[chris]

Re : [Résolu] VBA - Recherche de données sur grands volumes

Bonjour

Pas testé mais à défaut d'Access, une requête Msquery pourrait peut-être être plus rapide si on peut établir une relation 1 à n...