Why does my custom module take so lnog to process?

[paulcarthy]

If have written the following piece of code, basically it takes all orders from a customers database (pretty big ecommerce company) and loads up a .csv file with all orders that are pending or processing, reading for use on a packing bench with a scanner and seperate verification application.

 

The code takes 5 minutes to run and load the page. I'm guessing the longest amount of time is around the pull back of the prodcuts order info for each order.

 

Any suggestions on making this slicker?

 

Thanks

 

	// We find all the orders that are pending or processing.
$pointer= 0;
$orders_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where orders_status = 1 OR orders_status = 2");
while ($orders_id = tep_db_fetch_array($orders_query)){
	$orders_ids_array[$pointer]=$orders_id['orders_id'];
	$pointer++;
}

$orders_products_array = array();
$datafilename = BARCODE_FILE_FOLDER . BARCODE_FILE_NAME;
if(file_exists($datafilename)) unlink($datafilename);
$datafilehandle = fopen($datafilename, 'w') or die("can't open file");

// Now we pull back all the details of these orders, store them to an array and write them to the file.
$pointer= 0;
for ($idx = 0, $count = count($orders_ids_array); $idx < $count; $idx++){
	$orders_products = tep_db_query("select orders_id, products_name, products_model, products_quantity from " . TABLE_ORDERS_PRODUCTS . " 	where orders_id = '" . (int)$orders_ids_array[$idx] . "'");
	while($orders_prod = tep_db_fetch_array($orders_products)){

		if(strlen($orders_prod['products_model']) == intval(BARCODE_FLAG_LENGTH)) {
			$barcode= 1;
		} else{
			$barcode= 0;
		}

		$orders_products_array[$pointer] = array('orders_id' => $orders_prod['orders_id'],
											'products_name' => $orders_prod['products_name'],
											'products_model' => $orders_prod['products_model'],
											'products_quantity' => $orders_prod['products_quantity'],
											'barcode' => $barcode);

		fwrite($datafilehandle, $orders_products_array[$pointer]['orders_id'] . "," . $orders_products_array[$pointer]['products_name'] . "," . $orders_products_array[$pointer]['products_model'] . "," . $orders_products_array[$pointer]['products_quantity'] . "," . strval($orders_products_array[$pointer]['barcode']) . "\n");

		$pointer++;
	}

[kgt]

It's easier to speed it up if you pinpoint where the biggest slowdowns are.

 

This would be slow if you have a big orders table:

$orders_query = tep_db_query("select orders_id from " . TABLE_ORDERS . " where orders_status = 1 OR orders_status = 2");

 

This would be slow for a big orders table and/or a big orders_products table:

for ($idx = 0, $count = count($orders_ids_array); $idx < $count; $idx++){
	$orders_products = tep_db_query("select orders_id, products_name, products_model, products_quantity from " . TABLE_ORDERS_PRODUCTS . "	 where orders_id = '" . (int)$orders_ids_array[$idx] . "'");

Adding an index on orders_products.orders_id would speed it up drastically.

 

The database is most likely the holdup.