ActiveRecord Eager Loading vs. Query Builder for Large Dataset Retrieval

Short answer

For a read-only view over several thousand rows, use asArray() (or Query Builder) for the bulk retrieval and reserve ActiveRecord for the small subset of rows that actually need model logic. There is no universal record-count threshold where ActiveRecord "breaks" — the crossover depends on row width, loaded relations, and your memory_limit, so it must be measured, not assumed. What matters more than the ActiveRecord-versus-Query-Builder choice is whether you buffer all rows at once; batching or streaming keeps peak memory bounded regardless of approach.

What is confirmed vs. what is likely

Confirmed behavior: with() collapses related queries into a bounded number of SQL statements, eliminating N+1 — but it does not reduce the number of model objects created. Every returned row still becomes a full ActiveRecord instance carrying attribute storage, relation state, and behavior overhead. asArray() and createCommand()->queryAll() skip hydration entirely and return plain arrays, which lowers per-row memory. The trade-off is real: array results bypass model events, validation, accessors, and behaviors, which can silently change output if your models transform attributes (e.g., date formatting, decrypted fields, computed getters).

Likely, but verify in your environment: the claim that arrays use dramatically less memory per row. The actual gap varies with column count, relation depth, and attached behaviors. PHP's own memory allocator, output buffering, and the PDO driver's result buffering all influence peak usage, so numbers from someone else's benchmark won't transfer to your schema.

Answering your two questions directly

1. Does AR overhead outweigh eager-loading benefits past 1,000 rows? Not inherently. 1,000 hydrated models is usually trivial; the pain typically appears at tens of thousands of wide rows, or when relations multiply the object graph. If the view is truly read-only and needs no model logic, the eager-loading "benefit" is mostly irrelevant anyway — take the arrays.
2. At what volume must you switch? There is no fixed number. Measure peak memory at your real dataset size; switch (or batch) when you stop having comfortable headroom under memory_limit — a reasonable working rule is to keep peak usage well under half the limit so concurrent requests don't compound it.

Practical pattern

// Bulk read-only retrieval as arrays, streamed in chunks
$query = (new \yii\db\Query())
    ->from('order')
    ->where(['status' => 'closed']);

foreach ($query->batch(500) as $rows) {
    foreach ($rows as $row) {
        // render row; peak memory stays ~500 rows
    }
}

If you want to keep the ActiveRecord query API (scopes, relation definitions) without hydration cost, Order::find()->with('customer')->asArray()->batch(500) gives you both. Hydrate individual models only where business logic is needed:

$order = Order::findOne($id); // events, behaviors, validation intact

Verify before committing

• Wrap each variant in memory_get_usage(true) / memory_get_peak_usage(true) in a CLI script that mirrors production row width and size.
• Enable Yii's db log target in debug to confirm with() actually prevents N+1 (and that you're not lazy-loading inside the loop by accident).
• Compare three variants on the same query: full ActiveRecord, asArray(), and raw Query Builder. Decide from observed headroom, not a threshold from a blog post.
• Confirm behavior against your installed Yii 2 minor version and PHP version, since driver buffering details differ.

One detail that could change this recommendation

Do any of the models in this report override afterFind(), accessors, or attach behaviors that transform attribute values? If yes, switching to arrays will silently change the output, and the safer path is batched ActiveRecord or explicitly replicating those transformations in the query — so that answer determines whether the hybrid or the pure-array approach is correct here.