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

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.

• Regarding the code, since I did not actually verify it when writing qiita, an error may occur if it is a copy. However, by using a method that returns an array such as select map find detect collect, you can get the same data as when accessing the model from within a variable that has large data.

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.