5

What's the best way to optimize a Rails app where you need to pull info from the database for a collection of items?

For example, if I am building an online forum system... I would have a number of Posts and the posts would have_many :recommendations and :comments and :favorites. When I display the posts in my view, I might need to display this info - like a count of how many comments there are, and whether or not my current user has favorited/recommended the post.

I was playing around with this and it seems there are a huge number of DB queries being executed! First I'm grabbing my collection of posts - then, iterating through each one, I am going to the comments table, the favorites table, etc... to get counts or see if records exist for that particular post. So for every post I have, I'm executing 3 separate queries!

Is there a more efficient way to get this data, or are tons of queries like this normal for a scenario like I describe?

Improve this question
4
  • I'm not a Rails expert but it sounds the system would run faster if you had one view that got all the information you need, instead of multiple repeated queries. Is it possible to write a view or two, and then tell Rails to query against the view(s)?
    – FrustratedWithFormsDesigner
    Jul 11 '12 at 21:04
  • I guess the question is - how do I get all the information I need without multiple queries? I can pull the posts I am going to display, but to see if a post is "favorited", I need to query the favorites table (which holds a user_id and a post_id showing which users have favorited a post). To see comments, I need to query the comments table (which has user_id, post_id, and content of the comment), etc...
    – Jim
    Jul 11 '12 at 21:17
  • Maybe your ORM can't handle it. Is there a way to pass your own query string to the database and the process the results in a custom function? Or can you point your ORM at a view which hides all of that complexity?
    – FrustratedWithFormsDesigner
    Jul 11 '12 at 21:32
  • 2
    Have a look at the Rails Guides, the chapters about joining tables and eager loading.
    – thorsten müller
    Jul 12 '12 at 9:22
3

If information like how many comments, up-votes, favorites a post have is frequently used, you are better off storing these counts somewhere; querying the DB every time for number of comments in a post, number of followers a user have, etc. may quite soon become your performance bottleneck.

Two common approaches exist:

  1. Add column/field for Posts in the database that store the comment count. when new comments are added, increase that counter field by one (synchronously), or update that count every hour/day (asynchronously).

  2. Use cache (memcache, redis) to store those numbers to reduce database queries. Memory cache are usually required for high traffic sites, anyway.

Either way you can weave it into your ORM / Abstraction Layer, so there won't be too much additional code involved. For advantages/disadvantages, there are plenty of discussions/debates around this issue on the net. Google it for thorough and detailed explanations :)

Improve this answer
2
  • 2
    For a good explanation of implementing a counter cache, try this RailsCast.
    – Jason Lewis
    Jul 17 '12 at 14:06
  • I used this technique to cut down on the calls to the DB...still not quite where I would like it, but I'm getting there! Thanks for the answer
    – Jim
    Jul 23 '12 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.