Dear all

Starting a new thread from this one .

Wehn I originally wrote my code, I opened a DBD::mysql connection at the start of the script, and closed it at the end of the script. This worked just fine.

But then someday I updated perl, and I think my OS (from RH7.3 to RH9) and the DBD::mysql connection would consistently close prematurely.

I read in a user-group something along the lines of the instability of the connection, and how you need to reduce the 'time' between open and close

So I re-wrote my program so that it opened and closed every time it wanted to do something, and it works just fine.

But now, after having profiled my script, it's obvious that it takes up most of the time in my script!! (%10)

So my first step in optimisation is to reduce the number of times a new DBD::mysql connection is opened and closed, but I can't remember what the original problem was, and if the connection is unstable after one request, is it possible to do anything about this?

Here's an example of one function in my DB module: 

sub getNullEndIds{
    my $self = shift;
    my $dbh = DBI->connect($dbi,"monster_mirror","m1rr0r",{'RaiseError
+' => 1});
    my $sth = $dbh->prepare("SELECT id from high where end IS NULL");
    $sth->execute();
    my @id;
    while(my @dummy = $sth->fetchrow_array()){
        push(@id,$dummy[0]);
    }
    $sth->finish();
    $dbh->disconnect();
    return @id;
}

[download]
Every function just simply opens and closes WITHIN the function. My original code just opened a handle for the whole module, accessible from every function, but this was unstable

Cheers
Sam

In reply to DBD::mysql connections by seaver

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!
  • 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! —
  • 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:
    & &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.