Batch processing in array using PHP

Question

I got thousands of data inside the array that was parsed from xml.. My concern is the processing time of my script, Does it affect the processing time of my script since I have a hundred thousand records to be inserted in the database? I there a way that I process the insertion of the data to the database in batch?

Answer 1

Its unlikely to affect the processing time, but you'll need to ensure the DB's transaction logs are big enough to build a rollback segment for 100k rows.

Answer 2

Syntax is:

INSERT INTO tablename (fld1, fld2) VALUES (val1, val2), (val3, val4)... ;

So you can write smth. like this (dummy example):

foreach ($data AS $key=>$value)
{
    $data[$key] = "($value[0], $value[1])";
}
$query = "INSERT INTO tablename (fld1, fld2) VALUES ".implode(',', $data);

This works quite fast event on huge datasets, and don't worry about performance if your dataset fits in memory.

Answer 3

This is for SQL files - but you can follow it's model ( if not just use it ) -

It splits the file up into parts that you can specify, say 3000 lines and then inserts them on a timed interval < 1 second to 1 minute or more.

This way a large file is broken into smaller inserts etc.

This will help bypass editing the php server configuration and worrying about memory limits etc. Such as script execution time and the like.

New Users can't insert links so Google Search "sql big dump" or if this works goto: www [dot] ozerov [dot] de [ slash ] bigdump [ dot ] php

So you could even theoretically modify the above script to accept your array as the data source instead of the SQl file. It would take some modification obviously.

Hope it helps. -R

Answer 4

Or with the ADOdb wrapper (http://adodb.sourceforge.net/):

// assuming you have your data in a form like this:
$params = array(
             array("key1","val1"),
             array("key2","val2"),
             array("key3","val3"),
             // etc...
          );
// you can do this:
$sql = "INSERT INTO `tablename` (`key`,`val`) VALUES ( ?, ? )";
$db->Execute( $sql, $params );