When To Use Active Record And When Not.

How slow really is AR. I want to know when to use AR and when not. what is the right approach.

Also if my model extends CActiveRecord does it mean it is using active record or even if i just intend to use dao(via a model function) do i need to extend CActiveRecord.

Also is what i am doing right.


I have found problems with ActiveRecord when building CGridView while using $criteria->with (joining tables). It can build some really funky and inefficient COUNT() queries, sometimes up to 20 seconds on my local dev server. This is one technique I use while still using AR: http://www.mattiressler.com/customising-cgridview-using-a-function-to-display-related-data/

I follow the simple rule that if its a relatively simple query, then use AR, otherwise use DAO.

You do not need to extend ActiveRecord to use DAO

Sorry I do not have the time to review your code… at a quick glance, you have things in your controller (business logic) which I would put in my model.

Hi awebdeveloper,

I’m not sure how slow AR is. But according to my (poor) experience, you can do with AR 95% of your task without any performance issue.

It is true that AR is slower than DAO or pure SQL, and we sometimes do have to use them, but optimizing DB access is not the only task that we have to do. We have a lot more things to do to complete our project. And we have a limited span of time to accomplish it. That’s why we use AR and the framework.

Once you have accustomed to AR, it’s far more effective than pure SQL or DAO in coding the boring ordinary tasks. There’s no reason at all to avoid AR if you are going to use Yii for your development environment. :)

I agree with Softark.

I use AR a great deal. Its only when I have relatively complex queries that I switch to DAO. If I am using AR for a gridview/pagination, I either avoid joins or build my own dataProvider and count query using DAO.

I’ll second softark and Backslider. I tend to use AR since its comfortable and useful. You can turn on your DB logging and see for yourself how much SQL activity it creates. Nothing surprising there, hence the minor performance impact, at least in comparison to manual SQL work. At the end of the day its probably MySQL (or any SQL server you’re using) that’s the bottleneck, not the AR. To overcome that, you probably want to resort to wise caching procedures, crafted carefully to prevent introducing (software design based) bugs into the application.

This reminded me that there is an issue with related models, count query and pagination. Have a look here and in the resources mentioned in that thread. It might be what’s you’re hinting above.

If you still experience 20 seconds and more per query then I strongly recommend examining your DB schema design. A few indexes might all it takes to change the picture completely (but don’t settle for this only. Check your schema carefully).

From memory, the problem was the column that AR was selecting for the count (which was plain wrong). A simple COUNT(*) sufficed. Using DAO along with my own COUNT resolved the issue. There are times also that SQL_CALC_FOUND_ROWS is a better choice.

From my memory :slight_smile: you can use specify your hand crafted ‘count query’ and thus enjoy all worlds.

So we are on the same boat. :D

Why don’t you come and join us, awebdeveloper?

@Backslider and Boaz

One thing I like in Yii’s AR is that it’s relatively easy to use a plain SQL in it. "SELECT COUNT(*) … " is one of my favorite, too.

And I recently wrote a wiki on search by HAS_MANY relation.


Please take a look at it. I believe you two will like it. :)

Thanks @softark, @Backslider, @Boaz

After quiet a bit of research i decided to use AR for most writes with associated reads and dao for the pure reads.

I also want to know how can i benchmark it. I wish to do some benchmarking on DAO vs AR for my usecases.

You might find this topic interesting:


Do read the two pages - the bottom line is: use AR and then optimize the most complex queries to use DAO.

About benchmarking, see "Logging" page of the guide.

Especially 5.Performance Profiling and 6.Profiling SQL Executions


Thanks any other solution that gives for the whole code i.e which function took how much time … how much times did each query took… total execution time etc.