Converting ActiveRecord queries to native SQL in Rails application

Data and Databases
1

I’m having performance issues with my Rails app when it’s deployed on Heroku. The queries are timing out and I think switching to raw SQL might help speed things up.

Here’s my current ActiveRecord code that’s causing problems:

@transactions = BillingRecord.joins(:account).order('billing_records.created_at desc')
@transaction_failures = BillingError.joins(:account).order('billing_errors.created_at desc')

@combined_transactions = (@transactions + @transaction_failures)

What’s the best way to rewrite these ActiveRecord queries as raw SQL? I want to make sure I’m doing the joins and ordering correctly. Any tips on how to execute raw SQL queries in Rails would be really helpful too.

2

raw sql won’t fix the issue ur facing. merging in ruby is slow. try using UNION in one query to combine results directly in the db. that’ll be way more efficient than just switching to raw sql.

3

have u looked into the query exec times? sometimes the slowdown isn’t just ActiveRecord. maybe it’s missing indexes on created_at or the join cols. what does ur database output when u run EXPLAIN on those queries?

4

You can convert to raw SQL with ActiveRecord::Base.connection.execute() or find_by_sql(). Something like SELECT br.*, a.* FROM billing_records br JOIN accounts a ON br.account_id = a.id ORDER BY br.created_at DESC for your queries. But here’s the thing - your real problem isn’t the SQL. It’s combining two separate database calls in Ruby memory. I’ve hit this same issue with large transaction datasets. Try creating a database view that unions both tables, or add pagination with limit() and offset() to shrink your dataset before merging. Raw SQL won’t fix the core problem of loading everything into memory and concatenating arrays.