pb d'optimisation pour traiter + de 30000 lignes issues d'une table MySQL

**zaboug** · 22/10/2008, 13h10

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** · 22/10/2008, 13h23

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

**zaboug** · 22/10/2008, 13h28

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?

**zaboug** · 22/10/2008, 13h32

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 !

**Arioch** · 22/10/2008, 13h25

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.

**djibril** · 22/10/2008, 13h29

Envoyé par 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.

Mauvaise manip, j'ai dû squizzer ton post

Envoyé par 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?

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** · 22/10/2008, 13h35

Envoyé par djibril

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** · 22/10/2008, 13h43

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** · 22/10/2008, 13h49

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.

pb d'optimisation pour traiter + de 30000 lignes issues d'une table MySQL

SGBD Perl

Vue hybride

Discussions similaires

Partager

Partager