Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: Perl Programming guidlines/rules

by perrin (Chancellor)
on Nov 21, 2002 at 14:28 UTC ( [id://214773]=note: print w/replies, xml ) Need Help??


in reply to Perl Programming guidelines/rules

15. Always retrieve database rows into hash reference not arrays

A small tip: using bind_cols() is much faster, and the result is just as readable.

Replies are listed 'Best First'.
select * and bind_cols considered harmful
by blssu (Pilgrim) on Nov 22, 2002 at 15:21 UTC

    We've had terrible problems with bugs caused by developers using select * with bind_cols. Frequently the problem can not be reproduced on our development system. Table changes affect the order and/or number of columns, so the hard-coded variable list in bind_cols is a bug waiting to happen.

    Selecting explicit columns fixes the problem, but then the column list is redundant with the bound variable list -- bad (short, weird abbreviations, etc.) variable names can be used because it's "obvious" what the variable holds.

    I like fetchrow_hashref because it's safe to use with select *, self-documents use of database columns in the code, and does not have annoying redundancy between variable names and column names. Hakkr was probably thinking of these advantages.

      Sorry, but I'm flabbergasted that anyone would ever use "select *" in a real program! That is the bug waiting to happen, not bind_cols.

      I think typing out the list of lexical variables is worth the extra speed. There is some data on the speed difference in Tim Bunce's talk.

      Of course, if speed makes no difference in your application then use whatever you like.

        I'd categorize them as different kinds of bugs. The select * with fetchrow_hashref can lead to performance problems, but the bind_cols can completely break things.

        During code reviews I tell people to select explicit columns and use fetchrow_hashref. I know people are sometimes going to break standards and ignore my advice. There are three options I know of: (1) give them standards that will probably keep working even if not perfectly followed, (2) audit all code for compliance, (3) fire people who write bad code.

        Option (1) is my pragmatic choice. What's yours?

      I've worked at a company who produced a large banking application (about 700 binaries and 200 dynamic libraries). We didn't have much coding standards, but among the few we had was "you shall never use 'select *'". Beside the problem mentioned that out of order applying of patches can produce different order of columns on tables, the other disadvantage of using 'select *' is that it's a performance hit. It means that for each time the query is compiled, the database has to do lookups and figure out what the columns will be.

      'select *' is just there for interactive usuage - applications should never use them.

      Abigail

      Well, select * is harmful anyway.

      If you've got a perl program, and I add an extra column, or many columns, to the table you're using, then, even though these new columns are completely unnecessary in your program, it still retrieves the values, taking database, network and memory to do so (especially if, for example, I've added a graphic LOB).

      You've got to name the keys of the hash to do anything useful with them, so you might as well name them in the select statement. And that way, if somebody adds extra columns, there are no side-effects. If the columns are renamed, you've got to bring your select statement into sync, but you've got to change your code anyway, to update the keys.

      (This is unless you're writing something which merely dumps the entire table out for a human to look at, without really caring what the column values are.)

      --
      Tommy
      Too stupid to live.
      Too stubborn to die.

      Redundant variable names are no excuse not to bind. Nor does SELECT * self-document at all, you have to go look at the following code to see which columns are really being asked for. And this being Perl, which has hashes and excellent text munging, there's no reason you need to be redundant either.

      I shall paraphrase some code from chromatic's DBI is ok article on Perl.com:

      sub bind_hash { my $table = shift; my %results; my $sql = do { local $" = ", "; "SELECT @_ FROM $table"; }; my $sth = $dbh->prepare($sql); $sth->execute(); $sth->bind_columns(map { \$results{$_} } @_); return (\%results, sub { $sth->fetch() }); } # ... later: my ($res, $fetch) = bind_hash users => qw(name email); while ($fetch->()) { print "$res->{name} >$res->{email}>\n"; }
      It's not optimal yet, but as you see, a little inventiveness goes a long way.

      Makeshifts last the longest.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://214773]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others browsing the Monastery: (6)
As of 2024-03-28 13:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found