Performance Issue with Active Record Queries in Rails 6: Slow Response Times

rubyonrails-talk
1

I’m encountering significant performance issues with Active Record queries in my Rails 6 application, leading to slow response times, and I’m seeking assistance to resolve it.

Background: My Rails 6 application relies heavily on Active Record for database interactions. Recently, I’ve noticed a substantial slowdown in response times for pages that involve complex queries or large datasets. This performance degradation affects the overall user experience and efficiency of the application.

Issue: The main problem arises when executing certain Active Record queries. Queries that involve multiple joins, subqueries, or large tables are taking an excessive amount of time to complete, resulting in noticeably slow page loads. This issue is particularly evident in the parts of the application that generate reports or display aggregated data.

Troubleshooting: Here are the steps I’ve taken to troubleshoot the issue so far:

  1. Analyzed query performance using EXPLAIN to identify potential bottlenecks and inefficient query plans.
  2. Added database indexes on frequently queried columns to improve query performance.
  3. Used the includes method to eager load associations and reduce the number of database queries.
  4. Implemented caching strategies to store the results of expensive queries and reduce database load.
  5. Reviewed server and database logs for any indications of resource constraints or performance issues.

Observations: Despite these efforts, the performance issues with Active Record queries persist, leading to slow response times and a suboptimal user experience. This problem is critical as it impacts the application’s usability and scalability.

Request for Assistance: If anyone has experience with optimizing Active Record queries in Rails 6 or has encountered similar performance issues, I would greatly appreciate your insights and assistance. Specifically, I’m looking for advanced strategies or best practices for improving query performance and reducing response times in Rails applications. :smiling_face_with_tear: :grinning:

Thank you for your help and support!

2

Which DBMS are you using?

3

This can be a good start … and note that #includes will essentially do a SELECT * … on all JOINed tables. If you want better performance and there are some columns (especially large columns) that you don’t need to get back in these slow-running queries then it is good to create “covering” indexes – that is, indexes which include only the columns for a table that you need to get back for specific queries. And then only SELECT those columns.

For instance, if you have Customer and Order, and Order has a sizable “courier’s picture proof of dropping off this order” thing – some large image data, or maybe a memo field that can have lots of info inside, then you’ll want to omit those columns and just get back the specific stuff that’s useful, something like this:

SELECT customers.id, customers.name, customers.city,
       orders.order_date, orders.status

By default #includes can not choose specific columns. Although with The Brick then it can. You have to include a special flag as the first thing in your .select, and then you can use dotted notation to choose other specific columns that are part of the SELECT, like this:

Customer.includes(:orders)
        .select(:_brick_eager_load # Turn on filtering of t0_r3 style aliases
                :id,
                :name,
                :city,
                'orders.order_date',
                'orders.status'
               )
        .references(:orders)

You can also extract just this behaviour if you don’t want to use the full gem.

Aside from that optimisation, if you would be OK to share some of your ActiveRecord type queries then perhaps there are other optimisations which could be approached. Could do cool tricks using Derived Tables / CTEs or other SQL things to steer your database engine into being more performant.