Hack to speed up AceSEF in the administration area

Recently I had complaints of AceSEF being slow when going into the SefURLs admin area.  Well in this case there were over 35,000 Urls and it was indeed slow (around 1 minute 45 seconds). 

I traced it down to getTotal records and poor indexes on the database table.  The easy fix is to modify the getTotal() method and include another method.  To do this do the following:

- Open ~/administrator/components/com_acesef/models/sefurls.php
- Find the _buildViewQuery function
- Add the following function above it

    

function _buildCountViewQuery() {

    global $mainframe, $option;

    $type = $mainframe->getUserStateFromRequest($option.'.sefurls.type', 'type', 1);

    

    $where = $this->_buildViewWhere();

    

    if ($type == 5) {

        // Duplicated URLs

        return 'SELECT DISTINCT COUNT(*) as numRecords FROM jos_acesef_urls AS t1, jos_acesef_urls AS t2 '.$where;

    } else {

        // All URLs

        return 'SELECT COUNT(*) as numRecords FROM jos_acesef_urls '.$where;

    }

}

- Modify the getTotal method in the same file to look kind of like:

if (empty($this->_total)) {

    $database = JFactory::getDBO();

    $database->setQuery($this->_buildCountViewQuery());

    $result = $database->loadObjectList();

    $this->_total = $result[0]->numRecords;    

}

return $this->_total;

- Give it a go.


Why does this work?  Because the built in method _getListCount from Joomla does an execute on the query and then gets the rows.  Well if a Select * happens on a large number of rows it will take a long time.  So I just modified this to move the work to the database server.  We could have also just returned one column and it would have been much faster. 

DISCLAIMER: Please do this at your own risk. Adding in this patch makes it tougher to upgrade and will probably annoying the AceSEF devs since you are meddling in their code.  But given that it was over the top slow and it obviously hadn't been tested on a large number of records I figured I would bring it to everyone's attention.

-Mike