Hi, i’m trying to implement a bulk insert in my application and don’t know what’s the proper way to do it.
I want to insert multiple records in two tables at once (1-10.000 aproximately), so i decided to use Yii::$app->db->createCommand()->insert() to do it (seems like this is better suited for this kind of task than ActiveRecord, but not really sure). So at the moment i’m doing something like this:
if(!empty($ids)){
date_default_timezone_set("America/Santiago");
$flag = false;
$transaction = $conexion->beginTransaction();
try{
foreach($ids as $id){
$comando_vinculo = $conexion->createCommand()->insert('vinculo_actividad', [
'act_id' => $evento,
'cargo_id' => $id,
'detfact_id' => 0,
'conins_id' => 0,
'vincact_fechaing' => date('Y-m-d'),
'vincact_horaing' => date('H:i:s'),
'vincact_observaciones' => 'Asistente invitado intranet.',
'vincact_vigencia' => 1]);
$total = $comando_vinculo->execute();
if($total == 1){
$ultimoId = $conexion->getLastInsertID();
$comando_historico = $conexion->createCommand()->insert('historico_vinculoact', [
'tipvin_id' => 1,
'vincact_id' => $ultimoId,
'estvin_id' => 3,
'taract_id' => 0,
'histvin_fecha' => date('Y-m-d'),
'histvin_orden' => 0,
'histvin_precio' => 0,
'histvin_vigencia' => 1]);
$total_hist = $comando_historico->execute();
if($total_hist == 1){
$flag = true;
}
else{
$flag = false;
$transaction->rollback();
break;
}
}
}
if($flag){
$transaction->commit();
}
}catch(\Exception $e){
$transaction->rollback();
throw $e;
}catch(\Throwable $e){
$transaction->rollback();
throw $e;
}
}
Where $ids is an array of foreign keys from another table to be inserted into the new table and $evento is an id from some other table. Right now this works and inserts the data into the corresponding tables, but when i try to insert a high amount of rows (more than 1000) it gives me PHP fatal error: maximum execution time of 30 seconds exceeded. I know i can modify the maximum execution time to “solve” this issue, but i’m wondering what’s the best way to implement this so the inserts takes the less amount of time possible.
I was reading the docs and found out about the batch insert methods, but don’t know if that’s what i should be using (never used batch insert before).
Thanks in advance for any help.