Rails 3.2 - Query Performance (Postgres)

Question

Background:

I'm creating a dashboard as a project and I have a query that I think is going to be a big performance issue:

<% for outlet in @outlets %>
    <% if Monitoring.where(:outlet_id => outlet.id).where('date(created_at) = ?', Date.today).exists? %>
        <li>
            <a class="done" href="<%= outlet_url(outlet) %>" rel="tooltip" title="<%= outlet.name %>"></a>
        </li>
    <% else %>
        <li>
            <a href="<%= outlet_url(outlet) %>" rel="tooltip" title="<%= outlet.name %>"></a>
        </li>
    <% end %>
<% end %>

What I'm trying to achieve is a series of dots on a page. If the anchor tag has a class of done, it will display as green, if not it will be red (done through CSS).

Aside from the obvious DRY issues here, this query is very heavy, so I'm looking at ways to improve it.

An Outlet is Monitored at least once a day (An Outlet has_many :monitorings). For each outlet I need to check if it has been monitored on that particular day, and output the HTML accordingly.

If anyone could help me with this it would be fantastic. (Also, any advice on caching this would be appreciated).

Cheers in advance :).

dbenhur · Accepted Answer · 2012-03-29 02:21:06Z

1

You might make a conditioned association for current monitors, then use includes to fetch the associated current monitorings on the original query.

class Outlet
  has many :current_monitorings, :class_name => "Monitoring",
    :conditions => proc { [ 'monitorings.created_at > ?', Time.now.midnight ] }
end

@outlets = Outlet.includes(:current_monitorings)

@outlets.each do |outlet|
  if outlet.current_monitorings.empty?
    # the no monitor today case
  else
    # it's been monitored today
  end
end

At the Postgres level, you'll likely benefit from an index on monitorings(outlet_id,created_at) to support the outer join implied by #includes.

BTW, it's bad style to be executing database queries in your view. Put the domain logic in your models, and have your controller execute the query and supply the results to the presentation layer.

answered Mar 29, 2012 at 2:21

dbenhur

20.5k4 gold badges51 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dbenhur Over a year ago

BTW, this shouldn't actually be a ridiculously expensive query, so don't worry about caching until you've demonstrated an actual problem

Ammar Over a year ago

I like this solution the most I think. In terms of caching, I think this it's definitely needed. There could be up to 2000 outlets on the same page, refreshing every 5 mins or so. What would you recommend as the best way to cache this?

dbenhur Over a year ago

Fetching 2000 rows with one outer join is not an expensive operation. Implement it first then measure. I'm not going to recommend cache because I doubt you need it.

Austin · Accepted Answer · 2012-03-28 23:44:49Z

0

Maybe try:

<% @outlets.includes(:monitorings).each do |outlet| %>
  <% css_class = outlet.monitorings.any? { |m| m.created_at == Date.today } ? 'done' : '' %>
  <li><%= link_to '', outlet_url(outlet), :class => css_class, :rel => "tooltip", :title => outlet.name %></li>
<% end %>

It'll perform 1 big query.

answered Mar 28, 2012 at 23:44

Austin

3,9003 gold badges25 silver badges26 bronze badges

Comments

Nick Kezhaya · Accepted Answer · 2012-03-29 01:05:46Z

0

A good way to cache this would be denormalizing your database with the use of callbacks. In your Outlet model, you could add a field called last_monitored_on; anytime a Monitor is saved, update the appropriate Outlet model with the date. Then, you wouldn't have to query Monitors at all.

You could also consider caching that page fragment, and let it expire daily.

answered Mar 29, 2012 at 1:05

Nick Kezhaya

1137 bronze badges

Collectives™ on Stack Overflow

Rails 3.2 - Query Performance (Postgres)

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related