Search code examples
phpoptimizationzend-framework

Apache/ZF/MySQL/jQuery: how to test where the bottleneck is

I am not sure how to approach this. In my application (Zend Framework, MySQL, jQuery) one of the pages is taking over 10 seconds to load.

It's loading under 400 records from the database, so I don't think the bottleneck is in MySQL. Certainly a server-side select on the relevant table is not a problem: 

379 rows in set (0.00 sec)

Firebug reports the delay as waiting for server response in the original request, "0 ---> 11.09s Waiting". This is the first request, and all the jQuery code gets loaded afterwards, with an onLoad time of 11.54s. So the jQuery doesn't seem to be the bottleneck either.

No other controller/action is taking near this long to respond, so I can't imagine it being an Apache problem. Which leaves ZF itself.

I wonder if there is a way to test where the delay occurs. Here is the full text of the relevant action:

public function listAction() 
{
    $userDepts = new Application_Model_DbTable_UserDepartments;
    $ptcpDepts = new Application_Model_DbTable_ParticipantDepts;
    $participants = new Application_Model_DbTable_Participants;
    $depts = new Application_Model_DbTable_Depts;
    $ptcpAlerts = new Application_Model_DbTable_AlertsParticipants;

    //check if sub-list is being passed
    if ($this->_helper->flashMessenger->getMessages()) {
        $passedList = $this->_helper->flashMessenger->getMessages();
        $list = $passedList['0'];
    } else {
        $list = $participants->getStaffPtcps();
    }

    $goodList = array_unique($list);
    $number = count($goodList);
    $content = array();

    foreach ($goodList as $id) {
        $deptNames = array();
        $ptcpInfo = $participants->getRecord($id);
        $ptcpDept = $ptcpDepts->getList('depts', $id);

        $flags = $ptcpAlerts->getPtcpAlerts($id);
        if (count($flags) > 0) {
            $flagTest = TRUE;
        } else {
            $flagTest = FALSE;
        }

        foreach ($ptcpDept as $did) {
            $deptName = $depts->getDept($did);
            $deptNames[$did] = $deptName['deptName'];
        }

        $content[$id] = $ptcpInfo;
        $content[$id]['depts'] = $deptNames;
        $content[$id]['flag'] = $flagTest;
    }

    foreach ($content as $c => $key) {
        $sortLastName[] = $key['lastName'];
    }

    array_multisort($sortLastName, SORT_ASC, $content);

    $this->view->list = $content;
    $this->view->count = $number;

    $this->view->layout()->customJS = 
            '<script type="text/javascript" src="/js/datePicker.js"></script>' .
            '<script type="text/javascript" src="/js/ptcpCreate.js"></script>' . 
            '<script type="text/javascript" src="/js/editDataWithModal.js"></script>' . 
            '<script type="text/javascript" src="/js/filter.js"></script>'; 

    $form = new Application_Form_AddParticipant;
    $this->view->form = $form;        
}

Any ideas about optimizing this code - or pointers where else to look - would be greatly appreciated!

Solution

  • Well I dont know what your custom methods are doing but im going to assume youre making a tone od db calls:

    • $participants->getStaffPtcps(); get the inital list
    • then for each paticipant you:
      • get the full record
      • get a related list ($ptcpDepts->getList('depts', $id)) for departments
      • foreeach department you fetch even more data

    If this is correct then you are actually running a ton of queries depending on how many records the Staff Participants and Departments return. More than that each time you run this youre hydrating objects for each record unless you have specifically turned that off. This will make things super slow in a lot of cases you can probably optimize by doing this in only a couple queries with Joins, but i would have to know more about the db schema and what your methods are doing in order to tell you how.

    You could also make sure you are completely bypassing Zend_Db_Table_Row and Zend_Db_Table_Rowset.