php inserting data in mysql from json runs too slowly

Question

I have the following code to read a JSON and store the results in a DDBB.
The code works, but it takes more than a minute to insert just 400 records.
If I open the json, it loads pretty fast.
What I'm doing wrong?

    $db = new PDO('', '', '');
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    if(tableExists($db, 'locations') == 1)
    {
        $sql=$db->prepare("DROP TABLE locations");
        $sql->execute();
    }
    $sql ="CREATE TABLE `locations` (id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, evid INT(6) NOT NULL, place VARCHAR(150), country VARCHAR(150), reg_date TIMESTAMP)" ;
    $db->exec($sql);
    $json = file_get_contents('thejson.php');
    $data = array();
    $data = json_decode($json); 
    foreach ($data as $key => $object) 
    {
        if(is_object($object))
        {
            $id = $object->id;
            $place = $object->name;
            $country = substr(strrchr($object->name, "-"), 2);
            $stmt = $db->prepare("INSERT INTO `locations` (evid, place, country) VALUES (:evid, :places, :country)");
            $stmt->bindValue(':evid', $id, PDO::PARAM_INT);
            $stmt->bindValue(':places', $place, PDO::PARAM_STR);  
            $stmt->bindValue(':country', $country, PDO::PARAM_STR);      
            $stmt->execute();
        }
    }

why don't you insert the full array in single query instead of running a loop for each row — Pavan Jiwnani
– Pavan Jiwnani, Commented Feb 9, 2015 at 12:25
To improve the speed you should not create the key in the beginning. Insert the records without the key. Create the key at the end of the insert process. — Franz Holzinger
– Franz Holzinger, Commented Feb 9, 2015 at 12:45
Pavan Jiwnani, what do you mean exactly? Franz Holzinger, I don't understand what do you mean neither, can you give me an example? — user997593
– user997593, Commented Feb 9, 2015 at 13:29

halfer · Accepted Answer · 2023-11-07 11:31:25Z

2

The first two things I would try would be moving the prepare outside the loop, and wrapping it in a transaction:

try {
    $db->beginTransaction();
    $stmt = $db->prepare("INSERT INTO `locations` (evid, place, country) VALUES (:evid, :places, :country)");

    foreach ($data as $key => $object) 
    {
        if(is_object($object))
        {
            $id = $object->id;
            $place = $object->name;
            $country = substr(strrchr($object->name, "-"), 2);
            
            $stmt->bindValue(':evid', $id, PDO::PARAM_INT);
            $stmt->bindValue(':places', $place, PDO::PARAM_STR);  
            $stmt->bindValue(':country', $country, PDO::PARAM_STR);      
            $stmt->execute();
        }
    }

    $db->commit();

} catch (Exception $e) {
    $db->rollBack();
    throw $e;
}

Another thing you could do is try using bindParam to bind the vars by reference - this way you only need to call bindParam once on each variable name at the start, then just overwrite the values of those variables on each iteration and call execute.

try {
    $db->beginTransaction();
    $stmt = $db->prepare("INSERT INTO `locations` (evid, place, country) VALUES (:evid, :places, :country)");
     $id = 0;
     $place = '';
     $country = '';

     $stmt->bindParam(':evid', $id, PDO::PARAM_INT);
     $stmt->bindParam(':places', $place, PDO::PARAM_STR);  
     $stmt->bindParam(':country', $country, PDO::PARAM_STR); 

    foreach ($data as $key => $object) 
    {
        if(is_object($object))
        {
            $id = $object->id;
            $place = $object->name;
            $country = substr(strrchr($object->name, "-"), 2);
            $stmt->execute();
        }
    }

    $db->commit();

} catch (Exception $e) {
    $db->rollBack();
    throw $e;
}

Similar to this instead of calling bind* you could just pass the values in via execute:

try {
    $db->beginTransaction();
    $stmt = $db->prepare("INSERT INTO `locations` (evid, place, country) VALUES (:evid, :places, :country)");

    foreach ($data as $key => $object) 
    {
        if(is_object($object))
        {
            $params = array(
                ':id' => $object->id,
                ':places' => $object->name,
                ':country' => substr(strrchr($object->name, "-"), 2)
            );
                  
            $stmt->execute($params);
        }
    }

    $db->commit();

} catch (Exception $e) {
    $db->rollBack();
    throw $e;
}

I suspect using a transaction will get you a performance gain, but I don't know that there will be a ton of difference between switching up the binding methods.

Your best bet is probably to insert all the records in a single query as @PavanJiwnani suggests:

// first we need to compile a structure of only items 
// we will insert with the values properly transformed

$insertData = array_map(function ($object) {
     if (is_object($object)) {
        return array(
            $object->id,
            $object->name,
            substr(strrchr($object->name, "-"), 2)
        );
     } else {
       return false;
     }
}, $data);

// filter out the FALSE values
$insertData = array_filter($insertData);

// get the number of records we have to insert
$nbRecords = count($insertData);

// $records is an array containing a (?,?,?) 
// for each item we want to insert
$records = array_fill(0, $nbRecords, '(?,?,?)');

// now use sprintf and implode to generate the SQL like:
// INSERT INTO `locations` (evid, place, country) VALUES (?,?,?),(?,?,?),(?,?,?),(?,?,?)
$sql = sprintf(
    'INSERT INTO `locations` (evid, place, country) VALUES %s',   
    implode(',', $records)
);

$stmt = $db->prepare($sql);

// Now we need to flatten our array of insert values as that is what 
// will be expected by execute()
$params = array();
foreach ($insertData as $datum) {
   $params = array_merge($params, $datum);
}

// and finally we attempt to execute
$stmt->execute($params);

edited Nov 7, 2023 at 11:31

halfer

20.2k20 gold badges110 silver badges207 bronze badges

answered Feb 9, 2015 at 14:13

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user997593 Over a year ago

The last option works like a charm, thanks a lot for your help. Now it looks obvious and smart for me, but I wasn't able to find the solution. Only one thing more "get_object_vars" is needed in order to convert the object into an array. Thanks for your help!

prodigitalson Over a year ago

Hmm.. why did you need get_object_vars? all that should be taken care of with the call to array_map

user997593 Over a year ago

If I use it like you did it, the PHP launches an error complaining about the second argument of array_map not being an array (in fact it is an object)

prodigitalson Over a year ago

Oh.. i thought it was an array array of objects ie. [{"name":"somename","id":1}]... If that is the case you should use json_decode($jsonString, true) then it will decode as an associative array removing the need to use get_object_vars (which i personally find dirty and to be avoided).

Bart Van Den Boomen · Accepted Answer · 2015-02-09 12:38:19Z

0

Try to echo timestamps with milliseconds to see what is running slow. Probably executing 400 insert queries (including opening/closing connections).

answered Feb 9, 2015 at 12:38

Bart Van Den Boomen

536 bronze badges

Comments

Z.J. Cong · Accepted Answer · 2015-02-09 12:53:05Z

0

There are many factors that affects the performance of the database, please provide detailed information about the database system, PHP version and related hardware.

The bottleneck could be at:

file_get_contents('thejson.php')

if the JSON contents are fetched from a remote host, i.e. DB is running normally, network is slow.

You may also want to consider move:

$stmt = $db->prepare("INSERT INTO `locations` (evid, place, country) VALUES (:evid, :places, :country)");

out of the foreach loop.

answered Feb 9, 2015 at 12:53

Z.J. Cong

111 silver badge2 bronze badges

1 Comment

user997593 Over a year ago

I have move the prepare outside of the loop, but this doesn't fix the problem. If I open thejson.php straight, it serves the json quickly, so I'm not sure if this can be the problem. The server is configuration is Linux: PHP 5.3.10, MySQL 5.5.

Collectives™ on Stack Overflow

php inserting data in mysql from json runs too slowly

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related