Mis a jours BDD

Version imprimable

Mis a jours BDD

Voila j'ai crée une base de données assez conséquente (environ 540 Mo) et j'ai une table nommé protéine qui doit etre mis a jour, cette table contient environ 600 000 entrées. Pour mettre a jour cette page je dispose d'un fichier csv de 200Mo.
J'ai essayé pas mal de chose mais tous s'avère ultra lent voici ma méthode
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 $requete="select ACCES_NUMBER from protein "; $result=mysql_query($requete); while ($ligne = mysql_fetch_assoc($result)) { $tab_acces_nb[]=$ligne['ACCES_NUMBER']; } $nom_fichier="uniprot.emblformat.parse"; $fichier=fopen($nom_fichier,'r'); while (!feof($fichier)){ $ligne = fgets($fichier,4096); $liste=explode("\t",$ligne); $id_uniprot=$liste[0]; $acces_id=$liste[1]; $go=$liste[3]; if (in_array($acces_id,$tab_acces_nb)){ $requete="update protein set id_uniprot='$id_uniprot',go='$go' where acces_number='$acces_id' "; mysql_query($requete) or exit("Erreur : ".mysql_errno()); } } fclose($fichier);
Avez vous une solution plus rapide s'il vous plait car la c'est vraiment mais alors vraiment long j'ai laissé tourné 8h et ca n'était pas fini

26/04/2009, 17h15
Séb.

Et si tu passes directement par MySQL ?
http://dev.mysql.com/doc/refman/5.1/en/load-data.html
26/04/2009, 17h40
tomaprice

Citation:

Envoyé par Séb.

Et si tu passes directement par MySQL ?
http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Le Pb c'est que je n'ai seulement besoin des colonnes 1 et 3 de mon fichier csv et que je me sert de la colonne 2 du fichier csv pour dire la ou il faut faire la mise a jour dans la BDD.
26/04/2009, 17h59
Séb.
Pardon, je me rends compte que j'ai été bcp trop succinct :roll:

Puisque tu veux gagner du temps et étant donné les volumes il va falloir essayé de travailler avec le minimum d'intermédiaires, le cas échéant en essayant de shunter PHP.
Et si la RAM le permet (mais ça risque d'être difficile...) en évitant de travailler avec le disque dur (accès disques sont lents).

Ce que j'essaierai de faire :
1. Création d'une table de type MEMORY http://dev.mysql.com/doc/refman/5.1/...ate-table.html http://dev.mysql.com/doc/refman/5.1/...ge-engine.html en n'oubliant pas de créer un index
2. Import des données du CSV dans la table MEMORY avec LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html
3. A 1re vue un UPDATE multi-table tel que celui ci-dessous devrait être pas mal :
Code:

1 2 3 UPDATE protein INNER JOIN table_memory ON protein.acces_number = table_memory.acces_id SET protein.id_uniprot = table_memory.id_uniprot, protein.go = table_memory.go
Un retour sera apprécié ;)
26/04/2009, 18h22
tomaprice
Citation:
Envoyé par Séb.

Pardon, je me rends compte que j'ai été bcp trop succinct :roll:

Puisque tu veux gagner du temps et étant donné les volumes il va falloir essayé de travailler avec le minimum d'intermédiaires, le cas échéant en essayant de shunter PHP.
Et si la RAM le permet (mais ça risque d'être difficile...) en évitant de travailler avec le disque dur (accès disques sont lents).

Ce que j'essaierai de faire :
1. Création d'une table de type MEMORY http://dev.mysql.com/doc/refman/5.1/...ate-table.html http://dev.mysql.com/doc/refman/5.1/...ge-engine.html en n'oubliant pas de créer un index
2. Import des données du CSV dans la table MEMORY avec LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.1/en/load-data.html
3. A 1re vue un UPDATE multi-table tel que celui ci-dessous devrait être pas mal :

Code:

1 2 3 UPDATE protein INNER JOIN table_memory ON protein.acces_number = table_memory.acces_id SET protein.id_uniprot = table_memory.id_uniprot, protein.go = table_memory.go

Un retour sera apprécié ;)
j'ai crée ma table de type memory en choisissant engine=Memory mais l'import du fichier csv pose Pb puisque j'ai le message "la table memory est pleine " et il y a 53928 enregistrement dans cette table, or elle devra en contenir 4.6 Millions soit la totalité du fichier csv :aie: ; je suppose que ce message indique que j'ai atteint la limite de la memoire alloué a phpmyadmin non ?
Dois je changer quelque chose dans un des fichier ini ?
26/04/2009, 18h25
andr386
Est-ce que les 600 000 entrées vont être modifiées a chaque fois ? Ou seulement une partie d'entre-elles ?

Sinon il faut faire quelque chose en plusieurs etapes
1. Re-ecrire le fichier uniprot.emblformat.parse dans un autre fichier qui ne contient que les 3 premiers champs (ou pas).
2. Charger ce fichier dans une table temporaire uniprot-temp avec load-data
3. Maintenant tu crees une table proteines-temp que tu remplis avec un une LEFT JOIN entre la table [b]proteine[b] et uniprot-temp sur acces_id. Comme ca la table proteines-temp contient l'ancienne ligne de proteines et a sa suite les informations du fichier si il y avait une update pour cette ligne dans le fichier.
4. Tu fais un update de proteines-temp WHERE l'access_id de uniprot-temp n'est pas null. En fait tu remplace les anciennes valeurs par les nouvelles, s'il y en a.
5. Tu peux dropper uniprot-temp, renomer proteines en proteine-history, et proteines-temp en proteines. Eventuellement supprimer les 3 colones supplementaires.
Ca a peut-etre l'air d'etre un peu tire par les cheveux, mais j'ai déjà du faire ce genre de raisonnement/processus et je gagnais beaucoup de temps ainsi.

La LEFT JOIN est une simple fusion : tres rapide. L'update est une simple boucle sequentielle.

La seule facon rapide de charger 200 mo c'est d'utiliser load-data.
26/04/2009, 18h32
tomaprice

Citation:

Envoyé par andr386

Est-ce que les 600 000 entrées vont être modifiées a chaque fois ? Ou seulement une partie d'entre-elles ?

La seule facon rapide de charger 200 mo c'est d'utiliser load-data.

je confirme je viens de tester load data c'est très rapide
sinon oui les 600 000 devront être mis a jour car en fait j'ai du creer 2 nouveaux champs dans ma table proteine et maintenant je dois les remplir, je pourrais bien faire une autre table appelé lien externe mais apres pou afficher les résultats dont j'ai besoin je serais obliger de faire une jointure entre la table proteine et liens externe et ca va etre assez long (600 000 *4 600 000) donc je prefere perdre du temps lors de la création.
26/04/2009, 18h38
Séb.

Citation:

Envoyé par tomaprice

j'ai crée ma table de type memory en choisissant engine=Memory mais l'import du fichier csv pose Pb puisque j'ai le message "la table memory est pleine " et il y a 53928 enregistrement dans cette table, or elle devra en contenir 4.6 Millions soit la totalité du fichier csv :aie: ; je suppose que ce message indique que j'ai atteint la limite de la memoire alloué a phpmyadmin non ?

Hum, il ne faut pas passer par pypMyAdmin, passe directement par le client MySQL.

Citation:

Dois je changer quelque chose dans un des fichier ini ?

Oui, à propos de la taille max :

Citation:

MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server creates on the fly while processing queries. However, the two types of tables differ in that MEMORY tables are not subject to storage conversion, whereas internal tables are:

* If an internal table becomes too large, the server automatically converts it to an on-disk table. The size limit is determined by the value of the tmp_table_size system variable.

* MEMORY tables are never converted to disk tables.

* The maximum size of MEMORY tables is limited by the max_heap_table_size system variable, which has a default value of 16MB. To have larger (or smaller) MEMORY tables, you must change the value of this variable. The value in effect at the time a MEMORY table is created is the value used for the life of the table. (If you use ALTER TABLE or TRUNCATE TABLE, the value in effect at that time becomes the new maximum size for the table. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value.) You can set the size for individual tables as described later in this section.

http://dev.mysql.com/doc/refman/5.1/...ge-engine.html
26/04/2009, 18h43
andr386
Ok, si tu mets entierement la table a jour c'est encore plus facile :
1. Charger le fichier uniprot.emblformat.parse dans la table uniprot avec load-data.
2. Creer une table proteines-temp avec tous les memes champs que proteins. Et remplir cette table en faisant un SELECT INTO d' une jointure entre proteines et uniprot sur access_id. Et la les pour les valeurs de de id_uniprot et go, tu utilises uniprot.id_uniprot et uniprot.go au lieu de celles de proteines. Ensuite tu peux renomer les tables pour que proteines-temp devienne proteines
As-tu tout compris ?
26/04/2009, 18h56
tomaprice

grrrr

:evilred: mais c'est pas vrai il y a toujours un Pb, Vos solutions a tous les deux je l'ai ai compris et en plus ca fonctionne mais la jointure sur l'acces_number ne marche pas car il y a un espace devant l'acces_number dans le fichier csv, je présise que ce fichier csv n'est pas de moi et qu'en gros on m'a dit tiens j'ai parsé ce fichier maintenant débrouille toi avec :lol: Il y a t-il l'equivalent de la fonction php trim en sql ? edit : Bon j'ai vu que cette fonction existait je vais donc essayer avec LTRIM
26/04/2009, 18h57
Séb.

Ouaip, tu as LTRIM( ) pour virer les blancs en début de chaîne.
(TRIM( ) existe aussi)
27/04/2009, 09h27
tomaprice

It Works

:yaisse2: Merci a tous ca marche, c'est rapide, et pour finir j'ai appris plein de nouvelle chose grace a vous .