Comparing Fetching Database Query into Arrays and Arrayrefs

Since more and more real life problems are being automated by using computers, computer pograms are made to simplify our lifes. For some complex situation, complex database design is needed. In fact, in my company almost all our applications are storing into and retrieving from database intensively.

Sometimes the speed of retrieving data from database is the main issue in our applications, especially in creating reports. There are many ways to shorten your query time, from optimizing your SQL query string to upgrading the database system to a more sophisticated computer.

I have already found many articles in the Internet about optimizing your SQL query string. However, this time I am going to focus on using the right method from the database driver side which are used for accessing database. By using the right method, we can save some time.

As you may have known, in Perl we can use the DBI module to connect to our database and use the corresponding database driver to access a specific database application in object-oriented programming (OOP) style. For example, DBD::mysql and DBD::Pg are used for accessing MySQL and PostgreSQL database accordingly.

For the example below, I am assuming that you already know how to establish a connection to your database. Here is an example of OOP source code to retrieve some rows from database and print it to STDOUT.

my $sth=$main::dbh->prepare('SELECT name FROM product WHERE barcode LIKE \'899%\'');
$sth->execute() or die($sth->errstr);
while(my @row=$sth->fetchrow_array()) {
print $row[0],"\n";
}

Nothing's wrong with the source code above. It will work just fine. :)

The code starts with preparing the SQL query string, executing the query, and fetch the result rows into an array @row. I think this is the most often and widely used method to retrieve data.

When I first programmed using database, I wrote something similar to the code above almost all the time whenever I need to retrieve data from database. However, there is another method which can increase the speed up to 18% by changing only two lines. Amazing, isn't it?

Here are some slight changes I make:

my $sth=$main::dbh->prepare('SELECT name FROM product WHERE barcode LIKE \'899%\'');
$sth->execute() or die($sth->errstr);
while(my $row=$sth->fetchrow_arrayref()) {
print $$row[0],"\n";
}

In the method above, I use 'fetchrow_arrayref' instead of 'fetchrow_array'. This changes fetch the result rows into an array reference $row (instead of an array).

Surprisingly this small changes can really shorten data retrieval process. Do you wonder why? I think because when you use an array reference, you create a variable which refers to the value. On the other hand, when you use an array, you create a variable which is a copy of the original value. So, there are indeed 'hassles' in creating and dumping variables again and again. By using 'fetchrow_arrayref', we can avoid this thing.

If you then ask me how the fastest method will be, I believe that in some complex situation, SQL query string optimization still holds the biggest proportion. So, go learn SQL. :))