I've been handed the thankless task of cleaning up a custom PHP app built with the absence of any MVC or proper design patterns and am currently experiencing a massive performance hit on a view due to queries per row.
I'd like some general guideline on where to start optimizing and where my time would most benefit the loading issues.
Here is how it's working as handed to me. For example, each row is operating these functions:
$purchased = report::purchased_products_total($customer['No'],$from, $to);
$returned = report::returned_products_total($customer['No'],$from, $to);
$cust_inv = $purchased-$returned;
$on_hand_total = report::get_onhand_qty_total($customer['No'],$from, $to);
$log_sale_qty = report::get_logged_sale_qty($customer['No'],$from, $to);
$expected = $cust_inv-$log_sale_qty;
$salesman = report::get_salesman_by_code($customer['Salesperson_Code']);
$salesman_name = str_replace("'","",$salesman->Name);
$phone = str_replace("/","-",$customer['Phone_No']);
$name = str_replace("'","",$customer['Name']);
Example of his sql, here is the purchased_products_total function:
public static function purchased_products_total($no,$from=NULL,$to=NULL,$item=FALSE){
global $db;
$data= $db->QuerySingleValue("SELECT sum(a.Quantity) FROM ".INVOICE_LINES." as a
WHERE a.Type='Item'
AND No NOT LIKE 'ZB%' AND No NOT LIKE 'ZE%' AND No NOT LIKE 'ZN%'
AND No NOT LIKE 'ZP%'AND No NOT LIKE 'ZR%'AND No NOT LIKE 'ZZ%'
AND a.Sell_To_customer_No='$no'
AND a.Stock_No!='NULL'
AND Document_Posting_Date>='$from'
AND Document_Posting_Date<='$to'
"
);
return $data;
}
The Invoice Lines table is MyISAM.
I can post more of the functions if needed. I ran a performance test and mysql_query is being run about 3,500 times and is taking almost 3 min to operate.