How To Reduce Query Execution Time

Hello,

I need to reduce load time for the query which create a grid. The grid have about 12,000 records, and increasing on daily basis, with pagination. It takes around 150-160 seconds i.e. approximately.

The data displays in grid is fetched from 3 tables with 2 joins. I already applied CDBCache but still it’s taking so much time.

Please let me know if there is nay way in the Yii to reduce this load time.

Thanks in advance !!!

[color="#006400"]/* Moved from "Miscellaneous" to "General Discussion for Yii 1.1.x" */[/color]

Hi kishorkarmur, welcome to the forum.

12,000 might be a total count of records, but what is the page size?

And which are you using, CActiveDataProvider or CArrayDataProvider?

Could you explain your case a little more, including what your models are like and how do you construct the data provider?

This tool will help to increase the speed on overall website

Hello Softark,

Please find below the details regarding my query. Hope this will be useful

Table strucutre :

leads : id(PK),leadname,assignedto(FK->users) has model name "Leads";

users : user_id(PK), firstname, lastname has model name "Users";

clients : id(PK),lead_id(FK->leads),firstname,lastname,phone_number (multiple entries for leads) has model name "ClientContacts";

My Relation in model Leads :

public function relations()

{

return array(


	'assignedRel'=>array(self::BELONGS_TO,'Users','assigned_to','joinType'=>'LEFT JOIN','select'=>'assignedRel.lastname,assignedRel.firstname'),


	'contactRel'=>array(self::HAS_ONE,'ClientContacts','lead_id','joinType'=>'LEFT JOIN','select'=>'contactRel.firstname,contactRel.phone_number,contactRel.lastname'),


);

}

/Function for retriving data DataProvider/

public function search()

{

$criteria		= new CDbCriteria;


$criteria->together = true;


$criteria->with = array("contactRel","assignedRel");





return new CActiveDataProvider($this, array(


	'criteria' => $criteria,		


	'pagination'=>array('pageSize'=>50)		


));

}

I think there’s nothing special in your models and search() method. The fetching of 50 rows should end in milliseconds even when there are total of 12,000 rows.

Probably you have a problem in your usage of CGridView … Aren’t you trying to load all the records (around 12,000 rows) at a time into some data array when you render the grid?

How long does the same query take in phpmyadmin?

Problem with indexes or db design issue?

http://www.yiiframework.com/doc/guide/1.1/en/topics.performance