Performance Problem in Admin Order Screen

[ravithb]

Hi,

 

I'm Ravith Botejue a PHP developer in Sri Lanka. I'm having a web store which runs prestashop 1.5, and I've added several modules which were written by me to it. This is my first post in presatshop forums, and I'm not a native English speaker, therefore apologies for any grammatical errors.

 

Recently I've come across a serious performance issue, where if a particular customer has large number of orders and carts (thousands in my case), the admin orders page gets stuck on the searchCarts ajax call. It basically hangs the page in chrome/chromium (displays kill dialog) and firefox takes about 5 minutes to enable typing the product name input box.

 

I've investigated ant it turns out to be the following lines which load all the carts and orders for the particular customer.

 

AdminCartsController.php

public function displayAjaxSearchCarts()
	{
		$id_customer = (int)Tools::getValue('id_customer');
		$carts = Cart::getCustomerCarts((int)$id_customer);
		$orders = Order::getCustomerOrders((int)$id_customer);
		$customer = new Customer((int)$id_customer);

		if (count($carts))
			foreach ($carts as $key => &$cart)
			{
				$cart_obj = new Cart((int)$cart['id_cart']);
				if ($cart['id_cart'] == $this->context->cart->id || !Validate::isLoadedObject($cart_obj) || $cart_obj->OrderExists())
					unset($carts[$key]);
				$currency = new Currency((int)$cart['id_currency']);
				$cart['total_price'] = Tools::displayPrice($cart_obj->getOrderTotal(), $currency);
			}

I suggest the following modification to be made to make it efficient.

 

AdminCartsController.php

public function displayAjaxSearchCarts()
	{
		$id_customer = (int)Tools::getValue('id_customer');
		$carts = Cart::getCustomerCarts((int)$id_customer,true,5);
		$orders = Order::getCustomerOrders((int)$id_customer,false,null,5);
		$customer = new Customer((int)$id_customer);

		if (count($carts))
			foreach ($carts as $key => &$cart)
			{
				$cart_obj = new Cart((int)$cart['id_cart']);
				if ($cart['id_cart'] == $this->context->cart->id || !Validate::isLoadedObject($cart_obj) || $cart_obj->OrderExists())
					unset($carts[$key]);
				$currency = new Currency((int)$cart['id_currency']);
				$cart['total_price'] = Tools::displayPrice($cart_obj->getOrderTotal(), $currency);
			}

Order.php

/**
	 * Get customer orders
	 *
	 * @param integer $id_customer Customer id
	 * @param boolean $showHiddenStatus Display or not hidden order statuses
	 * @return array Customer orders
	 */
	public static function getCustomerOrders($id_customer, $showHiddenStatus = false, Context $context = null,$limit=false)
	{
		if (!$context)
			$context = Context::getContext();

		$res = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT o.*, (SELECT SUM(od.`product_quantity`) FROM `'._DB_PREFIX_.'order_detail` od WHERE od.`id_order` = o.`id_order`) nb_products
		FROM `'._DB_PREFIX_.'orders` o
		WHERE o.`id_customer` = '.(int)$id_customer.'
		GROUP BY o.`id_order`
		ORDER BY o.`date_add` DESC'.($limit>0?(' LIMIT '.$limit):''));
		if (!$res)
			return array();

		foreach ($res as $key => $val)
		{
			$res2 = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
				SELECT os.`id_order_state`, osl.`name` AS order_state, os.`invoice`
				FROM `'._DB_PREFIX_.'order_history` oh
				LEFT JOIN `'._DB_PREFIX_.'order_state` os ON (os.`id_order_state` = oh.`id_order_state`)
				INNER JOIN `'._DB_PREFIX_.'order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = '.(int)$context->language->id.')
			WHERE oh.`id_order` = '.(int)($val['id_order']).(!$showHiddenStatus ? ' AND os.`hidden` != 1' : '').'
				ORDER BY oh.`date_add` DESC, oh.`id_order_history` DESC
			LIMIT 1');

			if ($res2)
				$res[$key] = array_merge($res[$key], $res2[0]);

		}
		return $res;
	}

Cart.php

public static function getCustomerCarts($id_customer, $with_order = true, $limit = false)
	{
		return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
		SELECT *
		FROM '._DB_PREFIX_.'cart c
		WHERE c.`id_customer` = '.(int)$id_customer.'
		'.(!$with_order ? 'AND id_cart NOT IN (SELECT id_cart FROM '._DB_PREFIX_.'orders o)' : '').'
		ORDER BY c.`date_add` DESC' .($limit>0?(' LIMIT '.$limit):''));
	}

Please let me know your ideas, and comments. I should also be able to send a patch to resolve this if you all think it would be beneficial to the system.