Yii DAO vs Active Record Performance

I’m writing a new app using Yii Framework, but one of my concerns with Yii has been the impact of using ActiveRecord and ORM on the application performance, so I decided to actually compare how the two perform and the results are quite interesting…

Basically for a simple test what I did is created a single table in the database with like 6 records.  Then I created lean ActiveRecord and TableModule/TableDataGateway/DTO implementations to query all records in that table.  I should also note that for TableModule/TableDataGateway/DTO I actually created base classes in the components folder from which all my actual classes inherit.  Then in the site index I basically call the query one way or another and pass the data to the site index view for output.  To do the testing I used apache bench with ab -t 30 -c 10 url.  I did all the tests on the localhost, which isn’t the best but when you only change a single variable shouldn’t matter too much as it’s relative performance that you’re looking at.  Also it’s worth mentioning that I use eAccelerator as performance without any opcode caching is pretty crap, and if you’re using Yii, Zend or pretty much any other framework you should always use some opcode cache.  Also, I had Yii debugging and tracing turned off.

Well the results were quite interesting indeed… For ActiveRecord I consistently got around 35 requests per second from about 10 runs of ab tool.  For TableModule/TableDataGateway/DTO I got around 60 requests pers second, and that’s almost a doubling of performance!!!  In my world that’s a pretty massive difference and comfirms my thoughts about ActiveRecord and ORM…  Basically, ActiveRecord/ORM have quite significant performance implications, so if you’re after performance you should try to avoid using them and look at implementing the TableModule/TableDataGateway/DTO pattern.  Basically the main difference is that when you’re using this pattern you’re just writing the SQL yourself rather than trying to generated in code.  From a programming perspective everything else remains pretty similar.  You can actually use CFormModel in combination with TableModule/TableDataGateway and even use it as your Data Transfer Object.  All other bits stay pretty much the same.  There isn’t all that much more code to write when you’re implementing TableModule/TableDataGateway/DTO instead of ActiveRecord, but from a performance perspective the results speak for themselves!  I might actually write a bit of quick tutorial on how to implement TableModule/TableDataGateway/DTO pattern with Yii (it’s actually pretty simple).  Meanwhile, if you don’t know what TableModule/TableDataGateway/DTO patterns actually are, I would suggest getting Martin Fowler’s Patterns of Enterprise Application Architecture book or googling these patterns.

UPDATE: Today I did a bit more testing after someone on the Yii forums has suggested that the issue may be because I didn’t have schemaCachingDuration configured.  That was indeed the case… I actually had the property configured on the db component but didn’t configure the cache component because I was using eAccelerator 0.9.6 which doesn’t support it.  So I switched to APC instead, configured the cache component to CApcCache and re-ran the test.  After doing that I found that there was no significant difference in the results between using ActiveRecord and TableModule/TableDataGateway pattern.  So, what that means is that provided you have caching configured correctly ActiveRecord shouldn’t impact performance as long as you’ve got schema caching enabled on your db component, as it’s generally the disk access that really kills application performance.  Another interesting thing I found is that with using APC I actually got better speed with both ActiveRecord (for obvious reasons) and TableModule/TableDataGateway/DTO way of doing things.  Perhaps it’s my configuration of the caching modules or something else, but I got around 80 requests per second with both ActiveRecord and TableModule way of doing things (actually TableModule was slightly faster, but negligible).  This is understandable given that processing code is mainly CPU bound and what really slows down apps is the disk access (i.e. reading files or database queries if the database isn’t loaded into memory and cached).  Also just for comparison, with no opcode caching components installed at all I got around 20 requests per second with ActiveRecord…  This isn’t too bad but isn’t great either.  There’s about a 4 times difference when running with caching.  So if you intend to write a serious application make sure you use caching.