Ruby on Rails: Benchmarking your ActiveRecord queries

09 Aug Ruby on Rails: Benchmarking your ActiveRecord queries

First of all, in some cases, the time spent in that query is really low so maybe, a good way to see if you can improve it is to compare that functionality with the same query after making some tweaks, such as lazy loading, using joins, etc.

So here is the query, I’m going to be using for benchmarking:

User.select("
  count(distinct(availabilities.id)) as tot_availabilities,
  count(distinct(events.id)) as tot_events,
  MAX(cast(extract(epoch from availabilities.updated_at) as integer)) as last_av_updated,
  users.id"
).
joins(:availability_schedules).
joins("left join events on users.id = events.doctor_id").
joins("left join availabilities on availabilities.availability_schedule_id = availability_schedules.id").
group("users.id")

Benchmark.measure do
    new_results = User.select("
      count(distinct(availabilities.id)) as tot_availabilities,
      count(distinct(events.id)) as tot_events,
      MAX(cast(extract(epoch from availabilities.updated_at) as integer)) as last_av_updated,
      users.id"
     ).
     joins(:availability_schedules).
     joins("left join events on users.id = events.doctor_id").
     joins("left join availabilities on availabilities.availability_schedule_id = availability_schedules.id").
     group("users.id")
end

The results were

=> #<Benchmark::Tms:0x007fc8524e40b8 @cstime=0.0, @cutime=0.0, @label="", @real=0.00017499993555247784, @stime=0.0, @total=0.0, @utime=0.0>
#<Benchmark::Tms:0x007fc8532a9900 @cstime=0.0, @cutime=0.0, @label="", @real=0.00043000001460313797, @stime=0.0, @total=0.0, @utime=0.0>
=> #<Benchmark::Tms:0x007fc8381fa718 @cstime=0.0, @cutime=0.0, @label="", @real=0.00021500000730156898, @stime=0.0, @total=0.0, @utime=0.0>
=> #<Benchmark::Tms:0x007fc83628b170 @cstime=0.0, @cutime=0.0, @label="", @real=0.00032599992118775845, @stime=0.0, @total=0.0, @utime=0.0>

And about memory, I’m using these methods for that(you can copy and paste them in your terminal and then start using them):
https://github.com/heridev/memory_activerecord_into_csv_benchmark/blob/master/benchmark_helpers.rb

print_memory_usage do
  print_time_spent do
    another_results = User.select("
      count(distinct(availabilities.id)) as tot_availabilities,
      count(distinct(events.id)) as tot_events,
      MAX(cast(extract(epoch from availabilities.updated_at) as integer)) as last_av_updated,
      users.id"
     ).
     joins(:availability_schedules).
     joins("left join events on users.id = events.doctor_id").
     joins("left join availabilities on availabilities.availability_schedule_id = availability_schedules.id").
     group("users.id")
  end
end

Results:

1. 
Time: 0.0
Memory: 0.02 MB

2.
Time: 0.0
Memory: 0.0 MB

3.
Time: 0.0
Memory: 0.06 MB

So that’s it, you can make some tweaks to the base query and compare the results, so based on that you have a clear path on what to improve/change.
NOTE: if you didn’t notice that I’m using a different variable in the different tests I did

another_results = User.select("

I think that it is important because the assignment of those results will require a little bit of memory space and time in some cases.

Let me know if that helped you out, Cheers

H.