Alex Aslam

Posted on Jul 1

ActiveRecord vs. Raw SQL: Performance at Scale

#webdev #programming #rails #beginners

"Our database was on fire—all because we trusted ActiveRecord a little too much."

ActiveRecord is the comfort food of Rails development. It’s easy, familiar, and works great—until your app hits 10K+ records per query and your database starts gasping for air.

We learned this the hard way when our "simple" report page took 12 seconds to load. Here’s how we fixed it—and when to break out raw SQL instead.

1. The Great Divide

ActiveRecord (The Comfort Zone)

✅ Pros:

Beautiful, chainable queries (User.active.admins)
Built-in safety (SQL injection protection)
Database-agnostic (mostly)

❌ Cons:

Hidden N+1 queries
Inefficient joins
Memory-hungry object instantiation

Raw SQL (The Scalability Escape Hatch)

✅ Pros:

Precise control over query execution
Avoids ORM overhead
Advanced DB features (CTEs, window functions)

❌ Cons:

SQL injection risks if careless
Harder to maintain
Database lock-in

2. Performance Showdown

Benchmark: Loading 50K Orders with User Data

Approach	Time (ms)	Memory (MB)
`Order.includes(:user)`	1,200	480
Raw SQL + manual mapping	220	85
Difference	5.5x faster	5.6x less memory

Why?

ActiveRecord instantiates full Ruby objects for every row
Raw SQL returns lean hashes/arrays

3. When ActiveRecord Breaks Down

Case 1: Complex Joins

ActiveRecord:

User.joins(:orders)
    .where(orders: { status: :shipped })
    .group("users.id")
    .select("users.*, COUNT(orders.id) as order_count")

Problem:

Builds 3 separate queries under the hood
Merges results in Ruby (slow for large datasets)

Raw SQL Fix:

User.find_by_sql(<<~SQL)
  SELECT users.*, COUNT(orders.id) as order_count
  FROM users
  INNER JOIN orders ON orders.user_id = users.id
  WHERE orders.status = 'shipped'
  GROUP BY users.id
SQL

Case 2: Batch Processing

ActiveRecord:

User.where(active: true).find_each do |user|
  user.update(last_login_at: Time.now)
end

Problem:

Issues N UPDATE queries
Instantiates full User objects

Raw SQL Fix:

User.connection.execute(<<~SQL)
  UPDATE users
  SET last_login_at = NOW()
  WHERE active = TRUE
SQL

Result: 1 query, 0 Ruby objects

4. When to Stick with ActiveRecord

✅ Simple CRUD (95% of app code)
✅ Rapid prototyping
✅ Teams without deep SQL knowledge

Golden Rule:

Use ActiveRecord until you measure a bottleneck

5. The Hybrid Approach

Best of Both Worlds

# Use ActiveRecord for setup
scope = User.active.where(company_id: 1)

# Switch to raw SQL for final query
results = User.connection.select_all(<<~SQL)
  SELECT id, email FROM (#{scope.to_sql}) AS users
  WHERE last_login_at > NOW() - INTERVAL '7 days'
  LIMIT 1000
SQL

# Convert to structs for safety
Struct.new(:id, :email).tap do |user_struct|
  @users = results.map { |row| user_struct.new(row["id"], row["email"]) }
end

Benefits:

Keeps query building readable
Avoids object instantiation cost
Still SQL-injection safe

6. Critical Optimizations

For ActiveRecord

# Bad (instantiates Order objects)
Order.where("total > 100").map(&:id)

# Good (pluck avoids objects)
Order.where("total > 100").pluck(:id)

For Raw SQL

# Dangerous (SQL injection!)
User.connection.execute("SELECT * FROM users WHERE email = '#{params[:email]}'")

# Safe (Rails sanitization)
User.connection.execute(
  "SELECT * FROM users WHERE email = ?",
  [params[:email]]
)

DEV Community