zend framework paginaator results too slow

Question

I have a query that running way too slow. the page takes a few minutes to load. I'm doing a table join on tables with over 100,000 records. In my query, is it grabbing all the records or is it getting only the amount I need for the page? Do I need to put a limit in the query? If I do, won't that give the paginator the wrong record count?

$paymentsTable = new Donations_Model_Payments();
$select = $paymentsTable->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false)
    ->from(array('p' => 'tbl_payments'), array('clientid', 'contactid', 'amount'))
    ->where('p.clientid = ?', $_SESSION['clientinfo']['id'])
    ->where('p.dt_added BETWEEN  \''.$this->datesArr['dateStartUnix'].'\' AND \''.$this->datesArr['dateEndUnix'].'\'')
        ->join(array('c' => 'contacts'), 'c.id = p.contactid', array('fname', 'mname', 'lname'))
        ->group('p.id')
        ->order($sortby.' '.$dir)
        ;
        $payments=$paymentsTable->fetchAll($select);

// paginator
$paginator = Zend_Paginator::factory($payments);
$paginator->setCurrentPageNumber($this->_getParam('page'), 1);
$paginator->setItemCountPerPage('100'); // items pre page
$this->view->paginator = $paginator;

$payments=$payments->toArray();
$this->view->payments=$payments;

Answer 1

In your code, you are :

• first, selecting and fetching all records that match your condition
  • see the select ... from... and all that
  • and the call to fetchAll on the line just after
• and, only the, you are using the paginator,
  • on the results returned by the fetchAll call.

With that, I'd say that, yes, all your 100,000 records are fetched from the DB, manipulated by PHP, passed to Zend_Paginator which has to work with them... only to discard almost all of them.

Using Zend_Paginator, you should be able to pass it an instance of Zend_Db_Select, and let it execute the query, specifying the required limit.

Maybe the example about DbSelect and DbTableSelect adapter might help you understand how this can be achieved (sorry, I don't have any working example).

Answer 2

Please see revised code below. You need to pass the $select to Zend_Paginator via the correct adapter. Otherwise you won't see the performance benefits.

$paymentsTable = new Donations_Model_Payments();
$select = $paymentsTable->select(Zend_Db_Table::SELECT_WITH_FROM_PART);
$select->setIntegrityCheck(false)
    ->joinLeft('contacts', 'tbl_payments.contactid = contacts.id')
    ->where('tbl_payments.clientid = 39')
    ->where(new Zend_Db_Expr('tbl_payments.dt_added BETWEEN "1262500129" AND "1265579129"'))
    ->group('tbl_payments.id')
    ->order('tbl_payments.dt_added DESC');

// paginator
$paginator = new Zend_Paginator(new Zend_Paginator_Adapter_DbTableSelect($select));
$paginator->setCurrentPageNumber($this->_getParam('page', 1));
$paginator->setItemCountPerPage('100'); // items pre page
$this->view->paginator = $paginator;

Please see revised code above!

Answer 3

I personally count the results via COUNT(*) and pass that to zend_paginator. I never understood why you'd deep link zend_paginator right into the database results. I can see the pluses and minuses, but really, its to far imho.

Bearing in mind that you only want 100 results, you're fetching 100'000+ and then zend_paginator is throwing them away. Realistically you want to just give it a count.

    $items      = Eurocreme_Model::load_by_type(array('type' => 'list', 'from' => $from, 'to' => MODEL_PER_PAGE, 'order' => 'd.id ASC'));
    $count      = Eurocreme_Model::load_by_type(array('type' => 'list', 'from' => 0, 'to' => COUNT_HIGH, 'count' => 1));

    $paginator = Zend_Paginator::factory($count);
    $paginator->setItemCountPerPage(MODEL_PER_PAGE);
    $paginator->setCurrentPageNumber($page);

    $this->view->paginator = $paginator;
    $this->view->items = $items;