Slow getproducts() function, need advices to speed it up

[AfterGlow93]

Hi, 

I had a module customized for my business and i am facing speed issues on listing

The issue seems to come from the function getproducts.

The query in this function gets products informations from database from multiple product ids.

When i execute this query on mysql client, this query take less than 1 second to finish.

But, when processed through the controller i use for my module, i can see that each product id is processed as unique, creating multiple subqueries and slowing down the whole rendering of the module page.

When i show a simple product-list from a category, with more than 100.000 products, the page load in less than 1 second. I would like to understand why, is it the function itself, or something missing here that can slow down 10x the load time by processing each product id itself ?

Here is my getproduct code :

    public function getProducts($where, $id_lang, $p, $n, $order_by = null, $order_way = null, $get_total = false, $active = true, $random = false, $random_number_products = 1, $check_access = true, Context $context = null)
    {
        # validate module
        unset($check_access);
        if (!$context) {
            $context = Context::getContext();
        }
        $front = true;
        if (!in_array($context->controller->controller_type, array('front', 'modulefront'))) {
            $front = false;
        }
        if ($p < 1) {
            $p = 1;
        }
        $id_supplier = (int) Tools::getValue('id_supplier');

		
		        if ($get_total) {
            $sql = 'SELECT COUNT(cp.`id_product`) AS total
                FROM `' . _DB_PREFIX_ . 'product` p
                ' . Shop::addSqlAssociation('product', 'p') . '
                LEFT JOIN `' . _DB_PREFIX_ . 'category_product` cp ON p.`id_product` = cp.`id_product`
                ' . $where . '
                ' . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '') .
                    ($active ? ' AND product_shop.`active` = 1' : '') .
                    ($id_supplier ? 'AND p.id_supplier = ' . (int) $id_supplier : '');
            return (int) Db::getInstance()->getValue($sql);
        }
		
        $sql = 'SELECT DISTINCT p.id_product, p.*, product_shop.*, stock.out_of_stock, IFNULL(stock.quantity, 0) as quantity, product_attribute_shop.`id_product_attribute`, product_attribute_shop.minimal_quantity AS product_attribute_minimal_quantity, pl.`description`, pl.`description_short`, pl.`available_now`,
                pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, m.`name` AS manufacturer_name, cl.`name` AS category_default,
                DATEDIFF(product_shop.`date_add`, DATE_SUB(NOW(),
                INTERVAL ' . (Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20) . '
                    DAY)) > 0 AS new, product_shop.price AS orderprice
            FROM `' . _DB_PREFIX_ . 'category_product` cp
            INNER JOIN `' . _DB_PREFIX_ . 'product` p
                ON p.`id_product` = cp.`id_product`
            ' . Shop::addSqlAssociation('product', 'p') . '
            LEFT JOIN `' . _DB_PREFIX_ . 'product_attribute` pa
            ON (p.`id_product` = pa.`id_product`)
            ' . Shop::addSqlAssociation('product_attribute', 'pa', false, 'product_attribute_shop.`default_on` = 1') . '
            ' . Product::sqlStock('p', 'product_attribute_shop', false, $context->shop) . '
            LEFT JOIN `' . _DB_PREFIX_ . 'category_lang` cl
                ON (product_shop.`id_category_default` = cl.`id_category`
                AND cl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('cl') . ')
            LEFT JOIN `' . _DB_PREFIX_ . 'product_lang` pl
                ON (p.`id_product` = pl.`id_product`
                AND pl.`id_lang` = ' . (int) $id_lang . Shop::addSqlRestrictionOnLang('pl') . ')
            LEFT JOIN `' . _DB_PREFIX_ . 'manufacturer` m
                ON m.`id_manufacturer` = p.`id_manufacturer`
            ' . $where . '
                ' . ($active ? ' AND product_shop.`active` = 1' : '')
                . ($front ? ' AND product_shop.`visibility` IN ("both", "catalog")' : '')
                . ($id_supplier ? ' AND p.id_supplier = ' . (int) $id_supplier : '')
                . (Tools::getValue('filter_cat') ? ' AND cp.id_category='. (int) Tools::getValue('filter_cat') : '');
				
				            $orderby = 'p.price';
            if (Tools::getValue('order') == 'product.position.asc') {
                $orderby = 'cp.position ASC';
            }
            if (Tools::getValue('order') == 'product.name.asc') {
                $orderby = 'pl.name ASC';
            }
            if (Tools::getValue('order') == 'product.name.desc') {
                $orderby = 'pl.name DESC';
            }
            if (Tools::getValue('order') == 'product.price.asc') {
                $orderby = 'product_shop.price ASC';
            }
            if (Tools::getValue('order') == 'product.price.desc') {
                $orderby = 'product_shop.price DESC';
            }

            $sql .= ' ORDER BY ' . $orderby;

        $result = Db::getInstance()->executeS($sql);

        if (!$result) {
            return array();
        }
        return Product::getProductsProperties($id_lang, $result);
    }

Thanks for help

Edited May 16, 2021 by AfterGlow93 (see edit history)