SQL Logging provides SQL debugging information for Rails applications. It is available as a plug-in for Rails 1.x and 2.x applications, and a gem for Rails 3.x and later.

In Rails 2.x, calling find runs the query immediately, but using a named scope results in lazy-loading: the query isn’t run until you first enumerate the results.

In Rails 3, you’re encouraged to move away from find and use the where, order, etc., methods. These all return an ActiveRecord::Relation object, which lazy-loads just like a named scope does in Rails 2.x.

With SQL Logging, you’ll see when queries are actually run, with a backtrace pinpointing the exact line of your code that triggered the execution. Additionally, it will show how many rows and approximately how many bytes are in the response.

Here is an example of what that looks like:

Hit Load (667.2ms) SELECT COALESCE(client_version, '?') AS client_version, DATE(created_at AT TIME ZONE 'UTC') AS date, COUNT(DISTINCT device) AS total FROM "hits" WHERE (created_at AT TIME ZONE 'UTC' >= '2010-12-14 11:01:41.906871') GROUP BY client_version, date ORDER BY date
2 rows, 80 Bytes
app/controllers/dashboard_controller.rb:20:in `split_series'
app/controllers/dashboard_controller.rb:10:in `index'


At the end of every action, a “top 10” list of queries is shown, allowing you to focus on expensive queries and improve the overall performance of that action. It looks like this:

SQL Logging: 8 statements executed, returning 272 Bytes
Top 10 SQL executions:
Executed 1 time in 667.4ms (667.4/667.4/667.4ms min/median/max), returning 2 rows (80 Bytes):
Hit Load
First exec was: SELECT COALESCE(client_version, '?') AS client_version, DATE(created_at AT TIME ZONE 'UTC') AS date, COUNT(DISTINCT device) AS total FROM "hits" WHERE (created_at AT TIME ZONE 'UTC' >= '2010-12-14 11:01:41.906871') GROUP BY client_version, date ORDER BY date
app/controllers/dashboard_controller.rb:20:in `split_series'
app/controllers/dashboard_controller.rb:10:in `index'
config/initializers/scrap.rb:56:in `call'
...


This list may be sorted by total execution time, median execution time, times executed, rows returned or bytes returned.

Please see the blog posts for installation instructions on Rails 2.x and Rails 3 and later.