# AR, find(:all), loops and memory usage

**URL:** <https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121>\
**Category:** Rails\
**Created:** [December 3, 2006, 12:09am UTC](https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121 "2006-12-03T00:09:13Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![John-Scott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ac91a4/32.png) [@John-Scott](https://www.ruby-forum.com/u/John-Scott)\
**Post date:** [December 3, 2006, 12:09am UTC](https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121/1 "2006-12-03T00:09:13Z")

</div>

I’m a beginning programming using ActiveRecord outside of Rails to do  
conditional processing of database records. So far, I’ve been  
successful. However, my script loads all matching records into memory  
first. There are hundreds of thousands of matching records so the  
script quickly consumes over 500MB of RAM before any processing is  
done. Is there a way to avoid this preloading of row objects in memory?  
Below is an example of the type of thing I’m trying to do (although the  
actual table row-level processing I’m doing is more complicated than  
the example, but this isn’t relevant to my question):

#example code  
require ‘rubygems’  
require ‘mysql’  
require ‘active\_record’

ActiveRecord::Base.establish\_connection(  
:adapter =\> “mysql”,  
:username =\> “root”,  
:password =\> “password”,  
:database =\> “my\_schema”  
)

class MyTable \< ActiveRecord::Base  
end

for m in MyTable.find(:all, :conditions =\> “some\_column=‘criterion to  
match’”)  
m.other\_column = “new value”  
m.save  
end  
#thanks for any tips

---

<div class="post-metadata">

**Author:** ![John-Scott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ac91a4/32.png) [@John-Scott](https://www.ruby-forum.com/u/John-Scott)\
**Post date:** [December 4, 2006, 7:16pm UTC](https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121/2 "2006-12-04T19:16:03Z")

</div>

Well, for some reason it took a few days for my first message to appear  
in google-groups. In the meantime, I figured out an imperfect but  
useable solution. I changed the loop so that it only loads the id  
values into memory, and then issues a select query for each id, so that  
it only creates the an object with all column values for one row at a  
time. Now my loop looks like this:

for m in MyTable.find(:all, :select =\> “id”, :conditions =\>  
“some\_column=‘criterion to match’”)  
p = MyTable.find(m.id)  
p.other\_column = “new value”  
p.save  
end

In my particular case, this saved me from creating 500MB+ of objects in  
memory and instead only used ~70MB using only the id values.  
Granted the script has to issue a SELECT query for every id, but in my  
case this is acceptable as the loop is on a timer anyway (only querying  
the database every couple of seconds).

If anyone else has a more elegant solution to this problem, please  
chime in.

---

<div class="post-metadata">

**Author:** ![John-Scott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ac91a4/32.png) [@John-Scott](https://www.ruby-forum.com/u/John-Scott)\
**Post date:** [December 4, 2006, 8:00pm UTC](https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121/3 "2006-12-04T20:00:31Z")

</div>

You’d be better off writing custom SQL for this particular case. If your  
database supports subselects, you could do:

MyTable.execute(“UPDATE my\_table SET other\_column = ‘new\_value’ WHERE id  
IN  
(SELECT id FROM my\_table WHERE some\_column = ‘criterion to match’)”)

David R.

---

<div class="post-metadata">

**Author:** ![John-Scott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ac91a4/32.png) [@John-Scott](https://www.ruby-forum.com/u/John-Scott)\
**Post date:** [December 4, 2006, 8:16pm UTC](https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121/4 "2006-12-04T20:16:36Z")

</div>

David…thanks for the reply. However, I’m using the google-geocoder  
gem and so I have a timed loop (50,000 Google Maps API daily geocode  
limit = request every 1.728 seconds) that grabs an address (:condition  
=\> “latitude=’’”) and then geocodes the parcel. I just didn’t want to  
clutter up my example or distract the discussion with the particulars.  
For what it’s worth, here is the actual loop:

for m in Property.find(:all, :select =\> “id”, :conditions =\> “latitude  
= ‘0’”)  
p = Property.find(m.id)  
address = p.address + ", " + p.city.name + ", " + p.state + " " +  
p.zip  
begin  
location = gg.locate address  
rescue GoogleGeocode::AddressError  
else  
p.latitude = location.latitude  
p.longitude = location.longitude  
p.save  
end  
Time.new  
sleep 1.728  
end

Using the ‘google-geocode’ gem and Active Record to interact with the  
database was the cleanest and easiest way for me to accomplish my  
specific task. And it works. Again, I’m a beginner programmer so that  
has a lot to do with my choices here.  
So if there is a smarter way to do this, I’m all ears and eager to  
learn.

John-Scott

---

<div class="post-metadata">

**Author:** ![John-Scott](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/ac91a4/32.png) [@John-Scott](https://www.ruby-forum.com/u/John-Scott)\
**Post date:** [December 4, 2006, 8:52pm UTC](https://www.ruby-forum.com/t/ar-find-all-loops-and-memory-usage/78121/5 "2006-12-04T20:52:03Z")

</div>

John-Scott wrote:

> David…thanks for the reply. However, I’m using the google-geocoder  
> gem and so I have a timed loop (50,000 Google Maps API daily geocode  
> limit = request every 1.728 seconds) that grabs an address (:condition  
> =\> “latitude=’’”) and then geocodes the parcel. I just didn’t want to  
> clutter up my example or distract the discussion with the particulars.  
> For what it’s worth, here is the actual loop:
> 
> for m in Property.find(:all, :select =\> “id”, :conditions =\> “latitude  
> = ‘0’”)  
> p = Property.find(m.id)  
> address = p.address + ", " + p.city.name + ", " + p.state + " " +  
> p.zip  
> begin  
> location = gg.locate address  
> rescue GoogleGeocode::AddressError  
> else  
> p.latitude = location.latitude  
> p.longitude = location.longitude  
> p.save  
> end  
> Time.new  
> sleep 1.728  
> end
> 
> Using the ‘google-geocode’ gem and Active Record to interact with the  
> database was the cleanest and easiest way for me to accomplish my  
> specific task. And it works. Again, I’m a beginner programmer so that  
> has a lot to do with my choices here.  
> So if there is a smarter way to do this, I’m all ears and eager to  
> learn.
> 
> John-Scott

You may need an outer loop like:

main = Property.find(:all, :select =\> “id”, :conditions =\> "latitude

> = ‘0’", :limit=\>1000)

loop through and increment count

main = Property.find(:all, :select =\> “id”, :conditions =\> "latitude

> = ‘0’ and id \> #{count} ", :limit=\>1000)