special price product collection working but loading speed is too slow ,I don't understand why the speed is so slow please help!

Question

<?php

namespace Product\Discount\Block;
use Magento\Catalog\Api\CategoryRepositoryInterface;

/*
use Magento\Catalog\Api\CategoryRepositoryInterface;
use Magento\Catalog\Block\Product\Context;
use Magento\Framework\Data\Helper\PostHelper;
use Magento\Catalog\Model\Layer\Resolver;
use Magento\Framework\Url\Helper\Data;
use Magento\Catalog\Model\ResourceModel\Product\Collection;
use Magento\Framework\App\ResourceConnection;
*/
class Getproductspecialprice extends \Magento\Catalog\Block\Product\ListProduct
{
    protected $_collection;

    protected $categoryRepository;

    protected $_resource;
   
   
    public function __construct(
    \Magento\Catalog\Block\Product\Context $context,
            \Magento\Framework\Data\Helper\PostHelper $postDataHelper,
            \Magento\Catalog\Model\Layer\Resolver $layerResolver,
            CategoryRepositoryInterface $categoryRepository,
            \Magento\Framework\Url\Helper\Data $urlHelper,
            \Magento\Catalog\Model\ResourceModel\Product\Collection $collection,
            \Magento\Framework\App\ResourceConnection $resource,
            array $data = []
    ) {
        $this->categoryRepository = $categoryRepository;
        $this->_collection = $collection;
        $this->_resource = $resource;

        parent::__construct($context, $postDataHelper, $layerResolver, $categoryRepository, $urlHelper, $data);
    }

    public function getproductcollection()
    {
       
       
         $page=($this->getRequest()->getParam('p'))? $this->getRequest()->getParam('p') : 1;
         $pageSize=($this->getRequest()->getParam('limit'))? $this->getRequest()->getParam('limit') : 8;

        $category_id = $this->getRequest()->getParam('cid'); // pass your category id
        $objectManager =  \Magento\Framework\App\ObjectManager::getInstance();        
        $categoryFactory = $objectManager->get('\Magento\Catalog\Model\CategoryFactory');
        $allChildrenCategoryIdsArr=[];
        $setTitle='';
        if($category_id==66505){
            $category = $categoryFactory->create()->load(66462);
            $allChildrenCategoryIdsArr = $category->getAllChildren(true);
            
        }
        elseif($category_id==66506){
            $category = $categoryFactory->create()->load(66466);
            $allChildrenCategoryIdsArr = $category->getAllChildren(true);
            //$this->_setTitle='Men';
        }
        elseif($category_id==66507){
            $category1 = $categoryFactory->create()->load(66467);
            $allChildrenCategoryIdsArr1 = $category1->getAllChildren(true);
            
            $category2 = $categoryFactory->create()->load(66468);
            $allChildrenCategoryIdsArr2 = $category2->getAllChildren(true);
            
            $allChildrenCategoryIdsArr=array_merge($allChildrenCategoryIdsArr1,$allChildrenCategoryIdsArr2);        
            //$this->_setTitle='Kids';
        }
        else{
            $category1 = $categoryFactory->create()->load(66469);
            $allChildrenCategoryIdsArr1 = $category1->getAllChildren(true);
            
            $category2 = $categoryFactory->create()->load(66470);
            $allChildrenCategoryIdsArr2 = $category2->getAllChildren(true);
            
            $allChildrenCategoryIdsArr=array_merge($allChildrenCategoryIdsArr1,$allChildrenCategoryIdsArr2);        
            //$this->_setTitle='Baby';
        }       
        /*
        $collection = clone $this->_collection;
        $collection->clear()->getSelect()->reset(\Magento\Framework\DB\Select::WHERE)->reset(\Magento\Framework\DB\Select::ORDER)->reset(\Magento\Framework\DB\Select::LIMIT_COUNT)->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET)->reset(\Magento\Framework\DB\Select::GROUP);
        */
        $collection = clone $this->_collection;
        $collection->clear()->getSelect()->reset(\Magento\Framework\DB\Select::WHERE)->reset(\Magento\Framework\DB\Select::ORDER)->reset(\Magento\Framework\DB\Select::LIMIT_COUNT)->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET)->reset(\Magento\Framework\DB\Select::GROUP);

        $categorycollection = $allChildrenCategoryIdsArr;
        $today_date = date('Y-m-d');
        if (isset($categorycollection) && !empty($categorycollection))
        {
          
         
            $collection
                ->addMinimalPrice()
                ->addFinalPrice()
                ->addTaxPercents()
                ->addAttributeToSelect('*')
                ->addAttributeToFilter('special_price', ['neq' => ''])   
                ->addCategoriesFilter(['in' => $categorycollection])
                ->groupByAttribute('color')
                ->addAttributeToSort('entity_id','desc')    
                ->addAttributeToFilter('is_saleable', 1, 'left');
                
        }
        else
        {
            
             $collection
                ->addMinimalPrice()
                ->addFinalPrice()
                ->addTaxPercents()
                ->addAttributeToSelect('*')
                ->addAttributeToFilter('special_price', ['neq' => ''])   
                ->addAttributeToSort('entity_id','desc')
                ->groupByAttribute('color')
                ->addAttributeToFilter('is_saleable', 1, 'left');
        }
        
          //$collection->getSelect()->limit(3);
        
        $collection->setPageSize($pageSize);
        $collection->setCurPage($page);
        //$collection->setCurPage($page);
        //echo $collection->load(true);
        //exit;
        return $collection;
    }
    
protected function _prepareLayout()
{
    parent::_prepareLayout();
    $this->pageConfig->getTitle()->set(__('Discount Product'));


    if ($this->getproductcollection()) {
        $pager = $this->getLayout()->createBlock(
            'Magento\Theme\Block\Html\Pager',
            'product.discount.pager'
        )->setAvailableLimit(array(4=>4,8=>8))->setShowPerPage(True)->setCollection(
            $this->getproductcollection()
        );
        $this->setChild('pager', $pager);
        $this->getproductcollection()->load();
    }
    
    return $this;
}

public function getPagerHtml()
{
    
    return $this->getChildHtml('pager');
}

}

Answer 1

Here are some improvements you can do.

Try getting rid of ->addAttributeToSelect('*'). Select only what you need. Adding all product attributes can be expensive. You can use ->addAttributeToSelect($this->catalogConfig->getProductAttributes()) where $this->catalogConfig is an instance of \Magento\Catalog\Model\Config that you can provide as a dependency in constructor.

Also, I have a feeling that you method getproductcollection gets called more than once and everything inside it gets executed multiple times.

You can solve this by using memoization.

First of all remove this dependency from your constructor \Magento\Catalog\Model\ResourceModel\Product\Collection $collection (but leave the protected $_collection; member)

And replace it with a collection factory \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $collectionFactory,.

Now make your getproductcollection method looks something like this

if ($this->_collection === null) {
    //your logic in here
    //but replace
    //$collection = clone $this->_collection;
        $collection->clear()->getSelect()->reset(\Magento\Framework\DB\Select::WHERE)->reset(\Magento\Framework\DB\Select::ORDER)->reset(\Magento\Framework\DB\Select::LIMIT_COUNT)->reset(\Magento\Framework\DB\Select::LIMIT_OFFSET)->reset(\Magento\Framework\DB\Select::GROUP);

    //with $collection = $this->collectionFactory->create();

    //and at the end
    $this->collection = $collection;
}
return $this->collection;