Rob's Blog Coding, cooking and, well, not much else

Chunking Large Queries with Iterators in PHP

When executing large queries it's usually best not to load the whole result set in one go. Memory isn't infinite and PHP isn't renowned for handling it very well. So the obvious answer is to chunk the large query in to lots of smaller queries. This is a simple method I use for hiding the fact the query is being chunked behind an iterator.

We want something to which we can just provide a PDO object, an SQL query and the chunk size. We should then be able to iterate over the resulting object as though it were a single result set. Check out the code for ChunkedQueryIterator.

$sql    = "SELECT * FROM test LIMIT :offset, :limit";
$limit  = 1000;
$result = new ChunkedQueryIterator($pdo, $sql, $limit);
foreach ($result as $row) {
  // do stuff
}

This works well but the interface is a little clunky. What would be really nice is if we could execute chunked queries directly from our PDO object, like in the example below. We can achieve this with another common design pattern, the decorator. We only need to add the one method used above, here's the source for ChunkedQueryDecorator.

$pdo = new ChunkedQueryDecorator($pdo);
foreach ($pdo->chunkedQuery($sql, $limit) as $row) {
  // do stuff
}

How do you handle large database queries? Let me know in the comments...

Bookmark It