Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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(); } } }

In reply to Slow results for a query using Win32::ODBC by the_hawk_1

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2024-03-28 20:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found