PHP script taking too long. Need someone to take a look at my code to optimize it

Question

I have a script that fetches data from a remote restful API and saves it to the local database. I get the reponse from the server in chunks of 2000 so i have to check if a nextRecordsUrl exists so i can send the next GET request to receive the next batch. This script takes around 80s in total and the next part of the code causes 99% of it:

//GET request salesforce
function GetData($query = NULL, $nextUrl = NULL)
{ 
    global $start;
    echo('Sending CURL GET request '.(microtime(true) - $start).PHP_EOL);

    global $token;
    $sforgurl = 'https://salesforce.com';
    $sfversie = 'v49.0';
    $sfquery = ($query != NULL ? urlencode($query) : NULL);
    $sfurl = ($nextUrl == NULL ? $sforgurl.'/services/data/'.$sfversie.'/query/?q='.$sfquery : $sforgurl.$nextUrl);
  
    $curl = curl_init();
    curl_setopt_array($curl, array(
      CURLOPT_URL => $sfurl,
      CURLOPT_RETURNTRANSFER => true,
      CURLOPT_ENCODING => '',
      CURLOPT_MAXREDIRS => 10,
      CURLOPT_TIMEOUT => 0,
      CURLOPT_FOLLOWLOCATION => true,
      CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
      CURLOPT_CUSTOMREQUEST => 'GET',
      CURLOPT_HTTPHEADER => [$token, 'Cookie: BrowserId=tXRPlzlEEeuNeP289joTXg'],
    ));
  
    $response = json_decode(curl_exec($curl));
    curl_close($curl);

    echo('Returning GET data '.(microtime(true) - $start).PHP_EOL);
    return ['url' => (empty($response->nextRecordsUrl) ? NULL : $response->nextRecordsUrl),
            'data' => $response->records];
};

//Insert data
$InsertData = function($getQuery, $query, $target) use($localLink, $tableTelephoneNumber, $tableDeliveryContract, $tableContract, $start)
{
    echo('Starting INSERTDATA() '.(microtime(true) - $start).PHP_EOL);
    $data = GetData($getQuery);  
    foreach ($data['data'] as $value) { 
        if($target == $tableTelephoneNumber) {
            $query->bind_param('ss', $value->Number__c, $value->DeliveryContract__r->Id);
        } elseif($target == $tableDeliveryContract) {
            $query->bind_param('ss', $value->Id, $value->Contract__r->Id);
        } else {
            $query->bind_param('ssss', $value->Id, 
                                        $value->Account->Id, 
                                        $value->Account->Name, 
                                        $value->Account->ExactID__c);
        }

        $query->execute();
    }

    // Check if nextRecordsUrl is received from the salesforce.
    while($data['url'] != NULL) {
        $data = GetData(NULL, $data['url']);  
        foreach ($data['data'] as $value) { 
            if($target == $tableTelephoneNumber) {        
                $query->bind_param('ss', $value->Number__c, 
                                        $value->DeliveryContract__r->Id);
            } elseif($target == $tableDeliveryContract) {     
                $query->bind_param('ss', $value->Id, $value->Contract__r->Id);
            } else {
                $query->bind_param('ssss', $value->Id, 
                                        $value->Account->Id, 
                                        $value->Account->Name, 
                                        $value->Account->ExactID__c);
            }
            $query->execute();
        }
    }
    echo('Completed INSERTDATA() '.(microtime(true) - $start).PHP_EOL);
};

//Truncate tables
mysqli_query($localLink, "TRUNCATE TABLE $tableTelephoneNumber");
mysqli_query($localLink, "TRUNCATE TABLE $tableDeliveryContract");
mysqli_query($localLink, "TRUNCATE TABLE $tableContract");

//set time
$now = date("Y-m-d");
$date = (new DateTime())->modify('first day of last month')->format('Y-m-d');

//prepare and trigger TelephoneNumber insert
$insertQuery = "INSERT INTO $tableTelephoneNumber 
                        (TelephoneNumber, DeliveryContractId) 
                VALUES (?,?)";
$prepareQuery = $localLink->prepare($insertQuery);
$get = "SELECT Number__c, DeliveryContract__r.Id 
        FROM TelephoneNumber__c";
$InsertData($get, $prepareQuery, $tableTelephoneNumber);

//prepare and trigger DeliveryContract insert
$insertQuery = "INSERT INTO $tableDeliveryContract 
                            (DeliveryContractId, ContractId) 
                     VALUES (?,?)";
$prepareQuery = $localLink->prepare($insertQuery);
$get = "SELECT Id,Contract__r.Id 
        FROM Delivery_Contract__c 
        WHERE EndDate__c >= " . $now . " 
        OR EndDate__c >= ". $date ."";
$InsertData($get, $prepareQuery, $tableDeliveryContract);

//prepare and trigger Contract insert
$insertQuery = "INSERT INTO $tableContract 
                            (ContractId, AccountId, AccountName, 
                            AccountExactId)
                     VALUES (?,?,?,?)";
$prepareQuery = $localLink->prepare($insertQuery);
$get = "SELECT Id,Account.Id, Account.Name, Account.ExactID__c 
        FROM Contract";
$InsertData($get, $prepareQuery, $tableContract);

Good code indentation would help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](https://www.php-fig.org/psr/psr-12/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. — RiggsFolly, Dec 29 '20 at 14:11
WHY the replace nonsense in `$now = str_replace("/", "-", date("Y/m/d"));` a simple `$now = date("Y-m-d");` would seem a whole lots simpler — RiggsFolly, Dec 29 '20 at 14:15
And `$date = (new DateTime())->modify('first day of last month')->format('Y-m-d');` — RiggsFolly, Dec 29 '20 at 14:19
How much of you 99% is taken running the CURL? Because you cannot speed up how fast another site responds to your requests. And with potentially multiple calls to that Curl function that would explain a lot — RiggsFolly, Dec 29 '20 at 14:21
@RiggsFolly [here's](https://imgur.com/a/P85wlh0) a screenshot of the timing in microtime. — Neavehni, Dec 29 '20 at 14:44
It would be useful (I only have a little brain) to see where those debug lines are actually in the code — RiggsFolly, Dec 29 '20 at 14:46
Looks like its taking roughly half a second to do each curl. Multiply that by the number you are asking it to do, and how many seconds do you get? — RiggsFolly, Dec 29 '20 at 14:53
@RiggsFolly i've updated the code with. I've added `echo` at the start and end of `GetData` and `InsertData` functions. — Neavehni, Dec 29 '20 at 15:09
In total there are around 22 CURL requests. So it will take around 11 seconds. — Neavehni, Dec 29 '20 at 15:11
Why not to do inserts in batch? For example, [like this](https://stackoverflow.com/questions/15149157/best-way-to-insert-many-values-in-mysqli). You should profile the code to identify most time-consuming parts not to guess what is the most valuable part to look at. — astentx, Dec 29 '20 at 20:04

score 0 · Accepted Answer · answered Dec 31 '20 at 14:11

0

I went from ~80 seconds to ~14 seconds by dumping the data into a .csv file and importing it using LOAD INTO INFILE test.csv INTO TABLE mytable.

answered Dec 31 '20 at 14:11

Neavehni

337
1
2
14

PHP script taking too long. Need someone to take a look at my code to optimize it

1 Answers1