This plugin allows you to perform multiple statements, each optionally returning a resultset, with a single query to the database. Only MySQL is planned for now. When the DB server is remote, performance gains should be quite noticeable.

Here is an example of how we use the plugin

   columns_for_model(Person, Friends, Tags, Messages)

   a = b = nil
   multi_statement_query do |query|
      query.statement { a = User.find_by_id(2) }
      query.statement { b = User.find_by_first_name('eric') }
      query.statement { c = 8 }
   end

Installation

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

Limitations

• There must not be any data dependencies between the statements This also means that the columns must be known in advance as they also generate queries (there is a special method to quickly do this).
• If the code with a block generates more than one SQL statement then more than one query will be issued against MySQL.
• Be careful if the code for a statement uses continuations (quite rare).
• If a statement fails then all statements that follow return nothing (a limitation of MySQL i think).

Extras

• It plays nice with ActiveRecord caching, which means that if your statement block use the cache instead of querying the database then the query, as expected, is not executed on the DB.
• An easy to use method to pre-load metadata for all models at once.

How?

So how does all this work? To perform it’s magic, MultiStatement make ample use of continuations, which allows it to do really neat tricks with call stack.

Here is a pseudocode walkthough:

1. All calls to query.statement are recorded. Blocks are retained but not executed yet.
2. At the end of the multi-statement block (all calls to query.statement have been recorded) a call to gather_execute_then_process is made.
3. In the gather step, each query.statement block is executed until it reaches the call to @connection.query. At this point, the SQL statement is recorded as is the state of the call stack (in the form of a continuation). The block execution aborted at this point.
4. When all blocks have been processed as in (3) the SQL stamements are concatenated and executed against the database all at once (only MySQL is supported for now).
5. All results are now available. We call in turn each continuation with the result-set (if any) as the return-value (ActiveRecord cannot make the difference!). The effect is to continue the block execution that was aborted in (3).
6. If any of the continuations in (5) have requested additional calls to @connection.query then a new continuations and sql statements are recorded.
7. Go to step 4 until no more continuations are available.

As you can see, it’s possible to generate more than one call to the database when using multi statement queries but the number of calls will be as small as possible. Since most Active record methods generate only one query you should be able to group many statements easily. The only issue is with extra calls to ActiveRecord::Base#columns (show fields from). For this reason, we have an additional utility method which allows us to preload the columns for all specified models.

Enjoy!