rapidité de recherche....

[erics83]

Bonjour,

J'ai un classeur, 2 feuilles.
Feuille1 : en A, le numéro de siret des entreprises; en B le nom des entreprises. Il y a 350000 lignes.....
En Feuille2, une compilation de différents fichiers commerciaux : en A : le siret de l'entreprise, en B, le type de contact (téléphone, mail, etc...)

Donc, jusqu'à présent, je faisais un index/equiv dans la feuille2 qui allait me chercher en feuille1 le siret et mettait en colonne C, le nom de l'entreprise (récupéré de la feuille1) .

Mon soucis est le temps.... : Ma feuille2 est alimentée chaque mois d'environ 10000 données. Au début la recherche était "rapide", mais maintenant ma feuille2 atteint les 200000 lignes et Index/equiv devant chercher dans les 350000 lignes de la feuille1, cela met beaucoup de temps....

j'ai essayé Dictionary, formules matricielles (merci JB..), mais cela prend beaucoup de temps.

Auriez vous une petite idée et/ou solution ?

En vous remerciant pour votre aide,

 

[mapomme]

Bonjour à tous ,

Bonjour job75

Un essai sans dictionary.

Le principe est de trier les deux tableaux selon la clef SIRET. Puis on parcourt les tableaux (chacun à l'aide de son pointeur). Quand il y a égalité des SIRET correspondant aux deux pointeurs, on a trouvé le nom de l'entreprise correspondant au SIRET puis on incrémente le pointeur du tableau de la Feuil2, sinon on incrémente le pointeur correspondant au plus petit SIRET associé. Et on recommence.

Il y a deux macros :
1) macro Rechercher() :

• Cette macro ne rétablit pas l'ordre initial du tableau de Feuil1. C'est inutile puisque c'est une base de référence pour les recherches
• Cette macro ne rétablit pas l'ordre initial du tableau de Feuil2. Ce peut être génant
• La première exécution sera toujours plus longue que les suivantes (à la première exécution, on trie le tableau de la feuil1, à moins de changement dans le tableau de la feuil1, le tableau sera déjà trié pour les exécutions suivantes...)

Sur mon vieux PC (construit par mes soins, il a maintenant 10 ans), avec les données initiales et non triées du fichier joint, la 1ère exécution dure environ 7,2 s et les suivantes environ 4.7 s.

2) macro Rechercher_BIS() :

• Cette macro ne rétablit pas l'ordre initial du tableau de Feuil1. C'est inutile puisque c'est une base de référence pour les recherches
• Cette macro rétablit l'ordre initial du tableau de Feuil2
• La première exécution ne sera que très légèrement plus courte que les suivantes

Sur mon vieux PC (construit par mes soins, il a maintenant 10 ans), avec les données initiales et non triées du fichier joint, la 1ère exécution dure environ 10,2 s et les suivantes environ 9,2 s.

nota 1 : on recalcule à chaque fois l'ensemble du tableau de Feuil2 (rien ne dit en effet que la tableau de référence n'a pas changé. Par exemple des changements de nom d'entreprise tout en gardant le même SIRET - je ne sais pas si cela est possible )

Mais on pourrait adapter sans problème les macros pour ne traiter que les nouveaux ajouts dans le tableau de la feuil2.

nota 2 : dans le fichier joint, les deux tableaux sont entièrment 'mélangés'.

Question : je suis intéressé pour connaître des temps d'exécution sur des PC modernes & rapides par rapport à mon vieux PC (Intel Core 2 CPU 6420 - 2,13 GHz - 2 GB RAM, NVIDIA GeForce 8600 GTS)

 

[job75]

Bonjour le fil, le forum,

@ Laetitia as-tu bien suivi ? Mon fichier (2) n'utilise plus de formules.

Il faut distinguer 2 durées d'exécution :

- celle de la création du Dictionary sur Feuil1 (environ 11 secondes chez moi)

- celle de la mise à jour de la colonne C de Feuil2 (9 secondes si toutes les cellules sont traitées).

Dans ce fichier (3) le Dictionary est créé dès l'ouverture du fichier :

Private Sub Workbook_Open()
CreerDico Feuil1.Columns(1), Feuil1.Columns(2)
End Sub

Bonne journée.

Edit : salut mapomme, heureux de te croiser.

 

[job75]

Re,

Question : je suis intéressé pour connaître des temps d'exécution sur des PC modernes & rapides par rapport à mon vieux PC (Intel Core 2 CPU 6420 - 2,13 GHz - 2 GB RAM, NVIDIA GeForce 8600 GTS)

Mon portable ASUS a 2 ans et n'a rien d'extraordinaire (1,7 GHz, RAM installée 4,00 Go).

Rechercher => 5,7 s puis 3,7 s

Rechercher_BIS => 7,7 s puis 6,9 s.

A+

 

[mapomme]

Bonjour job75 ,

On voit quand même l'évolution des architectures. Avec une fréquence processeur inférieure à la mîenne, tu aboutis à des durées inférieures. Je suis aussi limité par la mémoire RAM et je me suis quelquefois trouvé confronté à des macros inutilisables car les tableaux étaient trop gros. Je vais attendre fin 2017 ou début 2018 pour changer de PC.

Dans ta dernière version (v3), pour le recalcul complet, j'obtiens une erreur "n°13 Incompatibilté de type" sur la ligne :

a.Columns(3).Value = Application.WorksheetFunction.Index(t, 0, 3)

Si je remplace la ligne par

a.Value = t

tout se passe bien.

J'ai diminué le nombre de lignes de Feuil2 par pas de 10 000 lignes. Arrivé à 60 000, la première méthode (avec index...) fonctionne. J'en déduis que je suis confronté à ma limite en mémoire au delà de 60 000 lignes.

L'instruction Index... doit donc utiliser plus de mémoire que a.Value = t. On retrouve comme dans pas mal de cas : Etre plus rapide demande plus de mémoire.

 

[pierrejean]

Bonjour à tous
@mapomme
Mon ordinateur monté il y a 7 ans: Processeur Intel E8400 3Ghz (tourne a 2,67) RAM 2Go DDR2 Windows10 pro sur disque SSD
Recherche= 5,0 puis 3,4
Recherchebis 4,7

 

[mapomme]

Bonjour Pierrejean ,

Merci pour ce retour. Manifestement, on remarque les trois ans de moins. Tu es plus véloce, surtout pour la version Recherche_bis (durée divisée par 2) qui, selon moi, est la plus intéressante.

 

[job75]

Re,

J'ai diminué le nombre de lignes de Feuil2 par pas de 10 000 lignes. Arrivé à 60 000, la première méthode (avec index...) fonctionne. J'en déduis que je suis confronté à ma limite en mémoire au delà de 60 000 lignes.

L'instruction Index... doit donc utiliser plus de mémoire que a.Value = t. On retrouve comme dans pas mal de cas : Etre plus rapide demande plus de mémoire.

C'est vraiment curieux cette histoire avec Application.Index.

On a l'impression que cette fonction est limitée à 65536 lignes chez toi (et chez Eric et Roland).

Comme Application.Transpose.

Peux-tu me dire ce que donne dans la feuille de calcul =INDEX(A1:C200000;100000;3) ?

A+

 

[mapomme]

Re,
Comme attendu, ça me donne le contenu de la cellule C100000.

 

[erics83]

Bonjour,

Je m'excuse pour ce retard de correspondance, mais j'avais des petits travaux ce week-end.....

Merci Rolland_M,

Merci laetitia90, ton code fonctionne très bien et est effectivement rapide, c'est surtout l'idée de mise sous tableau qui m'inspire et que je réutiliserai, merci !!! (pour info, il y avait juste un blocage car tu déclarais 2 fois s as long)

Merci pierrejean,

Merci job75, tout comme mapomme, j'ai c100000. Apparemment, effectivement il y a limitation aux 65536 lignes....j'avais déjà remarqué ce bug avec la réutilisation des tutos de JB qui utilisaient souvent des plages nommées et j'avais un soucis lorsqu'on dépassait les 65536....Merci

Merci mapomme, merci pour les 2 exemples de recherche. J'ai fait un essai avec W7 celeron G1610 @ 2.6Ghz : recherche 5,9 s ; recherchebis : 4,6s. Merci pour vos exemples et codes.

Grace à vous, je n'ai que l'embarras du choix maintenant....lol ....les 3 codes (laetitia90, job75 et mapomme) fonctionnent tous parfaitement, merci !!!

Merci pour votre aide,
Eric

 

[job75]

Bonjour Eric, le forum,

Mon fichier (3) beuguait si l'on supprimait des lignes en fin du tableau Feuil2, utilisez ce fichier (4).

Comparaison des 3 méthodes sur mon ordi :

laetitia90

- 200 000 lignes en Feuil2 => 19 s

- 10 000 lignes en Feuil2 => entre 1,2 s et 1,8 s (instable)

- 10 lignes en Feuil2 => entre 0,8 s et 1,3 s.

mapomme Rechercher_BIS

- 200 000 lignes en Feuil2 => 6,8 s

- 10 000 lignes en Feuil2 => 2,2 s

- 10 lignes en Feuil2 => 1,8 s.

job75 fichier (4) après création du Dictionary

- 200 000 lignes en Feuil2 => 10,7 s

- 10 000 lignes en Feuil2 => 0,64 s

- 10 lignes en Feuil2 => immédiat.

A+

 

[mapomme]

Bonjour job75 ,

Bravo pour ce comparatif . On remarque, comme bien souvent, que l'efficacité d'une méthode de tri ou de recherche dépend du volume de données à traiter. Pour bien faire, il faudrait, en fonction de ce volume, choisir une méthode plutôt qu'une autre .

 

[erics83]

Bonjour job75 et mapomme,

Merci job75 pour les différents essais. En fait, je les teste sur mon fichier "originel", e je vais copier-coller mes valeurs dans votre fichier et faire toutes les comparaisons, comme vous...je transmettrai les résultats...

Merci mapomme, effectivement, le choix initial est crucial.....je teste les différents fichiers et vous tiens informé

Merci et à tout de suite,

 

[erics83]

Re bonjour,

Les résultats :
en Feuil1 : 239520 lignes en A les numéro SIRET, en B le nom des entreprises
en Feuil2 : 354816 lignes

Job75 : 497,48 s
Laetitia90 : 599,02 s
mapomme et recherche : 3,7 s
mapomme et recherche bis : 4,1 s

Les écarts me semblent un peu importants, pourtant j'ai refait la simulation de mapomme dans les mêmes conditions et plusieurs fois....je pense que cela vient certainement du tri imposé au départ....

En tous cas, merci à toutes et tous, merci pour votre aide,

 

[job75]

Re Eric, bonsoir Nicole,

Job75 : 497,48 s

Je ne sais pas ce que vous avez testé ni comment mais sur ce fichier (4 bis), avec les nombres de lignes que vous indiquez, les durées d'exécution sont chez moi :

- création du Dictionary sur 239 520 lignes => 5 secondes

- mise à jour des 354 816 lignes en Feuil2 => 14 secondes

- mise à jour de 10 000 lignes en Feuil2 =>0,6 seconde comme précédemment.

A+

 

[job75]

Re,

Je ne sais pas ce que vous avez testé ni comment

Je pense que vous avez mis des valeurs nombres en colonne A des 2 feuilles.

Comme je l'ai dit au post #8 dans ce cas le Dictionary ne s'en sort pas, il faut des valeurs textes.

A+