Beefy Boxes and Bandwidth Generously Provided by pair Networks Cowboy Neal with Hat
Perl Monk, Perl Meditation
 
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
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 meditating upon the Monastery: (6)
As of 2014-04-18 02:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (460 votes), past polls