When to use $sth->fetchrow_hashref, $sth->fetchrow_arrayref and $sth->fetchrow_array?

Question

I know that:

• $sth->fetchrow_hashref returns a hashref of the fetched row from database,
• $sth->fetchrow_arrayref returns an arrayref of the fetched row from database, and
• $sth->fetchrow_array returns an array of the fetched row from database.

But I want to know best practices about these. When should we use fetchrow_hashref and when should we use fetchrow_arrayref and when should we use fetchrow_array?

Answer 1

You could do worse than read DBI recipes by gmax.

It notes, among other things:

The problem arises when your result set, by mean of a JOIN, has one or more columns with the same name. In this case, an arrayref will report all the columns without even noticing that a problem was there, while a hashref will lose the additional columns

Answer 2

In general, I use fetchrow_hashref (I get around two columns with the same name issue by using alias in the SQL), but I fall back to fetch (AKA fetchrow_arrayref) if I need it to be faster. I believe that fetchrow_array is there for people who don't know how to work with references.

Answer 3

I don't use any of them since switching all of my DB code to use DBIx::Class.

Answer 4

DBI has to do more work to present the result as a hashref than it does as an arrayref or as an array. If the utmost in efficiency is an issue, you will more likely use the arrayref or array. Whether this is really measurable is perhaps more debatable.

There might be an even more marginal performance difference between the array and the arrayref.

If you will find it easier to refer to the columns by name, then use the hashref; if using numbers is OK, then either of the array notations is fine.

If the first thing you're going to do is return the value from the fetching function, or pass it onto some other function, then the references may be more sensible.

Overall, there isn't any strong reason to use one over the other. The gotcha highlighted by Ed Guiness can be decisive if you are not in charge of the SQL.

Answer 5

When I wrote YAORM for $work, I benchmarked all of these in our environment (MySQL) and found that arrayref performed the same as array, and hashref was much slower. So I agree, it is best to use array* whenever possible; it helps to sugar your application to know which column names it is dealing with. Also the fewer columns you fetch the better, so avoid SELECT * statements as much as you can - go directly for SELECT <just the field I want>.

But this only applies to enterprise applications. If you are doing something that is not time-critical, go for whichever form presents the data in a format you can most easily work with. Remember, until you start refining your application, efficiency is what is fastest for the programmer, not for the machine. It takes many millions of executions of your application to start saving more time than you spent writing the code.