Ruby on Rails DB Tips for creating methods to reduce the load

Introduction

It's been four months since I changed jobs as a rails programmer. The other day, I experienced a DB down due to an access load on an application. I tried to review the query issuance process in the application in my business, and the handling of data became a little stronger. As a graduate of a programming school, I didn't remember learning about it, so I'll write my own memorandum as well. I'm going to make a portfolio and sell it to companies! In response to the question "Where did you do your best?", I heard that "I tried to eliminate unnecessary (duplicate) query issuance processing. The DB will be down due to the access load. I implemented it with the operation in mind. It was two birds with one stone because I learned the method. " I'd like to say something like "I'll do it, this guy".

Then immediately

What is querying in Rails in the first place ...?

Roughly speaking, it is the process of accessing the DB via the model. I often see it with controller and model

users_controller.rb



def index
   @users = User.all
   @user = User.find(params[:id])
   @user2 = User.find_by(user_id: current_user.id,
                         profile_id: params[:profile_id])
   @user3 = User.where(name: "aaaaa")
end

A model like. It refers to processing like ~ ~ ~.

When running locally, it loads the index page with the above code on the controller.

Gaze at the terminal in the meantime.

Then

スクリーンショット 2020-12-19 17.02.48.png

I'm sorry that the letters are small. And the query in this image is different from the one in the controller above. This is a reference image.

The purple letters are the actual queries. For the model, there are commands such as find and all, The model issues SQL like purple characters to the DB. This is query issuance.

The more this process, the slower the page load and the worse the usability.

(1.6ms) is the response time from the DB. This is a local value, so it is different from the measured value, but I think you can judge whether it is slow or fast here as well. Furthermore, now there are numbers for one access, In actual operation, there are thousands of accesses at the same time, so the time required for response will become slower and slower.

Of course, DB specifications are important, but let's do what we can do within the application.

How to actually suppress

Anyway, access to one model is basically once in each action!

If there is a process (.all) to acquire a large amount of data, extract the desired data from it!

What does that mean

For example

users_controller.rb



def index
   @users = User.all
   @user = User.find(params[:id])
   @user2 = User.find_by(user_id: current_user.id,
                         profile_id: params[:profile_id])
   @user3 = User.where(name: "aaaaa")
end

Assuming that there is processing like This is actually a huge waste. Aside from @users, for everything else You don't need to access the model, right?

As you may have noticed, @user and @ user2, @ user3 are already in the @users variable.

Then, you can get the information you want with the Ruby method from this users data!

users_controller.rb


def index
   @users = User.all
   @user = @users.to_a.detect{ |user| user.id == params[:id].to_i}
Or
   @user = @users.to_a.find { |user| user if user.id == params[:id].to_i} 


   @user2 = @users.to_a.map { |user| user if user.id == current_user.id && user.profile_id == params[:profile_id].to_i}

   @user3 = @users.to_a.select {|user| user.name == "aaaa"}
end

Perhaps it can be rewritten like this. However, if you are not careful about the data type of the matching data, it will not match well, so let's judge whether it is an integer type or a String type with params [: id] .class such as byebug. Since "1" and 1 have different data types, they are not considered the same and == is false. It doesn't return an array.

effect

Implemented in each controller.

Basically, if you have a large amount of data, use it to format it into smaller pieces. Do not issue duplicate queries.

From the day after implementing this process, it was confirmed that the access load was reduced to less than half and the loading speed increased as an actual measurement value! !! !!

One point note

Then take it all with all! You might think, but Please note that the larger the data collected at one time, the heavier the processing. This formatting is useful only when you absolutely have to collect a large amount of data. We also found that even if the same data is taken from the database, the more information that is searched (matched), the faster the response.

user_controller.rb



def index

  @users = User.where(user_id: current_user.id)
  @users2 = User.where(user_id: current_user.id,profile_id: 1)

end

Assuming there is code like Even if there is not much difference in the data to be acquired, the processing time seems to change significantly.

Actually, I also thought that the query issuance process in the controller could be set to 0 if I could easily acquire the data in a larger frame, so I changed the process from @ users2 to something like @users and changed the query issuance process in the controller. When I lost everything, the access load value to the database increased significantly from the next day. Lol Therefore, if you want to change the existing processing that was at the beginning and aim for a broader frame, it is desirable to implement it after making a solid load test.

Summary

It's a simple task, but it's also a simple study, and it's interesting to see the effect visually, so if you have a chance, please try it. You can praise yourself. Somehow I know Ruby so much that I feel like myself. I'm sorry if the code is a little wrong. Lol

If you find it difficult to understand, we welcome your comments.

Recommended Posts

Ruby on Rails DB Tips for creating methods to reduce the load
[Ruby on Rails] How to avoid creating unnecessary routes for devise
[Updated from time to time] Ruby on Rails Convenient methods
[Ruby On Rails] How to reset DB in Heroku
Explanation of Ruby on rails for beginners ② ~ Creating links ~
[Ruby on Rails] Column restrictions when saving to DB (4 representatives)
Explanation of Ruby on rails for beginners ③ ~ Creating a database ~
[Ruby on rails] The rails db: migrate: reset command displays FATAL: Listen error: unable to monitor directories for changes. [Rails tutorial]
How to use Ruby on Rails
[Rails / Heroku / MySQL] How to reset the DB of Rails application on Heroku
Understanding REST will improve the outlook for Ruby on Rails tutorials
[Ruby on Rails] Use the resources method to automatically create routes.
Things to remember and concepts in the Ruby on Rails tutorial
(Ruby on Rails6) Create a function to edit the posted content
[Ruby on Rails] Pass the parameters divided by date_select to FormObject.
[Ruby on Rails] Creating an inquiry form
[Ruby on Rails] How to use CarrierWave
[Ruby on Rails] About bundler (for beginners)
part of the syntax of ruby ​​on rails
Deploy to Heroku [Ruby on Rails] Beginner
Preparing to introduce jQuery to Ruby on Rails
[Ruby on Rails] How to use redirect_to
[Ruby on Rails] How to use kaminari
Explanation of Ruby on rails for beginners ①
[Ruby on Rails] Button to return to top
How to build a Ruby on Rails environment using Docker (for Docker beginners)
How to solve the local environment construction of Ruby on Rails (MAC)!
How to debug the processing in the Ruby on Rails model only on the console
[Ruby On Rails] How to search the contents of params using include?
[Ruby on Rails] When logging in for the first time ・ How to split the screen in half using jQuery
Validation settings for Ruby on Rails login function
[Ruby on Rails] How to make the link destination part of the specified id
Deploy to Ruby on Rails Elastic beanstalk (EB deploy)
[Ruby on Rails] Change the update date and creation date to your favorite notation
[Ruby on Rails] How to display error messages
[Ruby on Rails] Until the introduction of RSpec
How to add / remove Ruby on Rails columns
Publish the app made with ruby on rails
[Ruby on Rails] Select2 introduction memo for Webpacker
[Ruby on Rails] Rails tutorial Chapter 14 Summary of how to implement the status feed
Tips for using the Spotify app on Ubuntu
[Rails MySQL] How to reset DB on heroku
[Rails] Procedure for linking databases with Ruby On Rails
(Ruby on Rails6) Creating data in a table
Determine the current page with Ruby on Rails
[Ruby on Rails] How to Japaneseize the error message of Form object (ActiveModel)
[Ruby on Rails] How to install Bootstrap in Rails
[Ruby on Rails] How to use session method
When creating an app with the new command in Ruby on Rails bundler: failed to load command: spring (/ Users/user name/directory name to create the app/app name/vendor/bundle/ruby ​​/ 2.6.0/bin/spring )
[Ruby On Rails] Description that allows only specific users to transition to the edit page
[Ruby On Rails] How to use simple_format to display the entered text with line breaks
[Rails / Uniqueness constraint] How to check model validation on the console / Uniqueness constraint for multiple columns
[Ruby On Rails] Correct description location of unique constraint that gives uniqueness to DB
[Ruby on Rails] How to implement tagging / incremental search function for posts (without gem)
Explanation of Ruby on rails for beginners ④ ~ Naming convention and how to use form_Tag ~
[Introduction] Try to create a Ruby on Rails application
Method summary to update multiple columns [Ruby on Rails]
[Ruby on Rails] How to write enum in Japanese
[Ruby on Rails Tutorial] Error in the test in Chapter 3
Rails / Ruby: How to get HTML text for Mail
[Ruby on Rails] Change URL id to column name