Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
      Specifically, is it more efficient to connect once

There is some "setup time" involved with connecting to a database so I'd recommend doing that only once. One of the techniques I use for this involves writing a module that acts as a singleton. Here is a simplified example of that:

package Customer::EmployeeDB; use DBI; my $instance = undef; sub new { if($instance) { return $instance; } else { bless { dbh=> undef } ,"Customer::EmployeeDB"; $self->{dbh}=DBI->connect ("DBI:mysql:database=employee","user +","secret") or die $DBI->errstr; $instance=$self; return $self; } } sub prepare { my ($self,$shift)=@_; my $sth=$self->{dbi}->prepare($sql) or die $self->{dbi}->errstr; return $sth; } 1;

An example of using that module:

| handwaving here use Customer::EmployeeDB; my $db = new Customer::EmployeeDB(); my $sth = $db->prepare("select * from employee order by surname"); $sth->execute(); | do something with it

As I said, that's an incredibly simplified version of the actual family of modules I crafted to do the work. I actually go one step further and create modules to encapsulate the queries against the database and those modules instantiate the module encapsulating the database.

Using the singleton behavior ensures that only one connection to the database is made within the entire script. When I profiled the resultant code I was amazed at how much time it took to connect to the database (the real world example was against Sybase, so your mileage may vary) relative to where time was spent elsewhere. The code I had inherited actually made multiple connections to the database and the client wondered why it ran so poorly.


Peter L. Berghold -- Unix Professional
Peter -at- Berghold -dot- Net; AOL IM redcowdawg Yahoo IM: blue_cowdawg

In reply to Re: Using a single database handle or multiple handles by blue_cowdawg
in thread Using a single database handle or multiple handles by krfoot

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



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • 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! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, 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, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • Outside of code tags, you may need to use entities for some characters:
            For:     Use:
    & &amp;
    < &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.
  • Log In?
    Username:
    Password:

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

    How do I use this? | Other CB clients
    Other Users?
    Others chilling in the Monastery: (12)
    As of 2014-12-19 13:44 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      Is guessing a good strategy for surviving in the IT business?





      Results (83 votes), past polls