Reducing N+1 Queries Using the Bullet and Rspec Gems

Reducing n+1 queries using the Bullet and RSpec gems

The most effective way is not at all. There may be some legitimate advantages to reducing n+1 queries in tests, most obviously to speed up overall execution time. It is quite possible, however, that you are either testing too much or making less significant gains than what is possible. I also usually find it unappealing to write additional code to help prop up tests, rather than to contribute to the overall value of the application.

Please allow me to propose an alternate use of your time. Only unit test to the absolute minimum of your comfort level. I personally like to focus on complex methods involving validation related issues and money or other math, you may have different priorities. Drawing the line will free up a whole bunch of time writing the most useless and fragile part of your test suite that will occupy the majority of your maintenance budget.

Now, what to do will all of your extra time? Don't worry, we'll find something for you to do... You can start by writing some acceptance tests, particularly for the areas that use the objects that you just dropped a whole bunch of unit tests for. Now your n+1 warnings are actually coming from the same spot they will when the user hits the page. Now you can go on to remove all of the n+1 queries.

But wait! Don't do that either. Instead, spend a good deal less time setting up your relationships to use the touch option. Then, when a child object gets updated, the parent will be updated, too. What the hell does this have to do with n+1 queries, you might be wondering. It seems like we're just adding queries...

That's where the Russian doll caching comes in. Adding this, and properly testing it, will consume the freed up unit testing and n+1 elimination time (and then some, if you're not careful). The nice thing is that it is way more "real world", much more resilient to insignificant or irrelevant implementation changes in your models and whatnot, and a huge performance boost to your application well beyond what eliminating every n+1 query by eagerly loading everything up front might have provided. You will want to move as much as possible into nested caching and load everything as lazily as humanly possible, to take full advantage of this method.

Long live n+1!

Reduce N+1 Queries

Imho, the most efficient way to get rid of this N+1 query is to alter the current_user method, adding includes(:emp_functions) to the User.find call. The way you do it depends on the way you handle your authentication. If you're using some kind of gem (Devise or Sorcery for example) you'll need to reopen those classes and alter the method, not forgetting to use super. If youэму written your own authentication, you'd be able to do that more easily.

Second thing I noticed is that you don't actually need to use map on your user.emp_functions in the view, provided that emp_functions is a has_many association on the User model. You can just flatten it to current_user.emp_function_ids.include?(1). This will help you get rid of the N+1 query problem, but only in this particular case. If you do tackle the emp_functions of the current user often in many places, I'd recomend using the first path I described.

Third third thing, not directly relevant to the question is that I really don't think that code belongs in the view. You should move it to a helper or a decorator, keeping the view clean and readable.

Implement eager loading to stop N+1 - Rails

Bullet points you to the place where it detected multiple calls of the same association, not to the place where you should add eager loading.

Somewhere in the _list.html.erb template you probably traverse your RSVPs (whatever that is) and for each RSVP you are trying to determine whether it has any ticket or not by calling the tickets association.

Bullet advices you to add include(:tickets) to the finder which sets up the variable for RSVPs (probably somewhere in your AcceptedRSVPs controller) that you traverse in the template. Once that is done, there won't be an SQL run for each RSVP to find out its tickets and thus you'll get rid of the N+1 problem.

Rails query optimization(eliminating n+1 queries)

I do not see the query itself, so I probably wont be able to answer specifically for this case.

1. Use Eager Loading to transform N+1 queries into 1

In general, your first step should be using eager loading technique to prevent N+1 queries. Most likely you are requesting associated collection (or single object) that is not yet loaded.

# controller
def index
  @customers = Customer.active
end

# view
<% @customers.each do |c| %>
  <%= c.name %> # this is fine the object is already in memory from your controller
  <%= c.address %> # this one makes a query to the database
<% end %>

This is usually solved by adding includes(association).

@customers = Customer.active.includes(:address)

2. Make sure you have an index for association's foreign key

Another good thing to have is an index for association's foreign key.

add_index :customer, :address_id

DB engine may choose not to use this index when building a plan of execution for some complex query, but, for a simple one, this is the case.

3. Use bullet gem

There is a badass gem called bullet. It will watch your queries while you develop your application and notify you when you should add eager loading (N+1 queries), when you're using eager loading that isn't necessary and when you should use counter cache.

Rails - eliminating n + 1 queries without table joins

From the API documentation on #includes:

Specify relationships to be included in the result set.

Keyword is relationships: you specify a whole relationship to be included, not a field.

You should try this:

@user.friend_requests.includes(:friend) do |friendship|

  # whatever you need before

  # access the friend's name through the friend relationship
  # this should not require another query as the friend object was
  # preloaded into friendship using #includes
  friendship.friend.name

  # whatever you need after

end

Edit: actually, this will trigger 2 SQL queries, a first to get the user's friend_requests, then a second to get all friend relations at once.
The second query being of the form

SELECT "users".* FROM "users" WHERE "users"."id" IN (/*LIST OF IDs*/)

using all the ids found in the first friend_requests query.

If you want only one query to be fired, you should try a JOIN, probably like this:

@user.friend_requests.joins(:friend).includes(:friend)

Rails has_many :through and N+1

I think the problem here is this:

You are including :memberships, in the joins, so if you call chat.memberships it will use the memberships stored in memory.

But you are calling chat.memberships.order('id ASC') which is a sql query that Rails hasn't cached and so will have to call.

The simple solution is to sort in Ruby and not SQL:

chat.memberships.sort_by(&:id)

---

Related Topics