Yii Framework Forum: How To Reduce Query Execution Time - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

How To Reduce Query Execution Time Rate Topic: -----

#1 User is offline   kishorkarmur 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 14-June 13

Posted 14 June 2013 - 09:17 AM

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 !!!
0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 14 June 2013 - 06:49 PM

/* Moved from "Miscellaneous" to "General Discussion for Yii 1.1.x" */

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?
0

#3 User is offline   kishorkarmur 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 14-June 13

Posted 14 June 2013 - 11:16 PM

View Postsoftark, on 14 June 2013 - 06:49 PM, said:

/* Moved from "Miscellaneous" to "General Discussion for Yii 1.1.x" */

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?

0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 14 June 2013 - 11:38 PM

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

#5 User is offline   kumarkulandai 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 152
  • Joined: 18-November 11
  • Location:Bangalore

Posted 15 June 2013 - 12:07 AM

This tool will help to increase the speed on overall website

https://developers.g...peed/pagespeed/
Regards,
Kumar Kulandai.

Posted Image
0

#6 User is offline   kishorkarmur 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 14-June 13

Posted 15 June 2013 - 01:01 AM

View Postsoftark, on 14 June 2013 - 11:38 PM, said:

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


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)
));
}
0

#7 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,085
  • Joined: 16-February 11
  • Location:Japan

Posted 15 June 2013 - 04:02 AM

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?
0

#8 User is offline   waitforit 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 412
  • Joined: 09-February 11

Posted 15 June 2013 - 02:49 PM

How long does the same query take in phpmyadmin?

Problem with indexes or db design issue?
0

#9 User is offline   sefburhan 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 137
  • Joined: 08-February 12
  • Location:islamabad

Posted 17 June 2013 - 12:10 AM

View Postkishorkarmur, on 14 June 2013 - 09:17 AM, said:

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 !!!

http://www.yiiframew...ics.performance
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users