benchmark.rb

Benchmark.bm do|b|
  b.report("exclusion join ") do
    a = ""
    10_000.times do
      Element.find_by_sql(
        <<-SQL
          select e.* from elements e
          left outer join elements leafs
          on e.id = leafs.parent_id
          where leafs.parent_id is null
        SQL
      )   
    end 
  end 

  b.report("sub query ") do
    a = ""
    10_000.times do
      Element.find_by_sql(
        <<-SQL
          SELECT * FROM elements
          WHERE id NOT IN (
            SELECT parent_id FROM elements
            WHERE parent_id IS NOT NULL
          )
        SQL
      )   
    end 
  end 
end

# results:
#                    user     system      total        real
# exclusion join   2.440000   0.200000   2.640000 (  4.545996)
#      sub query   1.900000   0.180000   2.080000 (  3.591858)