Reduce N+1 Queries

Question

N+1 queries detected what does that mean what should i do to make it work.

I am using Bullet gem for showing N+1 queries

user: kalyan
N+1 Query detected
  Emp => [:functions]
  Add to your finder: :include => [:functions]
N+1 Query method call stack
  /home/Documents/app/views/tics/show.html.haml:20:in `_app_views_tics_show_html_haml___2301551533476719406_237290860'

This is the message from bullet gem.

_app_views_tics_show.html.haml

- if (@tic.assigned_to == current_user.emp) or (current_user.emp_functions.map{|x| x.id}.include?(1) if current_user.emp_functions.present? )
              = best_in_place @tic, :subject
            - else
              = @tic.subject

help me to reduce n+1 query problem

emp.rb

has_many :emp_functions, inverse_of: :emp,dependent: :restrict_with_exception

belongs_to :user, inverse_of: :emp

emp_functions.rb

belongs_to :emp , inverse_of: :emp_functions

belongs_to :function

function.rb

has_many :emp_functions, dependent: :restrict_with_exception

has_many :emp, through: :emp_functions

user.rb

has_one :emp, inverse_of: :user, dependent: :restrict_with_exception

semi-related - http://stackoverflow.com/a/26251892/525478 – Brad Werth Mar 04 '15 at 08:05 — Brad Werth, Mar 04 '15 at 08:05

score 1 · Accepted Answer · answered Mar 04 '15 at 07:03

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.

NoMethodError: undefined method `emp_function_ids' for # from /home/.rvm/gems/ruby-2.1.5@/gems/activemodel-4.2.0/lib/active_model/attribute_methods.rb:433:in `method_missing' — sreenivas, Mar 04 '15 at 10:40

Jasdeep Singh · Answer 2 · 2015-03-04T06:55:22.577

0

If emp_functions is a has_many association on your user model (and it seems like it is), You should be doing:

current_user.emp_function_ids.include?(1)

edited Mar 04 '15 at 06:55

answered Mar 04 '15 at 06:50

Jasdeep Singh

3,276
4
28
47

I'd think that it's better to edit the current_user method, so you don't tackle the emp_functions every time you need to find a user or get a list of users. It could be lots of data to get in the memory. – Almaron Mar 04 '15 at 06:52

score 0 · Answer 3 · answered Mar 04 '15 at 07:28

0

This is a very specific solution to your issue and I advise against using it in a view

change current_user.emp_functions.map{|x| x.id}.include?(1) to

current_user.emp_functions.where(id: 1).exists?

This will result in 1 single query to the db everytime you hit the page containing it.

You can also remove that if statement to remove another db query.

answered Mar 04 '15 at 07:28

jvnill

29,479
4
83
86

didn't worked, would you please modify it in another way. what i mean was actually that query you gave me fetching proper record, but still N+1 Query Problem. – sreenivas Mar 04 '15 at 10:19
then the N+1 query problem happens somewhere else. – jvnill Mar 05 '15 at 01:09

Reduce N+1 Queries

3 Answers3