ActiveRecrd VS sql staatements and ActiveRecrd performance

hi everyone,

recently i heared that the activerecord statement takes 10 times more than a simple sql statemtn in Yii.

i mean when i do for example :

$sql="SELECT * FROM news";

$rows = Yii::app()->db->createCommand($sql)->queryAll();

is 10 times less time executing than a :

$rows = News::model()->findAll();

is this true ? and if it’s true, so i suppose that when we will have a BIG aplication that contain a big number of model and CRUD manipulations so it will bea big problem ! because server side will be exhausted specially the sql server…

There is a quote you need to be aware of.

‘Avoid premature optimization’

In other words, you need to code your app and worry about the speed of your SQL later. Use AR until you can’t anymore. Profile your queries. If some of them are too slow, rewrite as SQL.

Yes but i’m about developping a large application that need massive interraction with database, so i need to know if it’s true or flase so i plan my developping way, i mean i must choose from the beginning if i use ActiveRecord statements or sql from the beginning.

because i have strict constraints with my sql server , it too slow, so i search the optimal way to work with less heavy statments, you see ?

did anyone have an idea ? :unsure:

Use Sql statement and only in sql standard and only in the model. In the future you could translate easily to ActiveRecord.

Simple AR queries (findByPk(), findAll()) won’t cause extra load on database server because they generate the same SQL queries that you would use with createCommand(). Simple relational queries will work without a problem too, since foreign keys are indexed. For queries with complex conditions that need to be optimized there’s CActiveRecord::findAllBySql(). For really heavy statistical queries you can always fall back on DAO.

Warning: AR will result in slower script execution and increased memory usage, but it happens on PHP-side rather than on the database server.

I agree, sql queries won’t take long time, but memory usage will be high when one will try to get 100 records from a database with AR. Once I had to switch to DAO, and not because queries were slow, but because of memory usage (I needed to get a really huge amount of data from db).

In my own personal testing, I got about a 3x performance difference.

For ~700 simple findByPk() queries, I got 3 seconds for AR and 1 second for DAO.

Also, keep in mind that you AR and DAO are slightly incompatible. That is, AR is an object interface and DAO is an array interface.




There are always trade offs to everything. Speed vs memory, or usability vs performance. In many cases AR is clearer and easier to understand but that always comes at a cost. The cost has been covered by others.

If you REALLY want to get the answers you can use profiling with XDebug or Zend and find what the costs are in your app for using AR vs DAO.

So it comes down to what do you want more or what does your app need?

CActiveRecord implements ArrayAccess interface, so $user[‘email’] should work, theoretically (didn’t do any extensive testing).

Honestly, I’d prefer to leave it as an object: $user = (object)$user; This way, you can start it off with active record and then migrate to DAO later easily.

Well the ig constraint is really on server, because it’s too charger and too slow, so i have to use less memory and less sql statements, so i decided to do it with DAO because it maintain the resources more less usable.

But i found many features that let me use Yii’s features like widgets (listView for example) with DAO results, like the CArrayDataProvider.

As i see Yii offer the possibility to work with DAO and AR easely and approximatly with the same level of performance :)