ActiveRecord Performance Optimization

Performance Benchmarking Methodology

Before optimizing, you need to measure. Here's how to properly benchmark your Rails database queries:

require 'benchmark'

# Enable query logging
ActiveRecord::Base.logger = Logger.new(STDOUT)

# Benchmark block
result = Benchmark.measure do
  # Your query here
  User.includes(:posts).limit(1000).each do |user|
    user.posts.count
  end
end

puts "Time: #{result.real.round(3)} seconds"

Key Metrics to Track

• Query Count: Number of database queries executed
• Execution Time: Total time including Ruby processing
• Memory Usage: RAM consumed by loaded objects
• Database Time: Time spent waiting for database responses

N+1 Query Problem: The Silent Performance Killer

The N+1 query problem is the most common ActiveRecord performance issue. Here's how to identify and fix it:

Problem: The N+1 Query Anti-pattern

# This code looks innocent but is a performance disaster
users = User.limit(100)           # 1 query
users.each do |user|              # +100 queries (one per user)
  puts "#{user.name} has #{user.posts.count} posts"
end

# SQL Log shows:
# SELECT * FROM users LIMIT 100
# SELECT COUNT(*) FROM posts WHERE user_id = 1
# SELECT COUNT(*) FROM posts WHERE user_id = 2
# ... 98 more identical queries

Solution 1: Using includes()

# This preloads all posts for all users
users = User.includes(:posts).limit(100)  # 2 queries total
users.each do |user|                      # No additional queries
  puts "#{user.name} has #{user.posts.size} posts"
end

# SQL Log shows:
# SELECT * FROM users LIMIT 100
# SELECT * FROM posts WHERE user_id IN (1,2,3...100)

Solution 2: Counter Caches for Simple Counts

# Add to your Post model:
belongs_to :user, counter_cache: true

# Add posts_count column to users table:
add_column :users, :posts_count, :integer, default: 0

# Now this is just one query with no JOINs:
users = User.limit(100)              # 1 query only
users.each do |user|
  puts "#{user.name} has #{user.posts_count} posts"
end

Query Optimization Techniques

1. Strategic Use of select()

Loading only the columns you need can dramatically reduce memory usage and transfer time:

# Loads all columns including 
# large text fields
User.all.each do |user|
  puts user.name
end

# Only loads the columns you need
User.select(:id, :name, :email)
    .each do |user|
  puts user.name
end

2. find_each() for Large Datasets

# BAD: Loads all records into memory at once
User.all.each { |user| process_user(user) }  # Memory explosion!

# GOOD: Processes in batches of 1000
User.find_each(batch_size: 1000) { |user| process_user(user) }

# EVEN BETTER: Custom batch size for your use case  
User.find_each(batch_size: 500) { |user| process_user(user) }

3. Database Indexes: The Foundation of Fast Queries

Even perfect ActiveRecord code is slow without proper indexes:

# In your migration file:
class AddIndexesToOptimizeCommonQueries < ActiveRecord::Migration[7.0]
  def change
    # Foreign keys (for JOINs)
    add_index :posts, :user_id
    add_index :comments, :post_id
    
    # Frequently queried columns
    add_index :users, :email, unique: true
    add_index :posts, :published
    add_index :posts, :created_at
    
    # Composite indexes for multi-column WHERE clauses
    add_index :posts, [:user_id, :published]
    add_index :events, [:user_id, :created_at]
  end
end

When to Use Raw SQL vs ActiveRecord

Use ActiveRecord When:

• Standard CRUD Operations: Creating, reading, updating single records
• Simple Queries: Basic WHERE, ORDER BY, LIMIT operations
• Association Traversal: Following relationships between models
• Security is Paramount: Built-in SQL injection protection
• Database Portability: Code needs to work across different databases

Use Raw SQL When:

• Complex Analytics: Multi-table aggregations with complex logic
• Performance Critical: Highly optimized queries for hot code paths
• Database-Specific Features: PostgreSQL arrays, MySQL JSON functions, window functions
• Bulk Operations: Inserting/updating thousands of records
• Complex Reporting: Queries that would generate inefficient ActiveRecord code

Performance Comparison Examples

# Multiple queries + Ruby processing
users = User.includes(:orders)
result = users.map do |user|
  {
    name: user.name,
    total_orders: user.orders.sum(:amount),
    avg_order: user.orders.average(:amount)
  }
end

# Single optimized query
sql = <<~SQL
  SELECT u.name,
         SUM(o.amount) as total_orders,
         AVG(o.amount) as avg_order
  FROM users u
  JOIN orders o ON u.id = o.user_id
  GROUP BY u.id, u.name
SQL
result = ActiveRecord::Base
         .connection.exec_query(sql)

Memory Usage Optimization

Understanding ActiveRecord Memory Patterns

Memory-Efficient Patterns

names = User.all.map(&:name)
# Creates 10,000 User objects just for names!

names = User.pluck(:name)
# Returns array of strings, no objects

if user.posts.count > 0
  # Counts ALL posts even though we only 
  # need to know if ANY exist
end

if user.posts.exists?
  # Stops at first match - much faster
  # for large datasets
end

Caching Strategies for Database Queries

1. Rails Query Caching

Rails automatically caches identical queries within a single request:

# Within the same request/action:
User.find(1)  # Database query executed
User.find(1)  # Returns cached result, no database hit
User.find(1)  # Still cached

# Cache is automatically cleared between requests

2. Application-Level Caching

# Cache expensive aggregations
def popular_posts
  Rails.cache.fetch("popular_posts", expires_in: 1.hour) do
    Post.joins(:comments)
        .group(:id)
        .having('COUNT(comments.id) > 10')
        .order('COUNT(comments.id) DESC')
        .limit(10)
  end
end

Performance Monitoring and Debugging

Tools for Performance Analysis

Query Analysis Checklist