Improve Performance By Findallbysql

<Scenario>: In advanced search, we need options in drop down list is only valid data from database. For example, we have one project field in the table, so in the advanced search, we only want to show all available projects in the drop down list. So the simple way we used was:




<?php echo $form->label($model,'tool_current_proj'); ?>

<?php //echo $form->textField($model,'tool_current_proj',array('size'=>20,'maxlength'=>20)); 

echo $form->dropDownList($model, 'tool_current_proj', CHtml::listData(ToolInventory::model()->findAll("`tool_current_proj` != ''"), 'tool_current_proj', 'tool_current_proj'), array('prompt'=>'--Select--'));

?>



Because the table contains over 10K rows, So the page take a few seconds to upload. It’s not good. To find out the reason, I turned on the tracking log, and I found the 3 seconds delay on create this drop down list. See below raw data from tracking log.

After a few tests, we found findAllBySql solved the issue in the best way, it improved the performance and make the page load shorter than 0.01 second.




<?php echo $form->label($model,'tool_current_proj'); ?>

<?php //echo $form->textField($model,'tool_current_proj',array('size'=>20,'maxlength'=>20)); 

echo $form->dropDownList($model, 'tool_current_proj', CHtml::listData(ToolInventory::model()->findAllBySql("SELECT `tool_current_proj` FROM `tool_inventory` WHERE `tool_current_proj` != '' GROUP BY `tool_current_proj` ORDER BY `tool_current_proj`"), 'tool_current_proj', 'tool_current_proj'), array('prompt'=>'--Select--'));

?>



This is tracking log after we changed the code, and you can see the page load much faster than before.

Summary: If your database is small, you may not have this issue, but if your database is growing bigger and bigger, then you may have same growing pain.

Hope this can bring some ideas for you.

[color="#556B2F"]/* Moved from "General Discussion for Yii 1.1.x" to "Tips, Snippets and Tutorials" */[/color]

Nice post.