当我运行下面的代码时,因为要下载到excel,发现效率太慢,要30多秒,请问要这么改善if(!empty($tempArrVal)){
foreach($tempArrVal as $key => $val){
$pur_price = '0'; //采购价
$fanli_price = '0'; //返利
$ywyc_price = '0'; //业务预存款
$sal_price = '0'; //卖价
$tcyc_price = '0'; //套餐预存款
$butie_price = '0'; //补贴款
$lhyc_price = '0'; //靓号预存款
$fclrxs = '0'; //分成利润系数
$hdfl = '0'; //活动返利
$cb_price = '0'; //成本
$sr_price = '0'; //收入
$sale_point = ''; //业绩点
//采购价 返利 业务预存款 卖价---------------------------------------------------------
$sql1 = "select distinct `one_pro_id` from `oa_one_pro_status` where `project_id`='".$val['id']."'";
$que1 = $db -> query($sql1);
while($ar = $db -> fetch_array($que1)){
$sql2 = "select `pur_price`,`fanli_price`,`ywyc_price`,`sal_price`,`imei` from `oa_one_pro` where id = '".$ar['one_pro_id']."'";
$que2 = $db -> query($sql2);
$arr = $db -> fetch_array($que2);
$pur_price += $arr['pur_price'];
$fanli_price += $arr['fanli_price'];
$ywyc_price += $arr['ywyc_price'];
$sal_price += $arr['sal_price'];
//套餐预存款 补贴款 分成利润系数 活动返利-------------------------------------------------
$sql3 = "select `tcyc_price`,`butie_price`,`fenchenglirunxishu`,`huodongfanli` from `cs_pro_info` where `pro_no` in (select `product_id` from `oa_one_pro` where `id`='".$ar['one_pro_id']."')";
$que3 = $db -> query($sql3);
while($brr = $db -> fetch_array($que3)){
$tcyc_price += $brr['tcyc_price'];
$butie_price += $brr['butie_price'];
$fclrxs += $brr['fenchenglirunxishu'];
$hdfl += $brr['huodongfanli'];
}
//---------------------------------------------------------------------
//靓号预存款---------------------------------------------------------------------
if(strlen($arr['imei'])==11){
$sql_ = "select `num_yck` from `cs_new_num` A inner join `vte_numbers` B on A.num_id = B.id where B.number = '".$arr['imei']."'";
$que_ = $db -> query($sql_);
$nrr = $db -> fetch_array($que_);
//找不到则赋值为0
if(empty($nrr)){
$lhyc_price += 0;
}else{
$lhyc_price += $nrr['num_yck'];
}
}
}
//------------------------------------------------------------------ //收入类总和
$sr_price = $butie_price + $sal_price;
//成本类总和
$cb_price = $pur_price + $fanli_price + $ywyc_price + $tcyc_price + $lhyc_price;
//业绩点计算
$sal_point = ($sr_price + $fclrxs - $cb_price - $hdfl)/100;
//保留小数点后2位
$sal_point = number_format($sal_point,2);
$tempArrVal[$key]['sale_point'] = $sal_point;
}
}
?>
foreach($tempArrVal as $key => $val){
$pur_price = '0'; //采购价
$fanli_price = '0'; //返利
$ywyc_price = '0'; //业务预存款
$sal_price = '0'; //卖价
$tcyc_price = '0'; //套餐预存款
$butie_price = '0'; //补贴款
$lhyc_price = '0'; //靓号预存款
$fclrxs = '0'; //分成利润系数
$hdfl = '0'; //活动返利
$cb_price = '0'; //成本
$sr_price = '0'; //收入
$sale_point = ''; //业绩点
//采购价 返利 业务预存款 卖价---------------------------------------------------------
$sql1 = "select distinct `one_pro_id` from `oa_one_pro_status` where `project_id`='".$val['id']."'";
$que1 = $db -> query($sql1);
while($ar = $db -> fetch_array($que1)){
$sql2 = "select `pur_price`,`fanli_price`,`ywyc_price`,`sal_price`,`imei` from `oa_one_pro` where id = '".$ar['one_pro_id']."'";
$que2 = $db -> query($sql2);
$arr = $db -> fetch_array($que2);
$pur_price += $arr['pur_price'];
$fanli_price += $arr['fanli_price'];
$ywyc_price += $arr['ywyc_price'];
$sal_price += $arr['sal_price'];
//套餐预存款 补贴款 分成利润系数 活动返利-------------------------------------------------
$sql3 = "select `tcyc_price`,`butie_price`,`fenchenglirunxishu`,`huodongfanli` from `cs_pro_info` where `pro_no` in (select `product_id` from `oa_one_pro` where `id`='".$ar['one_pro_id']."')";
$que3 = $db -> query($sql3);
while($brr = $db -> fetch_array($que3)){
$tcyc_price += $brr['tcyc_price'];
$butie_price += $brr['butie_price'];
$fclrxs += $brr['fenchenglirunxishu'];
$hdfl += $brr['huodongfanli'];
}
//---------------------------------------------------------------------
//靓号预存款---------------------------------------------------------------------
if(strlen($arr['imei'])==11){
$sql_ = "select `num_yck` from `cs_new_num` A inner join `vte_numbers` B on A.num_id = B.id where B.number = '".$arr['imei']."'";
$que_ = $db -> query($sql_);
$nrr = $db -> fetch_array($que_);
//找不到则赋值为0
if(empty($nrr)){
$lhyc_price += 0;
}else{
$lhyc_price += $nrr['num_yck'];
}
}
}
//------------------------------------------------------------------ //收入类总和
$sr_price = $butie_price + $sal_price;
//成本类总和
$cb_price = $pur_price + $fanli_price + $ywyc_price + $tcyc_price + $lhyc_price;
//业绩点计算
$sal_point = ($sr_price + $fclrxs - $cb_price - $hdfl)/100;
//保留小数点后2位
$sal_point = number_format($sal_point,2);
$tempArrVal[$key]['sale_point'] = $sal_point;
}
}
?>
在哪里呢?
只能是你的while句了。2个while,1个foreach;
建议:
重新优化此代码,把逻辑组织一番;
你是用phpExcel导出吗?
我是这做做的,直接把要导出的sql(带参数),通过sesseion传给phpExcel,再在phpExcel中读数据库,生成要下载的数据。
这样,比你直接把要下载的数据,传给phpExcel快的多。
但我C#如果直接文本写入到EXCEL的话5000条记录大概3秒钟。
如果是用了EXCEL控件的话就要3分钟。