CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3
I-am-simple-user
I-am-simple-user

Reputation: 409

Suggestion for resolving time out error and optimizing each loop in rails

Currently I am facing a problem in which I am getting the Timeout::Error. Here is my detailed scenario

I have following tables

  1. clients
  2. users - (contains client_id)
  3. scheme_transactions - (contains user_id)

Model - Client

class Client < ActiveRecord::Base
   has_many :users
   has_many :scheme_transactions
end

Model - User

class User < ActiveRecord::Base
   has_many :scheme_transactions
end

Model - SchemeTransaction

class SchemeTransaction < ActiveRecord::Base
   belongs_to :client
  belongs_to :user
end

Consider an example, I have a client (stored in the clients table) say ABCL which has around 1000 users (stored in the users table) and there are around 50000 transactions (stored in the scheme_transactions table) done by all of the respective client users.

Now I have to find total of the first transaction of each user. For this I have written following logic and it is working fine on the rails console but getting Time out error in the browser.

users = User.joins(:client).where(clients: {id: clients})
first_purchase = Array.new
users.each do |user|
    user_transaction = SchemeTransaction.joins(:user).where(scheme_transactions: {user_id: user.id}).first
    first_purchase << user_transaction.points unless user_transaction.nil?
end
total_first_purchase = first_purchase.inject(:+)

So is there any other way to avoid the problem or how can I simplify my logic.

I am using ruby 1.9.3 and rails 3.2.11

EDIT Also tried using find_each method

users.find_each(:batch_size => 1000) do |user|
   user_transaction = SchemeTransaction.joins(:user).where(scheme_transactions: {user_id: user.id}).first
   first_purchase << user_transaction.points unless user_transaction.nil?
end

Upvotes: 1

Views: 263

Answers (3)

Pramod Shinde
Pramod Shinde

Reputation: 1902

If user model has client_id, I'm not sure why are using 'joins' in following query 

 users = User.joins(:client).where(clients: {id: clients})

Why don't you use 

 users = User.where(client_id: clients)

Second thing is in each loop, As scheme_transactions has user_id, you don't need use 'joins' for following query. Is following query working for you?, In rails 4.1.6 its not working for me. 

user_transaction = SchemeTransaction.joins(:user).where(scheme_transactions: {user_id: user.id}).first

Simply replace this as 

user_transaction = SchemeTransaction.where(user_id: user.id).first

Or 

 user_transaction = user.scheme_transactions.first

Upvotes: 0

Matt Stevens
Matt Stevens

Reputation: 1124

There are three joins here, each join will be using increasing amounts of memory. The third joins a 50000 row table to the result of clients * 1000 rows. Thats 50,000,000 x clients rows held in memory and temporary hard disk memory (page swaps are slow).

Retrieve just the users for this client

users = Client.find(client_id).users

In your users loop retrieve only the transaction you want by sorting and taking the first transaction. You may need to consider specifying DESC in the sort_by if you get the last transaction rather than the first.

user_transaction = user.scheme_transactions.sort_by(:date).first

Your models appear to be set up ok, so this should be all you need.

Upvotes: 0

lunr
lunr

Reputation: 5279

You can run a SQL query:

SELECT SUM(points)
FROM scheme_transactions
WHERE id in (
    SELECT MIN(id)
    FROM scheme_transactions st
    INNER JOIN users u ON st.user_id = u.id
    WHERE u.client_id = :client_id
)

Your method will queries the database for each User, hence the timeout.

Upvotes: 0

Related Questions