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