Rails orm performance best practices

Not Leveraging SQL Effectively

ActiveRecord simplifies database interactions, but misusing its API can lead to severe performance bottlenecks.

Each of the methods count, length, and size serves different purposes:

• count triggers a SQL count query—efficient and straightforward.
• length loads all associated records into memory to calculate size, a heavy operation for large datasets.
• size smartly toggles between count or length based on data loading. Use with caution.

Directly using SQL functions through ActiveRecord can often bypass performance issues found in Ruby-level list manipulations.

Case Study: SQL for Sorting and Filtering

Let’s explore a practical example. Consider you need the first five users, alphabetically by name, from an account:

Inefficient Ruby Approach:

@users = @account.users.sort_by { |user| user.name.downcase }.first(5)

While it’s intuitive for Rubyists, this approach consumes more memory by loading all users into server memory.

Efficient SQL Approach:

@users = @account.users.order('LOWER(name)').limit(5)

Using order and limit directly in SQL is far more efficient, avoiding unnecessary Ruby loops and object loads.

Beware of the following Ruby for SQL tasks in Rails applications:

Anti-pattern: Calculating Collaborators in Ruby

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :comments
  has_many :articles, through: :comments

  def collaborators
    articles.collect { |a| a.users }.flatten.uniq.reject { |u| u == self }
  end
end

Loading and processing large datasets in Ruby is less efficient due to object instantiation and array operations.

Optimal Solution: Use SQL

# app/models/user.rb
class User < ActiveRecord::Base
  has_many :comments
  has_many :articles, through: :comments

  def collaborators
    User.joins(comments: { article: :comments })
      .where('articles.id IN (?) AND users.id != ?', article_ids, id)
      .distinct
  end
end

This results in the following SQL code:

SELECT DISTINCT users.* FROM users
  INNER JOIN comments ON comments.user_id = users.id
  INNER JOIN users users_comments ON users_comments.id = comments.user_id
  INNER JOIN articles ON articles.id = comments.article_id
  INNER JOIN comments comments_articles ON comments_articles.article_id = articles.id
  WHERE (articles.id in (1) AND users.id != 1)

By using ActiveRecord’s joins and where, we can perform complex queries directly in the database, significantly improving performance.

N+1 Queries

Consider the following set-up:

# app/models/post.rb
class Post < ApplicationRecord
  has_many :comments
end

# app/models/comment.rb
class Comment < ApplicationRecord
  belongs_to :post
end

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index
    @posts = Post.all
  end
end

# app/views/index.html.erb
<%= render @posts %>


# app/views/posts/_post.html.erb
<%= render @posts.title %>
<%= render @posts.comments %>

# app/views/comments/_comment.html.erb
<%= comment.content %>

Here, the issue is that when rendering the views, <%= render @posts.comments %> will iterate through all the posts and create a query for each <%= comment.content %> partial rendered.

The solution: use Post.includes(:comments)

# app/models/post.rb
class Post < ApplicationRecord
  has_many :comments
end

# app/models/comment.rb
class Comment < ApplicationRecord
  belongs_to :post
end

# app/controllers/posts_controller.rb
class PostsController < ApplicationController
  def index
    # Perf gains ⚡️
    @posts = Post.includes(:comments)
  end
end

This will basically create a JOIN in sql with the comments table and create a new instance of the model Post that will be loaded in ONE DB REQUEST instead of N+1 times.

Conclusion

Understanding and utilizing SQL’s full capabilities within Rails applications is crucial for performance.

Always consider SQL as your first solution for data manipulation tasks in Rails, reserving Ruby for application logic where SQL is not sufficient.

Remember, efficient code leverages the strengths of both Ruby and SQL appropriately.

Related posts