Hey guys,
I have some kind of a weird problem. I have a Yii application that is quite quite big using about 200 tables and more than 1 billion rows. Everything works fine. I had to custom build some queries because they were to slow because of joins made by CDbCriteria in search method of my models. I used then the CSqlDataProvider to manually write my queries using inner queries and some other things not supported by CDbCriteria.
I have only one site that is very slow and I cannot find out why. This site renders a CGridView showing 4 columns: each column is from a different table because I need the names for the foreign key’s ids. The table instancerelation is storing status, instancerelationtype_id, instanceto_id and instancefrom_id. I want to display the instancerelationtype’s name and the names for the two instances (instanceto_id and instancefrom_id).
The query is optimized because instancerelation has got 300 mio rows and using CDbCriteria->with made a query that examined 1.2 billion rows.
I used CProfileLogRoute to find out where the most time is used. I found out the query itself uses about 0.00017 sec (because result is in mysql query cache) but Yii’s call to queryAll in yiilite.php needs the most time of loading the site which is about 21 sec.
Here are the most important pieces of code that are executed for loading this site:
My search function:
public function searchForContainerIdForRelationview($container_id)
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
//trim and lower all input parameters
$this->instancefromname = trim(strtolower($this->instancefromname));
$this->instancetoname = trim(strtolower($this->instancetoname));
$this->instancerelationtypename = trim(strtolower($this->instancerelationtypename));
$this->status = trim($this->status);
$sql = 'select instancerelation.id as id,
instancerelation.status as status,
instancefrom.name as instancefromname,
instancerelationtype.name as instancerelationtypename,
instanceto.name as instancetoname,
instancefrom.id as instancefromid,
instancerelationtype.id as instancerelationtypeid,
instanceto.id as instancetoid
from instance as instancefrom,
instance as instanceto,
instancerelationtype as instancerelationtype,
(
SELECT instancerelationtype.id
FROM instancerelationtype
WHERE instancerelationtype.id in (select instancerelation.instancerelationtype_id
from instancerelation
where instancerelation.container_id = :container_id)'.
($this->instancerelationtypename!=null ? ' and lower(instancerelationtype.name) like :instancerelationtypename' : '').
'ORDER BY instancerelationtype.name
LIMIT 25
) AS inst
inner join instancerelation as instancerelation
on instancerelation.instancerelationtype_id = inst.id
where instancerelation.container_id = :container_id
and instancerelation.instanceto_id = instanceto.id
and instancerelation.instancefrom_id = instancefrom.id
and instancerelationtype.id = instancerelation.instancerelationtype_id'.
($this->instancefromname!=null ? ' and lower(instancefrom.name) like :instancefromname' : '').
($this->instancetoname!=null ? ' and lower(instanceto.name) like :instancetoname' : '').
($this->instancerelationtypename!=null ? ' and lower(instancerelationtype.name) like :instancerelationtypename' : '').
($this->status!=null ? ' and t.status = :status' : '');
$sort = new CSort();
$sort->attributes = array(
'defaultOrder'=>'instancerelationtype.name DESC',
'status'=>array(
'asc'=>'t.status',
'desc'=>'t.status desc',
),
'instancefromname'=>array(
'asc'=>'instancefrom.name',
'desc'=>'instancefrom.name desc',
),
'instancetoname'=>array(
'asc'=>'instanceto.name',
'desc'=>'instanceto.name desc',
),
'instancerelationtypename'=>array(
'asc'=>'instancerelationtype.name',
'desc'=>'instancerelationtype.name desc',
),
);
//array which contains the params to bind
$bindParamArray=array(":container_id"=>$container_id);
($this->instancefromname!=null ? $bindParamArray[":instancefromname"] = $this->instancefromname : null);
($this->instancetoname!=null ? $bindParamArray[":instancetoname"] = $this->instancetoname : null);
($this->instancerelationtypename!=null ? $bindParamArray[":instancerelationtypename"] = $this->instancerelationtypename : null);
($this->status!=null ? $bindParamArray[":status"] = $this->status : null);
$countQuery=($this->status==null && $this->instancefromname==null && $this->instancetoname==null && $this->instancerelationtypename==null) ?
null :
Yii::app()->db->createCommand('
select count(t.id)
from instance as instancefrom,
instance as instanceto,
instancerelationtype,
(select instancerelationtype.id
from instance as instancefrom,
instance as instanceto,
instancerelationtype,
(
SELECT instancerelationtype.id
FROM instancerelationtype
WHERE instancerelationtype.id in (select instancerelation.instancerelationtype_id
from instancerelation
where instancerelation.container_id = :container_id)'.
($this->instancerelationtypename!=null ? ' and lower(instancerelationtype.name) like :instancerelationtypename' : '').
'ORDER BY instancerelationtype.name
LIMIT 25
) AS inst
inner join instancerelation as instancerelation
on instancerelation.instancerelationtype_id = inst.id
where instancerelation.container_id = :container_id
and instancerelation.instanceto_id = instanceto.id
and instancerelation.instancefrom_id = instancefrom.id
and instancerelationtype.id = instancerelation.instancerelationtype_id'.
($this->instancefromname!=null ? ' and lower(instancefrom.name) like :instancefromname' : '').
($this->instancetoname!=null ? ' and lower(instanceto.name) like :instancetoname' : '').
($this->instancerelationtypename!=null ? ' and lower(instancerelationtype.name) like :instancerelationtypename' : '').
($this->status!=null ? ' and t.status = :status' : ''));
if($countQuery!=null){
$bindArray = ModelHelper::setParamsToBind($countQuery, $bindParamArray);
$count=$bindArray["sqlQuery"]->queryScalar();
}
else{
$count=Statistics::model()->findByPk($container_id)->totalRelation;
}
$temp = new CSqlDataProvider($sql,
array(
'id'=>get_class($this),
'pagination'=>new CPagination_M(),
'params'=>$bindParamArray,
'sort'=>array('attributes'=>$sort->attributes),
'totalItemCount'=>$count,
));
$url = Yii::app()->controller->getRoute(); $url = preg_replace("/\//", "_", $url);
$sortParam = Yii::app()->user->getState($url.'_'.get_class($this).'_sort',false);
$temp->getSort()->defaultOrder=$sortParam!=false ? $sortParam : "instancerelationtype.name";
if ($sortParam!=false){
if (preg_match("/instancefromname/", $sortParam)) {
$sortParam = preg_replace("/instancefromname/", "instancefrom.name", $sortParam);
}
else if (preg_match("/instancetoname/", $sortParam)) {
$sortParam = preg_replace("/instancetoname/", "instanceto.name", $sortParam);
}
else if (preg_match("/instancerelationtypename/", $sortParam)) {
$sortParam = preg_replace("/instancerelationtypename/", "instancerelationtype.name", $sortParam);
}
else if (preg_match("/status/", $sortParam)) {
$sortParam = preg_replace("/status/", "t.status", $sortParam);
}
}
$temp->sort-> sortVar = $url."_".get_class($this)."_sort";
$temp-> setPagination(array(
'pageSize'=> Yii::app()->user->getState($url.'_'.get_class($this).'_pageSize', Yii::app()->params['defaultPageSize']),
'pageVar'=>$url."_".get_class($this)."_page"
));
return $temp;
Yii::endProfile('blockIDsearch');
}
My view’s CGridView
$this->widget('ext.multilingual.CGridView_L', array(
'id'=>'container-grid-instancerelationtypes',
'dataProvider'=>$modelInstancerelation->searchForContainerIdForRelationview($model->id),
'filter'=>$modelInstancerelation,
'columns'=>array(
array( 'class'=>'CCheckBoxColumn',
'visible'=>Yii::app()->user->checkAccess("owner_$container_id"),
'selectableRows'=>100,
'id'=>'checkbox',
'value'=>'$data["id"]',
'footer'=>
CHtml::openTag("table")
.CHtml::openTag("tr")
.CHtml::openTag("td", array("title"=>Yii::t("dictionary", "_xml_export")))
.CHtml::imageButton(
"images/buttonIcons/exportxml.png",
array(
"target"=>"_blank",
"title"=>Yii::t("dictionary","_xml_export")))." ".CHtml::closeTag("td")
.CHtml::closeTag("td")
.CHtml::closeTag("tr")
.CHtml::closeTag("table"),
'htmlOptions'=>array('class'=>'align-center','width'=>'20px'),
),
array(
'header'=>Yii::t('dictionary','_status'),
'name'=>'status',
'type'=>'raw',
'filter'=>array(0=>Yii::t('dictionary','_not_yet_confirmed'), 1=>Yii::t('dictionary','_is_edited'), 2=>Yii::t('dictionary','_is_deleted'),3=>Yii::t('dictionary','_is_ok')),
'htmlOptions'=>array('class'=>'button-column'),
'value'=>'
($data["status"]==0 ? CHtml::image("images/buttonIcons/trafficlight_white.gif",Yii::t("dictionary","_not_yet_confirmed"), array("title"=>Yii::t("dictionary","_not_yet_confirmed"))) :
($data["status"]==1 ? CHtml::image("images/buttonIcons/trafficlight_yellow.gif", Yii::t("dictionary","_is_edited"), array("title"=>Yii::t("dictionary","_is_edited"))) :
($data["status"]==2 ? CHtml::image("images/buttonIcons/trafficlight_red.gif",Yii::t("dictionary","_is_deleted"), array("title"=>Yii::t("dictionary","_is_deleted"))) :
($data["status"]==3 ? CHtml::image("images/buttonIcons/trafficlight_green.gif", Yii::t("dictionary","_is_ok"),array("title"=>Yii::t("dictionary","_is_ok"))) : ""))))
'
),
array(
'name'=>'instancefromname',
'type'=>'raw',
'value'=>'CHtml::link(CHtml::encode($data["instancefromname"]), "index.php?r=manageinstance/manageinstance&instance_id=".($data["instancefromid"])."")'
),
array(
'name'=>'instancerelationtypename',
'type'=>'raw',
'value'=>'CHtml::link(CHtml::encode($data["instancerelationtypename"]), "index.php?r=instancerelationtypeattribute/admin&instancerelationtype_id=".($data["instancerelationtypeid"])."")'
),
array(
'name'=>'instancetoname',
'type'=>'raw',
'value'=>'CHtml::link(CHtml::encode($data["instancetoname"]), "index.php?r=manageinstance/manageinstance&instance_id=".($data["instancetoid"])."")'
),
array(
// The link-URL must be adapted to every page!
'header'=>Yii::t('dictionary', '_actions'),
'filter'=> CHtml_E::filterLink('container-grid-instancerelationtypes','container/view&id='.$model->id,$model->id, 'Instancerelation'),
'type'=>'raw',
'htmlOptions'=>array('class'=>'button-column'),
'value'=> 'CHtml::openTag("table").CHtml::openTag("tr").CHtml::openTag("td").
(Yii::app()->user->checkAccess("readContainer_'.$container_id.'") || $ispublicreadable ?
CHtml::link(
Chtml::image("images/buttonIcons/view.gif", "", array("title"=>Yii::t("dictionary","_view"))),
"index.php?r=instancerelation/view&id=$data[id]&container_id='.$container_id.'&container_view_Instancerelation__row=$row") : "")
.CHtml::closeTag("td").CHtml::openTag("td").
(Yii::app()->user->checkAccess("owner_'.$container_id.'") ?
CHtml::link(
Chtml::image("images/buttonIcons/exportxml.png", "", array("title"=>Yii::t("dictionary", "_xml_export"))),
"index.php?r=container/exportxmlinstancesorrelations&container_id='.$container_id.'&id=$data[id]&type=1",
array(
"target"=>"_blank")) : "")
.CHtml::closeTag("td").CHtml::closeTag("tr").CHtml::closeTag("table")'
),
),
));
My controller’s action
public function actionView($id)
{
if (Yii::app()->user->checkAccess("readContainer_$id") || Containerusage::model()->findByPk((int) $id)->ispublicreadable)
{
// depend on which cgrid- or clistview has changed, set filter and source params
if(isset($_GET['ajax'])) {
if($_GET['ajax']=="container-instancetypes-list") {
$this-> setFilterAndSourceParams("container_view_Instancetype", "list");
}
else if($_GET['ajax']=="container-instancerelationtypes-list") {
$this-> setFilterAndSourceParams("container_view_Instancerelation", "list");
}
else if($_GET['ajax']=="container-grid-instancetypes") {
$this-> setFilterAndSourceParams("container_view_Instance", "grid","container_view",array("container_view_Instance"));
}
else if($_GET['ajax']=="container-grid-instancerelationtypes") {
$this-> setFilterAndSourceParams("container_view_Instancerelation", "grid", "container_view",array("container_view_Instancerelation"));
}
else if($_GET['ajax']=="container-grid-instancemediaassociations") {
$this-> setFilterAndSourceParams("container_view_Instancemediaassociation", "grid", "container_view",array("container_view_Instancemediaassociation"));
}
} else {
$this-> setFilterAndSourceParams(null,null,null,array("usercontainerassociation_view_Subscribed", "container_index_Container"));
}
$modelInstancerelation = new Instancerelation('search');
$this->render('view',array(
'modelInstancerelation'=>$modelInstancerelation,
'container_id'=>$id,
));
} else {
throw new CHttpException(null,Yii::t('dictionary','_You_are_not_allowed_to_do_this.'));
}
}
The functions queryAll and queryInternal in yiilite.php
public function queryAll($fetchAssociative=true,$params=array())
{Yii::beginProfile('blockIDcallqueryall');
return $this->queryInternal('fetchAll',$fetchAssociative ? $this->_fetchMode : PDO::FETCH_NUM, $params);Yii::endProfile('blockIDcallqueryall');
}
private function queryInternal($method,$mode,$params=array())
{
$params=array_merge($this->params,$params);
if($this->_connection->enableParamLogging && ($pars=array_merge($this->_paramLog,$params))!==array())
{
$p=array();
foreach($pars as $name=>$value)
$p[$name]=$name.'='.var_export($value,true);
$par='. Bound with '.implode(', ',$p);
}
else
$par='';
if($this->_connection->queryCachingCount>0 && $method!==''
&& $this->_connection->queryCachingDuration>0
&& $this->_connection->queryCacheID!==false
&& ($cache=Yii::app()->getComponent($this->_connection->queryCacheID))!==null)
{
$this->_connection->queryCachingCount--;
$cacheKey='yii:dbquery'.$this->_connection->connectionString.':'.$this->_connection->username;
$cacheKey.=':'.$this->getText().':'.serialize(array_merge($this->_paramLog,$params));
if(($result=$cache->get($cacheKey))!==false)
{
return $result;
}
}
try
{
if($this->_connection->enableProfiling)
Yii::beginProfile('system.db.CDbCommand.query('.$this->getText().$par.')','system.db.CDbCommand.query');
$this->prepare();
if($params===array())
$this->_statement->execute();
else
$this->_statement->execute($params);
if($method==='')
$result=new CDbDataReader($this);
else
{
$mode=(array)$mode;
$result=call_user_func_array(array($this->_statement, $method), $mode);
$this->_statement->closeCursor();
}
if($this->_connection->enableProfiling)
Yii::endProfile('system.db.CDbCommand.query('.$this->getText().$par.')','system.db.CDbCommand.query');
if(isset($cache,$cacheKey))
$cache->set($cacheKey, $result, $this->_connection->queryCachingDuration, $this->_connection->queryCachingDependency);
//-----------------------------------------
//logging the time here (about 20secs)
//-----------------------------------------
Yii::beginProfile('blockIDcallqueryinternal7');
return $result;
Yii::endProfile('blockIDcallqueryinternal7');
}
catch(Exception $e)
{
if($this->_connection->enableProfiling)
Yii::endProfile('system.db.CDbCommand.query('.$this->getText().$par.')','system.db.CDbCommand.query');
$errorInfo = $e instanceof PDOException ? $e->errorInfo : null;
$message = $e->getMessage();
Yii::log(Yii::t('yii','CDbCommand::{method}() failed: {error}. The SQL statement executed was: {sql}.',
array('{method}'=>$method, '{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
if(YII_DEBUG)
$message .= '. The SQL statement executed was: '.$this->getText().$par;
throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
}
}