Rails: How do I minimize DB hits here? Eager loading isn't applicable

Question

Hi,

this question is maybe a little specific, but I think it's interesting from a general pov also.

In a Rails App users can subscribe to other users. When I show a list of users I have to check, if the current user has subscribed to the users in the list. If he has subscribed, I show the unsubscribe button and the other way around.

Because the whole thing depends on the current user I can't use eager loading. So when I show 20 users in the list, I generate 20 additional hits on the DB, which appears to me to be bad practice.

I'm thinking about a good way to solve this problem. The best solution I came up with so far is to load the ids of the users the current_user has subscribed to in the session during the login once and then just check every user.id against the ids in the session. But maybe this could lead to other issues when the user has subscribed to a lot of people. Also I'm not sure if it's the best way to load all subscriptions even though the user might never look at the user list during this session.

The next best thing which came to my mind was to do the same thing, but not during login but instead when a user-list is loaded.

What do you think?

Answer 1

You should definitely start working on a cache system. There are at least 3 ways you can follow. You can also combine them to get more efficiency.

Database caching

Create a relationship table to hold the ID relationships between the user and the subscribers so that you don't need to calculate them on the fly.

Model Caching

Expensive queries can be cached.

def find_subscribers
  Rails.cache.fetch("find_subscribers_#{current_user}") do
    # run the query
  end
end

View Caching

You can also cache view fragments to prevent expensive elaborations.

You might want to start with:

• Rockstar Memcaching
• Scaling Rails Videos

EDIT:

You query can be optimized.

ActiveRecord::Base.connection.execute("SELECT count(*) as c FROM subscribers_users WHERE user_id = #{other_user.id} AND subscriber_id = #{self.id}")

can become

counters = SubscribersUser.count(:conditions => { :subscriber_id => self.id }, :group => "user_id")

The query will return a Hash where the key is the user_id and the value the result of count. Then you can iterate the hash instead of running a query for any record in the view.

Answer 2

There's no rule against reloading the current user if it allows you to use eager loading:

user = User.find(current_user.id, :include => :subscribers)

Provided this is a simple has_many relation this is only 2 SQL statements.

Now you can iterate over user.subscribers without producing another DB hit.