I am making tests on performance on my query and I found out that prepared
statements seem slower than single query. Is this the expected output? Or I'm missing something on prepared statements. I basically prefer prepared statements for security reasons. But it is slower, maybe there's still a way to optimize it more?
I am using 10k records for testing.
Here's my queries:
//QUERY 1 - prepared statements - took 2433
ms
$mysqli->autocommit(FALSE);
//prepare our query
$query = "INSERT INTO user (name, email, address) VALUES (?,?,?)";
//bind parameters
$statement = $mysqli->prepare($query);
$statement ->bind_param("sss", $name, $email, $address);
//execute each statement
foreach ($user_object_array as $user) {
$name = $user["name"];
$email = $user["email"];
$address = $user["address"];
$statement->execute();
$affected_rows += $mysqli->affected_rows;
}
//finally, commit
$mysqli->commit();
//QUERY 2 single query - took 600
ms
$sql = "INSERT INTO user(name,email,address) VALUES ";
foreach ($user_object_array as $user) {
$name = mysqli_real_escape_string($mysqli, $user["name"]);
$email = mysqli_real_escape_string($mysqli, $user["email"]);
$address = mysqli_real_escape_string($mysqli, $user["address"]);
$sql .= "('".$name."', '".$email."', '".$address."'),";
}
//remove the last comma on string
$sql = substr($sql, 0, -1);
if (mysqli_query($mysqli, $sql)) {
$affected_rows = mysqli_affected_rows($mysqli);
} else {
echo "Error: " . $sql . "" . mysqli_error($mysqli);
}