Is there anyone who can help me make this PHP script run faster?

richardm

New member
I have a database table with about 7k rows/entries. There is one column, which I have to fill it in with calculated values. I have therefore created a PHP script to do this for each row by calling a function to do the calculation and then UPDATE the entry, but it takes quite long to finish. I am therefore asking for ways to make it faster.

NB: I have also put the function in custom_functions.php for automatic updating of field during insert or update event.

Thank you in advance.

Richie



[pre]
```
// Create connection to Database
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn)
{
die("Connection failed: " . mysqli_connect_error());
}

//Prepare SQL guerry for selecting a few columns;
$sql = "SELECT `dadabik_id`, `sales_agreement_number`, `sales_date`, `ERs` FROM $ccer_table WHERE 1 ORDER BY `dadabik_id`";

$result = mysqli_query($conn, $sql);

// Start working on rows - output data of each row
if (mysqli_num_rows($result) > 0)
{

while ($row = mysqli_fetch_assoc($result))
{
$sales_date = $row["sales_date"];
$dadabik_id = $row["dadabik_id"];
$end_of_monitoring_period = "2020-07-30";
$ERs = dadabik_get_ERs_emissions_reductions($sales_date, $end_of_monitoring_period);

$sql = "UPDATE $ccer_table SET `ERs`= $ERs WHERE `dadabik_id` = $dadabik_id";
if (mysqli_query($conn, $sql))
{
echo " Record updated successfully on table " . $ccer_table . " with ID = " . $dadabik_id . "<br>";
}
else
{
echo " Error updating record: " . mysqli_error($conn);
}
}
}
else
{
echo "0 results";
}
mysqli_close($conn);



function dadabik_get_ERs_emissions_reductions($sales_date, $end_of_monitoring_period)
{
$NCV_biomass = 0.015;
$EF_projected_fossilfuel = 81.6;
$fNRB_y = 77 / 100;
$N_old = 10;
$N_new_xyear_old = 28.59;
$N_new_1year_old = 28.59;
$N_new_2year_old = 28.59; // asumptions only!
$N_new_3year_old = 28.59; // asumptions only!
//$Fuel Savings=(1 - $N_old / $N_new_xyear_old) = 65%;
$Fuel_Savings = 65 / 100;
$C_y_fueltype_region_old = 2.275; //GACC Case Project Baseline Study Report for Rwanda (Aug. 2008)
$U = 100 / 100; // Usage Survey
$L = 95 / 100; //AMS II.G./Version 03 clause 23
$Applied_Weighted_Factor_for_Continued_Use_of_Baseline = 99.99 / 100;
$Days_in_a_Full_Year = 365;
$Commissioning_Date_Delay = 9;
$sales_date = $sales_date;
$sales_date_s = strtotime($sales_date);
$end_of_monitoring_period_s = strtotime($end_of_monitoring_period);

$datediff = $end_of_monitoring_period_s - $sales_date_s;
$Crediting_Days = round($datediff / (60 * 60 * 24)) - $Commissioning_Date_Delay;
$t_fraction = $Crediting_Days / $Days_in_a_Full_Year;
$N = $U * $t_fraction;
$B_old = $N * $C_y_fueltype_region_old * $L;
$B_y_savings = $B_old * $Fuel_Savings; // simplified
$B_y_savings = $B_old * (1 - $N_old / $N_new_xyear_old);
$ER_y = $B_y_savings * $fNRB_y * $NCV_biomass * $EF_projected_fossilfuel;

// English notation without thousands separator
$ER_y_english_format_number = number_format($ER_y, 2, '.', '');
return $ER_y_english_format_number;
}
```
[/pre]
 

richardm

New member
You are using DaDaBIK version 10.2-Manarola enterprise, installed on 16-07-2020 (installation code: 156855ef91af98bfec), the latest version of DaDaBIK is 10.2-Manarola released on 15-07-2020

You are runnning the last release of DaDaBIK

richardm Wrote:
-------------------------------------------------------
> I have a database table with about 7k rows/entries
> . There is one column, which I have to fill it in
> with calculated values. I have therefore created a
> PHP script to do this for each row by calling a fu
> nction to do the calculation and then UPDATE the e
> ntry, but it takes quite long to finish. I am ther
> efore asking for ways to make it faster.
>
> NB: I have also put the function in custom_functio
> ns.php for automatic updating of field during inse
> rt or update event.
>
> Thank you in advance.
>
> Richie
>
>
System info: PHP Version: 7.3.20 / mysql v > ersion: 5.5.5-10.4.13-MariaDB / Web server: Apache > /2.4.43 (Win64) OpenSSL/1.1.1g PHP/7.3.20 / Client > : Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:79. > 0) Gecko/20100101 Firefox/79.0 said:
>
>
> [pre]
> ```
> // Create connection to Database
> $conn = mysqli_connect($servername, $username, $pa
> ssword, $dbname);
> // Check connection
> if (!$conn)
> {
> die("Connection failed: " . mysqli_connect_err
> or());
> }
>
> //Prepare SQL guerry for selecting a few columns;
> $sql = "SELECT `dadabik_id`, `sales_agreement_numb
> er`, `sales_date`, `ERs` FROM $ccer_table WHERE 1
> ORDER BY `dadabik_id`";
>
> $result = mysqli_query($conn, $sql);
>
> // Start working on rows - output data of each row
> if (mysqli_num_rows($result) > 0)
> {
>
> while ($row = mysqli_fetch_assoc($result))
> {
> $sales_date = $row["sales_date"];
> $dadabik_id = $row["dadabik_id"];
> $end_of_monitoring_period = "2020-07-30";
> $ERs = dadabik_get_ERs_emissions_reduction
> s($sales_date, $end_of_monitoring_period);
>
> $sql = "UPDATE $ccer_table SET `ERs`= $ERs
> WHERE `dadabik_id` = $dadabik_id";
> if (mysqli_query($conn, $sql))
> {
> echo " Record updated successfully o
> n table " . $ccer_table . " with ID = " . $dadabik
> _id . "<br>";
> }
> else
> {
> echo " Error updating record: " . my
> sqli_error($conn);
> }
> }
> }
> else
> {
> echo "0 results";
> }
> mysqli_close($conn);
>
>
>
> function dadabik_get_ERs_emissions_reductions($sal
> es_date, $end_of_monitoring_period)
> {
> $NCV_biomass = 0.015;
> $EF_projected_fossilfuel = 81.6;
> $fNRB_y = 77 / 100;
> $N_old = 10;
> $N_new_xyear_old = 28.59;
> $N_new_1year_old = 28.59;
> $N_new_2year_old = 28.59; // asumptions only!
> $N_new_3year_old = 28.59; // asumptions only!
> //$Fuel Savings=(1 - $N_old / $N_new_xyear_old
> ) = 65%;
> $Fuel_Savings = 65 / 100;
> $C_y_fueltype_region_old = 2.275; //GACC Case
> Project Baseline Study Report for Rwanda (Aug. 200
> 8)
> $U = 100 / 100; // Usage Survey
> $L = 95 / 100; //AMS II.G./Version 03 clause 2
> 3
> $Applied_Weighted_Factor_for_Continued_Use_of_
> Baseline = 99.99 / 100;
> $Days_in_a_Full_Year = 365;
> $Commissioning_Date_Delay = 9;
> $sales_date = $sales_date;
> $sales_date_s = strtotime($sales_date);
> $end_of_monitoring_period_s = strtotime($end_o
> f_monitoring_period);
>
> $datediff = $end_of_monitoring_period_s - $sal
> es_date_s;
> $Crediting_Days = round($datediff / (60 * 60 *
> 24)) - $Commissioning_Date_Delay;
> $t_fraction = $Crediting_Days / $Days_in_a_Ful
> l_Year;
> $N = $U * $t_fraction;
> $B_old = $N * $C_y_fueltype_region_old * $L;
> $B_y_savings = $B_old * $Fuel_Savings; // simp
> lified
> $B_y_savings = $B_old * (1 - $N_old / $N_new_x
> year_old);
> $ER_y = $B_y_savings * $fNRB_y * $NCV_biomass
> * $EF_projected_fossilfuel;
>
> // English notation without thousands separato
> r
> $ER_y_english_format_number = number_format($E
> R_y, 2, '.', '');
> return $ER_y_english_format_number;
> }
> ```
> [/pre]
 

larryk

Well-known member
What is a "long time" ?

a loop to do 7000 updates, shouldn't take too long?
The function doesn't look bad... just calculations?

Currious, why do you have an order by clause? not needed, as every record will be updated anyway?

How is your server? Mabye it is not the SQL or update code, but rather the server itself?
 

richardm

New member
Dear LarryK,

Thank you very much for your reply.

Let me respond to your questions quickly:

1- What's a long time? - I said so because it was taking longer than the max_execution_time in php.ini of 300s. I had to increase it to 600s, I had to increase it to

2- ORDER BY clause: Yes, I see your point. There is no a need for this. I will remove it.

3- I am using Windows Version: Enterprise 64-bit with the following installations:
XAMPP Version: 7.3.20
Apache/2.4.43 (Win64)
PHP/7.3.20
mysqlnd 5.0.12-dev - 20150407


Thanks
 

larryk

Well-known member
wondering.... probably a pain to put a stop/start timer (in millie seconds) for each of those calculations in the function? Or maybe just group them... say, the beginning, middle, end? just in case some calculation is the cause?

I can't see any of those being an issue, BUT that isn't the point.. the point is to KNOW for a fact :)
that none of those calculations are the cause.

By server, I was meaning... CPU and memory of the server? Just to make sure hardware isn't an issue.


bottlenecks?:
- code/script
- functions/calculation
- server, processing power
- logic issue
- DB optimation
 

deep64blue

DaDaBIK Guru
Are you doing this within Dadabik itself? If so you don't need to crerate a connection yourself - the variable $conn already has a connection to the database so you would be creating another one. I doubt that's the problem but thought I would mention it anyway.
 

richardm

New member
deep64blue Wrote:
-------------------------------------------------------
> Are you doing this within Dadabik itself? If so y
> ou don't need to crerate a connection yourself - t
> he variable $conn already has a connection to the
> database so you would be creating another one. I
> doubt that's the problem but thought I would menti
> on it anyway.

Thank you for your reply. I was running it outside Dadabik, that is why I needed the connection.

I can now run the script in 2 seconds; Maybe the two connections were interfering each other?
 

richardm

New member
Thank you for your reply.

I can now run the script in 2 seconds and sometimes 26 seconds; Maybe the delay was caused by having multiple connections to the table at the same time. I am also not sure if echoing/printing each row increases the execution time.
 
Top