Optimizing Database Queries in Ruby on Rails: Best Practices and Examples - Part 2

Hi, This is Shubham Mishra from India,  this is the part of Ruby on Rails exploration journey.  In this post we will discuss about the ruby's some special concepts and I am sure you will get excited to make your hands dirty with those code, so why to wait let's get started...

Before jumping over different code snippets lets read some theory first,here are some tips for optimizing Ruby on Rails ActiveRecord queries:

1. Use eager loading: As discussed earlier, eager loading can be used to fetch associated records in a single query, rather than making a separate query for each association. This can greatly reduce the number of database queries and improve performance.
2. Avoid N+1 queries: N+1 queries occur when a query is made to fetch a record, and then additional queries are made for each associated record. This can be avoided by using eager loading, or by preloading associated records using the includes method.
3. Use appropriate indexing: Indexing can greatly improve query performance by allowing the database to quickly find the relevant rows. Make sure to index columns that are frequently used in WHERE, ORDER, GROUP BY, or HAVING clauses.
4. Use SQL fragments for complex queries: Sometimes, complex queries cannot be expressed using ActiveRecord query methods. In these cases, you can use SQL fragments to write custom SQL queries that can be executed by the database.
5. Use caching: Caching can be used to store the results of frequently executed queries, so that subsequent requests can be served from the cache instead of hitting the database. This can greatly improve performance, especially for read-heavy applications.
6. Use pagination: If you are dealing with large datasets, consider using pagination to limit the number of records returned by the query. This can improve query performance and reduce memory usage in the application.
7. Use counter caching: If you frequently need to count associated records, consider using counter caching to store the count in a separate column. This can greatly improve performance by avoiding the need to execute a separate count query.

By following these tips, you can write efficient and scalable ActiveRecord queries that can help your Ruby on Rails application perform at its best.

As above points are well know and we certainly have used all points but lets see some code snipper example for 4th point,

Let's say you have a Post model and you want to find all the posts that were created in the last 7 days, ordered by the number of comments they have. You can use the following SQL query to achieve this:


In this example, we use the select, joins, where, group, and order methods to construct the SQL query. The select method allows us to select the columns we want to retrieve, while the joins method allows us to specify the join condition between the posts and comments tables. The where method allows us to filter the records based on the created_at column, while the group method groups the records by the id column of the posts table. Finally, the order method orders the records by the comment_count column, which we defined using the COUNT function in the select clause. By using SQL fragments in this way, we can write custom SQL queries that are optimized for our specific use case, and execute them using the familiar ActiveRecord interface.

One more point I would like to highlight which I like the most is,

combine usage of joins, select, and group in ruby active record query

• User.joins(:posts, :comments) specifies that we want to fetch data from the users, posts, and comments tables, and that we want to join them together based on their associations.
• select("users.*, count(distinct posts.id) as post_count, count(distinct comments.id) as comment_count") specifies which columns we want to select from the joined tables, and includes two additional columns that count the number of posts and comments associated with each user.
• group("users.id") groups the results by the id column of the users table, which ensures that each row represents a distinct user.

This query uses the count function to count the number of distinct posts.id and comments.id values associated with each user. The distinct keyword ensures that we only count each post or comment once, even if it is associated with multiple records. When this query is executed, the database engine will evaluate each row in the joined table and count the number of distinct posts.id and comments.id values associated with each user_id. The results are then returned as a list of records, where each record contains the user's ID, the number of distinct posts, and the number of distinct comments.
This query is a good example of how to use the joins, select, and group methods in ActiveRecord to optimize a database query. By selecting only the columns we need and grouping the results by a single column, we can minimize the amount of data that needs to be transferred from the database to the application, and ensure that the results are easy to work with.

Lets see some additional methods and examples of best practices for database queries in Ruby on Rails:

1. Use pluck instead of select for fetching a single column:
   # Fetch the ids of all users with at least one post
   user_ids = User.joins(:posts).distinct.pluck(:id)
   
   
2. Use where instead of select for filtering:
   # Fetch all posts created in the last week
   posts = Post.where(created_at: 1.week.ago..Time.current)
   
   
3. Use includes or eager_load for preloading associated objects:
   # Fetch all posts and their associated comments with preloading
   posts = Post.includes(:comments).all
   
   

Explore aggregator methods for more optimised queries in ruby on rails

1. Use MAX instead of ORDER BY for finding the latest record:
   
   
   This example uses the DISTINCT ON syntax in Postgres to select the latest post for each user, without having to use a subquery or join. By ordering the results by user_id and created_at, and then using DISTINCT ON (user_id) to select the first row for each user, we can efficiently fetch the latest post for each user.
   
   
2. Use AVG instead of SUM and COUNT for computing averages:
   
   
   This example uses the average method in ActiveRecord to compute the average of the post_count values returned by the query. By using count(posts.id) instead of count(*), we can exclude null values and ensure that the result is accurate.
   
   
   
3. Use GROUP BY and HAVING for filtering by aggregate functions:
   
   
   This example uses GROUP BY and HAVING to filter the results by the number of posts associated with each user. By grouping the results by users.id and filtering by count(posts.id) >= 10, we can efficiently find users who have posted at least 10 times in the last week.
   
   

The examples I provided use aggregate functions like COUNT, MAX, and AVG to perform calculations on groups of records, which can be much more efficient than iterating over each record individually. By using these functions, we can reduce the number of database queries needed to perform the calculations and minimize the amount of data that needs to be transferred between the database and the application.

Additionally, the examples use techniques like DISTINCT ON, GROUP BY, and HAVING to filter and group the results of the queries, which can further reduce the amount of data that needs to be processed and returned.

Here are some further ways to optimize these queries:

1. Index the columns used in GROUP BY and HAVING clauses: If you are filtering or grouping by specific columns, make sure to index those columns in the database. This can significantly speed up queries by allowing the database engine to quickly find the relevant rows without having to scan the entire table.
   
2. Use LEFT JOIN instead of INNER JOIN if some records may not have associated data: If you are joining tables and some records in the left table may not have corresponding records in the right table, use a LEFT JOIN instead of an INNER JOIN. This will ensure that all records in the left table are included in the result set, even if they do not have matching records in the right table.
   
3. Use a subquery to filter records before joining: If you are joining large tables and only need a subset of the records, consider using a subquery to filter the records before joining. This can reduce the amount of data that needs to be processed and improve query performance.
   
4. Use pagination to limit the number of records returned: If you are dealing with large datasets, consider using pagination to limit the number of records returned by the query. This can improve query performance and reduce memory usage in the application.
   

Overall, by using techniques like aggregate functions, filtering, and grouping, and by optimizing the queries themselves with indexing, subqueries, and pagination, we can create efficient and scalable database queries.

You can explore my previous blog: Ruby on Rails optimizing Database query Part 1

Or you can explore my other blogs too here,

Get to know answers for common search on Google : A blog for posts which can help you for daily life problems, such as where to get free images, Topic suggestion for the blog.

Computer Science algorithms and other knowledge share : A blog for posts such as best search algorithm, Top interview questions for diffrent technologies, knowledge share for some frameworks or programming languages for the interview or in general terms.

My ideas to solve real world problems : A blog where me shared and presented my ideas to solve a real world problems, this will be interesting for me.

Ruby on Rails Web development Blog : As the name suggest, it is the blog for sharing few knowledge about RoR web development framework.

Liked my blogs, wanna to connect:

LinkedIn  |  GitHub  |  HackerEarth  |  ResearchGate  |  Twitter  |  Facebook  |  StackOverflow

Thanks for reading this post, Have a good day :)