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?