Most efficient way to generate Products CSV

Question

I am trying to generate a product CSV to upload into a third party CRM

The below code needs to grab the SKU, QTY, PRICE etc for each store in Magento. The problem is when it comes to large collections the script gets stuck, when I have it on a schedule to run every 5 or so minutes.

Can anyone advise on what I can do to make this function more efficiently please?

public function getOffersToUpload()
{
    //Get the productIds
    $productIds = $this->getStoreProducts(array(), true);

    //Check if we should include price column in store feed
    $storePrice = Mage::getStoreConfigFlag('marketplace/offer_settings/upload_offer_price');


    //Create the product.csv file
    $csvFilename = 'product.csv';
    $exportDir = Mage::getBaseDir() . '/var/products/' . $csvFilename;

    if (count($productIds) > 0) {

        $products = Mage::getModel('catalog/product');

        $stores = Mage::app()->getStores();

        foreach ($stores as $key => $store) {

            $storeId = $store->getId();

            if (Mage::getStoreConfigFlag('modulename/configuration/active', $storeId)) {

                //Unegister the shopId for each loop
                Mage::unregister('shop_id');

                //Create the array
                $products_row = array();
                $header = array('sku', 'product-id', 'product-id-type', 'price', 'discount-price', 'discount-from-date', 'discount-to-date', 'quantity', 'state');

                $products_row[] = $header; // CSV header

                $csv = new Varien_File_Csv();
                foreach ($productIds as $productId) {
                    $prod = $products->setStoreId($storeId)->load($productId);
                    $data['sku'] = $prod->getSku();
                    $data['product-id'] = $prod->getSku();
                    $data['product-id-type'] = 'SHOP_SKU';
                    $data['price'] = $offerPrice ? round($prod->getPrice()) : "";
                    $data['discount-price'] = $prod->getSpecialPrice();
                    $data['discount-from-date'] = $prod->getSpecialFromDate();
                    $data['discount-to-date'] = $prod->getSpecialToDate();
                    $data['quantity'] = round($prod->getStockItem()->getQty());
                    $data['state'] = '11';

                    $products_row[] = $data;

                }
                //write to csv file
                $csv->saveData($exportDir, $products_row);
                Mage::app()->setCurrentStore($store);
                Mage::register('shop_id', $storeId);
                $callApi = Mage::helper('modulename/callapi');
                $callApi->uploadData();

                //Unset the array() after first loop
                unset($products_row);

            }

        }
    } else {
        Mage::log('no product selection specified', null);
    }
}`

Answer 1

Possible optimization :

• Load bulk of data (per 1000 for example)
• Do not load product on each foreach, use collection and select only usefull attributes
• Maybe use differential export whereas full export. Export only updated products. The CRM need to be compatible with this approach.
• If you have some memory problems, you may try to use this function on each foreach : gc_collect_cycles before call gc_enable outside of your foreach.
• Separate file generation and file upload in two crons if your upload is not fast
• Separate cron task for each store

I will edit my answer if you need some precisions to be more accurate.