Yii Framework Forum: Improve Performance By Findallbysql - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Improve Performance By Findallbysql findAllBySql

#1 User is offline   Johnny Gan 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 205
  • Joined: 30-May 13

Posted 13 August 2013 - 10:30 AM

<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.

Quote

8:26:03.693113 trace system.db.ar.CActiveRecord

ToolInventory.findAll()
in C:\trisdev\tools\protected\views\toolInventory\_search.php (167)
in C:\trisdev\tools\protected\views\toolInventory\admin.php (50)
in C:\trisdev\tools\protected\controllers\ToolInventoryController.php (145)
in C:\trisdev\tools\index.php (19)

08:26:03.693260 trace system.db.CDbCommand

Querying SQL: SELECT * FROM `tool_inventory` `t` WHERE `tool_current_proj`
!= ''
in C:\trisdev\tools\protected\views\toolInventory\_search.php (167)
in C:\trisdev\tools\protected\views\toolInventory\admin.php (50)
in C:\trisdev\tools\protected\controllers\ToolInventoryController.php (145)
in C:\trisdev\tools\index.php (19)

08:26:06.829962 trace system.db.ar.CActiveRecord

ToolInventory.count()
in C:\trisdev\tools\protected\views\toolInventory\admin.php (126)
in C:\trisdev\tools\protected\controllers\ToolInventoryController.php (145)
in C:\trisdev\tools\index.php (19)



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.

Quote

08:23:49.272770 trace system.db.ar.CActiveRecord

ToolInventory.findAllBySql()
in C:\trisdev\tools\protected\views\toolInventory\_search.php (168)
in C:\trisdev\tools\protected\views\toolInventory\admin.php (50)
in C:\trisdev\tools\protected\controllers\ToolInventoryController.php (145)
in C:\trisdev\tools\index.php (19)

08:23:49.272872 trace system.db.CDbCommand

Querying SQL: SELECT `tool_current_proj` FROM `tool_inventory` WHERE
`tool_current_proj` != '' GROUP BY `tool_current_proj` ORDER BY
`tool_current_proj`
in C:\trisdev\tools\protected\views\toolInventory\_search.php (168)
in C:\trisdev\tools\protected\views\toolInventory\admin.php (50)
in C:\trisdev\tools\protected\controllers\ToolInventoryController.php (145)
in C:\trisdev\tools\index.php (19)

08:23:49.278909 trace system.db.ar.CActiveRecord

ToolInventory.count()
in C:\trisdev\tools\protected\views\toolInventory\admin.php (126)
in C:\trisdev\tools\protected\controllers\ToolInventoryController.php (145)
in C:\trisdev\tools\index.php (19)




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.
0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,994
  • Joined: 16-February 11
  • Location:Japan

Posted 13 August 2013 - 10:42 AM

/* Moved from "General Discussion for Yii 1.1.x" to "Tips, Snippets and Tutorials" */

Nice post.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users