prices-drop extremly slow when 500 products on sale

[Gomlers]

Does anyone have the same problem in 1.6.0.6?

 

Or does anyone know how to make the prices-drop load 10x faster with 500 products+ ?

[tuk66]

What you mean price-drop? How exactly you set the prices?

[Gomlers]

I mean the products that are listed under theURL: http://www.mydomain.com/prices-drop

 

I've just set up some rules for many of my products under backoffice->price rules -> catalog price rules.

These products have 60% off, and there are a total of about 500 products when visiting the prices-drop link for my shop.

 

All other pages in my prestashop loads in about 1second.

 

But the prices-drop-url uses about 10-15 seconds

[tuk66]

It can be a slow SQL query, too much SQL subqueries, missing indices or heavy code.The point is in the getPricesDrop method in the Product class.

[Gomlers]

Thank you for your answers.

 

I unfortunately don't know how to check this.

 

I run the default prestashop 1.6 theme, with minor changes in the CSS.

No other modules are running..

 

Do you know where I can start searching?

[Scully]

We had the exactly same problem with more than 1000 prices drop products and found a solution to reduce our already optimized execution time from appx. 3 seconds to ~ 0.5 seconds. The problem was found in /classes/product.php and there in the function getPricesDrop. The default behavior in

1.5.6. is: first get an array of id_products within a certain date, this is done in appx. line2230.

 

$ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);

 

Since we do not use valid from and valid to dates in products, we simply changed the code as follows.

From the original lines 2230 to line 2244:

 

                               $ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);

                               $tab_id_product = array();

                               foreach ($ids_product as $product)

                                               if (is_array($product))

                                                               $tab_id_product[] = (int)$product['id_product'];

                                               else

                                                               $tab_id_product[] = (int)$product;

                               $front = true;

                               if (!in_array($context->controller->controller_type, array('front', 'modulefront')))

                                               $front = false;

                               $groups = FrontController::getCurrentCustomerGroups();

                               $sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

 

To this new code:

                               // 31.07.2015 Fix to improve performance

                               // $ids_product = Product::_getProductIdByDate((!$beginning ? $current_date : $beginning), (!$ending ? $current_date : $ending), $context);

                              

                               // move this 3 lines upwards since alreaday needed earlier

                               $front = true;

                               if (!in_array($context->controller->controller_type, array('front', 'modulefront')))

                                               $front = false;

                                                           

                               // The following SQL generates a preselection of id_product with only the most important tables, less joins = less execution time

                               $sql = 'SELECT DISTINCT p.id_product

                                               FROM `'._DB_PREFIX_.'product` p

                                               '.Shop::addSqlAssociation('product', 'p').'

                                               LEFT JOIN '._DB_PREFIX_.'product_attribute pa ON (pa.id_product = p.id_product)

                                               '.Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.default_on=1').'

                                               '.Product::sqlStock('p', 0, false, $context->shop).'

                                               LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (

                                                               p.`id_product` = pl.`id_product`

                                                               AND pl.`id_lang` = '.(int)$id_lang.Shop::addSqlRestrictionOnLang('pl').'

                                               )

                                               WHERE product_shop.`active` = 1

                                               AND product_shop.`show_price` = 1

                                               '.($front ? ' AND p.`visibility` IN ("both", "catalog")' : '').'

                                               ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'

                                               LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

                              

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

                               // End Fix Schnellmann to improve performance

                              

 

                               $tab_id_product = array();

                               foreach ($ids_product as $product)

                                               if (is_array($product))

                                                               $tab_id_product[] = (int)$product['id_product'];

                                               else

                                                               $tab_id_product[] = (int)$product;

 

                               $groups = FrontController::getCurrentCustomerGroups();

                               $sql_groups = (count($groups) ? 'IN ('.implode(',', $groups).')' : '= 1');

// END OF FIX

 

Short explanation: This code does replace the getProductIdByDate call but also delivers an array of id_product which are valid for the selected products including the possible order by and orderway. It restricts the result to the N products (typically 10 per page).

One more small change is needed on the original lines appx. around 2303 and 2304. Change from:

 

                                                               ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'

                                                               LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

 

To new:

                                                               ORDER BY '.(isset($order_by_prefix) ? pSQL($order_by_prefix).'.' : '').pSQL($order_by).' '.pSQL($order_way).'';

                                                               // LIMIT '.(int)($page_number * $nb_products).', '.(int)$nb_products;

 

Note the ending of the first line!! The LIMIT clause may not take effect here any more since the LIMIT has already done in the preselect before.

You might want to check out the response times for our prices drop page here:

http://mit100wir.ch/wirshop/wir-aktionen

 

Known limitations

If you youse the product valid from and product valid to date ranges in order to automatically activate / deactivate products, you would have to do one step more in order to preserve this functionality.

 

best regards, Scully