Batch Insert in PHP MySQLi

Batch Insert in PHP MySQLi

When I was using oracle database long time ago, I know batch insert is much faster if we do know the data we want to insert. Recently I was using PHP 5.6 and MySQL. I found out that the batch insert is hundreds of times faster.

Here is how I do the batch insert.
public function batchInsertDailyInfo($dailySpends){
$logger = $this->ioc->getService("logger");

$conn = $this->getJobDBConn();
$query = "
INSERT INTO
daily_spendings (date, job_id, daily_spending) VALUES ";
$i = 0;
foreach ($dailySpends as $spend){
if($i > 0){
$query .= ", ";
}
$i++;
$query .= "('" . "{$spend['date']}". "', {$spend['job_id']}, {$spend['spending']})";
if($i > $this->batchSize){
$conn->query($query);
$i = 0;
$query = "
INSERT INTO
daily_spendings (date, job_id, daily_spending) VALUES ";
}
}

if($i > 0){
$conn->query($query);
}

$this->closeDBConn($conn);
}

And I have a very simple PHPUNIT code to test the perf.
//501 ms, Memory: 38.50MB, 35000 rows batch insert
public function testBatchInsertDailyInfo(){
$jobIDs = 4294967295;
$date = "2016-08-23";
$spending = 12.36;

$spendings = array();

//35000
for($i = 0; $i<35021; $i++){
$spendings[] = array(
'job_id' => $jobIDs + $i,
'date' => $date,
'spending' => $spending,
);
}

$this->mySQLDAO->batchInsertDailyInfo($spendings);

}

It should relate to the size of each row as well. But this works for me in this requirement.

There is one more option, we can prepare and bind_param as well.
/**

*

* @param unknown $dailySpends

* @return unknown

*/

public function batchInsertDailyInfo3($dailySpends){

$logger = $this->ioc->getService("logger");


$conn = $this->getJobDBConn();


$n = 0;

$values = array();

$params = array();

foreach ($dailySpends as $spend){

//prepare values

$values = array_merge($values,array($spend['date'], $spend['job_id'], $spend['spend']));

$n++;

if($n > $this->batchSize){

$this->batchPrepareInsert($conn, $values, $n);

$values = array();

$params = array();

$n = 0;

}

}


if($n > 0){

$this->batchPrepareInsert($conn, $values, $n);

}

$this->closeDBConn($conn);

}


private function batchPrepareInsert($conn, $values, $n){

$query = "

INSERT INTO

daily_spendings (date, job_id, daily_spending) VALUES(?, ?, ?)".str_repeat(",(?,?,?)",$n-1);

$stmt = $conn->prepare($query);


//prepare types and bind params

$types = str_repeat("sid", $n);

$params[] = &$types;

for($i = 0; $i < count($values); $i++) {

$params[] = & $values[$i];

}

//bind params

call_user_func_array(array($stmt, 'bind_param'), $params);

//excute batch

$stmt->execute();
}

References:
http://stackoverflow.com/questions/19512498/mysqli-multiple-row-insert-simple-multi-insert-query