Board index   FAQ   Search  
Register  Login
Board index PHP Your Scripts

looping slow

This is where you share YOUR scripts with others

Moderators: macek, egami, gesf

looping slow

Postby nodoubt » Mon May 23, 2011 10:24 pm

hi..
I got problem in retrieving data from database.
The problem happen when the data too big/much and it take too long to display the data.
In fact produce php runtime error.
What is the solution for this?any anybody can help?
here is the code. The code is correct but it cause runtime error when trying load to the page.
<SCRIPT LANGUAGE="JavaScript">
function MM_openBrWindow(theURL,winName,features) { //v2.0
window.open(theURL,winName,features);
}
</script>
<?php
ini_set('max_execution_time', 3600); // 1 hour
//ini_set('mysql.connect_timeout', 3600);
//ini_set('max_input_time',3600);

$sqlComs = "SELECT b.component_id FROM qla_score_weightage a,qla_ref_weightage b
WHERE a.weightage_id = b.weightage_id AND b.weightage_type_id = '1'
AND a.score_id = '$project_id' AND a.status = '1' ORDER BY b.weightage_id";

$rs2s=$db->Execute($sqlComs);
echo $sqlComs; ?>
<div id="title" align="center"> Summary Score of Assessment By Project </div>
<div><a onclick="MM_openBrWindow('fpdf16/slipverifyscore.php?score=<?php echo $project_id;?>','','location=yes,scrollbars=yes,resizable=yes,width=1000,height=800')" href="" >Print Report </a> | <a onclick="MM_openBrWindow('fpdf16/sijil.php?score=<?php echo $project_id;?>','','location=yes,scrollbars=yes,resizable=yes,width=1000,height=800')" href="" >Print Certtficate </a></div><br>
<table align ="center" width="700" class="tbldata">

<tr><td align ="left" class = "row03" >Component</td><td class = "row03" >Element/Sub Element</td><td class = "row03" >Ratio Compliance</td><td class = "row03" >Weightage Compliance(%)</td><td class = "row03" >Score (%)</td></tr>
<?php
while(!$rs2s->EOF){

$coms = $rs2s->fields[0];
$ratioCom=cal_ratio_com($coms,$project_id);
$weCom=get_wcomplainceCom2($project_id,$coms);
echo '<tr><td align ="left" class = "row04" >'.component_name($coms).'</td><td class = "row04" ></td><td class = "row04" >'.round($ratioCom,3).'</td><td class = "row04" >'.round($weCom,2).'</td><td class = "row04" >'.round(get_scoreCom($project_id,$coms)*round($weCom,2),2).'</td></tr>';
$sqlEles = " SELECT b.element_id FROM qla_score_weightage a,qla_ref_weightage b ,qla_ref_element c
WHERE a.weightage_id = b.weightage_id AND b.weightage_type_id = '2'
AND c.element_id = b.element_id AND c.component_id = '$coms'
AND a.score_id = '$project_id' AND a.status = '1'
order by b.element_id";
//echo $sqlEles;
$rsEles=$db->Execute($sqlEles);
while(!$rsEles->EOF){
$eles = $rsEles->fields[0];
$ratio=cal_ratio_ele2($coms,$eles,$project_id);
//$we=round(get_weigtageEleBy($project_id,$coms,$eles),2);
$we = get_wcomplainceEle2($project_id,$eles,$coms);
if(check_subELE($eles)==0){
echo '<tr><td class = "row06"></td><td class = "row06" valign = "top">'.element_name($eles).'</td><td class = "row06"><b>'.round($ratio,2).'</b></td><td class = "row06">'.round($we,2).'</td><td class = "row06">'.round(round($ratio,3)*round($we,3),2).'</td></tr>';
}else{
echo '<tr><td class = "row06"></td><td class = "row06" valign = "top">'.element_name($eles).'</td><td class = "row06"></td><td class = "row06">'.round($we,3).'</td><td class = "row06"></td></tr>';

}

$sqlEleSubs = " SELECT b.sub_element_id FROM qla_score_weightage a,qla_ref_weightage b ,qla_ref_sub_element c
WHERE a.weightage_id = b.weightage_id AND b.weightage_type_id = '3'
AND c.sub_element_id = b.sub_element_id AND c.element_id = '$eles'
AND a.score_id = '$project_id' AND a.status = '1' ORDER BY b.sub_element_id";
$rsEleSubs=$db->Execute($sqlEleSubs);
//echo $sqlEleSubs;


// echo '<tr><td class = "row06"></td><td valign = "top" class = "row06" ></td><td>';

while(!$rsEleSubs->EOF){
$sub_eles=$rsEleSubs->fields[0];
if(isset($sub_eles)AND $sub_eles!=""){
$subwe =get_wcomplainceSub2($project_id,$sub_eles,$eles);
//$subwe =get_weigtageSubEleBy($project_id,$eles,$sub_eles);
//echo $ub_we = $subwe*$we;
//echo $subwe.'-'.$we;
$subratio = cal_ratio_noloc2($eles,$sub_eles,$project_id);
$sub=$subwe*$we;
echo '<tr><td class="row10"></td><td class="row10" >'.sub_element_name($sub_eles).'</td><td class="row10">'.round($subratio,3).'</td><td class="row10">'.round($sub,2).'</td><td class="row10">'.round(round($sub,3)*$subratio,2).'</td></tr>';
} //include('apps/qlassic/fscore/fdefect3.php');
echo '</td>';
$rsEleSubs->MoveNext();
}
// echo '</tr></table>';

// echo '</td></tr>';


//$rsLocs->MoveNext();
// }


$rsEles->MoveNext();
}


$rs2s->MoveNext();
}
?>
</table>
nodoubt
New php-forum User
New php-forum User
 
Posts: 2
Joined: Mon May 23, 2011 10:11 pm

Re: looping slow

Postby nodoubt » Sun May 29, 2011 6:47 pm

ok as i know while looping to fetch data from database can cause slow loading in displaying the page if there are thousand data need to fetch from database.
so is there any ways or solution instead of using while loop.

like this one i use while loop to fetch data from db:
function cal_ratio_com($com,$project_id){
$db=new mySQL(host,user,pass,dbase);

$sqlDefect = " select item_id
from qla_assess_item
where component_id = '$com' AND score_id = '$project_id' ";

$result2 = $db->query($sqlDefect);
while($data1 = $result2->fetch()){
$item_id = $data1['item_id'];

$sqlget = "SELECT *
FROM qla_nilai
WHERE item_id ='$item_id'
";

$result = $db->query($sqlget);
//$yes = 0;
//$no = 0;
while($data = $result->fetch()){
$nilai = $data['nilai_item'];
// echo $item_id.'-'.$nilai;
if($nilai == '' || $nilai == 0){
}else if($nilai == 1){
$yes++;
}else if($nilai == 2){
$no++;
}else{}
}


}
if(( $yes + $no ) == 0){
return '-';
}else{
$ratio = $yes /( $yes + $no );

return $ratio; }


}


.....because this one cause slow loading page.
nodoubt
New php-forum User
New php-forum User
 
Posts: 2
Joined: Mon May 23, 2011 10:11 pm

Re: looping slow

Postby vishal1208 » Fri Jun 17, 2011 12:16 am

Hi,
You need to set database table indexing of fields which you most probably use in sql query. by using indexing your loop execution speed become 90% fast

Vishal
vishal1208
New php-forum User
New php-forum User
 
Posts: 10
Joined: Sun Jun 12, 2011 6:40 am


Return to Your Scripts

Who is online

Users browsing this forum: No registered users and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron