Nested Foreach loop taking too much time

Question

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'])."')");
    }
}

Is `$r['MST_MLS_NUMBER']` and `$pho['MST_MLS_NUMBER']` a different value inside single loop ? — jitendrapurohit, Aug 03 '16 at 07:15
No its a similar value for one time processing inside single loop. i have edited the question. — junkk rr, Aug 03 '16 at 07:17
@Anant Actually i am using the library to store the MLS data. The reason being to take the nested loop is that i need to store the images related to same mls number at the time of inserting the records. The Image Object Calling is different in the library. — junkk rr, Aug 03 '16 at 07:20

Somnath Muluk · Accepted Answer · 2016-08-03T07:37:52.613

0

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.

edited Aug 03 '16 at 07:37

answered Aug 03 '16 at 07:16

Somnath Muluk

55,015
38
216
226

It's much better way than mine above. but @somnath its taking time too. it may be because i am storing the base64 encoded image data as a type BLOB within the same query.? – junkk rr Aug 03 '16 at 07:32
That's why its taking time. because base64 encoded image taking approx 39000 words in a single tuple. – junkk rr Aug 03 '16 at 07:41
@junkkrr Also see https://www.quora.com/Why-would-an-INSERT-query-be-slow-in-MySQL and http://stackoverflow.com/questions/9722603/storing-image-in-database-directly-or-as-base64-data – Somnath Muluk Aug 03 '16 at 07:42
Ok bro. Thanx for your help. :-) – junkk rr Aug 03 '16 at 07:48

score 0 · Answer 2 · answered Aug 03 '16 at 07:18

you can make something like this

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);


    // generate partial query strings for insert multiple records
    $numbers=array();
    foreach ($objects as $pho)
    {
        $numbers[]= "('".mysqli_real_escape_string($con,$pho['MST_MLS_NUMBER'])."')";

    }

    mysqli_query($con,"insert into cmtval values".implode(",",$numbers)); // it will insert multiple record 
}

score 0 · Answer 3 · answered Aug 03 '16 at 07:32

You can use a prepare statement and execute with different values to be inserted

For example

// prepare and bind
$stmt = $conn->prepare("INSERT INTO MyGuests (firstname, lastname, email) VALUES (?, ?, ?)");
$stmt->bind_param("sss", $firstname, $lastname, $email);

// set parameters and execute
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();

$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();

$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();

Set parameters and execute line should be in your foreach loop.

Prepared Statements and Bound Parameters A prepared statement is a feature used to execute the same (or similar) SQL statements repeatedly with high efficiency.

Prepared statements basically work like this:

Prepare: An SQL statement template is created and sent to the database. Certain values are left unspecified, called parameters (labeled "?"). Example: INSERT INTO MyGuests VALUES(?, ?, ?) The database parses, compiles, and performs query optimization on the SQL statement template, and stores the result without executing it Execute: At a later time, the application binds the values to the parameters, and the database executes the statement. The application may execute the statement as many times as it wants with different values

Nested Foreach loop taking too much time

3 Answers3