Major lag issue

We’re having major lag issue while updating production status and updating production quantity. We’re using JQUERY AJAX post method to do that work. Things work smartly and with good speed, when we comment-out the function “recalculateProduction()” inside the “AppController.php” file for both updating status and updating quantity.

The function “recalculateProduction()” consist of huge calculation, updating records of different DB tables and deleting records from different tables.

public function recalculateProduction($production_id, $action_performed_by){
$this->loadModel(‘Production’);
$this->loadModel(‘ProductionItem’);
$this->loadModel(‘ProductionOrder’);
$this->loadModel(‘ProductionRecipe’);
$this->loadModel(‘ProductionIngredient’);
$this->loadModel(‘ProductionRawMaterial’);
$this->loadModel(‘ProductionOrderProductionRecipe’);
$this->loadModel(‘ProductionOrderProductionIngredient’);
$this->loadModel(‘ProductionOrderProductionRawMaterial’);

    $production = $this->Production->read(null, $production_id);
    
    $this->ProductionItem->updateAll(array('order_case_qty'=>0, 'quantity_lbs'=>0, 'production_case_qty'=>0), array('ProductionItem.production_id'=>$production_id));
    $this->ProductionRecipe->updateAll(array('qty_lbs'=>0, 'batch_qty_lbs'=>0, 'batches'=>0, 'batch_size'=>0, 'actual_lbs'=>0, 'percent_yield'=>0), array('ProductionRecipe.production_id'=>$production_id));
    $this->ProductionIngredient->updateAll(array('qty_lbs'=>0), array('ProductionIngredient.production_id'=>$production_id));
    $this->ProductionRawMaterial->updateAll(array('qty_lbs'=>0), array('ProductionRawMaterial.production_id'=>$production_id));
    
    $this->ProductionOrderProductionRecipe->deleteAll(array('ProductionOrderProductionRecipe.production_order_id IN (SELECT id FROM imrp_production_order ProductionOrder WHERE ProductionOrder.production_id='.$production_id.')'));
    $this->ProductionOrderProductionIngredient->deleteAll(array('ProductionOrderProductionIngredient.production_order_id IN (SELECT id FROM imrp_production_order ProductionOrder WHERE ProductionOrder.production_id='.$production_id.')'));
    $this->ProductionOrderProductionRawMaterial->deleteAll(array('ProductionOrderProductionRawMaterial.production_order_id IN (SELECT id FROM imrp_production_order ProductionOrder WHERE ProductionOrder.production_id='.$production_id.')'));
    
    $this->ProductionOrder->recursive = 0;
    $production_orders = $this->ProductionOrder->find('all', array('conditions'=>array('ProductionOrder.status IN (0, 1)', 'ProductionOrder.production_id'=>$production_id)));
    if(count($production_orders)>0){
        foreach($production_orders as $production_order){
            $this->processOrderDetails($production_id, $production_order['ProductionOrder']['item_id'], $production_order['ProductionOrder']['id'], $production['Production']['production_date'], $production_order['ProductionOrder']['quantity_lbs'], $production_order['ProductionOrder']['quantity_cases'], $action_performed_by);
        }
    }
    
    $this->ProductionItem->deleteAll(array('ProductionItem.production_id'=>$production_id, 'ProductionItem.order_case_qty'=>0.00, 'ProductionItem.quantity_lbs'=>0.00, 'ProductionItem.production_case_qty'=>0.00));
    $this->ProductionRecipe->deleteAll(array('ProductionRecipe.production_id'=>$production_id, 'ProductionRecipe.qty_lbs'=>0.00, 'ProductionRecipe.batch_qty_lbs'=>0.00000, 'ProductionRecipe.batches'=>0.00000, 'ProductionRecipe.batch_size'=>0, 'ProductionRecipe.actual_lbs'=>0.00, 'ProductionRecipe.percent_yield'=>0.000));
    $this->ProductionIngredient->deleteAll(array('ProductionIngredient.production_id'=>$production_id, 'ProductionIngredient.qty_lbs'=>0.00));
    $this->ProductionRawMaterial->deleteAll(array('ProductionRawMaterial.production_id'=>$production_id, 'ProductionRawMaterial.qty_lbs'=>0.00));
    
    $this->processProductionRecipes($production_id, $action_performed_by);
}

Can you guys see any optimization in the above function code? We already applied “recursive” but it’s not making any difference. Your help will be highly appreciated.

what is your cakephp and php version?

PHP Version: 5.6.35
CakePHP Version: 2.5.7

How many records are you dealing with. Some large companies run these type things over night or weekends because they can take some time. If dealing with tens of thousands of records yes it’s not going to be fast.

Furthermore I am surprised you aren’t using stored procedures for massive updating.

We’re only dealing with a few hundred records. At most, once in production, we may be dealing with several thousand records at most.

To better understand the user experience, here is a brief explanation of what is happening: The application picks up orders of finished food products from a tab delimited file on the server and then figures out how much raw material is needed to make the product based on recipe data. The specific part of the system that is lagging is where the admin can manually adjust the quantity of the customer orders. So, for example, the admin may decide to reduce the order quantity of pasta salad from Acme from 50 lbs to 40 lbs. The application then recalculates all of the necessary raw material as a result of that quantity change. Currently, it is taking upwards of two minutes for everything to be recalculated when a single quantity is changed.

I can investigate using stored procedures, but I was hoping to be able to salvage the work I’ve already done. My thinking is that because the PHP framework queries all relationship data in a single query. To stop fetching relationship data we applied the “recursive = 0” but that has not fixed the issue.

Thanks.

What I think I’d do is add a debug line here or there, either writing directly to the screen or, preferably, writing to a log file. What I think is happening is that you are loading a lot of background code and then recursively generate a lot of runtime actions. If you find thousands of debug lines, then you’ll know. :slight_smile: