Discussion :

[zaboug]

Bonjour,


Je m'explique : j'ai une table contenant 66000 lignes environs. je dois extraire 2 champs pour toutes les lignes de cette table et les écrire dans un fichier texte.

voici mon code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
##Ouverture en écriture du fichier de sortie
	my $nom_fichier = "dico_aliases_".$espece.".txt";
	my $fichier_sortie="/Users/admin/Scripts_math/text_mining/".$nom_fichier;
	open(ALIAS,">$fichier_sortie") or die "Impossible de creer le fichier $fichier_sortie\n";
 
	#construction du nom de la table contenant les genes et aliases dans la BD
	$table = $espece."_transcript_go";
 
	#requete récupération des aliases de tous les genes product
	my $requete="SELECT DISTINCT gene_product, gene_product_aliases FROM $table;";
 
	#prépare la requête sql
	my  $res = $dbconnect_loc->prepare($requete);
 
	#exécution de la requête sql
	$res-> execute() || die "pb de selection : $DBI::errstr";
 
	print (ALIAS "Genes\t||\tAliases \t|\n");
 
	while ((@aliases = $res -> fetchrow_array))
	{
		if ($aliases[1] ne "")
		{
			print (ALIAS $aliases[0]."\t||\t".$aliases[1]."\t|\n");
		}
		else
		{
			print (ALIAS $aliases[0]."\t|||\n");
		}
	}
 
	close (ALIAS);
 
	#spécifie la fin de la requête
	$res-> finish();
 
	#déconnection à la base de données
	$dbconnect_loc->disconnect();

Je ne sais pas comment faire pour optimiser le temps de génération du fichier (d'autant plus que je dois le faire 3 fois sur trois tables différentes d'environ la même taille...).

Pensez vous que ça puisse se faire en perl, ou moitier en perl moitié en SQL peut être... j'avoue ne pas avoir d'idées...

la première extraction de 34 468 lignes a pris environ 35min. Est ce qu'il y a un moyen de diminuer ce temps sachant que la prochaine table sera de 66 483 puis de 74 934?

EDIT : la première extraction était en fait incomplète : le fichier final ne comportait que 17301...

34000 lignes en 35 minutes ???

[djibril]

Essaye ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
##Ouverture en écriture du fichier de sortie
	my $nom_fichier = "dico_aliases_".$espece.".txt";
	my $fichier_sortie="/Users/admin/Scripts_math/text_mining/".$nom_fichier;
	open(ALIAS,">$fichier_sortie") or die "Impossible de creer le fichier $fichier_sortie\n";
 
	#construction du nom de la table contenant les genes et aliases dans la BD
	$table = $espece."_transcript_go";
 
	#requete récupération des aliases de tous les genes product
	my $requete="SELECT DISTINCT gene_product, gene_product_aliases FROM $table;";
 
	#prépare la requête sql
	my  $res = $dbconnect_loc->prepare($requete);
 
	#exécution de la requête sql
	$res->execute() || die "pb de selection : $DBI::errstr";
 
	print ALIAS "Genes\t||\tAliases \t|\n";
 
	while ( my ($gene_product, $gene_product_aliases) = $res->fetchrow_array ) {
		if ( defined $gene_product_aliases) {
			print ALIAS "$gene_product\t||\t$gene_product_aliases\t|\n";
		}
		else {
			print ALIAS "$gene_product\t|||\n";
		}
	}
 
	close (ALIAS);
 
	#spécifie la fin de la requête
	$res->finish();
 
	#déconnection à la base de données
	$dbconnect_loc->disconnect();

Sinon le code est OK. soit tu n'as pas assez de mémoire RAM. soit ça vient de ta base de données.

Pense aussi à mettre un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
use strict;
use warnings;

en début de script.

Tu devrais aussi penser à indexer ta table si le problème de lenteur persiste

[Arioch]

Mon message a été squizzé apparemment

Dans ce dernier, je te posais une question : vu qu'il me semble fort étrange qu'un fichier de 34000 lignes mette autant de temps à être alimenté à partir d'une base MySql, je te demandais s'il n'y avait pas de ralentissement en se connectant à cette BDD ?

Combien de temps mets ton programme pour s'y connecter ? Combien de temps la base mets avant de trouver le résultat à la ou les requète(s) SQL que tu lui envoies ?

Bref, perso je creuserais davantage du côté de la BDD plutôt que de ton code.

[zaboug]

j'ai pas mis le code entier, mais en début de script j'ai déjà :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
#!/usr/bin/perl -w
 
use strict;
use diagnostics;

Bon, je sens que le pb vient de MySQL du coup... Juste une dernière petite question avant que je passe sur l'autre forum : par indexer la table, tu parles du mettre un identifiant unique? si c'est le cas, alors ma table est déjà indexée. Sinon, c'est quoi la définition exacte de indexée pour une table MySQL?

[djibril]

Mon message a été squizzé apparemment

Dans ce dernier, je te posais une question : vu qu'il me semble fort étrange qu'un fichier de 34000 lignes mette autant de temps à être alimenté à partir d'une base MySql, je te demandais s'il n'y avait pas de ralentissement en se connectant à cette BDD ?

Combien de temps mets ton programme pour s'y connecter ? Combien de temps la base mets avant de trouver le résultat à la ou les requète(s) SQL que tu lui envoies ?

Bref, perso je creuserais davantage du côté de la BDD plutôt que de ton code.

Mauvaise manip, j'ai dû squizzer ton post

j'ai pas mis le code entier, mais en début de script j'ai déjà :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
#!/usr/bin/perl -w
 
use strict;
use diagnostics;

Bon, je sens que le pb vient de MySQL du coup... Juste une dernière petite question avant que je passe sur l'autre forum : par indexer la table, tu parles du mettre un identifiant unique? si c'est le cas, alors ma table est déjà indexée. Sinon, c'est quoi la définition exacte de indexée pour une table MySQL?

pour indexer un champ, voici ce que tu fais sur ta base:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE `TaTable` ADD INDEX ( `gene_product` );

[zaboug]

Arioch : je ne pense pas que la connexion à la BD soit longue car j'ai d'abord testé en limitant le nb de résultats à 1000. Le fichier était généré en moins d'une seconde...
En limitant à 2000, le temps passe à 24s tout compris (exécution de la fonction qui se connecte à la base, fait la requête, et écrit le fichier de résultats)
Puis en limitant à 4000, ça passe à un peu plus de 133s !

[zaboug]

pour indexer un champ, voici ce que tu fais sur ta base:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE `TaTable` ADD INDEX ( `gene_product` );

Ok, alors je pense pas pouvoir le faire : je travaille sur une Base de données qui est déjà en production et qui accessible en ligne. Je ne peux donc pas la modifier comme ça (enfin je pense pas)...

[Arioch]

Si je comprend bien, de ta bdd tu extirpes pour chaque enregistrement 2 champs uniquement que tu écris, ligne par ligne, dans un fichier txt.

Problème de RAM qui ferait que le temps de traitement s'allonge si considérablement ?

[zaboug]

Arioch, tu comprends bien, c'est exactement ce que je fais.

Pour la Ram c'est pas impossible, honnêtement je connais pas bien le matériel que j'utilise : je viens d'être embauchée
Mais le script, je le fais tourner directement sur le serveur où est la base de données. je sais juste que c'est un serveur Mac.

et je viens de retester le script sur la table entière (donc 34468 lignes) et il s'arrête (enfin il estime avoir finit puisqu'il rejette pas d'erreur) au bout de 17300 lignes seulement... donc environ à la moitié de la table.

[Arioch]

Question : je vois que tu utilises les pragmas qui vont bien , redirige-tu STDERR vers un fichier log ?

As-tu une trace de toute anomalie ou remarque que le compilateur de Perl pourrait éventuellement faire ?

Sinon, as tu moyen de poser ta question directement dans ta base de données ?

Si oui, crée toi un fichier log (exemple : tee totopouet.txt) depuis la ligne de commande MySql, pose ta requète et regarde déjà combien de temps la bdd mets pour constituer le fichier txt.

[zaboug]

oulà, excuse moi mais j'ai commencé à programmer en perl il y a 2-3 semaines et je comprends pas bien tes questions

je ne pense pas rediriger STDERR vers un fichier log puisque j'ignorais jusqu'à son existence ça correspond à quel type d'erreur et comment l'envoyer vers un fichier log?

pour le compilateur perl, j'utilise

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
use strict;
use diagnostics;

en début de code, donc générallement ça me renvoie toutes les erreurs de compilation, et là il n'y en a pas.

Sinon, as tu moyen de poser ta question directement dans ta base de données ?

Si oui, crée toi un fichier log (exemple : tee totopouet.txt) depuis la ligne de commande MySql, pose ta requète et regarde déjà combien de temps la bdd mets pour constituer le fichier txt.

euh, je comprends pas ce que tu veux dire là

[zaboug]

je viens de regarder comment est construite exactement la table sur laquelle je travaille :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
mysql> describe chicken_transcript_go;
+----------------------+-------------+------+-----+---------+-------+
| Field                | Type        | Null | Key | Default | Extra |
+----------------------+-------------+------+-----+---------+-------+
| Id                   | int(11)     | NO   |     | 0       |       |
| gene_product         | varchar(15) | YES  | MUL |         |       |
| description          | mediumtext  | YES  |     | NULL    |       |
| gi_ref               | int(11)     | YES  |     | 0       |       |
| refseq               | varchar(20) | YES  |     |         |       |
| chickenVirtualTag_id | int(11)     | YES  |     | 0       |       |
| gene_product_aliases | text        | YES  |     | NULL    |       |
+----------------------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

je ne connais pas la signification d'une clé MUL sous MySQL, est ce que ça peut correspondre à une indexation comme tu m'en parlais djibril?

EDIT : je viens de trouver dans la doc que MUL n'a rien à voir avec une indexation... oubliez ce post

[Arioch]

ok, procédons dans l'ordre alors

Rediriger le canal STDERR vers un fichier log permet de conserver une trace dans un fichier des éventuelles anomalies ou des éventuels problèmes rencontrés.

Au début de ton code, tu peux rajouter ceci par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

open(STDERR, ">traceprog.log") or die("pb pour créer le fichier trace : $!\n");

Si ce fichier se remplit durant le traitement du programme, ça signifie que Perl y aura écrit tous les trucs qui clochent qu'il aura pu toper.


Pour Mysql, ce que je te proposais, dans la mesure où il faut bien tester où ça coince, c'était d'écrire dans un fichier de sortie ce que MySql te retournerait si toi même posait la question en ligne de commande depuis MySql.

Exemple pour une table s'appelant TOTO :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
mysql> tee exportDATA.txt
mysql> SELECT DISTINCT gene_product, gene_product_aliases FROM TOTO;

Normalement, MySql va écrire le résultat directement dans le fichier txt créé par la commande SQL tee.

Mais pour celà, toute cette procédure t'oblige à te connecter en dur à la bdd

[zaboug]

ok, alors j'ai fait tout bien comme tu m'as dit

donc le fichier exportData renvoyé par MySQL (en l'espace de 0,08sec!) ne contient que 17300 lignes.

Je pensait qu'il en faisait 34000 et quelques car c'était le résultat de la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DISTINCT count(gene_product) FROM chicken_transcript_go;

je trouve quand même bizarre que je n'obtienne pas le même nombre de lignes que le résultat de count, non?

[Arioch]

D'après une petite recherche sur le net, il semblerait qu'un index de type MUL soit un index à clés multiples (MUL pour MULtiple)

Voir ici : http://lists.mysql.com/mysql/111893, il s'agirait de clés pouvant se trouver plusieurs fois dans ta table.

A mon avis, c'est peut être du côté de ta requète qu'il faut affiner les choses.

[zaboug]

yep, clairement...

Vive le travail sur des bases de données mal normalisées

M'enfin, merci pour tout en tous les cas, je vais tacher de creuser par là...

EDIT : au final, j'ai squizzé la partie perl : ça marche très bien en faisant avec la commande tee que tu m'as donné et c'était suffisant pour ce que je voulais faire. Je marque ce post comme étant résolu.

[djibril]

ok, alors j'ai fait tout bien comme tu m'as dit

donc le fichier exportData renvoyé par MySQL (en l'espace de 0,08sec!) ne contient que 17300 lignes.

Je pensait qu'il en faisait 34000 et quelques car c'était le résultat de la requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DISTINCT count(gene_product) FROM chicken_transcript_go;

je trouve quand même bizarre que je n'obtienne pas le même nombre de lignes que le résultat de count, non?

SELECT DISTINCT gene_product, gene_product_aliases FROM $table;

et

SELECT DISTINCT count(gene_product) FROM chicken_transcript_go;

c'est pas pareil

[zaboug]

au final, en refaisant la manip directement avec MySQL, j'avais le bon nombre de lignes et les deux correspandaient (le count et le nombre de lignes dans le fichier).

Mais après réflexion, oui tu as raison : les deux requêtes peuvent ne pas renvoyer le même nb de résultats parce que la première compte seulement les gene_product différents, alors que la seconde renvoie les couples de gene_products et gene_products_aliases différents. C'est bien ça?

Cela dit (et juste pour notre culture perso) comment tu ferais du coup pour vérifier que ta requête renvoie bien le bon nb de ligne et qu'elles sont toutes dans ton fichier?

[djibril]

au final, en refaisant la manip directement avec MySQL, j'avais le bon nombre de lignes et les deux correspandaient (le count et le nombre de lignes dans le fichier).

Mais après réflexion, oui tu as raison : les deux requêtes peuvent ne pas renvoyer le même nb de résultats parce que la première compte seulement les gene_product différents, alors que la seconde renvoie les couples de gene_products et gene_products_aliases différents. C'est bien ça?

oui c'est bien ça.

Cela dit (et juste pour notre culture perso) comment tu ferais du coup pour vérifier que ta requête renvoie bien le bon nb de ligne et qu'elles sont toutes dans ton fichier?

Pour connaitre le nombre de lignes renvoyées par ta requête, tu as une methode rows dans DBI

[zaboug]

yep, honte à moi ! je l'avais déjà utilisé...

merci pour tout. Mon problème est réglé et j'ai pu générer mes trois fichiers en moins de 2min (en tout )