Extra queries listed by MiniProfiler

Question

In my controller action, I included all associations needed by the view, to avoid multiple calls to the database. (I'm trying to isolate the the views layer to only render the data collected by the controller).

---

I'v found out that the view still communicates with the database (17 Queries):

---

These 17 extra queries are not needed. Since I have tested the controller queries from the console, and it successfully collects all the data needed by the partial _dropdown (in 5 Queries) without any further database communication.

Here is the query in my controller, It meants to avoid the N+1 problem. (Including all the variables called in the view)

---

Here is the dropdown code:

- @messages.each do |message|
    %li.conversation-container
        %a{href: conversation_path(message.conversation_id)}
            - if message.sender != current_user 
                .notification-avatar{style: "background: url(#{message.sender.avatar_url}); background-size: contain; background-repeat: no-repeat; background-position: 50% 50%;"}
            - else
                - other_participant = message.conversation.conversation_participants.select{|p| p.user_id != current_user.id }.first.user 
                .notification-avatar{style: "background: url(#{other_participant.avatar_url}); background-size: contain; background-repeat: no-repeat; background-position: 50% 50%;"}
            %p
                %strong
                    - if message.sender != current_user 
                        = message.sender.name
                    - else
                        = other_participant.name
                %br
                - if message.sender == current_user
                    %i.fa.fa-mail-reply-all
                = truncate(message.body,length: 25)

                .time
                    = time_ago_in_words(message.created_at)
                    ago
- if @messages.count == 0
    %li
        .empty-state-text-white
            No messages

---

Output from console:

2.0.0-p353 :006 > ms = Message.dropdown_for(3).all
  Message Load (1.2ms)  SELECT "messages".* FROM "messages" LEFT JOIN messages AS m ON messages.id != m.id 
 AND m.conversation_id = messages.conversation_id 
 AND messages.created_at < m.created_at INNER JOIN conversation_participants AS cp ON cp.conversation_id = messages.conversation_id AND cp.user_id = 3 WHERE (m.id IS NULL) ORDER BY cp.seen , cp.updated_at DESC LIMIT 5
  User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (6, 4, 5)
  Conversation Load (0.4ms)  SELECT "conversations".* FROM "conversations" WHERE "conversations"."id" IN (4, 2, 3)
  ConversationParticipant Load (0.2ms)  SELECT "conversation_participants".* FROM "conversation_participants" WHERE "conversation_participants"."conversation_id" IN (4, 2, 3)
  User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (6, 3, 4, 5)
 => [#<Message id: 8, body: "saSasa", sender_id: 6, conversation_id: 4, sent: true, attachment_id: nil, attachment_type: nil, created_at: "2014-11-17 16:05:40", updated_at: "2014-11-17 16:05:40">, #<Message id: 2, body: "asdnas dagsdashjdg jahs d", sender_id: 4, conversation_id: 2, sent: true, attachment_id: nil, attachment_type: nil, created_at: "2014-11-17 11:32:36", updated_at: "2014-11-17 11:32:36">, #<Message id: 6, body: "SADASD A DSA ", sender_id: 5, conversation_id: 3, sent: true, attachment_id: nil, attachment_type: nil, created_at: "2014-11-17 13:43:34", updated_at: "2014-11-17 13:43:34">] 

2.0.0-p353 :007 > ms.first.conversation.conversation_participants.select{|cp| cp.user_id != 3}.first.user
 => #<User id: 6, first_name: "Ddsfsd", middle_name: nil, last_name: "Fsdfsd", photo: nil, email: "[email protected]", encrypted_password: "$2a$10$5sGIb2DbQ1ctMrTzD3AJ0uV18hhiC5Ei1wcfE7MSAvRU...", reset_password_token: nil, reset_password_sent_at: nil, remember_created_at: nil, sign_in_count: 1, current_sign_in_at: "2014-11-17 15:27:06", last_sign_in_at: "2014-11-17 15:27:06", current_sign_in_ip: "127.0.0.1", last_sign_in_ip: "127.0.0.1", confirmation_token: nil, confirmed_at: "2014-11-17 15:27:48", confirmation_sent_at: "2014-11-17 15:27:05", unconfirmed_email: nil, failed_attempts: 0, unlock_token: nil, locked_at: nil, authentication_token: nil, created_at: "2014-11-17 15:27:05", updated_at: "2014-11-17 15:27:48", slug: "ddsfsd_fsdfsd"> 

2.0.0-p353 :008 > ms.count
 => 3 

---

How can I stop these queries from running without a purpose?

Answer 1

* Debugging

Well after debugging every possible factor that could cause this issue. I have tried to set config.cache_classes to true, in my development.rb. This successfully removed all the extra queries.

I concluded that (by default) the schema is not loaded for any model when the classes are not cached. In other words, when config.cache_classes is set to false, the schema for each model is loaded for every request as a separate query.

Here is a similar issue column_definitions method being called before and after every single SQL statement on PostgreSQL.

* Conclusion

cache_classes should be set to false in development environment. Ignore the extra internal queries from postgresql connection adapter loading the schema for each model since it not going to affect your production environment (production has config.cache_classes set to true).

Answer 2

This may be so called 'N + 1' problem, it happens due to lazy loading. I can't say for sure without application log. You can use eager loading as described here.

Answer 3

Simple question. Have you tried put a .to_a in the end of your method call? Like @messages.to_a ?

Answer 4

Your query is not well formulated. You should either use includes or joins.

Break down your query into two as follows:

message_ids = Message.joins("LEFT JOIN messages AS m ON messages.id != m.id 
          AND m.conversation_id = messages.conversation_id 
          AND messages.created_at < m.created_at")
   .where('m.id IS NULL')
   .joins("INNER JOIN conversation_participants AS cp 
          ON cp.conversation_id = messages.conversation_id 
          AND cp.user_id = #{user_id}")
   .order("cp.seen, cp.updated_at DESC")
   .limit(5).map(&:id)

messages = Message.includes(:sender).
    includes(conversation: [{conversation_participants: :user}]).
    where(id: message_ids)

Answer 5

You can try bullet gem which will tell you is there any N+1 prolem in query. If there is no problem of N+1 problem then you should try to implement fragment caching.

Answer 6

I would check the log to see what those 17 queries are, or perhaps clicking on the 17 sql link will show those queries. From there, you may be able to see that you forgot to includes a table that causes the N+1 problem.

EDIT:

As noted in the 'Lazy Loading' section of this site, you can add .all to the end of your query in your controller action to trigger its execution, and prevent the query from lazily executing in your view. As mentioned in my comment, Rails scopes let you build up queries and execute when you use them. To execute, you can call .all, .count, .each or .first. In Rails 4, you can use .load to execute the query in the controller.