Need help speeding up my code.

hyeteck · March 16, 2008

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 · March 16, 2008

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 · March 16, 2008

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 · March 16, 2008

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 · March 16, 2008

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 · March 16, 2008

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 · March 16, 2008

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 · March 16, 2008

<?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 · March 16, 2008

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

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

hyeteck · March 16, 2008

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 · March 16, 2008

btw, the following line has a slight issue.

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

it should be

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

Sign In

Need help speeding up my code.

Recommended Posts

hyeteck

Link to comment

Share on other sites

BlueSkyIS

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

hyeteck

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

sasa

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

hyeteck

Link to comment

Share on other sites

hyeteck

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information