TheLazy.Dev
Portfolio GitHub

Blog

rails |ruby |activerecord |sql

Optimizing Active Record queries

kattyacuevas

Kattya Cuevas

Posted on April 6, 2022

Optimizing Active Record queries

Context: Some time ago, I worked on a project where I had to make a lot of reports. We had a lot of data, and most of the reports should be in the application's dashboard. The data was divided into more than one table, so I had to make queries joining many tables every time I wanted to generate a new report. That made our application slow and even sometimes broke.

In this article, I'll show you how I used Active Record to fix the app's problems and if there's a limit on how to customize Active Record queries.

The sample app I'll use will be a clone (in a base form) of Spotify or any music app. Let me explain how will be the tables relationship in our database.

DB Schema

All the songs belong to an artist and an album. We also have the users table who can vote or rate a song or album. The ratings table has a polymorphic relation to songs and albums.

NOTE: A polymorphic relation is how Rails associate with more than one table simultaneously without the need to create a foreign key with the name of the association.

Before Start

¿How will we know if these solutions are fast?

There are a lot of ways to analyze your code. In this article, I'll make with these 3 ways:

  • Elapsed time
  • Memory: space in the disk that the code occupies in execution
  • Iterations per second

To have these 3 ways to measure our code, we will use the Benchmark module inside Ruby and the gems benchmark-ips and benchmark-memory.

First Problem: Get the highest rating albums

Possible Solutions

First solution:
Using a combination of Active Record and Ruby methods:

Album.includes(:ratings).map { |album| album.ratings.length }.max
Enter fullscreen mode Exit fullscreen mode

Second solution:
Using only Active Record methods

Album.from(Album.select(
  "albums.*", "COUNT(ratings.id) AS rating_count"
).joins(:ratings).group("albums.id")).maximum("rating_count")
Enter fullscreen mode Exit fullscreen mode

Both return the same result: 20. What is the difference?

Let's analyze the first solution

The first part of this solution uses an Active Record method:

Album.includes(:ratings)
Enter fullscreen mode Exit fullscreen mode

This expression executes the following SQL code:

SELECT "albums".* FROM "albums"

SELECT "ratings".* FROM "ratings" WHERE "ratings"."ratingable_type" = "Album" 
AND "ratings"."ratingable_id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, ...)
Enter fullscreen mode Exit fullscreen mode

The more albums we have in our database, the longer the ranking list we have.

The second part is

.map { |album| album.ratings.length }.max
Enter fullscreen mode Exit fullscreen mode

The map method iterates over the result of the includes method we did before. Inside every album, we call all the ratings associated with that album, and we calculate (with Ruby) how many ratings have every album. Once we have the list with all the totals, with the max method of the Enumerators in Ruby, we can calculate the element with the max value inside the array.

Let's analyze the second solution

Let's break the solution in two parts, the first is inside the from method.

Album.select(
  "albums.*", "COUNT(ratings.id) AS rating_count"
).joins(:ratings).group("albums.id")
Enter fullscreen mode Exit fullscreen mode

This will execute the following SQL code:

SELECT albums.*, COUNT(ratings.id) AS rating_count
FROM "albums" INNER JOIN "ratings"
ON "ratings"."votable_type" = "Album" AND "ratings"."votable_id" = "albums"."id"
GROUP BY "albums"."id"
Enter fullscreen mode Exit fullscreen mode

Here we add the COUNT(rating.id) column with the alias rating_count to our query to count how many associated ratings have every album.

The second part is:

Album.from(...).maximum("rating_count")
Enter fullscreen mode Exit fullscreen mode

This will execute the following SQL code:

SELECT MAX(rating_count) FROM (...) subquery
Enter fullscreen mode Exit fullscreen mode

The first part of the query we explained becomes the subquery on which we will run another query. The query will get the maximum value of the rating_count column.

Let's compare the solutions

Elapsed Time
This is the code we will execute:

Benchmark.bmbm do |x|
  x.report("Active Record + Ruby code") { first_solution }
  x.report("Only Active Record") { second_solution }
end
Enter fullscreen mode Exit fullscreen mode

Note: I'm using the bmbm method to execute the reports twice, so none of the methods will be affected, only for being the first.

This is the result:

Rehearsal -------------------------------------------------------------
Active Record + Ruby code   5.554467   0.788396   6.342863 (  7.404660)
Only Active Record          0.003204   0.001894   0.005098 (  0.741973)
---------------------------------------------------- total: 6.347961sec

                                user     system      total        real
Active Record + Ruby code   5.042479   0.540590   5.583069 (  5.776371)
Only Active Record          0.001858   0.000419   0.002277 (  0.532489)
Enter fullscreen mode Exit fullscreen mode

If we analyze the result, there is a slight time difference between the two solutions, with the second one being faster.

Memory
This is the code we will execute:

Benchmark.memory do |x|
  x.report("Active Record + Ruby code") { first_solution }
  x.report("Only Active Record") { second_solution }
  x.compare!
end
Enter fullscreen mode Exit fullscreen mode

This is the result:

Calculating -------------------------------------
Active Record + Ruby code
                       585.804M memsize (     0.000  retained)
                         6.099M objects (     0.000  retained)
                        50.000  strings (     0.000  retained)
  Only Active Record    53.270k memsize (     0.000  retained)
                       511.000  objects (     0.000  retained)
                        50.000  strings (     0.000  retained)

Comparison:
  Only Active Record:      53270 allocated
Active Record + Ruby code:  585803866 allocated - 10996.88x more
Enter fullscreen mode Exit fullscreen mode

If we look at this result, we can say that the first solution uses 1.54 times more memory than the second solution.

Iterations per second
This is the code we will execute:

Benchmark.ips do |x|
  x.report("Active Record + Ruby code") { first_solution }
  x.report("Only Active Record") { second_solution }
  x.compare!
end
Enter fullscreen mode Exit fullscreen mode

This is the result:

Warming up --------------------------------------
Active Record + Ruby code
                         1.000  i/100ms
  Only Active Record     1.000  i/100ms
Calculating -------------------------------------
Active Record + Ruby code
                          0.159  (± 0.0%) i/s -      1.000  in   6.277377s
  Only Active Record     12.768  (± 7.8%) i/s -     64.000  in   5.032453s

Comparison:
  Only Active Record:       12.8 i/s
Active Record + Ruby code:        0.2 i/s - 80.15x  (± 0.00) slower
Enter fullscreen mode Exit fullscreen mode

As we see in the results, the first solution is 1.44 times faster than the second one.

Results of comparison

  • The second solution is faster than the first solution at elapsed time.
  • In terms of memory, the first solution uses 10996x times more memory than the second solution.
  • And on the number of iterations per second, the first solution is 80.15 times slower than the second solution.

So why is the second solution the best? Because we are doing only one call on the database, and the whole operation is done from the DB side.

Second problem: Top 10 songs with their artists that have the highest rating

Possible solutions

This time I have 3 solutions:

First solution

Using Active Record and Ruby

ratings = Rating
            .where(votable_type: "Song")
            .group(:votable_id).average(:vote)
            .sort_by { |r| -r[1] }.take(n).to_h

songs = Song.includes(:artists).find(ratings.keys)

ratings.map do |song_id, rating|
   song = songs.find { |song| song.id == song_id }
   {
       song: song.title,
       artist: song.artists.map(&:name).join(", "),
       rating_avg: rating.to_f
   }
end
Enter fullscreen mode Exit fullscreen mode

Second solution

Using only one query with a lot of Active Record methods

Song
  .includes(:artists).joins(:ratings)
  .select("songs.*, AVG(ratings.vote) as rating_avg")
  .group("songs.id").order("rating_avg DESC").limit(n)
  .map do |song|
    {
      song: song.title,
      artist: song.artists.map(&:name).join(", "),
      rating_avg: song.rating_avg
    }
  end
Enter fullscreen mode Exit fullscreen mode

Third solution

Using 2 queries with Active Record methods and Ruby code

ratings = Rating
            .select("ratings.votable_id, AVG(ratings.vote) as rating_avg")
            .where(votable_type: "Song")
            .group(:votable_id).order("rating_avg DESC")
            .limit(n)

Song
  .includes(:artists)
  .find(ratings.map(&:votable_id))
  .map do |song|
    {
      song: song.title,
      artist: song.artists.map(&:name).join(", "),
      rating_avg: ratings.find { |rating| rating.votable_id == song.id }.rating_avg
    }
  end
Enter fullscreen mode Exit fullscreen mode

Let's analyze the first solution

To calculate the songs with the highest rating, I first search in the ratings table for the ratings related to the songs table.
Remember that the ratings table has a polymorphic relationship with songs, so we must look for those ratings whose votable_type is Song, so we will know which ratings are songs. Then we will group them by votable_id (song.id) to obtain the average of all the ratings.

Rating
  .where(votable_type: "Song")
  .group(:votable_id)
  .average(:vote)
Enter fullscreen mode Exit fullscreen mode

This expression will return an array of arrays with the following structure: [[votable_id, vote_avg], [votable_id, vote_avg]]. It has all the songs' ids and the average of their ratings.

It will sort them by the second element of the array in descending order, then take the first 'n' elements and convert it into a hash.

.sort_by { |r| -r[1] }.take(n).to_h
Enter fullscreen mode Exit fullscreen mode

The second part will get the songs of the hash we just generated, including the associated artists.

songs = Song.includes(:artists).find(ratings.keys)
Enter fullscreen mode Exit fullscreen mode

Lastly, we iterate over the hash of the ratings to generate the required data. In every iteration, we search for the song which belongs to the rating, and we build the hash to return as a result.

ratings.map do |song_id, rating|
   song = songs.find { |song| song.id == song_id }
   {
       song: song.title,
       artist: song.artists.map(&:name).join(", "),
       rating_avg: rating.to_f
   }
end
Enter fullscreen mode Exit fullscreen mode

Let's analyze the second solution

I tried to get the same result in this solution by doing only one query to the database.
First, I'll make a query over the joins of the tables songs and ratings, so I can have the data from both tables. I'll use the includes(:artists) method to call all the associated artists to the found songs.

Song.includes(:artists).joins(:ratings)
Enter fullscreen mode Exit fullscreen mode

Over the joins, we will ask for the average (in SQL) grouped by song.id, then sort them by rating_avg and at the end call only the first n records.

.select("songs.*, AVG(ratings.vote) as rating_avg")
.group("songs.id")
.order("rating_avg DESC")
.limit(n)
Enter fullscreen mode Exit fullscreen mode

Finally, we will iterate over the result to build the hash we should return as a result

.map do |song|
  {
    song: song.title,
    artist: song.artists.map(&:name).join(", "),
    rating_avg: song.rating_avg
  }
end
Enter fullscreen mode Exit fullscreen mode

Let's analyze the third solution

We make something similar to the first solution, but we don't use the average method from ActiveRecord. Otherwise, we will do it by SQL using the select method. We call the group method to make the calculation for every song, then we filter the search to only songs with the where method, and lastly, we sort them and limit the result to the n first records

ratings = Rating
            .select("ratings.votable_id, AVG(ratings.vote) as rating_avg")
            .where(votable_type: "Song")
            .group(:votable_id)
            .order("rating_avg DESC")
            .limit(n)
Enter fullscreen mode Exit fullscreen mode

Then, we search the songs with those ratings we found, and we format them to result in the expected result:

Song
  .includes(:artists)
  .find(ratings.map(&:votable_id))
  .map do |song|
    {
      song: song.title,
      artist: song.artists.map(&:name).join(", "),
      rating_avg: ratings.find { |rating| rating.votable_id == song.id }.rating_avg
    }
  end
Enter fullscreen mode Exit fullscreen mode

Let's compare the solutions

Elapsed Time

Rehearsal ----------------------------------------------------------------
Active Record + Ruby code      0.787713   0.196014   0.983727 (  1.645864)
Only Active Record             0.016700   0.002986   0.019686 (  0.567646)
Active Record + Ruby code v2   0.024173   0.000953   0.025126 (  0.403639)
------------------------------------------------------- total: 1.028539sec

                                   user     system      total        real
Active Record + Ruby code      0.595617   0.026087   0.621704 (  1.016591)
Only Active Record             0.008948   0.000577   0.009525 (  0.580103)
Active Record + Ruby code v2   0.002977   0.000137   0.003114 (  0.378619)
Enter fullscreen mode Exit fullscreen mode

Memory

Calculating -------------------------------------
Active Record + Ruby code
                       175.136M memsize (     0.000  retained)
                         2.422M objects (     0.000  retained)
                        50.000  strings (     0.000  retained)
  Only Active Record   303.235k memsize (     0.000  retained)
                         3.425k objects (     0.000  retained)
                        50.000  strings (     0.000  retained)
Active Record + Ruby code v2
                       347.840k memsize (     0.000  retained)
                         3.615k objects (     0.000  retained)
                        50.000  strings (     0.000  retained)

Comparison:
  Only Active Record:     303235 allocated
Active Record + Ruby code v2:     347840 allocated - 1.15x more
Active Record + Ruby code:  175135967 allocated - 577.56x more
Enter fullscreen mode Exit fullscreen mode

Iterations per second

Warming up --------------------------------------
Active Record + Ruby code
                         1.000  i/100ms
  Only Active Record     1.000  i/100ms
Active Record + Ruby code v2
                         1.000  i/100ms
Calculating -------------------------------------
Active Record + Ruby code
                          0.947  (± 0.0%) i/s -      5.000  in   5.294872s
  Only Active Record      1.867  (± 0.0%) i/s -     10.000  in   5.357158s
Active Record + Ruby code v2
                          2.646  (± 0.0%) i/s -     14.000  in   5.292725s

Comparison:
Active Record + Ruby code v2:        2.6 i/s
  Only Active Record:        1.9 i/s - 1.42x  (± 0.00) slower
Active Record + Ruby code:        0.9 i/s - 2.79x  (± 0.00) slower
Enter fullscreen mode Exit fullscreen mode

Results of comparison

  • The elapsed time of the third solution is faster than the two first solutions.The first solution is slower than the other two.
  • The third solution uses 1.15 times more memory than the second solution, and the first solution uses 557 times more memory than the second solution.
  • And on the number of iterations per second, the second solution is 1.42 times slower than the third solution and the first solution 2.79 times slower than the third solution.

In conclusion, the third solution is the best, and the first solution is the least optimal. Why is the solution using pure Active Record the least optimal this time? Because the query we are doing makes joins of two tables, this has an optimization cost, as we can observe.

Third Problem: Top "N" songs with the highest rating and pass as parameters if you want their artists and albums

Possible Solutions:

  • Build the result by calling different methods based on the parameters.
  • Using SQL y Materialized views.

SQL Views

  • They are virtual tables created from a SELECT query that usually joins multiple tables.
  • Each time they are called, the query they have been defined will be executed.

Materialized views

  • They are also virtual tables created from a SELECT query that normally joins multiple tables.
  • They store the query results in this virtual table. They are read-only.
  • They can be updated whenever you need them.

Let's compare the solutions

I'm using the best solution from the previous problem to compare with the SQL and Materialized views.

Elapsed Time

Rehearsal -------------------------------------------------------------
Active Record + Ruby code   0.096419   0.036263   0.132682 (  0.598458)
SQL View                    0.000640   0.000259   0.000899 (  0.001701)
Materialized View           0.000671   0.000172   0.000843 (  0.001408)
---------------------------------------------------- total: 0.134424sec

                                user     system      total        real
Active Record + Ruby code   0.008866   0.001029   0.009895 (  0.382414)
SQL View                    0.000122   0.000003   0.000125 (  0.000121)
Materialized View           0.000100   0.000000   0.000100 (  0.000098)
Enter fullscreen mode Exit fullscreen mode

Memory

Calculating -------------------------------------
Active Record + Ruby code
                       347.036k memsize (     0.000  retained)
                         3.611k objects (     0.000  retained)
                        50.000  strings (     0.000  retained)
            SQL View     1.992k memsize (     0.000  retained)
                        29.000  objects (     0.000  retained)
                         1.000  strings (     0.000  retained)
   Materialized View     1.992k memsize (     0.000  retained)
                        29.000  objects (     0.000  retained)
                         1.000  strings (     0.000  retained)

Comparison:
            SQL View:       1992 allocated
   Materialized View:       1992 allocated - same
Active Record + Ruby code:     347036 allocated - 174.21x more
Enter fullscreen mode Exit fullscreen mode

Iterations per second

Warming up --------------------------------------
Active Record + Ruby code
                         1.000  i/100ms
            SQL View    10.036k i/100ms
   Materialized View    10.049k i/100ms
Calculating -------------------------------------
Active Record + Ruby code
                          2.652  (± 0.0%) i/s -     14.000  in   5.284182s
            SQL View     99.936k (± 1.1%) i/s -    501.800k in   5.021873s
   Materialized View    100.465k (± 0.7%) i/s -    502.450k in   5.001495s

Comparison:
   Materialized View:   100465.2 i/s
            SQL View:    99935.8 i/s - same-ish: difference falls within error
Active Record + Ruby code:        2.7 i/s - 37887.16x  (± 0.00) slower
Enter fullscreen mode Exit fullscreen mode

Results of comparison

  • The elapsed time of the second and third solution are faster than the first one.
  • The first solution uses 174.21 times more memory than the second and third solutions.
  • And on the number of iterations per second, the first solution is 37887.16 times slowerd than the two first solutions.
  • In conclusion, the difference between the optimized solution from the previous problem and SQL and Materialized views is considerable.

Conclusions

  • The first problem showed us how to use Active Record to make it more powerful using SQL queries
  • The second problem shows us that it is OK to use Ruby to support Active Record.
  • The third one shows us that Active Record can also have a limit, and we can use SQL when needed.

Remember

  • If you will handle a considerable amount of data, try to get only the needed data. You can use the select and pluck methods from Active Record.
  • Sometimes, the Ruby methods are better than the Active Record ones.
  • Don't be afraid to use SQL with Active Record.
  • Use size or length instead of count if you don't want to make an extra query.
  • Avoid n + 1 queries with includes method.

I hope you enjoyed and learned something from this article. Thanks for reading! ❤️

💖 💪 🙅 🚩
kattyacuevas
Kattya Cuevas

Posted on April 6, 2022

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related

Optimizing Active Record queries
rails Optimizing Active Record queries

April 6, 2022