Santhosh Prabu Santhosh Prabu -4 years ago 194
MySQL Question

Mysql taking long time to insert and update Yii Framework

want to insert or update nearly 10000 records. It takes too long to insert in the for loop. Trying for the aternative way it only inserts the values but on duplicate_key entry shows the errors.

$var=0;

if($method == 1)
{

// $sql5 is the current way trying but shows error

$sql5='insert into stu_regnum_hall(hall_name,row,column,register_number,subject_code,exam_date,session,unique_key) values';
for ($i = 0; $i <=$arr_length; $i++)
{
for ($j = 1; $j <= 5; $j++)
{
for($k=0;$k<=4;$k++)
{
if(isset($show[$var+$k]))
{
if($k==0)
{
$hall_name=$arr1[$i];
$row=$j;
}
$column=$k+1;
$reg_num=$show[$var+$k];
$sub_code=$newArr12[$var+$k];
$unique_key=$reg_num.$date.$session;

//current way to insert This is taking too long time to store nearly 10000 records

Yii::$app->db->createCommand('insert into stu_regnum_hall(hall_name,row,column,register_number,subject_code,exam_date,session,unique_key) values ("'.$hall_name.'","'.$row.'","'.$column.'","'.$reg_num.'","'.$sub_code.'","'.$date.'","'.$session.'","'.$unique_key.'") ON DUPLICATE KEY UPDATE hall_name ="'.$hall_name.'",row="'.$row.'",column="'.$column.'",register_number="'.$reg_num.'",subject_code="'.$sub_code.'",exam_date="'.$date.'",session="'.$session.'",unique_key="'.$unique_key.'"')->query();

//concatenation of $sql5
$sql5.='("'.$hall_name.'","'.$row.'","'.$column.'","'.$reg_num.'","'.$sub_code.'","'.$date.'","'.$session.'","'.$unique_key.'") ON DUPLICATE KEY UPDATE hall_name= "'.$hall_name.'",row="'.$row.'",column="'.$column.'",register_number="'.$reg_num.'",subject_code="'.$sub_code.'",exam_date="'.$date.'",session="'.$session.'",unique_ key="'.$unique_key.'",';
}
else
{
break;
}
}
$var=$var+5;
}
}

$sql5.='("");';

// This is the required answer but getting error
Yii::$app->db->createCommand($sql5)->query();
}


the $sql5 query should work in this

Answer Source

Try Yii2 batchInsert with a little custom use INSERT IGNORE, REPLACE or ON DUPLICATE KEY UPDATE. It will insert very fast.

$sql = Yii::$app->db->queryBuilder->batchInsert('your_table_name', ['column1', 'column2','column3',...], $dataArray);
// For IGNORE on Duplicate
//$sql = 'INSERT IGNORE' . mb_substr($sql, strlen('INSERT'));
// For Replace on Duplicate
//$sql = 'REPLACE' . mb_substr($sql, strlen('INSERT'));
// For Update on Duplicate
$sql .= ' ON DUPLICATE KEY UPDATE `column1` = VALUES(`column1`), `column2` = VALUES(`column2`), ...';

Yii::$app->db->createCommand($sql)->execute();

Refer: http://www.yiiframework.com/doc-2.0/yii-db-querybuilder.html#batchInsert()-detail

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download