Filter in memory vs. hit the database again?


#1

Is it generally preferred to filter a large collection of objects in
memory or in the database. In the case of a single query, I know that
the database is preferred. But what about multiple filtering based on
atributes? For example,

@users = User.find(:all)
  @males = @users.find_all {|user| user if user.gender == 'male'}
  @females = @users.find_all {|user| user if user.gender ==

‘female’}

Then these are filtered AGAIN in the view:

males <%= @males.find_all{|user| user.ethnicity == 'white'}.size %> <%= @males.find_all{|user| user.ethnicity == 'black'}.size %> <%= @males.find_all{|user| user.ethnicity == 'asian'}.size %> <%= @males.find_all{|user| user.ethnicity == 'hispanic'}.size %> <%= @males.find_all{|user| user.ethnicity == 'other'}.size %>

My question is - should i hit the database separately for each of these
groups and subgroups, or filter in memory as I am doing now? Eventually
there could be 100,000+ records.


#2

Taylor S. wrote:

Then these are filtered AGAIN in the view:

My question is - should i hit the database separately for each of these
groups and subgroups, or filter in memory as I am doing now? Eventually
there could be 100,000+ records.

First a general and unhelpful answer: try both (maybe fake up some
data to see what happens as the database groups) and see what happens.

In this case you’re only (at least what you’ve shown) using the number
of people of the various types, so you could do something like

User.male.count :group => ‘ethnicity’
(assuming you had a male named scope doing the obvious) which will get
you the counts by ethnicity

Fred


#3

Frederick C. wrote:

In this case you’re only (at least what you’ve shown) using the number
of people of the various types, so you could do something like

User.male.count :group => ‘ethnicity’
(assuming you had a male named scope doing the obvious) which will get
you the counts by ethnicity

Fred

Thanks. I will give that a try.