Hi fellow Monks!
I'm making reports with perl, using the Win32::ODBC module. Unfortunately, it is quite slow. When using my SQL query tool, running the query takes 2-3 minutes, but when running it with perl, it takes 30-60 minutes!
Anyone have an idea?
Sorry all, here's the code:
#!/usr/local/bin/perl
$requete_sql="
SELECT Service.Service_c as NomCampagne
FROM AODCallDetail JOIN Service ON AODCallDetail.Service_Id=Ser
+vice.Service_Id
WHERE Service.Available_f = 1 AND Service.Deleted = 0 AND Servi
+ce.Application_Id IN (11)
GROUP BY Service.Service_c
ORDER BY Service.Service_c
";
#obtention des noms de champs
undef %MotCle;
$NbMotCle=0;
$reqtemp=$requete_sql;
$reqtemp =~ s/\n//gis;
@tableau = split(/,|(from)/si, $reqtemp);
foreach $match(@tableau) {
if($match =~ /as /i) {
@phrase = split(/ /s,$match);
$MotCle[$NbMotCle++]=$phrase[scalar(@phrase)-1];
}
}
GetDate();
print "$heure:$min:$sec - débuté.\n";
StartDb();
GetDate();
print "$heure:$min:$sec - Terminée.\n";
## SUBS ############################################################
+#########
sub GetDate() {
($sec,$min,$heure,$jour,$mois,$annee,$jour_se,$jour_an,$ete) = loc
+altime( time );
$annee+=1900;
@mot=("Janvier","Février","Mars","Avril","Mai","Juin","Juillet","A
+oût","Septembre","Octobre","Novembre","Décembre");
$moismot=$mot[$mois];
$mois+=1;
if($mois<10) {$mois="0".$mois;}
if($jour<10) {$jour="0".$jour;}
if($heure<10) {$heure="0".$heure;}
if($min<10) {$min="0".$min;}
if($sec<10) {$sec="0".$sec;}
}
sub StartDb {
use Win32::ODBC;
undef %DetData;
undef $NoRec;
# Le nom de la source de donnée ODBC
$DSN = "sorry, I don't think you need this info...";
# Ovrir la connection avec la BD
if (!($db = new Win32::ODBC("dsn=$DSN"))){
# Sur erreur
print "\nError connecting to $DSN -- Error: " . Win32::ODBC::E
+rror() . "\n";
exit;
} else {
# Si la connection marche, on passe à la requête
GetDate();
print "$heure:$min:$sec - SQL lancée.\n";
# Lancement de la requête
if ($db->Sql($requete_sql)){
# Sur erreur de requête
print "SQL failed. Error: " . $db->Error() . "\n";
$db->Close();
exit;
} else {
# Sur résultat
GetDate();
print "$heure:$min:$sec - SQL reçu.\n";
# Écriture des données
$NoRec=0;
while($db->FetchRow()){
undef %Data;
%Data = $db->DataHash();
for($i=0;$i<scalar(@MotCle);$i++) {
$DetData[$NoRec][$i]=$Data{$MotCle[$i]};
}
$NoRec++;
}
$db->Close();
}
}
}