Slow data insertion into mysql database

I am trying to make a csv upload page for an application that Im building. It needs to be able to upload thousands of rows of data in seconds each row including a first name, last name, and phone number. The data is being uploaded to a vm that is running ubuntu server. When I run the script to upload the data it takes almost 2 minutes to upload 1500 rows. The script is using PDO, I have also made a test script in python to see if It was a php issue and the python script is just as slow. I have made csv upload scripts in the past that are exactly the same that would upload thousands of rows in seconds. We have narrowed the issue down to the script as we have tested it on other vms that are hosted closer to us and the issue still persist. Is there an obvious issue with the script or PDO that could be slowing it down? Below is the code for the script.

<?php

$servername =[Redacted];
$username = [Redacted];
$password = [Redacted];

try {
    $conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
} catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

echo print_r($_FILES);

$fileTmpPath = $_FILES['fileToUpload']['tmp_name'];
$fileName = $_FILES['fileToUpload']['name'];
$fileSize = $_FILES['fileToUpload']['size'];
$fileType = $_FILES['fileToUpload']['type'];

$CSVfp = fopen($fileTmpPath, "r");

$final_array = [];
while (($data = fgetcsv($CSVfp)) !== false) {


    $recived = [];
    foreach ($data as $i) {
        array_push($recived, $i );
    }
    array_push($final_array, $recived);
}
echo print_r($final_array);

fclose($CSVfp);


$non_compliant_rows = [];



foreach ($final_array as $key => $row){

    $fname = preg_replace('/[^A-Za-z0-9]/', "", $row[0]);
    $lname = preg_replace('/[^A-Za-z0-9]/', "", $row[1]);
    $mobileNumber = preg_replace( '/[^0-9]/i', '', $row[2]);
    $sanatized_row = array($fname, $lname, $mobileNumber);
    $recived[$key] = $sanatized_row;
    if (strlen($mobileNumber) > 10 or strlen($mobileNumber) < 9){


        array_push($non_compliant_rows, $final_array[$key]);
        unset($final_array[$key]);


    }

}
$final_array = array_values($final_array);
echo print_r($final_array);



foreach($final_array as $item){
    try{


        $stmt = $conn->prepare("INSERT INTO bulk_sms_list(fname, lname, pn, message, send) VALUES (?, ?, ?, 'EMPTY', 1) ;");
        $stmt->execute($item);

    }catch(PDOException $e){
        echo $e;
    }

}
echo "done";

It looks like you’re doing each INSERT one at a time. Refactoring the insertion code to send a single batch insert will speed that up massively.

What you’re asking is kinda out of scope for MODX as you’re not using anything MODX specific there, so I’d suggest broadening your search. Here are some similar discussions on StackOverflow with suggestions and example code.

Thank you for your time. I will make a stack overflow question. The issue with batch insertion is that if one row errors then all of the other rows in the insertion won’t get inserted. Is there any way to make it so if a row doesn’t agree with a constraint then only that row gets errored?

Check your constraints when parsing the CSV into $final_array.

This topic was automatically closed 2 days after discussion ended and a solution was marked. New replies are no longer allowed. You can open a new topic by clicking the link icon below the original post or solution and selecting “+ New Topic”.