When to use Active Record is a common question among developers, Yii and overall.

I have about 500K records in my DB tables and each query there are 2 or 3 joins. I was getting data via AR, about a hundred records a time and noticed that using createCommand consumes less memory and CPU. I think with DAO instead of AR it will be faster.

It is true that Active Record consumes memory for storing objects and CPU cycles for instantiate these objects.

Is AR bad? Is it for small projects only? We have lots of inserts, about 5000 records an hour and we're using AR for that. Should we re-write?

AR is a pleasure to use when you're managing not that many records at the same time. CRUD is very easy with it. Yii 2 dirty attribute support (only what was modified is saved) off-loads database and mitigates many parallel editing conflicts. If you don't have complex logic in your app and you don't need entity abstractions, AR is an ideal choice.

AR is OK for queries not too complicated when they return no more than a hundred objects. Yes, it is faster and less memory consuming to use query builder or asArray() but working with arrays is significantly less convenient.

For complex queries, such as aggregates or reports, AR isn't recommended. These are easier to express with query builder or SQL. Same goes for import and export routines.