Unemployed Developer

There are several database wrapper libraries out there for your favourite web development languages. For instance, there’s PDO for PHP and DBI for Perl. What do these do? Well they give some handy functions that encapsulate away a lot of the dirty details of database work you shouldn’t have to worry about. For example, these allow you to use any database backend of your choice (MySQL, Postgres, sqllite, etc) and connect with them easily. Instead of building a messy DSN connection string, pass a couple of arguments and you’ve got yourself a useful database handle to perform database operations with.

What makes these things especially good are features like the prepare function. Whenever a query is submitted to the database to run, a query analyzer is run in order to best determine the fastest way to execute it. That way if there’s an index on a column, that can be used rather than performing a rowscan on the entire table. When you’re dealing with big tables, this becomes an issue very quickly. So how does a prepare statement help? When you prepare a query, you do a pre-query analysis step on it and store it in memory. That way if you run it multiple times, you can skip the analysis step and run the query. If you’re iterating over 100,000 records performing the same UPDATE query, this prepared query saves you substantial time.

Example using DBI in Perl:

my $dbh = DBI->connect($data_source, $user, $pass, $driver);
my $upd_stmt = $dbh->prepare("UPDATE public.address SET bar = ? WHERE name = ?");
my $sel_stmt = $dbh->prepare("SELECT name, address FROM public.account");
$sel_stmt->execute();

while (my $row = $sel_stmt->fetchrow_hashref()) {
  $upd_stmt->execute($row->{'address'}, $row->{'name'});
}

In this instance, the prepare on the SELECT doesn’t save any time, as it is only used once. However, the prepare on that UPDATE will save a query analysis each time that query is executed.

You might be wondering what those question marks are doing in that prepared UPDATE query. Those are called bound variables. That allows you to pass the escaping of values to the database wrapper so it will make sure data is safe before inserting into the database. As you can see, it integrates really well with prepared statements as you can pass different values in each time the query is run.

Prepared statements and bound variables also exist in PDO, although the row fetching syntax is slightly different. In either case, using a database wrapper can save you time and let you worry about more important details than mucking with low level database operations. ORMs provide even more encapsulation than these simpler wrappers, but they also require more overhead and might be overkill for your needs. As always, do a little research to determine what best suits your purposes.