Table of Contents
回复讨论(解决方案)
Home Backend Development PHP Tutorial php运行大数据量汇总操作时间过长网页失效 急急急!

php运行大数据量汇总操作时间过长网页失效 急急急!

Jun 23, 2016 pm 02:14 PM

PHP

运行时间大概2个小时,然后页面就失效了。有知道的朋友帮忙一下,万分感谢。

回复讨论(解决方案)

换其他语言比如C来处理统计汇总

代码呢。。。。


 function foreverselect(){
$db = Db::getInstance();
//根据编码查询数据
$id=$_REQUEST['id'];  //id
$datamonth=$_REQUEST['datamonth']; //日期
$ids=explode(',',$id); //截取
$jxtblist="";
$isok="yes"; //控制进度条标识
$datalist;//返回提示信息
$datalist1;
if(!empty($id)){
foreach($ids as $key=>$li){

$jxtbsql="select * from jx_tbprofile where id=".$li;

$jxtblist =$db-> query($jxtbsql);

foreach($jxtblist as $key=>$li1){
if($li1[execlevel]==0){ //0阶段
$list;
$success=0;//完成笔数

//先判断目的字段配置是否有误
$strtab="select * from jx_tbprofile_dtl where tableno='".$li1[tableno]."'"; //检索字段名称
$listtab=$db->query($strtab);
$strcol="show columns from ".$li1[desttable]; //获取目的表字段名称
$listcol=$db->query($strcol);

$ziduan;
$boole=false;

foreach ($listtab as $keytab=>$litab) {//配置目的表字段
foreach ($listcol as $keycol=>$licol)//目的表字段
{
if($litab[dest_column]==$licol['Field']){
$boole=true;    //两个名称相互比较,如果遇到相同的给$boole赋值true。
//echo "相等";
break;
}else{
//echo $litab[dest_column];
//echo $licol['Field'];
$boole=false; 
}
}
if(!$boole){//配置表中一个字段对应完之后获取$boole的值如果为false,就将改字段存入$ziduan函数中加入提示信息
$ziduan=$ziduan.$litab[dest_column];
$boole=false; 
break;
}
}
if(!$boole){
$datalist=$datalist."未找到目的字段".$ziduan.",配置错误!";
}else{

//清除旧数据
$sql=str_replace("|datamonth|",$datamonth,$li1[sqldeldest]);//配置的删除语句
// echo $sql;
//mysql_query($sql) or die(mysql_error()); 
if (mysql_query($sql)){
//echo mysql_query($sql);
$datalist=$datalist."原数据表已清除!
";
$sql1=$li1[remarks];//配置的查询语句
$re=str_replace("|datamonth|",$datamonth,$sql1); //替换字符

$list1 =$db-> query($re);
//mysql_query($re) or die(mysql_error()); 
$jx = M ($li1[desttable]);


foreach($list1 as $key=>$li2)
{
//$User = A($li1[tableno]); // 实例化action控制器对象


foreach ($listtab as $key=>$litab){
if(!empty($litab[mark])){//目的表中预设字段名称mark。
//echo $litab[mark];
$_POST[$litab[dest_column]]=$litab[mark];
}else{
$_POST[$litab[dest_column]]=$li2[$litab[source_column]];
}
}
//$User->fuValues1($li2);
$tru=$jx->create();
$bo=$jx->add();
$success=$success+1; //成功笔数
}


//echo date("Y-m-d h:i:s")."
".$li1[tableprofile]."结算完毕!成功".$success."笔数据。
";

$datalist=$datalist.date("Y-m-d h:i:s")."
".$datamonth.$li1[tableprofile]."结算完毕!成功".$success."笔数据。
";

}else{
$datalist=$datalist."删除语句出问题!
";

}

}

$this->assign('isok',$isok);
}
  }
}
}
}

你自己分析一下。是哪句代码执行过长,可以标示出来。


$list1 =$db-> query($re);
//mysql_query($re) or die(mysql_error()); 
$jx = M ($li1[desttable]);


foreach($list1 as $key=>$li2)
{
//$User = A($li1[tableno]); // 实例化action控制器对象


foreach ($listtab as $key=>$litab){
if(!empty($litab[mark])){//目的表中预设字段名称mark。
//echo $litab[mark];
$_POST[$litab[dest_column]]=$litab[mark];
}else{
$_POST[$litab[dest_column]]=$li2[$litab[source_column]];
}
}
//$User->fuValues1($li2);
$tru=$jx->create();
$bo=$jx->add();
$success=$success+1; //成功笔数
}

这段就是执行查询语句,然后将结果一条一条插入到目的表中。我在一张单独的表中配置了:来源表,目的表,查询语句,删除语句。在这段代码里找到配置的这条数据,然后执行查询表,插入新表的操作。其中有4条数据是量比较大的。我单独跑还挺快,但是4个连在一起跑就时间很长知道浏览器失效。

把最终生成的sql贴出来看看。表结构和索引情况如何,都描述清楚吧。

set_time_limit(0);???

把最终生成的sql贴出来看看。表结构和索引情况如何,都描述清楚吧。
在这3张表之间转换数据的jx_zy_detail_charge_belong,jx_zy_detail_charge_force,jx_zy_detail_charge

这是跑的第一条查询sql:jx_zy_detail_charge_belong->jx_zy_detail_charge_force
SELECT jx_zy_detail_charge_belong.data_month,   
         jx_zy_detail_charge_belong.inpatient_no,   
         jx_zy_detail_charge_belong.charge_date,   
         jx_zy_detail_charge_belong.op_id_code,   
         jx_zy_detail_charge_belong.charge_code,   
         jx_zy_detail_charge_belong.charge_fee,   
         jx_zy_detail_charge_belong.percentage,   
         jx_zy_detail_charge_belong.ward_code,   
         jx_zy_detail_charge_belong.dept_code,   
         jx_zy_detail_charge_belong.order_no,   
         jx_zy_detail_charge_belong.exec_unit,   
         jx_zy_detail_charge_belong.retprice,   
         jx_zy_detail_charge_belong.charge_amount,   
         jx_zy_detail_charge_belong.serial,   
         jx_zy_detail_charge_belong.group_no,   
         jx_zy_detail_charge_belong.bill_code,   
         jx_zy_detail_charge_belong.key_col,   
         jx_zy_detail_charge_belong.pat_name
    FROM jx_zy_detail_charge_belong ,jx_mz_charge_item 
   WHERE jx_zy_detail_charge_belong.data_month =  '|datamonth|' and
   jx_zy_detail_charge_belong.charge_code =jx_mz_charge_item.fee_code and
(jx_mz_charge_item.exec_unit is  null or
jx_mz_charge_item.exec_unit =' ') 
union all
SELECT jx_zy_detail_charge_belong.data_month,   
         jx_zy_detail_charge_belong.inpatient_no,   
         jx_zy_detail_charge_belong.charge_date,   
         jx_zy_detail_charge_belong.op_id_code,   
         jx_zy_detail_charge_belong.charge_code,   
         jx_zy_detail_charge_belong.charge_fee,   
         jx_zy_detail_charge_belong.percentage,   
         jx_zy_detail_charge_belong.ward_code,   
         jx_zy_detail_charge_belong.dept_code,   
         jx_zy_detail_charge_belong.order_no,   
         jx_mz_charge_item.exec_unit,  
         jx_zy_detail_charge_belong.retprice,   
         jx_zy_detail_charge_belong.charge_amount,   
         jx_zy_detail_charge_belong.serial,   
         jx_zy_detail_charge_belong.group_no,   
         jx_zy_detail_charge_belong.bill_code,   
         jx_zy_detail_charge_belong.key_col,   
         jx_zy_detail_charge_belong.pat_name
  
    FROM jx_zy_detail_charge_belong ,jx_mz_charge_item 
   WHERE jx_zy_detail_charge_belong.data_month = '|datamonth|'  and
    jx_zy_detail_charge_belong.charge_code= jx_mz_charge_item.fee_code and
jx_mz_charge_item.exec_unit is not null and
jx_mz_charge_item.exec_unit ' '
就是从一个配置表,jx_mz_charge_item 中找到本表charge_code和fee_code相同,找到配置表的exec_unit符合条件就用这个值替换原表的科室值

第二条转换查询sql:jx_zy_detail_charge_force-》jx_zy_detail_charge
select  data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
where jx_zy_detail_charge_force.exec_unit not in (select description from jx_code_ctl where item_no like 'ordept%') and data_month='|datamonth|'
union all
select  data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,exec_unit,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
 where  jx_zy_detail_charge_force.exec_unit  in (select description from jx_code_ctl where item_no like 'ordept%') and  not
             ( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%')  or
              jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|'
union all
select  data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
 where   jx_zy_detail_charge_force.exec_unit  in (select description from jx_code_ctl where item_no like 'ordept%') and 
         ( jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'orbill%')  or
         jx_zy_detail_charge_force.bill_code in (select description from jx_code_ctl where item_no like 'zlbill%') ) and data_month='|datamonth|'
union all
select  data_month,inpatient_no,charge_date,op_id_code,charge_code,charge_fee,dept_code,dept_code,retprice,charge_amount,serial,bill_code,pat_name
from jx_zy_detail_charge_force
 where  jx_zy_detail_charge_force.exec_unit is null and data_month='|datamonth|'

将执行科室exec_unit字段在表jx_code_ctl中配置的科室,并且收费大类bill_code字段也在表jx_code_ctl中配置的大类中的数据的执行科室exec_unit改为开单科室dept_code



一共分为门诊和住院两组表,过程是一样的,这里是住院的流程说明,我单跑门诊和住院的第一跳sql,然后跑门诊和住院第二条sql,速度大概1个小时可以跑完,但是4条一起跑就要很久,而且页面也会挂掉。

后台执行产生所需数据,前台展示结果。

后台执行产生所需数据,前台展示结果。 是的,就是我把这4个数据量最大一起跑就很耗费时间,2个2个跑就快很多了。

用 EXPLAIN 指令检查你的 sql 指令,看看 mysql 会给你什么建议

用 EXPLAIN 指令检查你的 sql 指令,看看 mysql 会给你什么建议

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Chat Commands and How to Use Them
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Explain JSON Web Tokens (JWT) and their use case in PHP APIs. Explain JSON Web Tokens (JWT) and their use case in PHP APIs. Apr 05, 2025 am 12:04 AM

JWT is an open standard based on JSON, used to securely transmit information between parties, mainly for identity authentication and information exchange. 1. JWT consists of three parts: Header, Payload and Signature. 2. The working principle of JWT includes three steps: generating JWT, verifying JWT and parsing Payload. 3. When using JWT for authentication in PHP, JWT can be generated and verified, and user role and permission information can be included in advanced usage. 4. Common errors include signature verification failure, token expiration, and payload oversized. Debugging skills include using debugging tools and logging. 5. Performance optimization and best practices include using appropriate signature algorithms, setting validity periods reasonably,

Explain the concept of late static binding in PHP. Explain the concept of late static binding in PHP. Mar 21, 2025 pm 01:33 PM

Article discusses late static binding (LSB) in PHP, introduced in PHP 5.3, allowing runtime resolution of static method calls for more flexible inheritance.Main issue: LSB vs. traditional polymorphism; LSB's practical applications and potential perfo

Framework Security Features: Protecting against vulnerabilities. Framework Security Features: Protecting against vulnerabilities. Mar 28, 2025 pm 05:11 PM

Article discusses essential security features in frameworks to protect against vulnerabilities, including input validation, authentication, and regular updates.

How to send a POST request containing JSON data using PHP's cURL library? How to send a POST request containing JSON data using PHP's cURL library? Apr 01, 2025 pm 03:12 PM

Sending JSON data using PHP's cURL library In PHP development, it is often necessary to interact with external APIs. One of the common ways is to use cURL library to send POST�...

Customizing/Extending Frameworks: How to add custom functionality. Customizing/Extending Frameworks: How to add custom functionality. Mar 28, 2025 pm 05:12 PM

The article discusses adding custom functionality to frameworks, focusing on understanding architecture, identifying extension points, and best practices for integration and debugging.

Describe the SOLID principles and how they apply to PHP development. Describe the SOLID principles and how they apply to PHP development. Apr 03, 2025 am 12:04 AM

The application of SOLID principle in PHP development includes: 1. Single responsibility principle (SRP): Each class is responsible for only one function. 2. Open and close principle (OCP): Changes are achieved through extension rather than modification. 3. Lisch's Substitution Principle (LSP): Subclasses can replace base classes without affecting program accuracy. 4. Interface isolation principle (ISP): Use fine-grained interfaces to avoid dependencies and unused methods. 5. Dependency inversion principle (DIP): High and low-level modules rely on abstraction and are implemented through dependency injection.

How does session hijacking work and how can you mitigate it in PHP? How does session hijacking work and how can you mitigate it in PHP? Apr 06, 2025 am 12:02 AM

Session hijacking can be achieved through the following steps: 1. Obtain the session ID, 2. Use the session ID, 3. Keep the session active. The methods to prevent session hijacking in PHP include: 1. Use the session_regenerate_id() function to regenerate the session ID, 2. Store session data through the database, 3. Ensure that all session data is transmitted through HTTPS.

See all articles