Out of memory while iterating through rowset

Question

Hi All,

I have a "small" table of 60400 rows with zipcode data, 6mb in total. I want to iterate through them all, update a column value, and then save it.

The following is part of my Zipcodes model which extends My_Db_Table that a totalRows function that - you guessed it.. returns the total number of rows in the table (60400 rows)

public function normalizeTable() {
    $this->getAdapter()->setProfiler(false);

    $totalRows = $this->totalRows();        
    $rowsPerQuery = 5;

    for($i = 0; $i < $totalRows; $i = $i + $rowsPerQuery) {
        $select = $this->select()->limit($i, $rowsPerQuery);

        $rowset = $this->fetchAll($select);
        foreach ($rowset as $row) {
            $row->{self::$normalCityColumn} = $row->normalize($row->{self::$cityColumn});
            $row->save();
        }
        unset($rowset);
    }
}

My rowClass contains a normalize function (basicly a metaphone wrapper doing some extra magic).

At first i tried a plain old $this->fetchAll(), but got a out of memory (128MB) right away. Then i tried splitting the rowset into chunks, only difference is that some rows actually gets updated. But still getting out of memory error.

Any ideas on how i can acomplish this, or should i fallback to ye'olde mysql_query()

Answer 1

I will suggest using the Zend_Db_Statement::fetch() function here.

http://files.zend.com/help/Zend-Framework/zend.db.statement.html

Answer 2

I suggest rebuilding the select statement so that only the columns needed to be upgraded will be selected $select->from($table, (array)$normalCityColumn)...