Re^2: Efficiency on Perl Code!

In fact, if your statement handles need not overlap, you could re-use your $sth lexical for new statements, too.

That's the wrong kind of efficiency. Scalars are cheap. Execution plans are expensive. It would be better to connect to the database (there appears to be only one), prepare all the different queries into separate statement handles (possibly in an array), and then sit in a loop forever and spin on them (with a sleep at the end of the loop), as it looks like the code is monitoring a flow, rather than a single one-off affair.

A lot of the queries look very similar. I would be tempted to union them all together to avoid thunking between to the database and Perl too much. Get it all in one hit, and then pull it apart in Perl-space afterwards.

Instead of trimming the trailing blanks off in Perl, format the data correctly in the first place in the select so you don't have to fiddle with it afterwards.

Finally, are these statements returning more than one row? If they are returning only a single row, there are better ways of processing such result sets other than a while/fetchrow_hashref (which is just about the worst way of processing a result set).

• another intruder with the mooring in the heart of the Perl

Comment on Re^2: Efficiency on Perl Code!

Replies are listed 'Best First'.
Re^3: Efficiency on Perl Code! by mr_mischief (Monsignor) on Oct 05, 2007 at 15:50 UTC
I didn't mention reusing $sth for reasons of execution efficiency. Some people find it more straightforward to reuse a scalar for a particular kind of resource in non-overlapping situations. One could have a block scope around $sth and reuse the name as different lexical variables. One could simply reuse it. There could be an array of statement handles that's pushed and popped like a stack in some situations, or that treats the array as a set of statement handles that are all visible at once in other situations. It's not a matter of only having one scalar for Perl to keep track of, but of only having to keep track of as many scalars in one's own head as there are active handles of that type. If I can reuse $sth, I know the one to fetch from is $sth. If not, is it `$sth[23]` or `$sth[24]` that holds the particular statment handle from which I need to fetch in this line? I know some people don't think this way, but it's a decision that some people make. If you'd rather have a separate variable for each handle, go ahead. If it's easier for you to only keep track of as many as are actually in use, then I think that's a valid approach as well.	[reply] [d/l] [select]
A reply falls below the community's threshold of quality. You may see it by logging in.