Cgridview Is Loading Very Slow, When Number Of Records Increasing

subash · January 9, 2014, 12:18pm

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 &#036;action = Yii::app()-&gt;controller-&gt;action-&gt;id;


	&#036;criteria=new CDbCriteria;





	


	&#036;action = Yii::app()-&gt;controller-&gt;action-&gt;id;


	if(&#036;action == 'active'){


		&#036;this-&gt;status = 'Y';	


		&#036;criteria-&gt;compare('status',&#036;this-&gt;status,true);


	}else if(&#036;action == 'inactive'){


		&#036;this-&gt;status = 'N';	


		&#036;criteria-&gt;compare('status',&#036;this-&gt;status,true);


	}else{


		&#036;criteria-&gt;compare('status',&#036;this-&gt;status,true);


	}


	


	&#036;condition = '';


	


	if(isset(&#036;_GET['Jobseeker']['keyword']) &amp;&amp; &#036;_GET['Jobseeker']['keyword'] &#33;=''){


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


		


		


	}


	


	&#036;criteria-&gt;mergeWith(array(


		'join'=&gt;'left JOIN tns_js_resume r ON r.jsid = t.jsid',


		'condition'=&gt;&#036;condition,


	));


	


	


	&#036;criteria-&gt;order = 't.id DESC';


	





	return new CActiveDataProvider(&#036;this, array(


		'criteria'=&gt;&#036;criteria,


		'pagination'=&gt;array(


			'pageSize'=&gt;15,


		),


	));


}

View.php

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

'id'=&gt;'jobseeker-grid',


'ajaxUpdate'=&gt;true,


'dataProvider'=&gt;&#036;model-&gt;searchjobseeker(),


'pager'=&gt;array(


        'class'=&gt;'CLinkPager',


        'nextPageLabel'=&gt;'Next',


        'prevPageLabel'=&gt;'Previous',


		'header' =&gt; '',


    ),





'filter'=&gt;&#036;model,


'columns'=&gt;array(


	array(


                'class'=&gt;'CDataColumn',


                'type'=&gt;'raw',


                'value'=&gt;'CHtml::link(ucfirst(&#036;data-&gt;name.&quot; &quot;.&#036;data-&gt;lname), array(Yii::app()-&gt;homeUrl.&quot;&#46;&#46;/jobseeker/&quot;.&#036;data-&gt;id),array(&quot;target&quot;=&gt;&quot;_parent&quot;))',


                'name'=&gt;'Jobseeker Name',


                'header'=&gt;_('Jobseeker Name'),


            ), 


	'email',


	array(


            'header'=&gt;'Phone',


            'value'=&gt;array(&#036;this,'gridJobSeekerPhone')


    ),


	'city',


	array(


            'header'=&gt;'Status',


            'value'=&gt;array(&#036;this,'gridJobSeekerStatus')


    ),





),

));

Controller

/**

 * Manages all models.


 */


public function actionAdmin()


{


	


	&#036;model=new Jobseeker('search');


	


	


	&#036;model-&gt;unsetAttributes();  // clear any default values


	if(isset(&#036;_GET['Jobseeker'])){


		&#036;model-&gt;attributes=&#036;_GET['Jobseeker'];


		if(isset(&#036;_GET['Jobseeker']['keyword']) &amp;&amp; &#036;_GET['Jobseeker']['keyword'] &#33;=''){


			&#036;model-&gt;keyword = &#036;_GET['Jobseeker']['keyword'];


		}


		


	}





	&#036;this-&gt;render('admin',array(


		'model'=&gt;&#036;model,


	));


}

Waiting for your valuable solution…

CeBe · January 9, 2014, 1:07pm

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.

subash · January 10, 2014, 4:41am

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.

CeBe · January 10, 2014, 2:21pm

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.

dniznick · January 10, 2014, 6:34pm

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.

Keith · January 10, 2014, 7:10pm

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