Zend_Db: fetchAll() or query()/fetch() for a huge number of records

Question

Assuming I have

$db is an instance of Zend_Db_Adapter_Abstract and
$sql = 'SELECT blah blah FROM table' will return a huge number of records.

There are two code fragments to process the returned data as follows.

// Code fragment 1 (let's call it C1).
$results = $db->fetchAll($sql);
foreach ($results as $row) {
    // Process $row
}

// Code fragment 2 (let's call it C2).
$stmt = $db->query($sql);
while ($row = $stmt->fetch()) {
    // Process $row
}

My understanding is that C1 will load all returned data to $results. So, a huge data is loaded to PHP memory. Below are my questions.

1. Does C2 load all data to PHP memory or does it process one by one like prepare/execute?
2. Assuming there is no other option, is C1 or C2 a better option?

Thanks!

Solution 1

Your hunch is correct. At least if you're using the PDO driver, ->fetch() reads the results unbuffered, whereas ->fetchAll() returns all the data in a big array.

Be aware that if you're using ->fetch(), you have to be careful about what you try to do inside your loop. You can't run additional queries on the same connection while you've still got an unbuffered result set.

So, if your plan is to update those same rows inside the loop, you'll need to find a way to delay executing the updates (by queuing then up somehow) until you've exited the loop.

Solution 2

To retrieve one row from the result set, use the fetch() method of the statement object. Reference

$sql = 'SELECT blah blah FROM table';
$stmt = $db->query($sql);
while ($row = $stmt->fetch()) {
    // Process $row
}

In above example $stmt = $db->query($sql); retrieved the resultset in the memory and fetch is being used to fetch the current row in the loop from the resultset, which moves the cursor to the next row until it reaches the the last row in the resultset.

To retrieve all the rows of the result set in one step, use the fetchAll() method. This is equivalent to calling the fetch() method in a loop and returning all the rows in an array.

$sql = 'SELECT blah blah FROM table';
$stmt = $db->query($sql);
$rows = $stmt->fetchAll();
echo $rows[0]['col1']; // The first field/column from the first row

Alternatively you can use

....
$table = new Mytable();
// Find a single row Returns a Rowset
$rows = $table->find(1234);

// Find multiple rows Also returns a Rowset
$rows = $table->find(array(1234, 5678));

Reference: Zend_Db_Table..

For more: Fetching a Row..

I think fetchAll() is faster because it retrieves all the data in one step and returns an array but consumes more memory but fetch() consumes less memory but retrieves the data one by one.

The API for fetch operations has been superseded to allow a Zend_Db_Table_Select object to modify the query. However, the deprecated usage of the fetchRow() and fetchAll() methods will continue to work without modification.

More Reference: Here.

Comments

[peidiam]

Thank you for your warning on using fetch() :)

[peidiam]

Your info is comprehensive and helpful. Thanks!