CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3
Slick23
Slick23

Reputation: 5897

Reducing database hits in Rails

I have two models, projects and words where project has_many :words (words is really just a model that holds the quantity of words written each day for each project.

I have a view that I build like this, which shows all the days from start to end in the project and how many, if any words were written on that day:

<% project_range(@project.start, @project.end).each do |day| %>
    <%= day %>
    <%= get_word_count_by_date(@project, day ) %>
<% end %>

And in my helper:

def project_range(start, finish)
  project_days = (start..finish).collect
end

def get_word_count_by_date(project, date)
  word_count = Word.find_by_project_id_and_wrote_on(project, date)
  if word_count
    word_count.quantity
  else
    0
  end
end

Trouble is, in the view, that hits my database a lot. For example, if the project is 30 days, I get:

  Word Load (0.2ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-01' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-02' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-03' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-04' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-05' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-06' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-07' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-08' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-09' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-10' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-11' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-12' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-13' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-14' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-15' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-16' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-17' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-18' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-19' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-20' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-21' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-22' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-23' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-24' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-25' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-26' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-27' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-28' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-29' LIMIT 1
  Word Load (0.1ms)  SELECT "words".* FROM "words" WHERE "words"."project_id" = 2 AND "words"."wrote_on" = '2011-09-30' LIMIT 1

Is there a way to do this without querying every single day in the length of the project? I tried starting by loading all of a project's words first, but couldn't figure out how to get the days with zero in there.

Upvotes: 1

Views: 142

Answers (3)

Kristian PD
Kristian PD

Reputation: 2695

You could use a block helper to keep it clean and avoid looking them up:

def project_range(project, start, finish, &blk)
  words = project.words.where(:wrote_on => start..finish)
  word_map = words.index_by(&:wrote_on)
  for day in start..finish
    word_count = word_map[day] ? word_map[day].quantity : 0
    blk.call(day, word_count)
  end
end

Then use it like

<% project_range(project, start, finish) do |day, word_count| %>
   <%= day %>
   <%= word_count %>
<% end %>

You could also clean up the helper a bit (avoid having SQL in it), maybe by passing the list of pre-fetched words or using a scope

EDIT: m_x suggested the start..finish where clause on wrote_on which is cleaner!

Upvotes: 2

lucapette
lucapette

Reputation: 20724

I'd go for something like:

@words = @project.words.where("wrote_on >= ? and wrote_on <= ?", start, end)

and than use group_by to display them in the view:

@words.group_by(&:wrote_on).each do |day, word|
<%= day %>
<%= word.quantity %>
end

Upvotes: 0

Jesse Wolgamott
Jesse Wolgamott

Reputation: 40277

This is a "n+1" problem... What you want to do is join words and projects in your query so that all the words for each project are included in the result set.

Assuming that your project "has_many :words":

@project = Project.find(:id, :include => :words)

Now the words collection on each project will be pre-populated with the words in just 1 query.

Read more under the "Eager Loading of Associations" http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

Upvotes: 3

Related Questions