Make your Ruby on Rails app 80x faster with SQL
Andrei Kaleshka
Posted on May 25, 2023
SQL can improve the performance and efficiency of your Ruby on Rails application. No need for heavy technologies. No need for switching to another programming language or framework.
SQL is a powerful tool that can take your Ruby on Rails expertise to the next level. Nowadays many technologies claim to replace SQL or deem Ruby on Rails as “too slow”. But it remains a popular and efficient choice for many web projects. This article will explore the importance of SQL for Ruby on Rails experts. It will show why it’s a must-have skill in today’s development landscape.
Why Ruby on Rails can be slow
Imagine the following class that’s used in a Rails controller to feed data to a page in your application. This class may look familiar to you. I’ve encountered similar classes in real-world applications:
class ServicesStatsQuery
attr_accessor :user
def initialize(user)
self.user = user
end
def call
projects_full = []
projects_empty = []
Service.where(user: user, status: "approved", active: true)
.order(category_id: :asc).each do |service|
ratings_average = 0
ratings_count = 0
ratings_total = 0
Rating.where(reviewee: user).each do |rating|
project = Project.find(rating.project_id)
if project.category_id == service.category_id
ratings_count += 1
ratings_total += rating.rating
end
end
ratings_average = (ratings_total / ratings_count.to_f).round(1).to_s if ratings_count != 0 && ratings_total != 0
completed_projects_count =
Project.where(vendor: user, status: "Complete", category_id: service.category_id).size
service_hash = {
category_id: service.category_id,
category_name: Category.find(Category.find(service.category_id).parent_id).name,
subcategory_name: Category.find(service.category_id).name,
completed_projects_count: completed_projects_count,
ratings_count: ratings_count,
ratings_average: ratings_average,
}
resolved_hash = service_hash.transform_keys{ |k| k.to_s.camelize(:lower) }
if completed_projects_count > 0
projects_full.push(resolved_hash)
else
projects_empty.push(resolved_hash)
end
end
projects_full.sort_by!{ |k| -k["completedProjectsCount"] }
projects_full.push(*projects_empty)
projects_full
end
end
Your application is running in production and serving the needs of hundreds of users. You’ve noticed that the related page is becoming slower and slower with each passing day. This can be a frustrating problem to solve. It’s natural to feel tempted to consider drastic measures. You can think of switching to a faster programming language. Or you might plan to use heavy technology.
Before taking such drastic steps take a step back and understand the root cause of the issue.
There are a few things that could be contributing to the slowness of this code:
N+1 queries: The code is making a separate database query for each rating. Then it gets the associated project. That can become slow if there are a large number of ratings.
Nested
eachloops: The code is using this method to iterate over the Service. For each service in the nested loop, it iterates through all related Rating records. That can also be slow if there are many records. Instead, it would be better to use SQL joins to fetch all the data in a single query.Redundant queries: The code is calling
Category.findmany times. Instead, better to fetch all the category data using a single query. And then use it to populate theservice_hashobject.
Optimizing this code will need a combination of SQL query optimization. That should cut unnecessary database queries and improve the performance of the code.
How to analyze slow Ruby On Rails code
There are plenty of tools out there, like bullet, benchmark-ips, or any others.
But before taking these radical steps use what you already have. Open logs (use command tail -f log/development.log for local). Observe what happens there while you open the slow page in your browser. If you spot many repeating lines like this one you have an N+1 problem:
00:03:22 web.1 | Rating Load (1.6ms) SELECT "ratings".* FROM "ratings" WHERE "ratings"."user_id" = $1 [["user_id", 15]]
Pay attention to the phrase “Rating Load” as it serves as an indicator. If you notice “CACHE Rating Load”, there’s no need to worry. The object is already in memory, retrieved from there without accessing the database. This suggests that eager loading is already working.
Another tool is to visually inspect the code. You can feel discomfort and uncertainty when trying to understand how the code pieces relate to each other. Yet, this is not related to performance. The most crucial aspect is that the code contains a nested loop, which indicates that its complexity is squared. As more objects are iterated, a squared function grows rapidly:
Sorry for getting into the math, but it’s important to understand that your code should ideally grow linearly or at least logarithmically. This is the best way to ensure that your code’s performance remains optimal for many years to come.
If you are unsure about what all of that means, now is a good time to start learning about the theory of algorithms and data structures.
Use SQL to improve Rails application performance
To fetch all the required data in a single query while performing the necessary aggregation, sorting, and grouping, consider using this SQL query:
select
s.category_id as "categoryId",
pc.name as "categoryName",
c.name as "subcategoryName",
count(p.id) as "completedProjectsCount",
r.count as "ratingsCount",
r.avg as "ratingsAverage"
from services s
join categories c on c.id = s.category_id
join categories pc on pc.id = c.parent_id
left join lateral (
select count(r.id), cast(round(coalesce(avg(r.rating), 0), 1) as text) as avg from ratings r
join projects rp on rp.id = r.project_id and s.category_id = rp.category_id
where r.reviewee_id = :user_id
) r on true
left join projects p on p.vendor_id = :user_id and p.status = 'Complete' and p.category_id = s.category_id
where s.status = 'approved' and s.active and s.user_id = :user_id
group by s.category_id, pc.name, c.name, r.count, r.avg
order by count(p.id) desc, s.category_id asc
One way to test this query is to replace the placeholder :user_id with an actual user id. Then, we can open the DB console and paste the query to see if it produces the expected results.
Make sure the query is successful and returns the desired results. Then we can replace the slow Ruby code with this efficient SQL query:
class ServicesStatsQuery
SQL = <<~SQL
select
s.category_id as "categoryId",
pc.name as "categoryName",
c.name as "subcategoryName",
count(p.id) as "completedProjectsCount",
r.count as "ratingsCount",
r.avg as "ratingsAverage"
from services s
join categories c on c.id = s.category_id
join categories pc on pc.id = c.parent_id
left join lateral (
select count(r.id), cast(round(coalesce(avg(r.rating), 0), 1) as text) as avg from ratings r
join projects rp on rp.id = r.project_id and s.category_id = rp.category_id
where r.reviewee_id = :user_id
) r on true
left join projects p on p.vendor_id = :user_id and p.status = 'Complete' and p.category_id = s.category_id
where s.status = 'approved' and s.active and s.user_id = :user_id
group by s.category_id, pc.name, c.name, r.count, r.avg
order by count(p.id) desc, s.category_id asc
SQL
attr_accessor :user
def initialize(user)
self.user = user
end
def call
sql = ActiveRecord::Base.sanitize_sql_array([SQL, user_id: user.id])
ActiveRecord::Base.connection.execute(sql).to_a
end
end
This SQL query can provide fast results for most Ruby On Rails applications. Although, it assumes that the DB has appropriate indexes defined.
Native SQL is much faster than the pure Ruby on Rails code
It’s time to perform measurements. First, install the benchmark-ips gem. Then you can prepare the following code and save it in a file. For example, t.rb, in the root directory of your Rails application:
require 'benchmark/ips'
user = User.find(10)
service = ServicesStatsQuery.new(user)
Benchmark.ips do |x|
x.report("ruby") { service.call_ruby }
x.report("sql") { service.call_sql }
x.compare!
end
We change the ServicesStatsQuery a bit to have the two methods defined call_ruby and call_sql instead of one method call:
class ServicesStatsQuery
SQL = <<~SQL
select
s.category_id as "categoryId",
pc.name as "categoryName",
c.name as "subcategoryName",
count(p.id) as "completedProjectsCount",
r.count as "ratingsCount",
r.avg as "ratingsAverage"
from services s
join categories c on c.id = s.category_id
join categories pc on pc.id = c.parent_id
left join lateral (
select count(r.id), cast(round(coalesce(avg(r.rating), 0), 1) as text) as avg from ratings r
join projects rp on rp.id = r.project_id and s.category_id = rp.category_id
where r.reviewee_id = :user_id
) r on true
left join projects p on p.vendor_id = :user_id and p.status = 'Complete' and p.category_id = s.category_id
where s.status = 'approved' and s.active and s.user_id = :user_id
group by s.category_id, pc.name, c.name, r.count, r.avg
order by count(p.id) desc, s.category_id asc
SQL
attr_accessor :user
def initialize(user)
self.user = user
end
def call_ruby
projects_full = []
projects_empty = []
Service.where(user: user, status: "approved", active: true).order(category_id: :asc).each do |service|
ratings_average = 0
ratings_count = 0
ratings_total = 0
Rating.where(reviewee: user).each do |rating|
project = Project.find(rating.project_id)
if project.category_id == service.category_id
ratings_count += 1
ratings_total += rating.rating
end
end
ratings_average = (ratings_total / ratings_count.to_f).round(1).to_s if ratings_count != 0 && ratings_total != 0
completed_projects_count = Project.where(vendor: user, status: "Complete", category_id: service.category_id).size
service_hash = {
category_id: service.category_id,
category_name: Category.find(Category.find(service.category_id).parent_id).name,
subcategory_name: Category.find(service.category_id).name,
completed_projects_count: completed_projects_count,
ratings_count: ratings_count,
ratings_average: ratings_average,
}
resolved_hash = service_hash.transform_keys{ |k| k.to_s.camelize(:lower) }
if completed_projects_count > 0
projects_full.push(resolved_hash)
else
projects_empty.push(resolved_hash)
end
end
projects_full.sort_by!{ |k| -k["completedProjectsCount"] }
projects_full.push(*projects_empty)
projects_full
end
def call_sql
sql = ActiveRecord::Base.sanitize_sql_array([SQL, user_id: user.id])
ActiveRecord::Base.connection.execute(sql).to_a
end
end
Run it with the command: rails runner t.rb and see the results:
Warming up --------------------------------------
ruby 1.000 i/100ms
sql 48.000 i/100ms
Calculating -------------------------------------
ruby 5.658 (± 0.0%) i/s - 29.000 in 5.147460s
sql 451.144 (± 8.6%) i/s - 2.256k in 5.042193s
Comparison:
sql: 451.1 i/s
ruby: 5.7 i/s - 79.74x slower
Performance boost of almost 80 times! The slow-loading page that took over 30 seconds now loads within 300ms. This significant improvement is not the programming language or framework merit.
Demo: Faster SQL in Ruby on Rails App
I have developed a demo application using both Ruby and SQL versions and made it available on GitHub as an open source project. This means that you have the opportunity to experiment with the code and run benchmarks on your own.
Determine whether to use the measurement tools or not
You can analyze performance issues and measure optimization results without fancy tools. benchmark-ips is for demonstration purposes. The best tools are reading the code, and understanding it. Then analyze its complexity. Finally, use SQL knowledge. These tools are enough. Nothing can replace them. Well, except in the distant future, ChatGPT may offer more help.
There are always plenty of built-in tools by your hand. Learn how to use them. For example, moving further, the written SQL can be slow. You will have to check why. Use explain analyze for that. This is a built-in tool in all modern SQL databases.
Why Ruby on Rails expert should know SQL
Ruby on Rails is a powerful web application framework. It provides developers with high-level abstractions and conventions. That allows to build web applications quickly and efficiently. But, despite its powerful abstractions, Ruby on Rails experts need to know SQL.
SQL allows Ruby on Rails experts to write efficient and optimized database queries. That can improve the performance of a web application. By understanding SQL, a Ruby on Rails expert can design the database schema and data access layer. If that’s done well, the application maximizes performance, scalability, and maintainability.
SQL is a widely-used and powerful language. People use it beyond interacting with the database. Ruby on Rails experts use SQL to perform complex calculations and data transformations. They can generate reports that are challenging to achieve using Ruby code alone.
SQL allows Ruby on Rails experts to have more control over their web applications. They write more efficient and optimized code. They make better data-driven decisions.
How can a Ruby on Rails expert learn SQL
Nowadays, there are plenty of courses and books available on the subject. So it’s hard to recommend specific resources. You can choose whatever you like and suits your learning style.
But, if you are like me and prefer hands-on learning, you might find the following resource helpful - https://pgexercises.com/.
You might also find this article I wrote previously interesting - Financial plan on PostgreSQL.
Make your Ruby on Rails app 80x faster with SQL was originally published by Andrei Kaleshka at WideFix Blog on March 30, 2023.
Posted on May 25, 2023
Join Our Newsletter. No Spam, Only the good stuff.
Sign up to receive the latest update from our blog.