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. :))

if-else

While some programmers emphasize writing codes which have more readability, I prefer writing codes which are much much more efficient. Why? Because it makes your program run faster and 'smoother' and it also shows your quality as a programmer.

If you have done programming work before, I am sure you have used the if-else statement. However, I really think that if-else statement is sometimes inefficient. For some simple cases (like the one I am about to demonstrate below), if-else statement are found to be less efficient.

A very simple example of if-else statement:

my $x=0;
my $string='';

if ($x > 10) {
$string='bigger than ten';
} else {
$string='lesser than or equal to ten';
}

Note: this code is used just for demonstration purpose only. It's almost impossible that you will need exactly the same code in your program :)

The simple task above can also be done in one simple line by using the ?: operator.

my $x=0;
my $string='';

$string=($x > 10)? 'bigger than ten' : 'lesser than or equal to ten';

I used the Benchmark module to compare code efficiency for each method. In my computer, the ?: operator is 13% faster than the if-else method. Not only does it shorten your program runtime, but also shortens your time typing your program code. :)

Below is my favorite, by using 'and' and 'or'. Unfortunately I seldom see it being used. However, I use it quite often in my programs.

my $x=0;
my $string='';

($x > 10 and $string='bigger than ten') or $string='lesser than or equal to ten';

At first, it may seem strange and unusual, but wait until you get used to it, you will love it! :) By using the benchmark module, in my computer I found this 'and/or' method to be 29% faster than the if-else method.

As you can see, the 'and/or' method is the fastest to do the simple task. However, if you don't like it or find it less readable, I still recommend the ?: operator. Avoid using if-else statement for such an easy and simple task. The ?: operator is better and the 'and/or' method is the best in my opinion. :)