Php is running a large data volume summary operation for a long time. webpage failure is urgent!

Php is running a large data volume summary operation for a long time. webpage failure is urgent! PHP

The running time is about 2 hours, and the page becomes invalid. Thank you for your help.

Reply to discussion (solution)

Use other languages such as C to process Statistical summary

Code ....

Function foreverselect (){
$ Db = Db: getInstance ();
// Query data by encoding
$ Id = $ _ REQUEST ['id']; // id
$ Datamonth = $ _ REQUEST ['datamonth']; // date
$ Ids = explode (',', $ id); // capture
$ Jxtblist = "";
$ Isok = "yes"; // control progress bar ID
$ Datalist; // return the prompt message.
$ 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) {// stage 0
$ List;
$ Success = 0; // number of completed Records

// First determine whether the target field configuration is correct
$ Strtab = "select * from jx_tbprofile_dtl where tableno = '". $ li1 [tableno]. "'"; // Retrieve the field name
$ Listtab = $ db-> query ($ strtab );
$ Strcol = "show columns from". $ li1 [desttable]; // Obtain the name of the target Table field
$ Listcol = $ db-> query ($ strcol );

$ Ziduan;
$ Boole = false;

Foreach ($ listtab as $ keytab => $ litab) {// Configure the target Table field
Foreach ($ listcol as $ keycol => $ licol) // target Table field
{
If ($ litab [dest_column] = $ licol ['field']) {
$ Boole = true; // compare the two names. if the two names are the same, assign true to $ boole.
// Echo "equal ";
Break;
} Else {
// Echo $ litab [dest_column];
// Echo $ licol ['field'];
$ Boole = false;
}
}
If (! $ Boole) {// obtain the value of $ boole after a field in the configuration table is matched. if it is false, the changed field is saved to the $ ziduan function and a prompt is added.
$ Ziduan = $ ziduan. $ litab [dest_column];
$ Boole = false;
Break;
}
}
If (! $ Boole ){
$ Datalist = $ datalist. "Target Field Not Found". $ ziduan. ", configuration error! ";
} Else {

// Clear old data
$ SQL = str_replace ("| datamonth |", $ datamonth, $ li1 [sqldeldest]); // delete the configuration statement
// Echo $ SQL;
// Mysql_query ($ SQL) or die (mysql_error ());
If (mysql_query ($ SQL )){
// Echo mysql_query ($ SQL );
$ Datalist = $ datalist. "The original data table has been cleared!
";
$ Sql1 = $ li1 [remarks]; // The configured query statement.
$ Re = str_replace ("| datamonth |", $ datamonth, $ sql1); // replace the character

$ List1 = $ db-> query ($ re );
// Mysql_query ($ re) or die (mysql_error ());
$ Jx = M ($ li1 [desttable]);


Foreach ($ list1 as $ key => $ li2)
{
// $ User = A ($ li1 [tableno]); // instantiate the action Controller object


Foreach ($ listtab as $ key => $ litab ){
If (! Empty ($ litab [mark]) {// The default field name mark in the target table.
// 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; // Number of successful records
}


// Echo date ("Y-m-d h: I: s ")."
". $ Li1 [tableprofile]." The settlement is complete! Successful ". $ success." pen data.
";

$ Datalist = $ datalist. date ("Y-m-d h: I: s ")."
". $ Datamonth. $ li1 [tableprofile]." The settlement is complete! Successful ". $ success." pen data.
";

} Else {
$ Datalist = $ datalist. "An error occurred while deleting the statement!
";

}

}

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

Analyze it yourself. Which code is executed too long can be identified.

$ List1 = $ db-> query ($ re );
// Mysql_query ($ re) or die (mysql_error ());
$ Jx = M ($ li1 [desttable]);


Foreach ($ list1 as $ key => $ li2)
{
// $ User = A ($ li1 [tableno]); // instantiate the action Controller object


Foreach ($ listtab as $ key => $ litab ){
If (! Empty ($ litab [mark]) {// The default field name mark in the target table.
// 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; // Number of successful records
}

This section executes the query statement and inserts the result into the target table one by one. I configured a source table, destination table, query statement, and delete statement in a separate table. In this code, locate the configured data, execute the query table, and insert a new table. Four of the data items are relatively large. I ran it quite fast, but I knew that the browser was invalid when four were connected for a long time.

Paste the final generated SQL statement. Clearly describe the table structure and indexes.

Set_time_limit (0 );???

Paste the final generated SQL statement. Clearly describe the table structure and indexes.
Jx_zy_detail_charge_belong, jx_zy_detail_charge_force, jx_zy_detail_charge

This is the first query 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.exe c_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 <>''
From a configuration table, the charge_code and fee_code of the current table are the same in jx_mz_charge_item. if the exec_unit of the configuration table meets the conditions, use this value to replace the department value of the original table.

The second SQL query for conversion: jx_zy_detail_charge_force-"jx_zy_detail_charge
Select data_month, inpatient_no, charge_date, op_id_code, charge_code, charge_amount, 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_amount, 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_amount, 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_amount, 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 |'

Change the exec_unit field of the execution Department configured in the jx_code_ctl table, and the bill_code field of the billing class to exec_unit, which is the data of the execution Department configured in the jx_code_ctl table, to dept_code

There are two groups of tables in total: outpatient and hospitalization. The process is the same. here is the description of the hospitalization process. The first hop SQL of my single-run outpatient and hospitalization, then run the second SQL statement in the outpatient service and hospitalization, which can be completed in about one hour, but it takes a long time for the four to run together, and the page will also go down.

The background execution generates the required data and the foreground displays the results.

The background execution generates the required data and the foreground displays the results. Yes, that is, it takes a lot of time for me to run the four largest data volumes together, and it is much faster to run two or two.

Use the EXPLAIN command to check your SQL command and see what advice mysql will give you.

Use the EXPLAIN command to check your SQL command and see what advice mysql will give you.