3

I'm building a module where i'm adding a few extra columns to the default magento reports under Reports > Sales > Orders & Reports > Sales > Tax

I'm adding the extra columns to the corresponding aggregation tables from the module's install script. Now after adding the columns, I have to truncate the table & then aggregate all the existing data all over again. I got it working on my local with some sample data & it worked as expected.

The problem is that I added set_time_limit(0) to index.php so that it doesn't timeout & even though it worked correctly it took like 10 minutes. It might get even worse when the amount of existing data grows.

So my question is if there is any better way to do it? Maybe putting some consecutive crons that run one after other & refresh all the data in batches?

Thanks

UPDATE

I decided it would be better to just ask users to run the refresh script from the terminal. The idea here is that a user would run it from ssh & keep it running for maybe a day & all the data gets refreshed in that time. So, working on that, I now have a file that I'm running from the command line. Below is the code (with time recording, Mage::log & echo statements stripped out)

require_once 'app/Mage.php';
Mage::setIsDeveloperMode(true);
ini_set('display_errors', 1);
umask(0);
Mage::app();

$from = strtotime('-1 day');
$to   = strtotime('now');
$day = $to - $from;
do {
    try {
        Mage::getResourceModel('sales/report_order')->aggregate($from, $to);
        echo 'success.';
        $to   = $from;
        $from = $from - $day;
    } catch(Mage_Core_Exception $e) {
        echo $e->getMessage();
    } catch(Exception $e) {
        echo 'Failed due to ' . $e->getMessage();
    }
    sleep(4);
} while($to > strtotime($argv[1]));

This also works but still, it's taking forever. By judging from the logs etc. it seems that it just randomly selects days on which it has to spend 5-10 minutes & there are others that are done within seconds. The code, again, is working. But whenever it chooses to take 5-10 minutes, then during that time the server is in a crisis. Another interesting thing is that none of the queries actually failed or threw any exception. It's always a success. Any thoughts?

2

You can do the below from a cron, though, if your module has an installer, inside of that installer you can call the aggregator as you wrap up. This example assumes that you do it at the end of your install or upgrade script.

<?php
/* @var $installer MyCompany_MyClass_Model_Entity_Setup */
$installer = $this;
$installer->startSetup();

//.... install stuff


try {

$aliases = array(
    'sales'       => 'sales/report_order',
    'tax'         => 'tax/report_tax',
    'shipping'    => 'sales/report_shipping',
    'invoiced'    => 'sales/report_invoiced',
    'refunded'    => 'sales/report_refunded',
    'coupons'     => 'salesrule/report_rule',
    'bestsellers' => 'sales/report_bestsellers',
);

foreach ($aliases as $key=>$val) {
        Mage::getResourceModel($val)->aggregate();
    }
    Mage::getSingleton('adminhtml/session')->addSuccess(Mage::helper('adminhtml')->__('Lifetime statistics have been updated.'));
} catch (Mage_Core_Exception $e) {
    Mage::getSingleton('adminhtml/session')->addError($e->getMessage());
} catch (Exception $e) {
    Mage::getSingleton('adminhtml/session')->addError(Mage::helper('adminhtml')->__('Unable to refresh lifetime statistics.'));
    Mage::logException($e);
}

$installer->endSetup();
4
  • +1 for i wasn't using the try-catch but the problem is that the call to aggregate() is the one taking forever for each report. If i move it to cron then that particular cron will take forever. I wanted some way to break the aggregate function in batches Feb 12 '13 at 5:08
  • If you move it to a cron it will run on a schedule. Do you want it to run more than once, or only once after your install script?
    – philwinkle
    Feb 12 '13 at 14:35
  • just once. BTW I checked it & this query took up 100% of the CPU of a quad core machine for like 6+ minutes even when i only had a few processes running & i used the refresh statistics from admin. So this thing will definitely kill a server. Feb 12 '13 at 15:30
  • This query is 'borrowed', in full, from the refreshLifetime controller in Magento. I would put some profiling on your Magento install with xhprof or cachegrind while running the default lifetime refresh - I'm confident it will be the same.
    – philwinkle
    Feb 12 '13 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.