Refactoring - Making use of database functions

May 26, 2013

We’ve been doing some heavy performance optimization on the MoviePass application. As I was working on improving the performance of a users check-in, I noticed a method that was pulling in all the check-ins for the day and calculating the sum of the ticket prices.

The code looked like this

def self.cumulative_price_for_checkins_today
    updated_today.inject(0) { |sum, res| sum + (res.ticket_price || 0) }
  end

There would be nothing wrong with this method if we were not pulling in a bunch of ActiveRecord models. We are doing a lot of unnecessary work that our database could be doing for us. Let’s be honest any thing that we can move away from Ruby is usually a speed advantage for us.

MySQL provides us with a bunch of neat functions, but obviously the one we want to use is SUM. The SUM function takes the column name and optionally the DISTINCT keyword as arguments. The DISTINCT keyword will cause only unique values to be sumed.

Rails provides us with an easy way to do this. Simply call the sum method on an ActiveRecord::Relation class, while passing the column as a parameter and it will handle the rest.

So the performance refactoring of this method ended up looking like

def self.cumulative_price_for_checkins_today
    updated_today.sum(:ticket_price)
  end

This is a much better solution than what we had before. We could obviously do better by incrementing the cumulative price in a store such as redis after each movie ticket was redeemed. Then querying for the value within this method. That would remove the need for going to MySQL for the value, unless it was a failover.

Quick Fact: If you were to execute the SQL this method generates, MySQL would return NULL and not 0. Rails returns 0, if the result happens to be NULL. You can see this happening on line 5.

activerecord/lib/active_record/calculations.rb, line 296

 1 def type_cast_calculated_value(value, column, operation = nil)
 2     if value.is_a?(String) || value.nil?
 3       case operation.to_s.downcase
 4       when 'count' then value.to_i
 5       when 'sum'   then type_cast_using_column(value || '0',
 6         column)
 7       when 'avg' then value.try(:to_d)
 8       else type_cast_using_column(value, column)
 9       end
10     else
11       value
12     end
13   end

I will update this post after doing some benchmarks on the performance difference.