Out of memory loadObjectList

[stephenhb]

Hello all,

Is there a method in the API to iterate through rows without loading the entire result from a select?

I'm currently running out of memory (Fatal error: Allowed memory size of 16777216 bytes exhausted) when loading a large table (14k rows) using $db->loadObjectList();

The table is only going to get bigger, so loading the result into an array seems a crazy thing to do.

I've had a good hunt through JTable's loadAssoc and other methods in JDatabase but I must be missing something!

Is there a Joomla API equivalent of ..?

Code: Select all

while ($row = msql_fetch_row($result)) {
    echo $row[0] . ': ' . $row[1] . "\n";
}

[stephenhb]

Sorry to (have to) answer my own question, but the answer is NO.

There is nothing in the Joomla db API that avoids these loadObjectList errors when dealing with very large tables.

Poor design decision to curtail scalability at such a fundamental level in the API.

Ah well.

[secteur]

Hi stephenhb,

I agree with you, and I faced that issue as well. The problem is that we don't get a choice of clearing the mysql result resource when calling e.g. loadRow and loadObject (I believe these would be the closest methods to what you're mentioning). The resource is cleared automatically straight after getting the first record.

Although I really don't like to modify core joomla files, here's what I did in the libraries/joomla/database/database/mysql.php file:

Code: Select all

function loadRow( $first=true, $clear=true )
	{
		if( $first ) {
			if( !$cur = $this->query() ) {
				return null;	
			}
		} else {
			if( !$cur = $this->_cursor ) {
				return null;	
			}	
		}
		
		$ret = null;
		if ( $row = mysql_fetch_row( $cur ) ) {
			$ret = $row;
			if( $clear ) {
				mysql_free_result( $cur );
			}
		} else {
			mysql_free_result( $cur );
		}
		return $ret;
	}

It's compatible with current uses of loadRow thanks to the default values of $first and $clear.
Now if I use loadRow( false, false ) I'm actually keeping the mysql resource (not doing the query again) and moving pointer to the next record. In the end the resource is cleared.

Then to obtain what you mentioned, I use: (in a model)

Code: Select all

// any query
$query = 'SELECT * FROM #__weblinks WHERE 1';
$this->_db->setQuery( $query );
// First record, need to create the mysql resource
$row = $this->_db->loadRow( true, false ); // do what you need with this row
// Now go through all the rows without reloading the mysql resource
while( $row = $this->_db->loadRow( false, false ) ) {
   // do what you need with each row
   echo $row[0];
}

The $clear parameter helps if you want to stop your iteration on the way and still clear the resource properly.

Did the same with loadObject() as well.

But maybe there is a reason for the behavior chosen by the core developers. If so, it would be great if someone could let us know.