I need to insert many rows ( between 150 to 300 ) into MySQL table and I want to know the better of the following approaches in terms of performance:
Approach 1 :
foreach( $persons as $person ){
$stmt = $dbLink->prepare( "INSERT INTO table SET id = :ID,
name = :name,
email = :email,
mobile = :mobile");
$stmt->execute( array( ':ID'=>$person->getID(),
':name'=>$person->getName(),
':email'=>$person->getEmail(),
':mobile'=>$person->getMobile(),
) );
}
Approach 2:
$stmt = $dbLink->prepare( "INSERT INTO table SET id = :ID,
name = :name,
email = :email,
mobile = :mobile");
$stmt->bindParam( ':ID', $person->getID(), PDO::PARAM_STR );
$stmt->bindParam( ':name', $person->getName(), PDO::PARAM_STR );
$stmt->bindParam( ':email', $person->getEmail(), PDO::PARAM_STR );
$stmt->bindParam( ':mobile', $person->getMobile(), PDO::PARAM_STR );
foreach( $persons as $person ){
$stmt->execute();
}
It is the amount of calls to the database what makes the difference. Reduce the amount of calls as much as possible.
Instead of this:
insert (a,b,c) values (d,e,f);
insert (a,b,c) values (g,h,i);
insert (a,b,c) values (j,k,l);
insert (a,b,c) values (m,n,o);
do this:
insert (a,b,c) values (d,e,f),(g,h,i),(j,k,l),(m,n,o);
Thus making in one call what you would do in 4 calls.
To answer to your question, this is the way you should structure your prepare / bind / execute phases:
//prepare the query only the first time
$stmt = $dbLink->prepare( "INSERT table (id, name, email, mobile)
VALUES (:ID, :name, :email, :mobile)" );
//bind params and execute for every person
foreach( $persons as $person ){
$stmt->bindValue( ':ID', $person->getID(), PDO::PARAM_STR );
$stmt->bindValue( ':name', $person->getName(), PDO::PARAM_STR );
$stmt->bindValue( ':email', $person->getEmail(), PDO::PARAM_STR );
$stmt->bindValue( ':mobile', $person->getMobile(), PDO::PARAM_STR );
$stmt->execute();
}
If you have PDO::ATTR_EMULATE_PREPARES = false, the query will be prepared by mysql only the first time.
In the first case it would be re-prepared for every loop cycle
As correctly other users are saying, remember that a better performance improvement would be to make ONLY one insert instead of many insert in a for loop
EDIT: How to use parameter bindings AND one query
To use parameters' binding and only one query a solution could be:
$placeholders = ""; //this will be filled with placeholders : ( :id_1, :name_1, :email_1, :mobile_1),( :id_2 ... )
$parameters = array(); //this will keep the parameters bindings
$i = 1;
foreach( $persons as $person )
{
//add comma if not first iteration
if ( $placeholders )
$placeholders .= ", ";
//build the placeholders string for this person
$placeholders .= "( :id_$i, :name_$i, :email_$i, :mobile_$i )";
//add parameters for this person
$parameters[":id_$i"] = $person->getID();
$parameters[":name_$i"] = $person->getName();
$parameters[":email_$i"] = $person->getEmail();
$parameters[":mobile_$i"] = $person->getMobile();
$i++;
}
//build the query
$stmt = $dbLink->prepare( "INSERT INTO table (id, name, email, mobile)
VALUES " . $placeholders );
//execute the query passing parameters
$stmt->execute( $parameters );
In the first part of the loop we build the string $placeholders with a set of placeholders for every person, in the second part of the loop we store the bindings of the values of the placeholders in the $parameters array
At the end of the loop we should have all the placeholders and parameters set, and we can execute the query passing the $parameters array to the execute method. This is an alternative way in respect to use the bindValue / bindParam methods but the result should be the same
I think this is the only way to use parameter bindings AND use only one query
You can use the below code to avoid multiple SQL calls and insert the data in Single SQL call
$first_string = "INSERT INTO table (id, name, email,mobile) VALUES ";//Basic query
foreach( $persons as $person )
{
$first_string .="(".$person->getID().",".$person->getName().",".$person->getEmail().",".$person->getMobile()."),";//Prepare the values
}
$final_query_string = substr($first_string, 0,-1);// This will remove the extra , at the end of the string
$stmt = $dbLink->prepare($final_query_string);
$stmt->execute();
Now execute the final query string prepared.
This way the query is prepared as the string and you need to execute it in one go. This will make a single SQL call
//declare array of values to be passed into PDO::Statemenet::execute()
$values = array();
//prepare sql string
$sql = 'INSERT INTO students ( id, name, email, mobile ) VALUES ';
foreach( $students as $student ){
$sql .= '( ?, ?, ?, ? ),'; //concatenate placeholders with sql string
//generate array of values and merge with previous values
$values = array_merge( $values, array( $student->getID(),
$student->getName(),
$student->getEmail(),
$student->getMobile(),
)
);
}
$sql = rtrim( $sql, ',' ); //remove the trailing comma (,) and replace the sql string
$stmt = $this->dbLink->prepare( $sql );
$stmt->execute( $values );
Full credits to all who have inspired me to arrive at this solution. This seems to be terse and clear:
In particular, the answer of JM4 at PDO Prepared Inserts multiple rows in single query really helped. I also recognise Moppo on this page.