Progress bar

[timstring]

I have a script that rummages trough a MySQL database that at the moment has 432,773 rows and grows by 2,500 entries every day. When I run the report, the computer stews, bubbles, then boils over. My problem is that from the time the routine starts until something happens on the screen is an agonizing 20 seconds. Most people, including myself, don't have the patience for a 20-second empty span.

Here's a snippet of the code I have generated:

echo '<table style = "width:700; margin:auto; text-align:center; font-weight:normal;">';
	echo '<tr>';
		echo '<th style = "width:33%; text-align:left">System</th>';
		echo '<th style = "width:25%; text-align:right">Calls</th>';
		echo '<th style = "width:25%;text-align:right">Work Orders</th>';
		echo '<th style = "width:20%;text-align:right">Percentage</th>';
	echo '</tr>';

$stmt = $mysqli->prepare("SELECT SystemNum, LongSystem FROM Systems WHERE SystemNum != 67 ORDER BY LongSystem");

$stmt->execute();
$SysResult = $stmt->get_result();
	while ($row = $SysResult->fetch_assoc()) 
		{
		$LongSystem = $row["LongSystem"];
		$SystemNum = $row["SystemNum"];


	$stmt = $mysqli->prepare("SELECT TechNum, CellNum, IDNum FROM Techs WHERE SystemNum  = '$SystemNum' ORDER BY TechNum");

		$stmt->execute();
		$TechResult = $stmt->get_result();
	while ($row = $TechResult->fetch_assoc()) 
		{
		$TechNum = $row["TechNum"];
		$CellNum = $row["CellNum"];
		$IdNum = $row["IdNum"];


		//$percent = (int)($IdNum / $NumRows * 100);
		//update_progress($percent);


$result = $mysqli->query("select count(*) as `c` from `CallVolume` WHERE ANI = '$CellNum' AND WorkDate BETWEEN '$StartDate' AND '$EndDate'");
$CallCount = $result->fetch_object()->c;

$result = $mysqli->query("select count(*) as `d` from `TOAWorkOrders` WHERE TechNum = '$TechNum' AND WorkDate BETWEEN '$StartDate' AND '$EndDate'");
$WorkOrders = $result->fetch_object()->d;


$SubCallCount = $SubCallCount + $CallCount;
$SubWorkOrders = $SubWorkOrders	+ $WorkOrders;


		}



				if ($SubWorkOrders != 0)
					{	
					$SubSub = $SubCallCount / $SubWorkOrders;
					$SubPercent = (int) ($SubSub * 1000) / 10;
					}
					else $SubPercent = 'No Work Orders';

					if ($SubCallCount != 0 || $SubWorkOrders != 0)
						{

						$Counter = $Counter + .5;


						if ($Counter == (int) ($Counter)) print '<tr style="background-color:#A9F5A9">';
							else print '<tr style="background-color:#FFFFFF">';	

								print '<td style="text-align:left">' . $LongSystem . '</td>';
								print '<td style="text-align:right;">' . $SubCallCount . '</td>';
								print '<td style="text-align:right">' . $SubWorkOrders . '</td>';
								print '<td style="text-align:right">' . $SubPercent . '</th>';
							print '</tr>';

						$TotalCallCount = $TotalCallCount + $SubCallCount;
						$TotalWorkOrders = $TotalWorkOrders + $SubWorkOrders;

						$WorkOrders = 0;
						$CallCount = 0;
						$SubWorkOrders = 0;
						$SubCallCount = 0;	
						$Percentage = 0;			




						}
		} 

I have two questions: First, Is there a way to get the table rows to start printing row by row as the script gets to them? Right now, printing the table takes place after all the code has run.

Second, is there anything I can do to increase the performance of the script?

[sneakyimp]

When a PHP script starts to output a lot of text (e.g., a few hundred Kilobytes or more), then the web server may avoid outputting anything until a substantial amount has piled up in a buffer somewhere. If you are running a loop many times that has echo or print statements, I believe you can force PHP (and hopefully your webserver) to go ahead and force the output to a user's browser with the [man]flush[/man] command. I would strongly encourage you to read the documentation in detail on that command.

Secondly, yes you could probably improve this script. Generally speaking, you should try to avoid running a query that fetches a bazillion records, loop through all those records, and then within each iteration of your loop run still more queries for each record that was found with the first query. This is a very good way to force your server to do a lot of work. It's much more efficient to concoct one smart query that fetches the stuff you need in the first place by using an SQL JOIN. I have not looked at your script closely enough to determine whether or not it is possible to get rid of this queries you are running in your loop or not, but it's been my experience that in most cases you can replace many queries with a single one and some smarter PHP logic.

[NogDog]

Along with sneakyimp's reply, if you are going to run the same query over and over within a loop (not that I recommend it if it can be avoided), do the prepare() before the loop, then use bind_param() within the loop before execute()-ing it.

$stmt = $db->prepare($theQuery);
while($whatever) {
   $stmt->bind_param('s', $someVar);
   $result = $stmt->execute();
}

And don't forget all the usual stuff about optimizing queries, usually dealing with determining where you need indexes on table columns that are used in joins, wheres, and order by's.