Code Optimizing Help

[eluser]bhenry001[/eluser]
I have noticed when working with a large amount of data from a database results, processing times slow down. I broke down the results into smaller chunks and processing time speeds up. There are more calls to MySQL, but there seems to be a speed increase. Has anyone noticed this? Or Am I just writing poor code. Below is an example of my code. Simple enough I believe.


$str='<tbody>';
$incidents=0;
$red=0;
$yellow=0;
foreach($query->result() as $row)
{
$str=$str.'<tr class='.$row->sla1.'>';
//Count the tasks
$incidents++;
switch($row->sla1) {
case 'red':
$red++;
break;
case 'yellow':
$yellow++;
break;
}
//Reformat all the date fields to display
$row->service_restored=$this->format_date($row->service_restored);
$row->create_date=$this->format_date($row->create_date);
$row->modified_date=$this->format_date($row->modified_date);
$row->progress_update=$this->format_date($row->progress_update);
$row->acknowledge_date=$this->format_date($row->acknowledge_date);
$row->datetime_service_restored=$this->format_date($row->datetime_service_restored);
//Only output the fields needed to the HTML Table
foreach($fields as $field)
{
$str=$str.'<td title="'.form_prep($row->notification_text).'" >';
if($field == 'incident_id')
{
$str=$str.anchor_popup('main/incident/'.
$row->$field,form_prep($row->$field));
}
else
{
$str=$str.form_prep($row->$field);
}
$str=$str.'</td>';
}
$str=$str.'</tr>';
} //end foreach
$query->free_result();
$str=$str.'</tbody>';

$result = array('incidents'=>$incidents,'red'=>$red,'yellow'=>$yellow,'tbody'=>$str);
return $result;

[eluser]überfuzz[/eluser]

Code:

$str='<tbody>';
        $incidents=0;
        $red=0;
        $yellow=0;
        foreach($query->result() as $row)
        {
         //messing about with the query result.
        } //end foreach
    $query->free_result();
        $str=$str.'</tbody>';
    
        $result = array('incidents'=>$incidents,'red'=>$red,'yellow'=>$yellow,'tbody'=>$str);
        return $result; //if you have yellow etc in database you should count it using SQL!

What are you fetching from the database?

[eluser]xwero[/eluser]
where is the $fields variable coming from?

It's not code i would write today, all that processing should be handled before you send it to a view file. The view file should at best only have echos and loops. An occasional if is allowed but the view file should mainly contain markup.

People don't read large data lists so why not paginate, that will speed up your page with minimal effort.

[eluser]bhenry001[/eluser]
Yes, I see you are correct. MySQL has been much faster in retrieving results. This code is from a model. The $fields variable is coming from a query where I marked only fields that I want to show in the final view. I do not want to show everything. I could optimize it by retrieving only the data I need, but there are fields I want available in the query result but I do not want to display them. Well anyways, I have discovered my code is not as efficient as it could be and the handling of large amounts of data seems to differ not too much. The one thing I could not figure out was how to retrieve the data and have the dates formatted they way I wanted.
Thanks!
Bruce