Hello there,

it’s late in Bremen, so I will keep this thing as short as possible: I just wasted the complete day’s work at Tansporter due to a bad preparation… dumb me! (Remember: Benchmark before any optimization!)

I tried to optimize some of our ActiveRecord find statements, installed a whole new column on a table solely for that reason, changed the model to fill this new column right and then changed the actual method I would like to spice up.

It was a 70 line monster of very ugly code. After refactoring it, there were just 40 lines left and it was very cool, clean and beautiful code. The main change: I replaced a find statement which returned a very huuuuge result set, which I had to iterate and do some computing on every element, with 4 very cool queries with just one result, each.

Sounds cool, but unfortunately it isn’t.

After I had a short “wow, that’s great stuff!” experience, I thought it might not be unclever to benchmark the new method and compare it to the old one.
And here comes the trouble: The old method finished in about 0.018 seconds, the new one needed somewhat between 0.54 and 0.4 seconds… What a pitty!

So I switched back to the ugly version, to my rescue the added column could be used elsewhere so my effort was not completely useless.

What I’ve learned: One query with a large result set apparently performs MUCH better than 4 (not absolute trivial) queries with a very small result set, even if you have to iterate through the whole big result set.

Anyway I stumbled upon two cool, small things today that are worth mentioning them:

1st: Get a random “thing” out of the database.

Until today we managed this with a method at the particular model looking somehow like this:

1
2
3
4
5
6
7
8
9
10

  def self.random_good
    good = nil
    while (!good)
      begin
        good = Good.find(rand(Good.maximum(:id)))
        rescue ActiveRecord::RecordNotFound => e
      end
    end
    good
  end

Today I received my copy of The Rails Way and one of the first things I found was the native, MySQL “ORDER BY RAND()” function. Pretty cool. Because database portability doesn’t really matter for us, the same function as above now looks like this:

1
2
3

  def self.random_good
    Good.find(:first, :order => 'RAND()')
  end

2nd: Don’t forget about the power of ActiveRecord’s :include option!

The :include option of a find statement is very powerful! I read about this maybe a year ago, never needed it again and forgot about it. Today I rediscovered the veeeeery nice nesting capability:

1
2

  tickets = Ticket.find(:all, :include => [:user,
      {:operator => {:open_tickets => :user}}])

I can’t really believe it, but after this find statement you can do this:

1

  tickets.first.operator.open_tickets.last.user.nickname

…without ANY extra query! It’s all joined together in the one find query and still runs at a reasonable pace.

Ok, now it’s really time for me to go to bed.

Stay tuned,

Thorben
FEtMab-Team

Related posts