Cgridview Is Loading Very Slow, When Number Of Records Increasing

I have jobseeker module,jobseekers are increasing daily, CGridview loading slowly

Jobseeker Table have more than 2000 records, joining two tables to get data.

Here is my code.

Model


public function searchjobseeker()

{


	// Warning: Please modify the following code to remove attributes that


	// should not be searched.


    //echo $action = Yii::app()->controller->action->id;


	$criteria=new CDbCriteria;





	


	$action = Yii::app()->controller->action->id;


	if($action == 'active'){


		$this->status = 'Y';	


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


	}else if($action == 'inactive'){


		$this->status = 'N';	


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


	}else{


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


	}


	


	$condition = '';


	


	if(isset($_GET['Jobseeker']['keyword']) && $_GET['Jobseeker']['keyword'] !=''){


		$condition = "t.name like'%".$_GET['Jobseeker']['keyword']."%' || t.lname like'%".$_GET['Jobseeker']['keyword']."%' || r.qualification like'%".$_GET['Jobseeker']['keyword']."%'  || t.email like'%".$_GET['Jobseeker']['keyword']."%'  || r.curindustry like'%".$_GET['Jobseeker']['keyword']."%'  || t.designation like'%".$_GET['Jobseeker']['keyword']."%'  || t.street like'%".$_GET['Jobseeker']['keyword']."%'  || t.city like'%".$_GET['Jobseeker']['keyword']."%'  ||  t.zipcode like'%".$_GET['Jobseeker']['keyword']."%'  || t.district like'%".$_GET['Jobseeker']['keyword']."%'  || t.dob like'%".$_GET['Jobseeker']['keyword']."%'  || t.landline like'%".$_GET['Jobseeker']['keyword']."%'  || t.mobile like'%".$_GET['Jobseeker']['keyword']."%'  || r.skills like'%".$_GET['Jobseeker']['keyword']."%' || r.specialization like'%".$_GET['Jobseeker']['keyword']."%' || r.emptype like'%".$_GET['Jobseeker']['keyword']."%'";


		


		


	}


	


	$criteria->mergeWith(array(


		'join'=>'left JOIN tns_js_resume r ON r.jsid = t.jsid',


		'condition'=>$condition,


	));


	


	


	$criteria->order = 't.id DESC';


	





	return new CActiveDataProvider($this, array(


		'criteria'=>$criteria,


		'pagination'=>array(


			'pageSize'=>15,


		),


	));


}

View.php


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

'id'=>'jobseeker-grid',


'ajaxUpdate'=>true,


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


'pager'=>array(


        'class'=>'CLinkPager',


        'nextPageLabel'=>'Next',


        'prevPageLabel'=>'Previous',


		'header' => '',


    ),





'filter'=>$model,


'columns'=>array(


	array(


                'class'=>'CDataColumn',


                'type'=>'raw',


                'value'=>'CHtml::link(ucfirst($data->name." ".$data->lname), array(Yii::app()->homeUrl."../jobseeker/".$data->id),array("target"=>"_parent"))',


                'name'=>'Jobseeker Name',


                'header'=>_('Jobseeker Name'),


            ), 


	'email',


	array(


            'header'=>'Phone',


            'value'=>array($this,'gridJobSeekerPhone')


    ),


	'city',


	array(


            'header'=>'Status',


            'value'=>array($this,'gridJobSeekerStatus')


    ),





),

));

Controller


/**

 * Manages all models.


 */


public function actionAdmin()


{


	


	$model=new Jobseeker('search');


	


	


	$model->unsetAttributes();  // clear any default values


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


		$model->attributes=$_GET['Jobseeker'];


		if(isset($_GET['Jobseeker']['keyword']) && $_GET['Jobseeker']['keyword'] !=''){


			$model->keyword = $_GET['Jobseeker']['keyword'];


		}


		


	}





	$this->render('admin',array(


		'model'=>$model,


	));


}

Waiting for your valuable solution…

If you are using pagination there should be no difference in performance. Also 2000 records does not sound like a big number to handle for your database…

What specs does the server have you are running this site on?

Btw. Your code is vulnerable to SQL injection as you create condition directly from $_GET values.

Thanks CeBe

I will change that to YII GET values.

I hosted my site in "Sahred Hosting", even i checked in my local system. Both are loading slow.

Dont know, how to improve performance.

please suggest.

In the code you showed I can not see any problems. Need to be somewhere else. You may try some debugger or profiler to dig into this and find out where most of the time is spent.

Your problem is that monstrous like query.

Do you really need to search ALL those fields?

Do you really need to search on both sides of the term? Or just the right? Because then you could use indexes.

Required reading:

http://stackoverflow.com/questions/9928929/mysql-like-query-takes-too-long

Might look into fulltext searching also, but that will require you to use MATCH(column1, column2) AGAINST ‘searchTerm’ syntax in your query.

You should investigate other ways of searching that data… that is your bottleneck.

That won’t protect you from SQL injection, you need to use parameterized queries.