Re: DBI SQL Query Question

I don't see any problems with your code, but you could make it a bit more efficient with fetchrow_hashref().

$UniqueDay = "SELECT count(*) as COUNT, max(time) as MAX, min(time) as
+ MIN, attacksignature from np_data where month=? and day=? group by a
+ttacksignature as ATTACKSIG order by COUNT desc";

##Unique Events
$unique_events_sth = $db->prepare( $UniqueDay );
$unique_events_sth->execute($Month, $Day);

while ( my $unique = $unique_events_sth->fetchrow_hashref ) {
    push( @unique_array, $unique );
}
[download]

Comment on Re: DBI SQL Query Question Download Code

Replies are listed 'Best First'.
Re: Re: DBI SQL Query Question by runrig (Abbot) on Jan 11, 2002 at 23:00 UTC
How exactly is fetchrow_hashref more efficient? From the DBI docs: `Because of the extra work "fetchrow_hashref" and Perl have to perform, it is not as efficient as "fetchrow_arrayref" or "fetchrow_array".` [download] Also, you should not save the reference in an array, you may get bitten later. Also from the DBI docs: `Currently, a new hash reference is returned for each row. This will change in the future to return the same hash ref each time, so don't rely on the current behaviour.` [download] If you really need to save all the results in an array of hashes, use selectall_arrayref (this changed recently, selectall_hashref use to be the solution to this, but as of DBI 1.20 it now returns a hashref, not an array). If you want to prepare the statement, that's fine, the selectall_* methods will take a sql string or a statement handle as an argument, or you can use one of the new fetchall_* methods.	[reply] [d/l] [select]
Re: Re: Re: DBI SQL Query Question by ehdonhon (Curate) on Jan 11, 2002 at 23:35 UTC
How exactly is fetchrow_hashref more efficient? I didn't mean to slight anybody, or say that the posted way was bad. Please accept my appologies if it came out that way. You are correct about fetchrow_hashref being less efficient than fetchrow_array. But, the original code is taking the elements of the returned array, copying them into its own array, then creating a hash and copying the array into the hash element by element. I can't imagine that is faster than fetchrow_hashref (though I haven't benchmarked it, so maybe it is). It also is less maintainable later if the requirements from the database change since it requires modifying code in more locations. But I suppose that really doesn't have to much to do with execution efficency. Also, you should not save the reference in an array, you may get bitten later. Thanks. That's why I like this place, you learn something new every day.	[reply]