Fast counters in Yii

In release 1.1.8 of the PHP framework Yii, an additional method appeared in the CActiveRecord class for working with counters, namely saveCounters. Probably many of you read about it in the release announcement . Under the cut, I will tell what is its coolness and why you should use it. Perhaps after reading you will run to refactor your code.

After reading the announcement about the appearance of saveCounters, I thought that it was just a wrapper for the existing functionality. Neither the documentation on the method nor the manual on improving performance said anything about any of its features in terms of speed. In general, I read and forgot.

Often there are tasks in which some data must be periodically aggregated. In my case, it was a simple banner twist. When the banner was shown, a line was added to the raw table. On the crown, a script was called that ran through raw data in a cycle and, in particular, increased display counters for banners. Inside the loop was this code:

$banner->hits++; $banner->save(); 

Everything worked fine, but as the load increased, problems started. Cron-script was not enough time, he began to fall on timeout. In the process of profiling and scientific typing, the script logic changed, everything changed, except for the above-mentioned code fragment. He was beyond suspicion. As it turned out, nothing.
')
It was then that I remembered the saveCounters special method. Replacing the code with this one solved all the problems with the performance of the script:

 //    " "=>"  " $banner->saveCounters(array('hits'=>1)); 

I was surprised by this solution and decided to compare the performance of the methods, as well as look at their code.
Test environment: core2 6420, ram 3gb, Zend Server CE 5.5 with standard settings.
Test code:

 $start = microtime(true); for($i=0;$i<1000;$i++) { $banner->hits++; $banner->save();  $banner->saveCounters(array('hits'=>1)); } echo microtime(true)-$start; 

There were several measurements, the results are as follows:
- save () ~ 39 seconds
- saveCounters () ~ 23 seconds
those. saveCounters ~ 41% faster!

So what is the secret?

Everything turned out to be very simple. The saveCounter method code is simpler, but this is not the main thing. Most importantly, what sql queries form these methods.

The save () method generates a heavy query that includes all the fields in the table.
Something like this:

 UPDATE `banners` SET `id`=1, `name`='test', `info`='long-long description', `hits`=3560, `iduser`='2', `created_at`='2012-02-17 13:14:02', ... WHERE `banners`.`id`=1 

The saveCounters () method forms the optimal query for this operation:

 UPDATE `banners` SET `hits`=`hits`+1 WHERE `banners`.`id`=1 

This is the secret of its performance.

I hope this information will be useful for you and will make your web applications a little faster.