Using CActiveDataProvider in CGridView taking a lot of time !

Hello !

I’m stuck with a loading estimated at more than one minute !

I want to create datatable based on a model and several relation.

In my Controller I have :


  public function actionAdmin()

    {

        $model = new UserGiftPoint('search');

        $model->unsetAttributes();


        if(isset($_GET['UserGiftPoint'])) {

            $model->setAttributes($_GET['UserGiftPoint']);

        }


        $this->render('admin', array('model' => $model,));

    }

In my model I have the search function :


    public function search() {

        $criteria = new CDbCriteria;


        $criteria->compare('id', $this->id, true);

        $criteria->compare('user_id', $this->user_id, true);

        $criteria->compare('event_id', $this->event_id, true);

        $criteria->compare('order_id', $this->order_id, true);

        $criteria->compare('amount', $this->amount, true);

        $criteria->compare('operation', $this->operation, true);

        $criteria->compare('comment', $this->comment, true);

        $criteria->compare('type', $this->type, true);




        return new CActiveDataProvider('UserGiftPoint', array( //$this::model()->resetScope()

            'criteria' => $criteria,

            'sort'=>array(

                'defaultOrder'=>'created DESC',

            ),

            'pagination' => array(

                'pageSize' => $this->getPageSize(),

            )

        ));

    }

In my admin view I have :


Yii::app()->clientScript->registerScript('search', "

$('.search-button').click(function(){

$('.search-form').toggle();

return false;

});

$('.search-form form').submit(function(){

$.fn.yiiGridView.update('offer-grid', {

data: $(this).serialize()

});

return false;

});

");

?>


<?php echo CHtml::link(Yii::t('app', 'Advanced Search'),'#',array('class'=>'search-button')); ?>

<div class="search-form" style="display: none">

    <?php $this->renderPartial('_search',array(

        'model'=>$model,

    )); ?>

</div><!-- search-form-->




<div class="widget">

    <div class="widget-header">

        <span class="title">Suivi des utilisateurs et de leurs Moovpoints</span>

    </div>

    <?php

    $this->widget('zii.widgets.grid.CGridView', array(

        'id'            => 'userhistory-grid',

        'dataProvider'  => $model->search(),

        'ajaxUpdate'    => false,

        'filter'        => $model,

        'pagerCssClass' => 'dataTables_paginate paging_bootstrap pagination',

        'htmlOptions'   => array( 'class' => 'widget-content table-container' ),

        'itemsCssClass' => 'table table-striped table-fixed',

        'template' => '

  <div class="dataTables_wrapper form-inline">

    <div class="dt_header">

      <div class="row-fluid">

        <div class="span6">{summary}</div>

        <div class="span6">{pager}</div>

      </div>

    </div>

    {items}

    <div class="dt_footer">

      <div class="row-fluid">

        <div class="span6">{summary}</div>

        <div class="span6">{pager}</div>

      </div>

    </div>

  </div>',

        'pagerCssClass' => 'dataTables_paginate paging_bootstrap pagination',

        'htmlOptions'   => array( 'class' => 'widget-content table-container' ),

        'itemsCssClass' => 'table table-striped table-fixed',

        'pager' => array(

            'header'               => false,

            'cssFile'              => false,

            'htmlOptions'          => array( 'class' => "" ),

            'prevPageLabel'        => 'prev',

            'hiddenPageCssClass'   => 'disabled',

            'nextPageCssClass'     => 'next',

            'selectedPageCssClass' => 'active'

        ),

        'columns' => array(

            array(

                'name'              => 'id',

                'htmlOptions' => array( 'class' => 'small-cell' ),

                'filterHtmlOptions' => array( 'class' => 'small-cell' ),

                'headerHtmlOptions' => array( 'class' => 'small-cell' ),

            ),

            array(

                'name'   => 'user_id',

                'value'  => 'isset($data->user->id)?$data->user->id:"N/A"',

                'filter' => CHtml::listData(User::model()->resetScope()->findAll(), 'id', 'id'),

            ),

            array(

                'name'   => 'user_id',

                'value'  => 'isset($data->user->name)?$data->user->name:"N/A"',

                'filter' => CHtml::listData(User::model()->resetScope()->findAll(), 'id', 'name'),

            ),

            array(

                'name'   => 'event_id',

                'value'  => 'isset($data->event->id)?$data->event->id:"N/A"',

                'filter' => CHtml::listData(Event::model()->resetScope()->findAll(), 'id', 'id'),

            ),

            array(

                'name'   => 'place',

                'value'  => 'isset($data->place->id)?$data->place->id:"N/A"',

                'filter' => CHtml::listData(Place::model()->resetScope()->findAll(), 'id', 'id'),

            ),

            array(

                'name'   => 'place',

                'value'  => 'isset($data->place->name)?$data->place->name:"N/A"',

                'filter' => CHtml::listData(Place::model()->resetScope()->findAll(), 'id', 'name'),

            ),

            array(

                'name'   => 'type',

                'value'  => 'isset($data->transac)?$data->transac:"N/A"',

                'filter' => CHtml::listData(TransactionTypeMp::model()->resetScope()->findAll(), 'id', 'name'),

            ),

            array(

                'name'   => 'comment',

                'htmlOptions' => array( 'class' => '' ),

                'filterHtmlOptions' => array( 'class' => '' ),

                'headerHtmlOptions' => array( 'class' => '' ),

            ),

            array(

                'name' => 'created',

                'value'  => 'date("Y-m-d", strtotime($data->created))',

                'htmlOptions' => array( 'class' => '' ),

                'filterHtmlOptions' => array( 'class' => '' ),

                'headerHtmlOptions' => array( 'class' => '' ),

            ),

            array(

                'name' => 'created',

                'htmlOptions' => array( 'class' => '' ),

                'filterHtmlOptions' => array( 'class' => '' ),

                'headerHtmlOptions' => array( 'class' => '' ),

            ),

            // 'content:html',

            array(

                'name' => 'operation',

                'type' => 'html',

                'value'  => function($data, $row){

                    if($data->operation == "+") { return CHtml::tag("i", array("class" => "icon-plus", "style" => "color:green; text-align: center important!"), "");}

                    elseif($data->operation == "-") {return CHtml::tag("i", array("class" => "icon-minus", "style" => "color:red; text-align: center important!"), "");}

                    else { return "N/A"; }},

                'htmlOptions' => array( 'class' => '' ),

                'filterHtmlOptions' => array( 'class' => '' ),

                'headerHtmlOptions' => array( 'class' => '' ),

            ),

            array(

                'name' => 'amount',

                'htmlOptions' => array( 'class' => '' ),

                'filterHtmlOptions' => array( 'class' => '' ),

                'headerHtmlOptions' => array( 'class' => '' ),

            ),


            array(

                'name'   => 'status',

                'type' => 'html',

                'value'  => '$data->getStatus()',

                'filter' => CHtml::listData(TransactionTypeMp::model()->resetScope()->findAll(), 'id', 'name'),

            ),

            array(

                'name' => 'user.gift_point',

                'header'=>Yii::t('app', 'Solde de Moovpoints'),

                'value'  => 'isset($data->user->gift_point)?$data->user->gift_point:"Aucun"',

                'filter' => CHtml::listData(User::model()->resetScope()->findAll(), 'id', 'gift_point'),

            ),


            array(

                'class'       => 'ButtonColumn',

                'htmlOptions' => array( 'class' => 'action-col' ),

                'template'    => '<span class="btn-group">{view} {modal}</span>',

                'buttons'     => array(

                    'view' => array(

                        'imageUrl' => false,

                        'url' => 'array("user/update", "id" => $data->id, "page"=> isset($_GET["Place_page"]) ? $_GET["Place_page"] : null)',

                        'options'  => array( 'title' => 'view', 'class' => 'btn btn-small' ),

                        'label'    => '<i class="icon-search"></i>',

                    ),

                    'modal' => array(

                        'imageUrl' => false,

                        'options'  => array( "title" => "Modérer",

                            "class" => "btn btn-small btn-primary launch-popup",

                            "data-toggle" => "modal",

                            "data-target" => "#modal-show",

                            "data-id" => '$data->id',

                            // Here, we specify that we want title to be evaluated

                            'evaluateOptions' => array('data-id')),

                        'label'    => '<i class="icon-pencil"></i>',

                    ),

                )

            ),

        ),

    ));

    ?>

In my _search view I have :


<div class="wide form">


<?php $form = $this->beginWidget('CActiveForm', array(

	'action' => Yii::app()->createUrl($this->route),

	'method' => 'get',

));?>


<?php //$user = $model->user; ?>


	<div class="row">

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

		<?php echo $form->textField($model,'id',array('size'=>10,'maxlength'=>10,'class'=>'input-xxlarge')); ?>

	</div>


	<div class="row">

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

		<?php echo $form->dropDownList($model, 'user_id', CHtml::listData(User::model()->resetScope()->findAll(),'id', 'name')); ?>

	</div>


	<div class="row">

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

		<?php echo $form->dropDownList($model, 'event_id', CHtml::listData(Event::model()->findAll(),'id', 'name'), array('prompt' => 'None')); ?>

	</div>


	<div class="row">

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

		<?php echo $form->dropDownList($model, 'place', CHtml::listData(Place::model()->findAll(),'id', 'name'), array('prompt' => 'None')); ?>

	</div>


	<div class="row">

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

		<?php echo $form->dropDownList($model, 'type', CHtml::listData(TransactionTypeMp::model()->findAll(),'id', 'name'), array('prompt' => 'None')); ?>

	</div>




	<div class="row">

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

		<?php $this->widget('CJuiDateTimePicker',

						 array(

							'model'=>$model,

                                                        'name'=>'UserGiftPoint[created]',

							//'language'=> substr(Yii::app()->language,0,strpos(Yii::app()->language,'_')),

                                                        'language'=> 'en',

							'value'=>$model->created,

                                                        'mode' => 'date',

							'options'=>array(

                                                                        'showAnim'=>'fold', // 'show' (the default), 'slideDown', 'fadeIn', 'fold'

                                                                        'showButtonPanel'=>true,

                                                                        'changeYear'=>true,

                                                                        'changeMonth'=>true,

                                                                        'dateFormat'=>'yy-mm-dd',

                                                                        ),

                                                    )

					);

					; ?>

	</div>


	<div class="row">

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

		<?php echo $form->textField($model,'operation'); ?>

	</div>




	<div class="row">

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

		<?php echo $form->textField($model,'amount'); ?>

	</div>


<!--	<div class="row">-->

<!--		--><?php //echo $form->label($user, 'gift_point'); ?>

<!--		--><?php //echo $form->textField($user,'gift_point',array('size'=>60,'maxlength'=>128,'class'=>'input-xxlarge')); ?>

<!--	</div>-->




	<div class="row buttons">

		<?php echo CHtml::submitButton(Yii::t('app', 'Search')); ?>

	</div>


<?php $this->endWidget(); ?>


</div><!-- search-form -->

It takes more than one minute to display ! And… I obviously got :


Fatal error: Maximum execution time of 30 seconds exceeded in /var/www/vhost/library/Yii/collections/CMap.php on line 73

The Yii logs are impressive !

I can see UserGiftPoint.findAll() more than 100 times !

I can see a lots of find() on linked relation… And find() on linked relation from linked relation…

I have more than 700 lines in my table. Even if I paginate with 60 or 1 record it takes the same time.

How can I fix this problem ?

EDIT :

I tried to replace :


 return new CActiveDataProvider('UserGiftPoint', array(

by :


 return new CActiveDataProvider($this::model()->resetScope(), array(

But nothing has changed…

I also tried to remove the renderPartial of _search view and it takes now 55 sec and displays my datatable but…55 sec is too long ! :confused:

Thanks…

[list=1]

[*]use eager loading of related object: $criteria->with[] = ‘user’; and same for other related data you want to display in grid/list - this way related objects will be loaded in same query (not separately for every parent object like in lazy scenario)

[*]make sure you have got proper indexes on fields that are used as filters or join conditions (EXPLAIN is your friend)

[/list]

Thanks for your help !

I tried first solution :


    public function search() {

        $criteria = new CDbCriteria;


        $criteria->compare('id', $this->id, true);

        $criteria->compare('user_id', $this->user_id, true);

        $criteria->compare('event_id', $this->event_id, true);

        $criteria->compare('order_id', $this->order_id, true);

        $criteria->compare('amount', $this->amount, true);

        $criteria->compare('operation', $this->operation, true);

        $criteria->compare('type', $this->type, true);

        $criteria->with = array('user' => array('alias' => 'u'),

            'event' => array('alias' => 'e'),

            'place' => array('alias' => 'pl'),

            'partner' => array('alias' => 'pa'),

            'order' => array('alias' => 'o'));


        $this->beforeSearch($criteria);


        return new CActiveDataProvider($this::model()->resetScope(), array(

            'criteria' => $criteria,

            'sort'=>array(

                'defaultOrder'=>'t.created DESC',

            ),

            'pagination' => array(

                'pageSize' => $this->getPageSize(),

            )

        ));

    }

The loading time lowered from 35 seconds to 25secondes…

Not concluant.

I don’t understand the second proposition :confused:

[b][u]

EDIT :[/u][/b]

Ok I understand EXPLAIN keyword (I forgot… It’s an equivalent to EXPLAIN PLAN in Oracle SQL) but… I dont’ know how to execute query with this.

The with condition has not reduced loading time… It was because I used chrome instead of FF -__-’

check your logged queries generated by activerecord for their plans. See how long the query takes itself. try to optimize it.

Also if query returns many rows, but you do not need them to show in grid - try to reduce selected columns (‘select’ part of criteria object). converting long rows to PHP objects may take quite long.

you can also turn on profile logs, enable sql profiling, add web log route to see them at the bottom of web page and check what takes most of the time.