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();
        }
    }
}
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.