At RailsConf 2007 DHH mentioned that Rails 2.0 would support query caching on the client side in order to speed up AR. I immediately thought to myself, “Huh? Why do it on the client side when the database server will handle that?”.

The answer is that ActiveRecord (AR) doesn’t support bind parameters. In fact, AR is downright deceptive in this regard, because it sure *looks* like it’s using bind parameters. Consider this simple example:

   orders = Order.find(
      :all,
      :conditions => 'name = ?', 'Daniel Berger'
   )

In DBI, that query would look something like this:

   ...
   sth = dbh.prepare('select * from order where name = ?')
   sth.execute('Daniel Berger')
   orders = sth.fetch
   ...

Anyone coming from a DBI background would look and the AR version and think that bind parameters were being used. But, in fact, AR is doing variable interpolation behind the scenes. This is not optimal for databases that support bind parameters. But first, a bit about Oracle and bind parameters. This mostly applies to PostgreSQL as well ¹.

When you send a query to an Oracle DB server a few things happen. First, the query is parsed to ensure that it’s formed properly. Then, Oracle determines the execution plan (aka ‘explain plan’). The execution plan is, in short, the strategy that the DB server forms in order to fetch the rows for the query, e.g. “do a full table scan on table X and an index scan on table Y”. Finally, it fetches the data.² Parsing the SQL and creating the execution plan is the most expensive portion of the operation.

Back to the part about why not supporting bind parameters is a bad idea. First, and foremost, is performance. With variable interpolation SQL is re-parsed and the execution plan is re-formed every time the query is run. By using bind parameters the execution plan is generated only *once* (and is stored in a query cache) since the strategy for fetching the rows doesn’t change, merely the particular column value that you happen to be looking for.

To prove my point, here’s some sample code that’s very similar to what I use in a production report. It grabs a list of telephone numbers from a plain text file and gets the necessary information based on that number. The first example uses variable interpolation, the second uses bind parameters. Benchmarks then follow. Column and table names changed to protect the innocent:

   # Variable Interpolation
   IO.foreach('numbers.txt'){ |tn|
      sth = dbh.prepare("
         select so.order_number, so.order_id, so.service_number
         from some_order so, network nh, service se
         where se.service_id = so.service_id
         and nh.child_link = se.child_link(+)
         and nh.telephone_number = '#{tn}'
      ")
      sth.execute
      info = sth.fetch
   }

   # Bind parameters
   sth = dbh.prepare("
      select so.order_number, so.order_id, so.service_number
      from some_order so, network nh, service se
      where se.service_id = so.service_id
      and nh.child_link = se.child_link(+)
      and nh.telephone_number = ?
   ")

   IO.foreach('numbers.txt'){ |tn|
      sth.execute(tn)
      info = sth.fetch
   }

When I ran this against our reports database against 10,000 telephone numbers, the first example took approximately 3:50 in repeated runs, while the second example took approximately 1:45 in repeated runs.³ This example is typical and, in fact, most of my reports consist of nested cursors and much more complex SQL. In those cases the performance difference is even more significant.

In addition bind parameters are also a better defense against SQL injection attacks. AR protects you via quoting. It also cuts down on CPU cycles being used by the DB server, which the DBA’s will appreciate. Lastly, bind parameters are *absolutely necessary* for getting at binary data. This point is crucial, because it’s a problem that caching queries on the client side won’t solve.

What can we do about it? I believe Izumi ⁴ has the best idea - refactor the AR design to support actual bind parameters for those vendors that support them, and fake the rest with quoting to make the interface seamless. When necessary, disable automatic binding for those situations where they aren’t ideal. ⁵ In the case of MySQL, that would probably be often, since bind parameters actually seem to slow down many queries. Furthermore, I’ve been told that the MySQL query cache is cleared every time an INSERT or UPDATE occurs - not very useful.

His suggestion has been submitted, but the adapters still need work. Please take a look.

One final Oracle-specific note. Oracle has the concept of “hints”, little bits of meta information you can embed in the sql directly in order to alter the way Oracle generates its explain plan and/or to change the behavior of the results, e.g. PARALLEL to take advantage of multiple cpu architectures and FIRST_ROWS to optimize for immediate results.⁶. I’d like to see the Oracle adapter support this, and it would be easier to integrate if we adopt Izumi’s architecture.

See you next Wednesday.⁷

¹ I’ll talk about MySQL later. I am not familiar enough with DB2 or SQLServer to comment, but my hunch is that they support bind parameters as well.

² For a more in depth explanation of what’s going on behind the scenes, please see Luca Mearelli’s excellent article at http://www.oracle.com/technology/pub/articles/mearelli-optimizing-oracle-rails.html

³ For those wondering, I actually used the NOCACHE hint in the SQL to make sure the DB server wasn’t using a pre-existing cache.

⁴ http://izumi.plan99.net/blog/ - scroll down a bit for the SVG. Don’t use IE.

⁵ Such as histograms and ‘like’ queries.

⁶ Tools like TOAD use this so they can display a small result set immediately before all the rows are actually fetched. This would be useful for pagination.

⁷ On a final note I would like to thank Tim Bunce of (Perl fame) for his DBI book that taught me some basic knowledge of bind parameters back when I was a fledgling Perl programmer.