ActiveRecord vs Raw SQL: Performance Comparison

Test Setup

Simple SELECT Queries

Complex Queries

Memory Usage Comparison

1. Simple CRUD Operations

# Fast and clean
user = User.find(123)
user.update(name: 'New Name')

# Efficient batch operations
User.where(active: false).update_all(status: 'inactive')

# Built-in optimizations
User.includes(:posts).where(posts: { published: true })

2. Association Handling

# Automatic N+1 prevention
users = User.includes(:posts, :comments).limit(10)

# Efficient eager loading
posts = Post.includes(user: :profile, comments: :author)

# Counter caches
class User < ApplicationRecord
  has_many :posts, counter_cache: true
end

# No additional queries needed
user.posts_count  # Uses cached counter

3. Development Speed

1. Complex Analytical Queries

SELECT 
  u.region,
  COUNT(*) as user_count,
  AVG(o.amount) as avg_order,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY o.amount) as median_order,
  LAG(COUNT(*)) OVER (ORDER BY u.region) as prev_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY u.region
ORDER BY user_count DESC;

2. Large Dataset Processing

# Raw SQL for bulk operations
sql = "
  UPDATE products 
  SET price = price * 1.1 
  WHERE category_id IN (
    SELECT id FROM categories 
    WHERE name LIKE '%electronics%'
  )
"
ActiveRecord::Base.connection.execute(sql)

# Memory-efficient data extraction
connection.select_all("
  SELECT id, email FROM users 
  WHERE created_at > '2024-01-01'
").each do |row|
  # Process without ActiveRecord overhead
  send_email(row['email'])
end

3. Database-Specific Features

# JSON operations
SELECT * FROM products 
WHERE metadata @> '{"features": ["waterproof"]}';

# Full-text search with ranking
SELECT *, ts_rank(to_tsvector('english', description), query) as rank
FROM products, to_tsquery('english', 'smartphone') query
WHERE to_tsvector('english', description) @@ query
ORDER BY rank DESC;

# Full-text search
SELECT *, MATCH(title, content) AGAINST('rails tutorial') as relevance
FROM posts 
WHERE MATCH(title, content) AGAINST('rails tutorial')
ORDER BY relevance DESC;

# JSON path queries
SELECT * FROM users 
WHERE JSON_EXTRACT(preferences, '$.notifications.email') = true;

ActiveRecord Object Overhead

Memory Optimization Strategies

# 1. Use pluck for simple data extraction
# Memory: 240MB → 78MB (67% reduction)
user_emails = User.pluck(:email)  # Instead of User.all.map(&:email)

# 2. Select only needed columns
# Memory: 240MB → 120MB (50% reduction)
users = User.select(:id, :email, :name)

# 3. Find in batches for large datasets
# Memory: Constant ~25MB (instead of 240MB+)
User.find_each(batch_size: 1000) do |user|
  process_user(user)
end

# 4. Use raw SQL for aggregations
# Memory: 240MB → 5MB (98% reduction)
stats = ActiveRecord::Base.connection.select_all("
  SELECT region, COUNT(*) as user_count, AVG(age) as avg_age
  FROM users 
  GROUP BY region
")

Memory vs Performance Trade-offs

1. Query Optimization

# Bad: N+1 queries
users = User.all
users.each { |user| puts user.posts.count }

# Good: Eager loading
users = User.includes(:posts)
users.each { |user| puts user.posts.count }

# Better: Counter cache
class User < ApplicationRecord
  has_many :posts, counter_cache: true
end
users.each { |user| puts user.posts_count }

# Best: Single query with calculation
User.left_joins(:posts)
    .group(:id)
    .select('users.*, COUNT(posts.id) as posts_count')

2. Index Strategy

# Migration with performance-focused indexes
class OptimizeQueries < ActiveRecord::Migration[7.0]
  def change
    # Composite index for common query patterns
    add_index :posts, [:user_id, :published, :created_at]
    
    # Partial index for active records
    add_index :users, :email, where: "active = true"
    
    # Covering index to avoid table lookups
    add_index :orders, [:user_id, :status], include: [:total, :created_at]
  end
end

3. Caching Strategies

# Query result caching
Rails.cache.fetch("user_stats_#{Date.current}", expires_in: 1.hour) do
  User.group(:region).count
end

# Fragment caching
class User < ApplicationRecord
  def cache_key_with_version
    "#{cache_key}-#{posts_count}-#{updated_at}"
  end
end

# Russian doll caching
<% cache [user, user.posts.maximum(:updated_at)] do %>
  <%= render user.posts %>
<% end %>

1. Query Planning

# Use EXPLAIN to analyze query performance
explain_result = ActiveRecord::Base.connection.execute("
  EXPLAIN (ANALYZE, BUFFERS) 
  SELECT * FROM users u 
  JOIN posts p ON p.user_id = u.id 
  WHERE u.active = true
")

# Optimize based on explain output
# Look for: Sequential Scans, High cost, Buffer usage

2. Prepared Statements

# Use parameterized queries for repeated operations
prepare_statement = ActiveRecord::Base.connection.raw_connection.prepare("
  SELECT * FROM users WHERE region = $1 AND age > $2
")

# Execute with different parameters
result1 = prepare_statement.exec(['west', 25])
result2 = prepare_statement.exec(['east', 30])

3. Connection Management

# Use connection pool efficiently
ActiveRecord::Base.connection_pool.with_connection do |conn|
  # Batch multiple queries in single connection
  results = []
  results << conn.select_all("SELECT COUNT(*) FROM users")
  results << conn.select_all("SELECT COUNT(*) FROM posts")
  results
end

# For read-heavy operations, consider read replicas
class User < ApplicationRecord
  connects_to database: { 
    writing: :primary, 
    reading: :replica 
  }
end

Performance Decision Matrix

1. ActiveRecord with Raw SQL Optimization

class User < ApplicationRecord
  # Use ActiveRecord for associations and validations
  has_many :posts
  validates :email, presence: true, uniqueness: true
  
  # Use raw SQL for complex queries
  def self.top_contributors(limit = 10)
    find_by_sql([
      "SELECT users.*, 
              COUNT(posts.id) as post_count,
              AVG(posts.score) as avg_score
       FROM users 
       LEFT JOIN posts ON posts.user_id = users.id 
       WHERE posts.published = true
       GROUP BY users.id 
       HAVING COUNT(posts.id) > 5
       ORDER BY post_count DESC, avg_score DESC 
       LIMIT ?", 
      limit
    ])
  end
  
  # Use select for calculated fields
  scope :with_post_stats, -> {
    select('users.*, 
            COUNT(posts.id) as posts_count,
            MAX(posts.created_at) as last_post_date')
    .left_joins(:posts)
    .group('users.id')
  }
end

2. Service Objects for Complex Operations

class UserAnalyticsService
  def self.generate_report(date_range)
    # Use raw SQL for heavy aggregations
    stats = ActiveRecord::Base.connection.select_all(
      sanitize_sql([
        "SELECT 
           DATE(created_at) as date,
           COUNT(*) as signups,
           AVG(age) as avg_age,
           COUNT(CASE WHEN premium = true THEN 1 END) as premium_signups
         FROM users 
         WHERE created_at BETWEEN ? AND ?
         GROUP BY DATE(created_at)
         ORDER BY date",
        date_range.begin, date_range.end
      ])
    )
    
    # Use ActiveRecord for related data
    stats.map do |row|
      {
        date: row['date'],
        signups: row['signups'],
        avg_age: row['avg_age'].to_f,
        premium_signups: row['premium_signups'],
        # Add ActiveRecord-based associations
        top_referrers: User.where(created_at: row['date'].all_day)
                          .group(:referrer)
                          .limit(5)
                          .count
      }
    end
  end
end

3. Caching Layer Strategy

class DashboardService
  def self.user_stats(user_id)
    Rails.cache.fetch("user_stats_#{user_id}", expires_in: 1.hour) do
      # Use raw SQL for expensive aggregations
      raw_stats = ActiveRecord::Base.connection.select_one(
        sanitize_sql([
          "SELECT 
             COUNT(DISTINCT posts.id) as total_posts,
             COUNT(DISTINCT comments.id) as total_comments,
             AVG(posts.score) as avg_post_score,
             SUM(CASE WHEN posts.created_at > ? THEN 1 ELSE 0 END) as recent_posts
           FROM users 
           LEFT JOIN posts ON posts.user_id = users.id
           LEFT JOIN comments ON comments.user_id = users.id
           WHERE users.id = ?",
          30.days.ago, user_id
        ])
      )
      
      # Use ActiveRecord for relationships
      user = User.includes(:posts, :followers).find(user_id)
      
      raw_stats.merge({
        followers_count: user.followers.count,
        following_count: user.following.count,
        recent_activity: user.posts.recent.limit(5)
      })
    end
  end
end

Performance Monitoring

# Enable query logging in development
# config/environments/development.rb
config.log_level = :debug

# Monitor slow queries in production
# config/application.rb
config.active_record.warn_on_records_fetched_greater_than = 1000

# Use tools like:
# - New Relic for APM
# - Scout for Rails monitoring  
# - Bullet gem for N+1 detection
# - rack-mini-profiler for detailed timing