ActiveRecord searching in batch (with find_by_sql)

rubyonrails-talk
1

I have an issue where I am getting a large number of records from the database and I cant afford to keep it in the memory due to performance issues.

Therefore using the following would be ideal,

User.find_each(:batch_size => 5000, :start => 2000) do |user|   NewsLetter.weekly_deliver(user) end

however,

I would like to use find_by_sql since my query is user defined. Question is, is it possible to use find_by_sql with batch_size and iterate through all the record-sets?

Thanks Bhavesh

2

With difficulty - you'd have to be able to modify the query passed to find_by_sql in order to add the stuff that limits the number of records returned (not that this isn't as simple as just adding limit/ offset - on mysql at least large offsets are slow. This might not be a problem for you)

Fred

3

Frederick Cheung wrote in post #1018465:

however,

I would like to use find_by_sql since my query is user defined. Question is, is it possible to use find_by_sql with batch_size and iterate through all the record-sets?

With difficulty - you'd have to be able to modify the query passed to find_by_sql in order to add the stuff that limits the number of records returned (not that this isn't as simple as just adding limit/ offset - on mysql at least large offsets are slow. This might not be a problem for you)

Fred

Thanks Fred,

I was afraid that would be the case. Would have been great if ActiveRecord had limit/offset as parameters in find_by_sql.

One more question if you dont mind,

What does the following return

statement_handle = ActiveRecord::Base.connection.execute(@sql)

Everthing I read tells me that this actually executes the sql and returns the dataset which will then be stored in the memory.

However, my colleague believes that it returns the statement_handle and than we can use statement_handle.fetch to return row one by one. I am having a hard time processing that.

Any input?

4

Why don't you just open a console and try it?