2. PHPRunner General questions
  3. phprunner and php performance settings
This topic is locked

phprunner and php performance settings

 		1/16/2021 6:00:28 PM
PHPRunner General questions
ruzgarajans author

Hello,

I'm copying data to the spreadsheet with the code below.

I'm getting a 360 execution time error.

How do you think a solution should be.

button code:

while($record = $button->getNextSelectedRecord())

{

$sql = ("INSERT INTO copy_1 (2, 3, 4, 5, 6, 7, 8, 9, 10,11) values

('".($record["2"])."','".($record["3"])."','".($record["4"])."','".($record["5"])."',

'".($record["6"])."','".($record["7"])."','".($record["8"])."','".($record["9"])."','".($record["10"])."','".($record["11"])."')

");

CustomQuery($sql);
}

$result["txt"] = $params["txt"]." GÖNDERİM BAŞARILI";



(tag names shown as numbers)
in this example it sent 300 records. then he gave an error.

What should I do for 10,000 records
A
 		acpan 1/16/2021

Try to extend the script execution time before start (and remember to set it back after that), eg:

set_time_limit(18000);
your codes ...
// set back

set_time_limit(1800);
Google for more info on PHP set_time_link Or check this link for php.ini settings:

https://www.geeksforgeeks.org/maximum-execution-time-taken-by-a-php-script/
ruzgarajans author 1/17/2021

thank you very much...
Very helpful. Professional solution.

I have a question

so how can I be faster.
there are only village and town and city names

Too long for only 10 fields with varchar 55 and int datatype
A
 		acpan 1/17/2021

It seems your SQL statement is not formed correctly, your $sql should be a string, but you put brackets around it. I suggest you echo out the SQL statements and run manually in your database server and see if you still face the same issue. Likely you may get an error and the loop could not continue and timeout.
However, if your data rows are huge, then you should consider bulk insert into, forming all the data values in the while loop and insert only once using one single statement outside the loop,like so:



INSERT INTO projects(name, start_date, end_date)

VALUES

('AI for Marketing','2019-08-01','2019-12-31'),

('ML for Sales','2019-05-15','2019-11-20');



Where the rows after VALUES are the collected data in the while loop to form as value lists for single insert after the While loop.
For your case, you can issue one single insert command after the while loop, with all data collected, instead of execute insert in every while loop:



$sql = "";

while($record = $button->getNextSelectedRecord())

{

$sql .= "('" .($record["2"])."','".($record["3"])."','".($record["4"])."','".($record["5"])."',

'".($record["6"])."','".($record["7"])."','".($record["8"])."','".($record["9"])."',

'".($record["10"])."','".($record["11"])."')" ;

// add a comma every value lists

$sql .= ",";

}
// Out of the loop, all data collected, remove the last unwanted comma.
$sql = rtrim($sql,",");
// Form the final SQL statement.
$sql = "INSERT INTO copy_1 (2, 3, 4, 5, 6, 7, 8, 9, 10,11) values " . $sql;
// execute

CustomQuery($sql);


The size of the SQL statement is limited by SQL server's setting max_allowed_packet in general. If you want to do 10,000 records in one insert, you may have to increase max_allowed_packet or you can also break the single insert into say 100 records per insert, that will still speed up a lot since you need to execute insert only 100 times vs 10,000 times.
ruzgarajans author 1/18/2021

Hello again,

I will rearrange my tables. then i will try your code. (There may be a problem with the table view)

this makes perfect sense.

I will explain the results here.

thank you
Sergey Kornilov admin 1/18/2021

This has nothing to do with PHP or PHPRunner performance, just a poorly written code. If you insert records one by one it will be very slow. You need to find a better way to insert multiple records.
There are always many options to do so.

  1. It looks like you are moving data from one table to another. MySQL has a handy syntax that will allow you to copy multiple records at once from one table to another:

    https://www.mysqltutorial.org/mysql-insert-into-select/
  2. If the above doesn't work you can try another option, MySQL has the syntax that will insert multiple records via a single SQL statement.

    https://www.mysqltutorial.org/mysql-insert-multiple-rows/
    Maybe you can build your SQL queries the way they insert ten records at once.
  3. There is also an option to load data from the text file but this is probably too much for this kind of task:

    https://dev.mysql.com/doc/refman/8.0/en/load-data.html

    1 or #2 should do the job.