How to use undef in DBI sql query result collection

punch_card_don has asked for the wisdom of the Perl Monks concerning the following question:

Magnetic Monks, I've used 'undef' like this:

while ($data = $sth->fetchrow_array()) {
    $hash{$data} = undef;
}
[download]

But I was wondering if there's an opportunity to use it to improve the efficiency of queries like this:

$sql = 'SELECT col_1 FROM theTable';
.....
while ($data = $sty->fetchrow_array()) {
    push @my_list, $data;
}
[download]

$sql = 'SELECT col_1, col_2, col_3, col_4, col_5 FROM theTable';
.....
while (@data = $sty->fetchrow_array()) {
    $my_hash{$data[0]} = [ $data[1], $data[2], $data[3], $data[4] ];
}
[download]

Thanks.

Forget that fear of gravity,
Get a little savagery in your life.

Comment on How to use undef in DBI sql query result collection Select or Download Code

Replies are listed 'Best First'.
Re: How to use undef in DBI sql query result collection by dragonchild (Archbishop) on Feb 17, 2005 at 13:26 UTC
The most efficient way to pull data from a database, as per the DBI documentation, is a combination of bind_columns() and fetch(). I'm not quite sure what you're trying to do with undef, but I don't think it will help you any. If what you want is to pull the set of data from the database, it would be slightly (by not much) faster to do this: `# Error handling assumed. my $sql = " ... "; my $sth = $dbh->prepare_cached( $sql ); $sth->bind_columns( \(my ($foo)) ); my @data; while ($sth->fetch) { push @data, $foo; } $sth->finish;` [download] The reason is that Perl doesn't have to allocate and deallocate return values - the data is placed right into the scalar reference you passed bind_columns(). A similar method can be used for your HoA in the third snippet. You can, alternatively, do: `# Error handling assumed. my $sql = " ... "; my $sth = $dbh->prepare_cached( $sql ); my @stuff; $sth->bind_columns( \(my ($foo), @stuff[0..3]) ); my %data; while ($sth->fetch) { $data{$foo} = [ @stuff ]; } $sth->finish;` [download] Now, the alternatives I'm presenting won't double your throughput. We're talking, at best, maybe 5-10%, if that. Most applications will see a 0-2% improvement. The guarantee is that this isn't slower and it might be faster. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply] [d/l] [select]
Re: How to use undef in DBI sql query result collection by rdfield (Priest) on Feb 17, 2005 at 14:36 UTC
You're missing a `my`: `while (my @data = $sty->fetchrow_array()) {` [download] rdfield	[reply] [d/l] [select]
Re^2: How to use undef in DBI sql query result collection by dragonchild (Archbishop) on Feb 17, 2005 at 14:41 UTC
You're missing a my: How do you know? Snippets were shown, not necessarily running code. Maybe @data is scoped higher than the while-loop for other reasons. Maybe strict isn't being used in this code for various reasons. (Did you know that CGI doesn't use strict?) There are a ton of completely legitimate reasons why a my may have been left out. Commenting upon it without actually helping out the OP is a complete waste of my time, for having to read your reply. Being right, does not endow the right to be rude; politeness costs nothing. Being unknowing, is not the same as being stupid. Expressing a contrary opinion, whether to the individual or the group, is more often a sign of deeper thought than of cantankerous belligerence. Do not mistake your goals as the only goals; your opinion as the only opinion; your confidence as correctness. Saying you know better is not the same as explaining you know better.	[reply]