Rails - Aug 2011 - ActiveRecord searching in batch (with find_by_sql)

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

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Aug 25, 3:24 pm, Bhavesh Sharma
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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?
>
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

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Frederick Cheung wrote in post #1018465:
> On Aug 25, 3:24pm, Bhavesh Sharma
<li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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?

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Thu, Aug 25, 2011 at 8:53 AM, Bhavesh Sharma
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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 Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
http://about.me/hassanschroeder
twitter: @hassan

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.