ActiveRecord searching in batch (with find_by_sql)

Rails
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

On Aug 25, 3:24pm, Bhavesh S. [email protected] wrote:

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

3

Frederick C. wrote in post #1018465:

On Aug 25, 3:24pm, Bhavesh S. [email protected] wrote:

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

On Thu, Aug 25, 2011 at 8:53 AM, Bhavesh S. [email protected]
wrote:

What does the following return

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

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

–
Hassan S. ------------------------ [email protected]

twitter: @hassan