Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

Slow results for a query using Win32::ODBC

by the_hawk_1 (Scribe)
on Jul 16, 2007 at 20:31 UTC ( #626916=perlquestion: print w/ replies, xml ) Need Help??
the_hawk_1 has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on Slow results for a query using Win32::ODBC
Download Code
Replies are listed 'Best First'.
Re: Slow results for a query using Win32::ODBC
by daxim (Chaplain) on Jul 17, 2007 at 00:42 UTC
      Sorry. I've added the code.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://626916]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (11)
As of 2015-07-31 07:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (275 votes), past polls