Magento2.3.5: How to optimize product export scripts?

Question

I Have a product export script that uses product collection to create an array and write the array to CSV, I want to know if this script can be optimized.

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

ini_set('memory_limit', '16G');

use Magento\Framework\App\Bootstrap;

include __DIR__ . '/../app/bootstrap.php';

$params = $_SERVER;

$bootstrap = Bootstrap::create(BP, $params);
$obj = $bootstrap->getObjectManager();
$obj->get('Magento\Framework\Registry')->register('isSecureArea', true);
$appState = $obj->get('\Magento\Framework\App\State');
$appState->setAreaCode('frontend');


$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$productCollectionFactory = $objectManager->get('\Magento\Catalog\Model\ResourceModel\Product\CollectionFactory');
$StockState = $objectManager->get('\Magento\CatalogInventory\Api\StockStateInterface');

$dirurl = '/var/www/html/magento245/pub/ProductExports/';
$chunk = 1;
$urlcsvfilename = 'productexport_' . $chunk . '.csv';
echo strftime('%c');
echo "\n";
if (file_exists($dirurl . $urlcsvfilename)) {
    unlink($dirurl . $urlcsvfilename);
    echo "\n unlink done \n";
}

$batchSize = 50000;
$file = $dirurl . $urlcsvfilename;

echo $file, file_exists($file) ? " exists" : " does not exist", "\n";
echo $file, is_readable($file) ? ' is readable' : ' is NOT readable', "\n";
echo $file, is_writable($file) ? ' is writable' : ' is NOT writable', "\n";

$fp = fopen($dirurl . $urlcsvfilename, "w");
$array[] = ["name", "description", "short_description", "url_key", "price", "sku", "qty", "product_type", "weight", "visibility", "meta_title", "categories", "stockstatus", "base_image", "base_image_label", "additional_attributes_manufacturer", "additional_attributes_condition", "additional_attributes_google_product_category"];

$collection = $productCollectionFactory->create();
$collection->addAttributeToSelect('*');
$collection->getSelect()->reset(Zend_Db_Select::ORDER);
$collection->setOrder('entity_id', 'ASC');

$recordCount = 0; // Initialize a counter for the records
$counter = 0;

foreach ($collection as $product) {
        $categoryname = [];
        foreach ($product->getCategoryIds() as $categoryId) {
            $parentCategories = $objectManager->get('\Magento\Catalog\Model\CategoryFactory')->create()->load($categoryId)->getParentCategories();
            foreach ($parentCategories as $cat) {
                $categoryname[] = $cat->getName();
            }
        }
        $product_category = implode(" / ", $categoryname);

        if ($StockState->getStockQty($product->getId(), $product->getStore()->getWebsiteId()) == 0) {
            $stockstatus = 'Out of Stock';
        } else {
            $stockstatus = 'In Stock';
        }

        $array[] = [
            $product->getName(),
            $product->getDescription(),
            $product->getShortDescription(),
            $product->getProductUrl(),
            $product->getFinalPrice(),
            $product->getSku(),
            $StockState->getStockQty($product->getId(), $product->getStore()->getWebsiteId()),
            $product->getTypeId(),
            $product->getData('weight'),
            $product->getAttributeText('visibility')->getText(),
            $product->getMetaTitle(),
            $product_category,
            $stockstatus,
            $product->getThumbnail(),
            $product->getThumbnailLabel(),

            $product->getResource()->getAttribute('manufacturer')->getFrontend()->getValue($product),
            $product->getResource()->getAttribute('condition')->getFrontend()->getValue($product),
            $product->getResource()->getAttribute('google_product_category')->getFrontend()->getValue($product),
        ];

        $recordCount++;

        if ($recordCount >= $batchSize) {
            // Export the current batch to the CSV file
            foreach ($array as $d) {
                fputcsv($fp, $d);
            }
            echo "\n Batch $chunk done\n";

            // Reset the array and counter for the next batch
            $array = [];
            $recordCount = 0;
            $chunk++;

            // Close the current file and open a new one
            fclose($fp);
            $urlcsvfilename = "productexport_" . $chunk . '.csv';
            $fp = fopen($dirurl . $urlcsvfilename, "w");
            $array[] = ["name", "description", "short_description", "url_key", "price", "sku", "qty", "product_type", "weight", "visibility", "meta_title", "categories", "stockstatus", "base_image", "base_image_label", "additional_attributes_manufacturer", "additional_attributes_condition", "additional_attributes_google_product_category"];
        }
        $counter++;
}

if (!empty($array)) {
    // Export any remaining products
    foreach ($array as $d) {
        fputcsv($fp, $d);
    }
    echo "\n Final batch done\n";
}

fclose($fp);
echo "\nTotal Products Exported: " . $counter . "\n";
echo strftime('%c');
echo "\n";

Answer 1

I recommend to use standard performance improvement loop:

• Testing (profiling)
• Find bottleneck
• Fix it

and repeat

https://github.com/NoiseByNorthwest/php-spx is good for quick start

For example

app:/magento/pub# SPX_ENABLED=1  SPX_SAMPLING_PERIOD=50 php aa.php

../pub/ProductExports/productexport_1.csv does not exist
../pub/ProductExports/productexport_1.csv is NOT readable
../pub/ProductExports/productexport_1.csv is NOT writable

 Batch 1 done

 Batch 2 done

 Final batch done

Total Products Exported: 1200


*** SPX Report ***

Global stats:

  Called functions    :   522.0K
  Distinct functions  :     2.6K

  Wall time           :    7.91s
  ZE memory usage     :  120.9MB

Flat profile:

 Wall time           | ZE memory usage     |
 Inc.     | *Exc.    | Inc.     | Exc.     | Called   | Function
----------+----------+----------+----------+----------+----------
    1.24s |    1.23s | -264.9MB | -264.7MB |    11.7K | Magento\Framework\DB\Statement\Pdo\Mysql::tryExecute
  642.0ms |  247.1ms |    7.6MB |    1.7MB |     5.5K | 1@Magento\Framework\DB\Platform\Quote::quoteIdentifierAs
  237.0ms |  219.7ms |   13.3MB |   12.4MB |     1.6K | Magento\Framework\DB\Select::_tableCols
  207.8ms |  158.2ms |    2.6MB |    2.1MB |     2.6K | Magento\Framework\DB\Platform\Quote::replaceQuoteSymbol
  111.2ms |  111.2ms |   -1.4MB |   -1.4MB |     1.3K | Zend_Db_Select::getPart
   91.5ms |   75.3ms |    6.7MB |    5.7MB |      650 | Magento\Catalog\Model\ResourceModel\AbstractResource::_isCallableAttributeInstance
   49.6ms |   49.6ms |  492.5KB |  492.5KB |      389 | Magento\Framework\DB\Platform\Quote::getQuoteIdentifierSymbol
   36.2ms |   36.2ms |    2.6MB |    2.6MB |      278 | Magento\Eav\Model\Entity\Attribute\Backend\AbstractBackend::afterLoad
  294.3ms |   32.5ms |   -7.9MB |   -2.4MB |     1.8K | Zend_Db_Statement::__construct
   32.1ms |   32.1ms |  869.9KB |  869.9KB |      245 | Magento\Staging\Model\Event\Manager::isAllowedEvent

So, looks like most time is spent in queries to database

Let's dig deeper run

app:/magento/pub# SPX_ENABLED=1 SPX_REPORT=full   SPX_SAMPLING_PERIOD=50 php aa.php

and open UI mage.perf/?SPX_UI_URI=/report.html&key=spx-full-20231120_210341-app-312-618969061

So, based on flame graph almost all time is in StockState::getStockQty method, due to fact it load product from Repository one by one

Answer 2

Start by not using object manager but dependency injectrion.

Then in term of performance i would say

The closest you are from the system the fastest it is.

That means accessing threw repositories will be slower than collection which will be slower than raw sql.

So if your only requirements is to go faster...sql might be a good thing (even though it's bad practice).

You can also consider adding index to your tables to speed up the database returning results.

I would also suggest not to write the csv at each loop but to store everything then just loop on a global array printing the csv, this will avoid to open one million times the same files and getting issues with lock.

Answer 3

Your script looks quite comprehensive, and it's good that you are considering optimization. Here are some suggestions to optimize and improve your script:

1. Use Dependency Injection: Instead of using the ObjectManager directly, it's better to use dependency injection. Inject the necessary classes into the constructor of your class.

// Inject necessary classes
public function __construct(
    \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory,
    \Magento\CatalogInventory\Api\StockStateInterface $stockState,
    // Add any other dependencies
) {
    $this->productCollectionFactory = $productCollectionFactory;
    $this->stockState = $stockState;
    // Assign other dependencies
}

This way, you won't be tightly coupled to the ObjectManager, making your code more flexible and testable.

2. Avoid Direct SQL Query Manipulation: Avoid using $collection->getSelect()->reset(Zend_Db_Select::ORDER);. Instead, use the appropriate methods provided by the collection class.

$collection->clear();

$collection->setOrder('entity_id', 'ASC');

3. Use CSV Writer Library: Instead of manually handling CSV writing, consider using a CSV writer library, such as League\Csv. It provides a clean API for working with CSV files and can simplify your code.

4. Batch Processing: Instead of loading the entire product collection at once, consider using batch processing with $collection->setPageSize($batchSize). This can help reduce memory usage.

5. Avoid Direct ObjectManager Usage: Avoid using the ObjectManager directly; use dependency injection instead. For example, replace $objectManager->get('\Magento\Catalog\Model\CategoryFactory') with dependency injection.

6. Minimize Database Queries: Minimize the number of database queries inside the loop. For example, instead of calling $StockState->getStockQty multiple times, store the value in a variable.

7. Handle Errors Gracefully: Implement error handling to gracefully handle exceptions that might occur during the export process.

8. Consider Asynchronous Processing: If the export process takes a long time, consider implementing asynchronous processing using tools like queues or cron jobs to avoid script timeouts.

Remember to thoroughly test any changes to ensure they do not introduce new issues. These suggestions are meant to enhance the overall structure and performance of your script.

Answer 4

You are using $collection->addAttributeToSelect(*) it getting all attributes. Try to define array with attribute names only that you need. Collection won't be so heavy.