On May 12, 12:04 am, OldWest <[email protected]> wrote:
> I am really a bit lost on why this is happening. I have read a considerable
> amount of online info regarding CakePHP and slow queries, but *I don't
> *believe
> its anything wrong with CakePHP. There has got to be a way to speed things
> up.
>
> My CakePHP query is below.
>
> Processing of query is considerably slower on new server. (all details
> below). I am running a php/MySQL query. I have some peculiar (slow down)
> things happening when I transferred the site to another server. *I am
> running the exact same php script (and query options) on both servers. The
> database tables and data are also identical. *
>
> Test #1 (Server #1): (default) 32 queries took 1 ms
> Test #1 (Server #2): (default) 32 queries took 10 ms (9ms longer)
>
> Test #2 (Server #1): (default) 132 queries took 5 ms
> Test #2 (Server #2): (default) 144 queries took 60 ms (55ms longer)
>
> To translate the difference into seconds of waiting time, server #1
> generally processes any query (meaning results are displayed) within 3
> seconds. And server #2 generally 7-14 seconds.
Do you not find it a bit weird that you're trying to find why one
server is 7-11 seconds slower in responding than the other, and
pointing out a 55ms difference in db query times?
>
> I am sorry to post my entire search() function (which is returning the
> results as expected), but if anyone can evaluate it for problem points and
> *harsh
> *critique I am all ears!
>
> function search($ApplicantAge = 0, $SpouseAge = 0, $NumberChildren = 0,
> $Vision = 0, $Zip = 0) {
> $search = $this->data['Plan'];
> $searchdetails = array();
> if (isset($search['ApplicantAge'])) {
> $this->Plan->set($this->data);
> if ($this->Plan->validates()) {
> $ApplicantAge = $search['ApplicantAge'];
> } else {
> $errors = $this->Plan->invalidFields();
> $error_messages = implode(' ', $errors);
> $this->Session->setFlash($error_messages);
> $this->redirect('/');
> }
> }
> $searchdetails[0] = $ApplicantAge;
> if (isset($search['SpouseAge']) && !empty($search['SpouseAge'])) {
> $SpouseAge = $search['SpouseAge'];
> }
> $searchdetails[1] = $SpouseAge;
> if (isset($search['NumberChildren']) &&
> !empty($search['NumberChildren'])) {
> $NumberChildren = $search['NumberChildren'];
> }
> $searchdetails[2] = $NumberChildren;
> if (isset($search['Vision']) && !empty($search['Vision'])) {
> $Vision = $search['Vision'];
> }
> $searchdetails[3] = $Vision;
> if (isset($search['ZipCode']) && !empty($search['ZipCode'])) {
> $Zip = $search['ZipCode'];
> }
> $searchdetails[4] = $Zip;
> $memberCount = 1;
> if ($SpouseAge > 0) {
> $memberCount += 1;
> }
> if ($NumberChildren > 0) {
> $memberCount += $NumberChildren;
> }
> $comboType = 'sa';
> if ($ApplicantAge < 18) {
> $comboType = 'sc';
> }
> if ($SpouseAge > 0) {
> if ($NumberChildren > 0) {
> $comboType = 'asc';
> } else {
> $comboType = 'as';
> }
> } else {
> if ($NumberChildren > 0) {
> $comboType = 'ac';
> }
> }
> $options = array(
> 'joins' => array(
> array(
> 'table' => 'plans_zips',
> 'alias' => 'PZips',
> 'type' => 'inner',
> 'foreignKey' => false,
> 'conditions' => array('Plan.id = PZips.plan_id')
> ),
> array(
> 'table' => 'zips',
> 'alias' => 'Zips',
> 'type' => 'inner',
> 'foreignKey' => false,
> 'conditions' => array('Zips.id = PZips.zip_id')
> )
> ),
> 'conditions' => array(
> "AND" => array(
> array($ApplicantAge . ' BETWEEN Age.Min_Age AND
> Age.Max_age'),
> 'Zips.title' => $Zip,
> 'Applicant.amount' => array($comboType, $memberCount)
> )
> ),
> 'contain' => array(
> 'PlanDetail' => array(
> 'fields' => array(
> 'id',
> 'effective_date',
> 'expiration_date',
> 'active',
> 'name',
> 'plan_type_id',
> 'max_benefit',
> 'deductible',
> 'preventive',
> 'basic',
> 'major',
> 'ortho',
> 'application_url',
> 'company_id',
> 'plan_type_id',
> 'plan_detail_note_id'),
> 'Company' => array('fields' => array(
> 'id',
> 'name',
> 'company_logo_url'
> )),
> 'PlanType' => array('fields' => array(
> 'id',
> 'name',
> 'description'
> ))),
> 'Age' => array('fields' => array(
> 'id',
> 'name',
> )),
> 'Applicant' => array('fields' => array(
> 'id',
> 'name',
> )),
> 'Zip' => array('fields' => array(
> 'id',
> 'title')
> )
> ),
> 'limit' => 100,
> 'order' => array('monthly_cost' => 'ASC')
> );
> if ($Vision == 1) {
> array_push($options['conditions'], "dental_cost > 0");
> }
> $url = array('controller' => 'plans', 'action' => 'search');
> $this->paginate = $options;
> $this->set('searchdetails', array_merge($url, $searchdetails));
> $this->set('plans', $this->paginate('Plan'));
> $this->set('ApplicantAge', $ApplicantAge);
> $this->set('SpouseAge', $SpouseAge);
> $this->set('NumberChildren', $NumberChildren);
> $this->set('Vision', $Vision);
> $this->set('Zip', $Zip);
> if ($this->RequestHandler->isPost()) {
> $this->Session->write('search_form_sess', $this->data);
> $initial_url = $ApplicantAge . '/' . $SpouseAge . '/' .
> $NumberChildren . '/' . $Vision . '/' . $Zip;
> $this->redirect(array('action' => 'search', $initial_url));
> exit();
> }
> $this->layout = 'default';
>
> }
>
> *MySQL Server Data*
>
> ***Server #1***
> Server: Localhost via UNIX socket
> Server version: 5.0.92-community
> MySQL client version: 5.0.92
> PHP extension: mysql
> Protocol version: 10
> ----------------------------------------
> ***Server #2***
> Server: Localhost via UNIX socket
> Server version: MySQL - 5.0.51a-24+lenny5-log
> MySQL client version: 5.0.51a
> PHP extensions: mysql
> Protocol version: 10
When you enabled debug kit and looked at the timer waterfall - what
did it show on both servers?
AD
--
Our newest site for the community: CakePHP Video Tutorials
http://tv.cakephp.org
Check out the new CakePHP Questions site http://ask.cakephp.org and help others
with their CakePHP related questions.
To unsubscribe from this group, send email to
[email protected] For more options, visit this group at
http://groups.google.com/group/cake-php
