extreme looping?

dreams4000

Hey all, I am trying to do a couple of loops... the code I have does what it needs, but it is taking an extremely long time to go through about 5000+ rows of data and doesn't always make it as it times out at around 5 min. Just wondering if there is any way to improve upon this.

I basically have a loop that finds a list of names/ids in one table.. then what I am trying to do is match up data with those found ids and do some sums and avgs and other math operations with the numbers. The problem is that I have to have them between date ranges.. eg finding the sum of some number between Jan 03 and Jan 04 for example. Thats just one, but gives the idea.

So to do this, I am trying to loop through a created timestamp to match up with timestamps that the data already has. This takes a while since I loop through the first list of names, then inside that is a loop that goes through each year from 2003 up to 2007, then inside that is a loop that goes through each month in those years.. so I end up running a lot of checks and sql inserts/updates. Here is the little bit of code from the timestamp loop on down.

//Loop for names is up here

//Takes the years up to 2007
	for($j = 3; $j < 8; $j++) {
	      $year =  "200".$j;
                          //Builds the month for each year
		for($k = 1; $k < 13; $k++) {

		if ($k < 10)
		$k = "0".$k;

$my_timestamp= $year.$k."01000000";

//Bundle of sql calls that compare against that timestamp to see if the data fits the criteria.


}
}

is there a way to be able to compare specific dates like this, without having to loop through each specific date or speed this up somehow?🙂

Erestar

It sounds to me like you're doing a lot of things in PHP that you could do in mysql. I'm at work now and can't fully address your problem, but I'd suggest looking through the mysql manual and seeing if you can get a way to do more of the processing on the database side, or post some more code (or step-by-step psudeo-code) about what you're trying to do.

dreams4000

The code is actually pretty basic and doesn't visually do a lot, just a lot of processing as you say, but all of it is in sql calls as is.. I have five different calls doing different things with various parts of data but all of them are similar to the code below, some are sums, avgs etc.. there is also a check at the end to look to see if an entry that matches one of the dates exists, if it does, it does an update, if it doesnt, it does an insert. :

$tmpquery1 = "select SUM(some_num1), SUM(some_num2), SUM(some_num3), SUM(some_num4) from my_table WHERE YEAR(date1) = YEAR($my_timestamp) AND MONTH(date1) = MONTH($my_timestamp) AND id = $some_id";
my_sum($tmpquery1);
							$total_firstnum= $row[0]; 
					$total_secondnum = $row[1];
					$total_thirdnum= $row[2];
					$total_fourthnum= $row[3];

I then take those values and use them for the inserts/updates. The other sql calls I have do similar things but are different enough that they can't be combined into this one. Problem time wise is that these calls run for each month in each year for each name for some 5000+ names.. which tends to take a while🙂 If there is a better approach, would love to know how. Thanks for the advice!🙂

Weedpacket

You could consider using a GROUP BY clause. Certainly for the user id, and probably on the date as well.