Query Batching with Db::batch() + Db::each()

When you need to work with a large data set, you run the risk of running into memory limit errors if you fetch all the data at once.

To mitigate that risk, you can use query batching, which divides the data into batches, in theory limiting the amount of memory PHP is consuming.

Yii provides two methods for executing a query in batches: yii\db\Query::batch() and yii\db\Query::each().

However, MySQL connections are buffered by default, so they’ll still return all the data to PHP at once, mostly eliminating any benefit you’d expect from batching the query.

Craft 3.7 introduced craft\helpers\Db::batch() and craft\helpers\Db::each() to potentially help alleviate this MySQL limitation. It’s best to use these methods wherever you’re taking advantage of batched queries in your own code:

// Old 
foreach ($query->each() as $row) {
    // ...
}

// New
foreach (\craft\helpers\Db::each($query) as $row) {
    // ...
}

The change will have no impact on PostgreSQL connections which already batch properly, and on its own it will have no impact on MySQL’s buffered connections.

Enabling the useUnbufferedConnections config setting, however, will execute batch queries on a separate, unbuffered MySQL connection. This config setting is false by default because it comes with caveats:

1. Maintaining two database connections requires more memory for the database engine, which could negatively impact resource-limited systems—even though PHP’s required memory and execution time would likely be lowered.
2. Actually paginating results on this separate connection could lead to unexpected behavior for complex query operations.

We strongly recommend testing batch queries and real-world performance impacts when using MySQL and enabling the useUnbufferedConnections config setting.