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??

As suggested here you need to Benchmark - there are a range of options as noted.

As far as you code goes you database work is pretty ordinary. Given you are connecting to a DB and then executing sql you should cache as much as possible. Database connections, STH preparation all take time (lots of it) so you want to avoid as much as possible. You use our $dbh but don't use $dbh ||= connect so you are making a connection with every call to your main subroutine. It seems you are also not aware of ? bind placeholders. You also appear not either finishing your STHs or disconnecting from the DB. You should be doing something like this (broad brush):

package Main; my $dbh = connect..... my $sth_cache; $sth_cache->{query1} = $dbh->prepare_cached('SELECT foo FROM bar WHERE + foo = ?'); $sth_cache->{query2} = $dbh->prepare_cached('SELECT foo1 FROM bar1 WHE +RE foo1 = ?'); END{ $sth_cache->{$_}->finish for keys %{$sth_cache}; $dbh->disconnect if $dbh; } #blah sub widget { $sth_cache->{query1}->execute($bind_value_for_foo_WHERE_clause); $res = sth->fetchall_arrayref(); # blah }

This will be faster. You have only given us an incomplete chunk of your code so it is hard to say what else you could do (other than loose a lot of the if/elsif as suggested). Note fetchall_arrayref is often several times faster than fetching in a loop. I would note that you are effectively generating a fetchall_array_ref in a loop which is a waste. As always YMMV and Benchmarking will show you the ONE TRUE WAY. The one true way is not always worth pursuing as THE ALMOST TRUE WAY THAT TOOK ME FAR LESS TIME TO FIND is ofen good enough to get the job done.

As a general DB thing if you have a WHERE foo = ... clause and a big DB and you don't have a primary key, unique index or a least an index on 'foo' then you are doing a linear search through EVERY record in that table. In a big table (say a million records) this will take seconds without and index (roughly 2-3 on the current top of the range Linux type Xeon/RaidX hardware). It will take microseconds with an index. If you are doing that you are wasting the power of the DB and might as well use a flat file....

Speaking of hardware you can almost never have enough memory for a DB or Perl. You can almost always spend memory and gain speed.

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print


In reply to Re: sloooowwwwww code! by tachyon
in thread sloooowwwwww code! by Anonymous Monk

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 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? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2021-11-30 07:24 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?