Perl => Optimisation afin de limiter les requetes

À (at) Fri, 29 Jan 2010 04:55:15 +0100,
Mag <mag@laposte.net> écrivait (wrote):

je cherche a optimiser un peu un de mes vieux scripts.

J'ai donc un script qui mets a jour une base de donnée,
je prends un fichier logs, je le lis ligne par ligne et a chaque
ligne je fait une requete sql de type:

$query5 = "UPDATE $Table_Logs_Summary SET
mails_recus=(mails_recus+1),MO_Transfered=(MO_received+$taillemsg)
WHERE dom_id=$dom_id AND Date_Start="$datejour $heure:00:00" AND
Date_End="$datejour $heure:59:59"";

Cela marche bien mais maintenant cela fait beaucoup de requete.

J'aimerais faire en sorte qu'au lieu de faire des +1 a la base,
il initialise un compteur:
mails_reçu=0
MO_received=0
qu'il mette des id (le dom_id et les dates)
puis qu'il fasse les +1 en memoire.

A la fin, je recupere une sorte de base de donnée en memoire qui
peux ensuite mettre a jour la bdd sql avec 10x moins de requete

ex:

dom_id date_start date_end mails_recus MO_Transfered
1 29/01/2010 06:00:00 29/01/2010 06:59:59 105 2800
1 29/01/2010 07:00:00 29/01/2010 07:59:59 119 1200
2 29/01/2010 06:00:00 29/01/2010 06:59:59 84 900
2 29/01/2010 07:00:00 29/01/2010 07:59:59 37 184
etc

pour mettre a jour ce tableau d'exemple dans ma base, je fais en gros
345 requetes sql la ou 4 peuvent suffires

Sans plus de précision sur les volumes à traiter (tant côté log que
côté base de données), il est difficile de savoir ce qui serait le
plus efficace... Je supposerai donc que le stockage en mémoire est
plus efficace.

Si j'ai bien compris, chaque ligne du fichier de log décrit un mail
par sa taille et une date/heure et ce mail est associé à un dom_id. Et
dans la base, on retrouve pour chaque tranche d'une heure et pour
chaque dom_id, le nombre de mails et leur volume total.

Première remarque : dans la base, si date_end est systématiquement une
heure plus tard que date_start autant ne pas stocker cette
information (ni les minutes et secondes inutiles).

Pour faire le calcul en mémoire, on peut créér une table de hachage à
plusieurs niveaux : la clé de premier niveau est le dom_id, celle de
second niveau est la date, celle de troisième niveau est l'heure et la
clé de quatrième niveau permet de choisir le champ nb_mails ou volume.

En pseudo code Perl, cela donnerait :

my %data;
while (my ($dom_id, $date, $heure, $taille) = mail_suivant_du_log()) {
$data{$dom_id}{$date}{$heure}{nb_mails}++;
$data{$dom_id}{$date}{$heure}{volume} += $taille;
}

Il suffit ensuite de parcourir %data pour mettre à jour la
base. Toujours en pseudo code Perl :

foreach my $dom_id (keys %data} {
foreach my $date (keys %{$data{$dom_id}}) {
foreach my $heure (keys %{$data{$dom_id}{$date}}) {
my $nb_mails = $data{$dom_id}{$date}{$heure}{nb_mails};
my $volume = $data{$dom_id}{$date}{$heure}{volume};

# mettre à jour la base avec
# $dom_id, $date, $heure, $nb_mails et $volume
...
}
}
}

J'imagine que parfois c'est une mise à jour d'un enregistrement
existant et que, sinon, il faut créer un nouvel enregistrement...

--
Paul Gaborit - <http://perso.mines-albi.fr/~gaborit/>
Perl en français - <http://perl.mines-albi.fr/>

In article <4b625c25$0$1665$426a34cc@news.free.fr>,
Mag <mag@laposte.net> wrote:

Bonjour,

je cherche a optimiser un peu un de mes vieux scripts.

J'ai donc un script qui mets a jour une base de donnée,
je prends un fichier logs, je le lis ligne par ligne et a chaque
ligne je fait une requete sql de type:

$query5 = "UPDATE $Table_Logs_Summary SET
mails_recus=(mails_recus+1),MO_Transfered=(MO_received+$taillemsg) WHERE
dom_id=$dom_id AND Date_Start="$datejour $heure:00:00" AND
Date_End="$datejour $heure:59:59"";

Je suppose que tu utilises la gestion de bases de donnees internes a perl
(DBI).

Si c'est bien le cas, au lieu de faire des cochonneries dans ce genre,
apprend prepare/execute, ca va gagner monstrueusement.

e.g.,
$query = $db->prepare("UPDATE $Table_Logs_Summary SET mails_recus=(mails_recus+1),MO_Transfered=(MO_received+(?)) WHERE
dom_id=(?) AND Date_Start=(?) AND
Date_End=(?)";

$query->execute($taillemsg, $dom_id, "$datejour $heure:00:00", "$datejour $heure:59:59");

si c'est bien du DBI, l'autre acceleration possible, c'est d'envoyer tes
requetes par lot (desactiver l'auto-commit) et faire les choses a la main.
En regardant ce que ta base supporte cote transactions,
et de mettre plusieurs update dans la meme transaction... j'ai fait ca sur
du sqlite, et j'ai gagne un facteur 10.

Sur fr.comp.lang.perl, Marc Espie disait :

$query = $db->prepare("UPDATE $Table_Logs_Summary SET mails_recus > (mails_recus+1),MO_Transfered=(MO_received+(?)) WHERE dom_id=(?) AND
Date_Start=(?) AND Date_End=(?)";

Pourquoi mets-tu des paranthèses autour de tes placeholders ?

--
Le seul problème quand on garde l'esprit ouvert, c'est qu'on trouve
toujours quelqu'un qui tient absolument à y fourrer tout un tas de choses.
-- T. Pratchett

In article <20100131091044.27b0df4c.jogo@matabio.net>,
Jogo <jogo@matabio.net> wrote:

Sur fr.comp.lang.perl, Marc Espie disait :

$query = $db->prepare("UPDATE $Table_Logs_Summary SET mails_recus >> (mails_recus+1),MO_Transfered=(MO_received+(?)) WHERE dom_id=(?) AND
Date_Start=(?) AND Date_End=(?)";

Pourquoi mets-tu des paranthèses autour de tes placeholders ?

C'est juste parce que je les vois mieux comme ca.

Marc Espie wrote in message <hk3f5h$2e21$1@saria.nerim.net>:

$query = $db->prepare("UPDATE $Table_Logs_Summary SET mails_recus >>> (mails_recus+1),MO_Transfered=(MO_received+(?)) WHERE dom_id=(?) AND
Date_Start=(?) AND Date_End=(?)";

Pourquoi mets-tu des paranthèses autour de tes placeholders ?

C'est juste parce que je les vois mieux comme ca.

$query = $db->prepare(
"UPDATE $Table_Logs_Summary " .
"SET " .
"mails_recus = mails_recus + 1, " .
"MO_Transfered = MO_received + ? " .
"WHERE " .
"dom_id = ? AND " .
"Date_Start = ? AND " .
"Date_End = ?"
);

C'est pas plus lisible, comme ça ? On pourrait même utiliser un heredoc pour
éliminer encore les guillemets.

In article <4b654172$0$18421$426a74cc@news.free.fr>,
Nicolas George <nicolas$george@salle-s.org> wrote:

Marc Espie wrote in message <hk3f5h$2e21$1@saria.nerim.net>:

$query = $db->prepare("UPDATE $Table_Logs_Summary SET mails_recus >>>> (mails_recus+1),MO_Transfered=(MO_received+(?)) WHERE dom_id=(?) AND
Date_Start=(?) AND Date_End=(?)";

Pourquoi mets-tu des paranthèses autour de tes placeholders ?

C'est juste parce que je les vois mieux comme ca.

$query = $db->prepare(
"UPDATE $Table_Logs_Summary " .
"SET " .
"mails_recus = mails_recus + 1, " .
"MO_Transfered = MO_received + ? " .
"WHERE " .
"dom_id = ? AND " .
"Date_Start = ? AND " .
"Date_End = ?"
);

C'est pas plus lisible, comme ça ? On pourrait même utiliser un heredoc pour
éliminer encore les guillemets.

Oui, non, je sais pas. J'etais juste en train de reprendre l'exemple de
depart.

C'est tres rare que j'ecrive du code qui fabrique directement des requetes sql.
a l'arrivee, ca ressemble plutot a ca chez moi

sub prepare_inserter
{
my ($ins, $table, @cols) = @_;
$ins->{insert}->{$table} = $ins->prepare(
"INSERT OR REPLACE INTO $table (".
join(', ', @cols).
") VALUES (".
join(', ', map {'?'} @cols).")");
}

(et oui, c'est dans un contexte ou je maitrise suffisamment les noms des tables
et colonnes pour ne pas avoir a les quoter).