junkk rr junkk rr - 4 months ago 8
MySQL Question

Nested Foreach loop taking too much time

I am using the Nested Foreach loop to store the data in the mysql. But its taking too much processing time. How i can reduce the maximum execution time.

foreach ($results as $r) {
mysqli_query($con,"insert into commercial values('".mysqli_real_escape_string($con,$r['MST_MLS_NUMBER'])."')");
$val=1;
$objects = $rets->GetObject('Property', 'Photo', $r['MST_MLS_NUMBER'], '*', 0);
foreach ($objects as $pho) {
mysqli_query($con,"insert into cmtval values('".mysqli_real_escape_string($con,$r['MST_MLS_NUMBER'])."')");
}
}

Answer

You should use bulk insert

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

By foreach loop you should make first query to execute and then execute query with mysqli_query.

$query1 = "insert into commercial values ";
$query2 = "insert into cmtval values ";
foreach ($results as $r)
{
     $query1 .= "('" . mysqli_real_escape_string( $con, $r['MST_MLS_NUMBER']) . "'), ";

     $val=1;
$objects = $rets->GetObject('Property', 'Photo', $r['MST_MLS_NUMBER'], '*', 0);
  foreach ($objects as $pho)
  {
     $query2 .= "('" . mysqli_real_escape_string( $con, $r['MST_MLS_NUMBER']) . "'), ";
  }
}

mysqli_query($con, $query1);
mysqli_query($con, $query2);

I haven't tested code. Test and let me know if anything is missing. Batch updates reduces some time.

Also if you are saving too much data in DB in single query and if you too much indexes it takes time to insert data.

Comments