Converting ActiveRecord queries to raw SQL in Rails application

Data and Databases
1

I’m facing performance issues with my Rails app hosted on Heroku. The application often encounters timeout errors when executing these ActiveRecord queries. I believe switching to raw SQL could help improve performance.

@invoices = Invoice.joins(:customer).order('invoices.updated_at desc')
@invoice_failures = InvoiceFailure.joins(:customer).order('invoice_failures.updated_at desc')

@combined_invoices = (@invoices + @invoice_failures)

How can I effectively rewrite these joins and order operations in raw SQL? I want to merge the results from both tables similarly to how I’m currently combining them in Ruby. Any suggestions would be appreciated!

2

To effectively rewrite your ActiveRecord queries in raw SQL, you can use the following SQL statements:

SELECT invoices.*, customers.* FROM invoices 
JOIN customers ON invoices.customer_id = customers.id 
UNION ALL 
SELECT invoice_failures.*, customers.* FROM invoice_failures 
JOIN customers ON invoice_failures.customer_id = customers.id 
ORDER BY updated_at DESC;

However, I recommend checking for proper indexing, as it can significantly improve performance. Ensure that the necessary indexes exist on foreign keys and the updated_at columns, as this often resolves performance issues more effectively than just switching to raw SQL.

3

have you considered using a UNION query instead of combining arrays in ruby? that might be way more efficent than pulling all records and merging them. what kind of timeout errors are you seeing exactly - is it the db query itself or heroku’s request timeout?