Need help speeding up my code.

[hyeteck]

Hi,  I'm trying to speed up my code but can't think of any other way to do so.  Let me explain what the function below is doing. 

 

I have a tab delimited text file with 17,000 lines of data.  There is a product id, price, and quantity on each line.  I am reading in this data and updating the price and quantity to the corresponding product Id in my mysql database.  I need to speed up the process as much as possible.  Any ideas on what else i can do to the code below to increase performance?

 

function update_prices()
{
$handle = @fopen("items.txt", "r");
if($handle && count(file("items.txt"))>1000)
{
	$buffer = fgets($handle, 4096); 
	while (!feof($handle))
	{
		$buffer = fgets($handle, 4096);
		$chunks = explode("	", $buffer);
   	   
		$prodId = $chunks[0];

		$rs_getfreight = mysql_query("SELECT pFreight FROM products WHERE pID = '$prodId'");
		if(mysql_num_rows($rs_getfreight)==1)
		{
			$prodPrice = $chunks[1];
			$prodPriceW = $prodPrice;
			$prodMap = $chunks[2];
			$prodQuant = $chunks[3];

			if($prodPrice==0)
			{
				$result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error());
			}
			else
			{
				if($prodPrice < 10) {
					$prodPrice *= 1.8;
				} elseif($prodPrice >= 10 && $prodPrice < 25) {
					$prodPrice *= 1.7;
				} elseif($prodPrice >= 25 && $prodPrice < 50) {
					$prodPrice *= 1.6;
				} elseif($prodPrice >= 50 && $prodPrice < 100) {
					$prodPrice *= 1.5;
				} elseif($prodPrice >= 100 && $prodPrice < 200) {
					$prodPrice *= 1.3;
				} elseif($prodPrice >= 200 && $prodPrice < 300) {
					$prodPrice *= 1.09;
				} elseif($prodPrice >= 300 && $prodPrice < 400) {
					$prodPrice *= 1.08;
				} elseif($prodPrice >= 400 && $prodPrice < 500) {
					$prodPrice *= 1.13;
				} elseif($prodPrice >= 500 && $prodPrice < 600) {
					$prodPrice *= 1.12;
				} elseif($prodPrice >= 600 && $prodPrice < 700) {
					$prodPrice *= 1.11;
				} elseif($prodPrice >= 700 && $prodPrice < 800) {
					$prodPrice *= 1.10;
				} elseif($prodPrice >= 800 && $prodPrice < 900) {
					$prodPrice *= 1.09;
				} elseif($prodPrice >= 900 && $prodPrice < 1000) {
					$prodPrice *= 1.08; 
				} else {
					$prodPrice *= 1.07;
				}
				$prodPrice = floor($prodPrice);
				$prodPrice += 0.99;

				$row_freight = mysql_fetch_assoc($rs_getfreight);
				if($row_freight['pFreight']==1)
					$prodPrice += 90;

				print $prodId . "\n";	
				$result = mysql_query("UPDATE products SET pPrice='$prodPrice', pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'") or die(mysql_error());

			}		
		}
	}
	fclose($handle);
}
}

 

thanks

[BlueSkyIS]

don't know if it will help, but i would load the entire file into an array and then close the file, then loop over the array elements with the mysql queries.

[Barand]

I'm with BlueSkyIS

 

your code

 

file("items.txt")

 

is reading the file into an array already, so you are reading the file twice

[hyeteck]

the while loop is what is taking 99% of the time.  The IF statement that reads the file to check the number of lines is actually really quick.

[Barand]

The point is this:

 

If you are already reading it (quickly) into an array then loop through that array and forget about the fopen (), fgets (), etc. As I said, you are reading the file TWICE, once with file() and again with each fgets(). 

[PFMaBiSmAd]

This appears to be an administrative function. How long is it taking? If you are doing this once per day, functionally, it does not matter how long it is taking.

 

About the only thing I can see to change is your SELECT query (in a loop) is wasted time. You check if the mysql_num_rows() == 1 (meaning it found an id), then later you go to the trouble of fetching a row and checking if pFreight == 1 and adjusting the price.

 

You can simplify this to just looping through the id's from the file without the SELECT query. The WHERE clause in the UPDATE statements will prevent any data from being changed if there is not a matching id and you can put a mysql IF() statement in to test the value of pFreight and set the pPrice column to either '$prodPrice' or '$prodPrice + 90' directly in the UPDATE query.

 

 

[PFMaBiSmAd]

This should work (untested, but I am pretty sure the mysql IF() syntax is correct) -

 

<?php
function update_prices()
{
$file = "items.txt";
$contents = file($file);
if(count($contents)>1000)
{
	foreach($contents as $buffer)
	{
		$chunks = explode("	", $buffer);
		$prodId = $chunks[0];
		$prodPrice = $chunks[1];
		$prodPriceW = $prodPrice;
		$prodMap = $chunks[2];
		$prodQuant = $chunks[3];

		if($prodPrice==0)
		{
			$result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error());
		}
		else
		{
				if($prodPrice < 10) {
					$prodPrice *= 1.8;
				} elseif($prodPrice >= 10 && $prodPrice < 25) {
					$prodPrice *= 1.7;
				} elseif($prodPrice >= 25 && $prodPrice < 50) {
					$prodPrice *= 1.6;
				} elseif($prodPrice >= 50 && $prodPrice < 100) {
					$prodPrice *= 1.5;
				} elseif($prodPrice >= 100 && $prodPrice < 200) {
					$prodPrice *= 1.3;
				} elseif($prodPrice >= 200 && $prodPrice < 300) {
					$prodPrice *= 1.09;
				} elseif($prodPrice >= 300 && $prodPrice < 400) {
					$prodPrice *= 1.08;
				} elseif($prodPrice >= 400 && $prodPrice < 500) {
					$prodPrice *= 1.13;
				} elseif($prodPrice >= 500 && $prodPrice < 600) {
					$prodPrice *= 1.12;
				} elseif($prodPrice >= 600 && $prodPrice < 700) {
					$prodPrice *= 1.11;
				} elseif($prodPrice >= 700 && $prodPrice < 800) {
					$prodPrice *= 1.10;
				} elseif($prodPrice >= 800 && $prodPrice < 900) {
					$prodPrice *= 1.09;
				} elseif($prodPrice >= 900 && $prodPrice < 1000) {
					$prodPrice *= 1.08; 
				} else {
					$prodPrice *= 1.07;
				}

			$prodPrice = floor($prodPrice);
			$prodPrice += 0.99;

			if($row_freight['pFreight']==1)
				$prodPrice += 90;

			print $prodId . "\n";
			$query = "UPDATE products SET pPrice= IF(pFreight = 1,'$prodPrice + 90','$prodPrice'), pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'";
			$result = mysql_query($query) or die(mysql_error());
		} // end of prodPrice == 0 else
	} // end of foreach loop
} // end of count > 1000
} // end of function
?>

[sasa]

<?php
function update_prices()
{
$pri = array(0,  10, 25, 50, 100,200, 300, 400, 500, 600, 700,800, 900, 1000);
$f   = array(1.8,1.7,1.6,1.5,1.3,1.09,1.08,1.13,1.12,1.11,1.1,1.09,1.08,1.07);

$file = "items.txt";
$contents = file($file);
if(count($contents)>1000)
{
	foreach($contents as $buffer)
	{
		$chunks = explode("	", $buffer);
		$prodId = $chunks[0];
		$prodPrice = $chunks[1];
		$prodPriceW = $prodPrice;
		$prodMap = $chunks[2];
		$prodQuant = $chunks[3];

		if($prodPrice==0)
		{
			$result = mysql_query("UPDATE products SET pSell=0, pDisplay=0 WHERE pID='$prodId'") or die(mysql_error());
		}
		else
		{
/*					if($prodPrice < 10) {
					$prodPrice *= 1.8;
				} elseif($prodPrice >= 10 && $prodPrice < 25) {
					$prodPrice *= 1.7;
				} elseif($prodPrice >= 25 && $prodPrice < 50) {
					$prodPrice *= 1.6;
				} elseif($prodPrice >= 50 && $prodPrice < 100) {
					$prodPrice *= 1.5;
				} elseif($prodPrice >= 100 && $prodPrice < 200) {
					$prodPrice *= 1.3;
				} elseif($prodPrice >= 200 && $prodPrice < 300) {
					$prodPrice *= 1.09;
				} elseif($prodPrice >= 300 && $prodPrice < 400) {
					$prodPrice *= 1.08;
				} elseif($prodPrice >= 400 && $prodPrice < 500) {
					$prodPrice *= 1.13;
				} elseif($prodPrice >= 500 && $prodPrice < 600) {
					$prodPrice *= 1.12;
				} elseif($prodPrice >= 600 && $prodPrice < 700) {
					$prodPrice *= 1.11;
				} elseif($prodPrice >= 700 && $prodPrice < 800) {
					$prodPrice *= 1.10;
				} elseif($prodPrice >= 800 && $prodPrice < 900) {
					$prodPrice *= 1.09;
				} elseif($prodPrice >= 900 && $prodPrice < 1000) {
					$prodPrice *= 1.08; 
				} else {
					$prodPrice *= 1.07;
				}
*/					
			$c=14;
			while ($pri[--$c] > $a);
			$prodPrice *= $f[$c];
			$prodPrice = floor($prodPrice);
			$prodPrice += 0.99;

			if($row_freight['pFreight']==1)
				$prodPrice += 90;

			print $prodId . "\n";
			$query = "UPDATE products SET pPrice= IF(pFreight = 1,'$prodPrice + 90','$prodPrice'), pInStock='$prodQuant', pMapprice='$prodMap', pWholesalePrice='$prodPriceW' WHERE pID='$prodId'";
			$result = mysql_query($query) or die(mysql_error());
		} // end of prodPrice == 0 else
	} // end of foreach loop
} // end of count > 1000
} // end of function
?>

[PFMaBiSmAd]

That reminds me, the following two lines of code should be removed -

 

if($row_freight['pFreight']==1)
$prodPrice += 90;

[hyeteck]

thanks guys, both of those code snippets really increased the speed.  Removing the extra mysql statement really made a very big difference.  It runs in 5 seconds on a VPS host but on a shared host it takes 20 minutes.  I guess thats because the MySQL server is on the localhost for the VPS but on a shared host, the mysql server is on a different machine.

 

Is there anyway to consolidate all the queries (about 17,000 of them) into one big query?  I think this would improve performance on the shared host account because it would only need to send 1 query to the mysql machine instead of sending 17,000 queries.

[hyeteck]

btw,  the following line has a slight issue.

 

while ($pri[--$c] > $a);

 

it should be

 

while ($pri[--$c] > $prodPrice);