Multi-Query-Statement

This is the permanent page dedicated to multi-query-statement: a Rails plugin that allows you to perform multiple statements, each optionally returning a resultset, with a single query to the database. Only MySQL is supported though. When the DB server is remote, performance gains should be quite noticeable as rails tends to generate quite a few statements. This might be particularly useful for pages that cannot easily take advantage of caching.

Links:

Motivation

This was really a personal challenge. I saw the number of queries that Rails was generating for each request and I wondered if it was possible to reduce their numbers. Of course, caching was the first thing that came to mind but sometimes caching is not always applicable, which may be the case with personal and very dynamic sites.

Installation

This plugin also has a home on rubyforge.org. So to install it just issue the following command from your Rails application directory:

   script/plugin install svn://rubyforge.org/var/svn/multi-stmt-qry

Usage

The plugin is rather easy to use but some thought must go into sequencing your statements correctly. Here is an example:


column_for_models(User, Albums, Song, Playlist, Review, Rating, Tag)

multi_statement_query do |q|
   q.statement { a = User.find_by_id(2) }
   q.statement { b = Playlist.find_by_name('romantic', :include => :songs) }
   q.statement { c = Review.find_by_user_id(2) }
   q.statement { c = Tag.calculate_tag_cloud }
end

The first statement will load the metadata for all of the models with a single query. That's seven queries reduced to one. The next piece of code combines four statements into one. The result will be the same as if the statements were executed sequentially but in reality they will be executed in parallel.

There is a catch. You as the programmer must make sure that:

  • There are no data dependencies between the statements inside a multi_statement_query block. This is to be expected as they are virtually executed in parallel.
  • If the code with a statement block generates more than one SQL statement then more than one query will be issued. Think of the plugin more as query minimization.
  • Be careful if your code uses continuations.
  • If a statement fails with a MySQL error then all statements that follow return nothing (a limitation of MySQL i think).

How Does it Work?

The implementation makes good use of a rare Ruby idom: continuations. It's a hack but nice one I think. There are no tests yet but I eventually might add some. Consider this alpha status.

Feel free to report bugs and potential ways to better this plugin.

Enjoy!