Prestashop Core Application V1.5

• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Security Level: All-users
• Labels:

  None

In the getList() function of the AdminTab, the loading takes a long time when having big database (we have 100.000+ order rows and the list takes about 14 seconds to load.
This is because in the query for the listTotal you load the entire set of orders in the list, but is should be better to add a count as total, and the read that one for the total. If i do that, the list takes about 3-4 seconds to load (on 100.000+ rows).
And when i wan't to filter the query (like customer name = 'Mark Seinen'), it takes even longer. So i changed the code for fetching the list and the listTotal, i really hope you guys will look into this and integrate this change in the next release.

The code (in AdminTab, rule 1084 - 1116, now looks like this:

/* Query in order to get results with all fields */
$sql = 'SELECT SQL_CALC_FOUND_ROWS
'.($this->_tmpTableFilter ? ' * FROM (SELECT ' : '').'
'.($this->lang ? 'b., ' : '').'a.'.(isset($this->_select) ? ', '.$this->_select.' ' : '').'
FROM `'.DB_PREFIX.$sqlTable.'` a
'.($this->lang ? 'LEFT JOIN `'.DB_PREFIX.$this->table.'_lang` b ON (b.`'.$this->identifier.'` = a.`'.$this->identifier.'` AND b.`id_lang` = '.(int)($id_lang).')' : '').'
'.(isset($this->_join) ? $this->_join.' ' : '').'
WHERE 1 '.(isset($this->_where) ? $this->_where.' ' : '').($this->deleted ? 'AND a.`deleted` = 0 ' : '').(isset($this->_filter) ? $this->_filter : '').'
'.(isset($this->_group) ? $this->_group.' ' : '').'
'.((isset($this->_filterHaving) || isset($this->_having)) ? 'HAVING ' : '').(isset($this->_filterHaving) ? ltrim($this->_filterHaving, ' AND ') : '').(isset($this->_having) ? $this->_having.' ' : '').'

'.($this->_tmpTableFilter ? (((isset($this->_filterHaving) || isset($this->_having))?'':' HAVING 1')).$this->_tmpTableFilter : '').'

ORDER BY '.(($orderBy == $this->identifier) ? 'a.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).
($this->_tmpTableFilter ? ') tmpTable WHERE 1'.$this->_tmpTableFilter : '').'
LIMIT '.(int)($start).','.(int)($limit);

$this->_list = Db::getInstance()->ExecuteS($sql);

$result = Db::getInstance()->ExecuteS('SELECT
'.($this->_tmpTableFilter ? ' count as total FROM (SELECT ' : ' count as total, ').'
'.($this->lang ? 'b., ' : '').'a.'.(isset($this->_select) ? ', '.$this->_select.' ' : '').'
FROM `'.DB_PREFIX.$sqlTable.'` a
'.($this->lang ? 'LEFT JOIN `'.DB_PREFIX.$this->table.'_lang` b ON (b.`'.$this->identifier.'` = a.`'.$this->identifier.'` AND b.`id_lang` = '.(int)($id_lang).')' : '').'
'.(isset($this->_join) ? $this->_join.' ' : '').'
WHERE 1 '.(isset($this->_where) ? $this->_where.' ' : '').($this->deleted ? 'AND a.`deleted` = 0 ' : '').(isset($this->_filter) ? $this->_filter : '').'
'.(isset($this->_group) ? $this->_group.' ' : '').'
'.((isset($this->_filterHaving) || isset($this->_having)) ? 'HAVING ' : '').(isset($this->_filterHaving) ? ltrim($this->_filterHaving, ' AND ') : '').(isset($this->_having) ? $this->_having.' ' : '').'

'.($this->_tmpTableFilter ? (((isset($this->_filterHaving) || isset($this->_having))?'':' HAVING 1')).$this->_tmpTableFilter : '').'

ORDER BY '.(($orderBy == $this->identifier) ? 'a.' : '').'`'.pSQL($orderBy).'` '.pSQL($orderWay).
($this->_tmpTableFilter ? ') tmpTable WHERE 1'.$this->_tmpTableFilter : ''));

if ($result && count($result)>0) $this->_listTotal = $result[0]['total'];

• Assignee:

  Unassigned

  Reporter:

  Mark Seinen

• Created:

  11/May/11 3:40 PM

  Updated:

  11/Apr/13 5:37 PM

  Resolved:

  11/Apr/13 5:37 PM