Very Very Slow Code

[feelstiff]

I'm finally getting the results I need but it's taking more than a minute for the page to display them. I know there's a better way. How can I speed this up? thanks in advance.

$PTYID = 2201771;
$PRYSID = 2;
$PAASID = 1;

$y = 1;

$query = "SELECT DISTINCT StatusYYMM
FROM pdstg
WHERE PTYID = '$PTYID'
AND PRYSID = '$PRYSID'
GROUP BY StatusYYMM
ORDER BY StatusYYMM DESC";
$result = mysql_query($query, $link) or die(mysql_error());
$num_rows = mysql_num_rows($result);
while ($row = mysql_fetch_assoc($result))
{
$StatusYYMM = $row['StatusYYMM'];
$myStatusYYMM[] = $row['StatusYYMM'];

	echo "$StatusYYMM<br>";

}
echo "<p>";
$query1 = "SELECT DISTINCT TGTDESC, TGTCOD
FROM pdstg
WHERE PTYID = '$PTYID'
AND PRYSID = '$PRYSID'
GROUP BY TGTDESC
ORDER BY TGTDESC";
$result1 = mysql_query($query1, $link) or die(mysql_error());
$num_rows1 = mysql_num_rows($result1);
while ($row = mysql_fetch_assoc($result1))
{

	$myTGTDESC = $row['TGTDESC'];	
	$TGTCOD = $row['TGTCOD'];	

echo "<p>$myTGTDESC";

for($i = 0; $i < $num_rows; $i++) {
$query2 = "SELECT Sum(TGTNUM) AS pestCount
FROM pdstg
WHERE PTYID = '$PTYID'
AND PRYSID = '$PRYSID'
AND TGTCOD = '$TGTCOD'
AND StatusYYMM = '$myStatusYYMM[$i]'
GROUP BY TGTCOD";
$result2 = mysql_query($query2, $link) or die(mysql_error());
$num_rows2 = mysql_num_rows($result2);
while ($row = mysql_fetch_assoc($result2))
{

	$pestCount = $row['pestCount'];	

echo "<br>$pestCount";
}
}

$y++;
}

[crazychris]

Is it the script that is running slow, or the query? Time it and find the slow point.

How many results do the queries return?

[feelstiff]

Here's my revised code.

$PTYID = 2201771;
$PRYSID = 2;
$PAASID = 1;


$y = 1;
$x = 0;

$query = "SELECT DISTINCT StatusYYMM
			FROM pdstg
			WHERE PTYID = '$PTYID'
			AND PRYSID = '$PRYSID'
			GROUP BY StatusYYMM
			ORDER BY StatusYYMM DESC";
		$result = mysql_query($query, $link) or die(mysql_error());
		$num_rows = mysql_num_rows($result);
		while ($row = mysql_fetch_assoc($result))  	
		{
		$StatusYYMM = $row['StatusYYMM'];
		$myStatusYYMM[] = $row['StatusYYMM'];

	echo "$chart [ 'chart_data' ][ 0 ][ $x ] = $StatusYYMM<br>";
$x++;		
}
echo "<p>";
$query1 = "SELECT DISTINCT TGTDESC, TGTCOD
			FROM pdstg
			WHERE PTYID = '$PTYID'
			AND PRYSID = '$PRYSID'
			GROUP BY TGTDESC
			ORDER BY TGTDESC";
		$result1 = mysql_query($query1, $link) or die(mysql_error());
		$num_rows1 = mysql_num_rows($result1);
		while ($row = mysql_fetch_assoc($result1))  	
		{		

	$myTGTDESC = $row['TGTDESC'];	
	$TGTCOD = $row['TGTCOD'];	

echo "<p>$chart [ 'chart_data' ][ $y ][ 0 ] = $myTGTDESC";


for($i = 0; $i < $num_rows; $i++) { 
$query2 = "SELECT Sum(TGTNUM) AS pestCount
			FROM pdstg
			WHERE PTYID = '$PTYID'
			AND PRYSID = '$PRYSID'
			AND TGTCOD = '$TGTCOD'
			AND StatusYYMM = '$myStatusYYMM[$i]'
			GROUP BY TGTCOD";
		$result2 = mysql_query($query2, $link) or die(mysql_error());
		$num_rows2 = mysql_num_rows($result2);
		while ($row = mysql_fetch_assoc($result2))  	
		{		

	$pestCount = $row['pestCount'];	

echo "<br>$chart [ 'chart_data' ][ $y ][ $i ] = $pestCount";
}
}

$y++;
}

Here are the results. I need it organized this way for a chart I am drawing. Again, it takes forever.

[ 'chart_data' ][ 0 ][ 0 ] = 2006 01
[ 'chart_data' ][ 0 ][ 1 ] = 2005 12
[ 'chart_data' ][ 0 ][ 2 ] = 2005 11
[ 'chart_data' ][ 0 ][ 3 ] = 2005 10
[ 'chart_data' ][ 0 ][ 4 ] = 2005 09
[ 'chart_data' ][ 0 ][ 5 ] = 2005 08
[ 'chart_data' ][ 0 ][ 6 ] = 2005 07
[ 'chart_data' ][ 0 ][ 7 ] = 2005 06
[ 'chart_data' ][ 0 ][ 8 ] = 2005 05
[ 'chart_data' ][ 0 ][ 9 ] = 2005 04
[ 'chart_data' ][ 0 ][ 10 ] = 2005 03
[ 'chart_data' ][ 0 ][ 11 ] = 2005 02
[ 'chart_data' ][ 0 ][ 12 ] = 2005 01
[ 'chart_data' ][ 0 ][ 13 ] = 2004 12
[ 'chart_data' ][ 0 ][ 14 ] = 2004 11
[ 'chart_data' ][ 0 ][ 15 ] = 2004 10
[ 'chart_data' ][ 0 ][ 16 ] = 2004 09
[ 'chart_data' ][ 0 ][ 17 ] = 2004 08

[ 'chart_data' ][ 1 ][ 0 ] = American Roaches
[ 'chart_data' ][ 1 ][ 1 ] = 1
[ 'chart_data' ][ 1 ][ 2 ] = 2
[ 'chart_data' ][ 1 ][ 3 ] = 1
[ 'chart_data' ][ 1 ][ 4 ] = 2
[ 'chart_data' ][ 1 ][ 5 ] = 2
[ 'chart_data' ][ 1 ][ 6 ] = 4
[ 'chart_data' ][ 1 ][ 13 ] = 1
[ 'chart_data' ][ 1 ][ 14 ] = 1
[ 'chart_data' ][ 1 ][ 16 ] = 2

[ 'chart_data' ][ 2 ][ 0 ] = Bees
[ 'chart_data' ][ 2 ][ 3 ] = 2
[ 'chart_data' ][ 2 ][ 5 ] = 35

[ 'chart_data' ][ 3 ][ 0 ] = Blow Flies
[ 'chart_data' ][ 3 ][ 13 ] = 20
[ 'chart_data' ][ 3 ][ 17 ] = 12

[ 'chart_data' ][ 4 ][ 0 ] = Carpenter Ants
[ 'chart_data' ][ 4 ][ 3 ] = 22
[ 'chart_data' ][ 4 ][ 4 ] = 3

[ 'chart_data' ][ 5 ][ 0 ] = Cigarette Beetles
[ 'chart_data' ][ 5 ][ 0 ] = 1

[ 'chart_data' ][ 6 ][ 0 ] = Clothes Moths
[ 'chart_data' ][ 6 ][ 16 ] = 5

[ 'chart_data' ][ 7 ][ 0 ] = Crickets
[ 'chart_data' ][ 7 ][ 1 ] = 2
[ 'chart_data' ][ 7 ][ 2 ] = 124
[ 'chart_data' ][ 7 ][ 3 ] = 717
[ 'chart_data' ][ 7 ][ 4 ] = 689
[ 'chart_data' ][ 7 ][ 5 ] = 1260
[ 'chart_data' ][ 7 ][ 6 ] = 199
[ 'chart_data' ][ 7 ][ 8 ] = 7
[ 'chart_data' ][ 7 ][ 10 ] = 3
[ 'chart_data' ][ 7 ][ 11 ] = 4
[ 'chart_data' ][ 7 ][ 12 ] = 8
[ 'chart_data' ][ 7 ][ 13 ] = 10
[ 'chart_data' ][ 7 ][ 14 ] = 23
[ 'chart_data' ][ 7 ][ 16 ] = 239
[ 'chart_data' ][ 7 ][ 17 ] = 397

[ 'chart_data' ][ 8 ][ 0 ] = Drugstore Beetles
[ 'chart_data' ][ 8 ][ 3 ] = 1

[ 'chart_data' ][ 9 ][ 0 ] = Earwigs
[ 'chart_data' ][ 9 ][ 2 ] = 1
[ 'chart_data' ][ 9 ][ 10 ] = 1

[ 'chart_data' ][ 10 ][ 0 ] = Fire Ants
[ 'chart_data' ][ 10 ][ 16 ] = 10

[ 'chart_data' ][ 11 ][ 0 ] = Fruit Flies
[ 'chart_data' ][ 11 ][ 7 ] = 20
[ 'chart_data' ][ 11 ][ 10 ] = 2
[ 'chart_data' ][ 11 ][ 12 ] = 60
[ 'chart_data' ][ 11 ][ 14 ] = 4
[ 'chart_data' ][ 11 ][ 15 ] = 10
[ 'chart_data' ][ 11 ][ 16 ] = 12
[ 'chart_data' ][ 11 ][ 17 ] = 30

[ 'chart_data' ][ 12 ][ 0 ] = Fungus Gnats
[ 'chart_data' ][ 12 ][ 7 ] = 22
[ 'chart_data' ][ 12 ][ 9 ] = 70
[ 'chart_data' ][ 12 ][ 10 ] = 196
[ 'chart_data' ][ 12 ][ 11 ] = 36
[ 'chart_data' ][ 12 ][ 12 ] = 80
[ 'chart_data' ][ 12 ][ 13 ] = 13
[ 'chart_data' ][ 12 ][ 14 ] = 30
[ 'chart_data' ][ 12 ][ 15 ] = 10
[ 'chart_data' ][ 12 ][ 16 ] = 63

[ 'chart_data' ][ 13 ][ 0 ] = General Spiders
[ 'chart_data' ][ 13 ][ 0 ] = 1
[ 'chart_data' ][ 13 ][ 1 ] = 2
[ 'chart_data' ][ 13 ][ 2 ] = 4
[ 'chart_data' ][ 13 ][ 3 ] = 5
[ 'chart_data' ][ 13 ][ 4 ] = 3
[ 'chart_data' ][ 13 ][ 5 ] = 10
[ 'chart_data' ][ 13 ][ 6 ] = 7
[ 'chart_data' ][ 13 ][ 10 ] = 6
[ 'chart_data' ][ 13 ][ 14 ] = 5
[ 'chart_data' ][ 13 ][ 17 ] = 6

[ 'chart_data' ][ 14 ][ 0 ] = German Roaches
[ 'chart_data' ][ 14 ][ 1 ] = 1
[ 'chart_data' ][ 14 ][ 2 ] = 1
[ 'chart_data' ][ 14 ][ 3 ] = 1
[ 'chart_data' ][ 14 ][ 4 ] = 1
[ 'chart_data' ][ 14 ][ 5 ] = 9
[ 'chart_data' ][ 14 ][ 6 ] = 66
[ 'chart_data' ][ 14 ][ 10 ] = 6
[ 'chart_data' ][ 14 ][ 13 ] = 1

[ 'chart_data' ][ 15 ][ 0 ] = Hide Beetles
[ 'chart_data' ][ 15 ][ 12 ] = 1
[ 'chart_data' ][ 15 ][ 14 ] = 7

[ 'chart_data' ][ 16 ][ 0 ] = Horse/Deer Flies
[ 'chart_data' ][ 16 ][ 11 ] = 3
[ 'chart_data' ][ 16 ][ 15 ] = 10

[ 'chart_data' ][ 17 ][ 0 ] = House Flies
[ 'chart_data' ][ 17 ][ 0 ] = 3
[ 'chart_data' ][ 17 ][ 1 ] = 6
[ 'chart_data' ][ 17 ][ 2 ] = 15
[ 'chart_data' ][ 17 ][ 3 ] = 12
[ 'chart_data' ][ 17 ][ 4 ] = 41
[ 'chart_data' ][ 17 ][ 5 ] = 25
[ 'chart_data' ][ 17 ][ 6 ] = 43
[ 'chart_data' ][ 17 ][ 8 ] = 21
[ 'chart_data' ][ 17 ][ 9 ] = 2
[ 'chart_data' ][ 17 ][ 10 ] = 1
[ 'chart_data' ][ 17 ][ 13 ] = 18

[ 'chart_data' ][ 18 ][ 0 ] = Indian Meal Moths
[ 'chart_data' ][ 18 ][ 1 ] = 10
[ 'chart_data' ][ 18 ][ 2 ] = 21
[ 'chart_data' ][ 18 ][ 3 ] = 44
[ 'chart_data' ][ 18 ][ 4 ] = 47
[ 'chart_data' ][ 18 ][ 13 ] = 2
[ 'chart_data' ][ 18 ][ 17 ] = 3

[ 'chart_data' ][ 19 ][ 0 ] = Mice
[ 'chart_data' ][ 19 ][ 2 ] = 1
[ 'chart_data' ][ 19 ][ 3 ] = 2
[ 'chart_data' ][ 19 ][ 5 ] = 6
[ 'chart_data' ][ 19 ][ 6 ] = 1
[ 'chart_data' ][ 19 ][ 11 ] = 1
[ 'chart_data' ][ 19 ][ 13 ] = 4
[ 'chart_data' ][ 19 ][ 14 ] = 2

[ 'chart_data' ][ 20 ][ 0 ] = Moths, Species Unknown
[ 'chart_data' ][ 20 ][ 0 ] = 19
[ 'chart_data' ][ 20 ][ 2 ] = 48
[ 'chart_data' ][ 20 ][ 3 ] = 47
[ 'chart_data' ][ 20 ][ 4 ] = 46
[ 'chart_data' ][ 20 ][ 5 ] = 61
[ 'chart_data' ][ 20 ][ 6 ] = 5
[ 'chart_data' ][ 20 ][ 7 ] = 19
[ 'chart_data' ][ 20 ][ 9 ] = 5
[ 'chart_data' ][ 20 ][ 10 ] = 14
[ 'chart_data' ][ 20 ][ 11 ] = 6
[ 'chart_data' ][ 20 ][ 12 ] = 8
[ 'chart_data' ][ 20 ][ 14 ] = 50
[ 'chart_data' ][ 20 ][ 16 ] = 47
[ 'chart_data' ][ 20 ][ 17 ] = 38

[ 'chart_data' ][ 21 ][ 0 ] = Nighttime Beetles
[ 'chart_data' ][ 21 ][ 0 ] = 12
[ 'chart_data' ][ 21 ][ 1 ] = 4
[ 'chart_data' ][ 21 ][ 2 ] = 62
[ 'chart_data' ][ 21 ][ 3 ] = 33
[ 'chart_data' ][ 21 ][ 6 ] = 17
[ 'chart_data' ][ 21 ][ 14 ] = 5

[ 'chart_data' ][ 22 ][ 0 ] = Nighttime Moths
[ 'chart_data' ][ 22 ][ 1 ] = 115
[ 'chart_data' ][ 22 ][ 6 ] = 30
[ 'chart_data' ][ 22 ][ 8 ] = 40
[ 'chart_data' ][ 22 ][ 13 ] = 61

[ 'chart_data' ][ 23 ][ 0 ] = Other Filth Flies
[ 'chart_data' ][ 23 ][ 3 ] = 15

[ 'chart_data' ][ 24 ][ 0 ] = Other Flour Beetles
[ 'chart_data' ][ 24 ][ 12 ] = 1

[ 'chart_data' ][ 25 ][ 0 ] = Other Nighttime Flying Insects
[ 'chart_data' ][ 25 ][ 2 ] = 12
[ 'chart_data' ][ 25 ][ 3 ] = 2
[ 'chart_data' ][ 25 ][ 4 ] = 15
[ 'chart_data' ][ 25 ][ 6 ] = 200
[ 'chart_data' ][ 25 ][ 8 ] = 115
[ 'chart_data' ][ 25 ][ 14 ] = 11

[ 'chart_data' ][ 26 ][ 0 ] = Phorid Flies
[ 'chart_data' ][ 26 ][ 11 ] = 5
[ 'chart_data' ][ 26 ][ 13 ] = 48

[ 'chart_data' ][ 27 ][ 0 ] = Rats
[ 'chart_data' ][ 27 ][ 3 ] = 1

[ 'chart_data' ][ 28 ][ 0 ] = Roach Species Unknown
[ 'chart_data' ][ 28 ][ 12 ] = 1
[ 'chart_data' ][ 28 ][ 13 ] = 1
[ 'chart_data' ][ 28 ][ 14 ] = 5

[ 'chart_data' ][ 29 ][ 0 ] = Smoky Brown Roaches
[ 'chart_data' ][ 29 ][ 17 ] = 2

[ 'chart_data' ][ 30 ][ 0 ] = Stored Product Pest
[ 'chart_data' ][ 30 ][ 4 ] = 35

[Wynder]

My guess is that your last for() it what's causing issue -- if you have 300 rows returned in query1, you'll be executing 300 queries inside of that for(), which shouldn't really be that much of an issue, but you'll definitely want to do something to pre-prune the rows you won't need to absolutely check.

Best thing to do is to chop of code, starting at the bottom and see what kicks up your page execution time, but my bet's on the for().

[dream_scape]

If you are trying to find where the script is slowing down at, you might want to consider a script profiler/debugger. I use XDebug, but there are others (APD comes to mind). They will help you pinpoint the exact location of script bottlenecks.

[frost110]

I bet it is in your database. Try adding an index of how you select the data from the DB.

IE if you always select by name then add an index to the name column of the db. This will speed up the queries to the database 100 fold!

--FrosT