Lannister Lannister - 2 years ago 64
Ruby Question

How to efficiently get all the rows from a huge table in Rails?

I have two tables

with more than 50k records in users and 90k records in notes.

class User < ActiveRecord::Base
has_many :notes

Schema of users_table:

id email created_at

Schema of notes_table:

id user_id created_at category impact_score

I am trying to create a temporary table
which can have data of both the tables on which i can perform ActiveRecord queries to fetch the data.

Schema of user_notes_table:

id user_id notes_id email user_created_at notes_created_at category impact_score

I am doing this

def self.populate_temp_user_notes
users = User.all
users.each do |user|
user.notes.each do |note|
user_notes =
user_notes.user_id =
user_notes.notes_id =
user_notes.auth_token_created_at = user.auth_token_created_at
user_notes.notes_recorded_at = note.recorded_at
user_notes.category = note.category
user_notes.well_being_score = note.perception_score

Looping through all the uses and their notes is very long memory eating process what other approach can i use?

EDIT From here:-

My requirement is: I have series of queries seperated by AND and OR Conditions that take use of folowing table: users, notes, transactions, subscription. suppose my query to get target users is
(Query1 OR Query2) AND Query3 AND Query4

then output of every query is input of next query.


total users in DB = 1000
1. user_list = (Query1 or Query2) #=> 500 users
2. taking 500 users from user_list as input for next query
3. user_list = user_list AND Query3 #=> 300 users
4. taking 300 users from point 3 as input for query in point 4
5. user_list = user_list AND Query4 #=> 50 users

in the last user list i have my target users and there notes.

Answer Source

I would use a combination of find_each and includes to decrease both: memory usage and the number of database queries:

def self.populate_temp_user_notes
  User.includes(:notes).find_each do |user|
    user.notes.each do |note|
        user_id:     ,
        notes_id:    ,
        auth_token_created_at: user.auth_token_created_at,
        notes_recorded_at:     note.recorded_at,
        category:              note.category,
        well_being_score:      note.perception_score,

Another very fast option might be to do this with in plain SQL like this:

INSERT INTO temp_user_notes
  (user_id, notes_id, auth_token_created_at, notes_recorded_at, category, well_being_score)
  SELECT,, users.auth_token_created_at, notes.recorded_at, notes.category, notes.perception_score
    FROM users INNER JOIN notes ON = notes.user_id;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download