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

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);

Answer

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.

Advertisement

Answer