我正在使用Nested Foreach循环将数据存储在mysql中。但它需要太多的处理时间。如何减少最长执行时间。
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'])."')");
}
}
您应该使用批量插入
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
通过foreach循环,您应该首先执行查询,然后使用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);
我还没有测试代码。测试一下,如果有什么遗漏,请告诉我。批量更新减少了一些时间。
此外,如果在单个查询中在数据库中保存了太多数据,并且索引太多,则插入数据需要时间。
您可以制作这样的
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
}
您可以使用prepare语句并使用不同的值执行以插入
例如
// 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();
设置参数和执行行应该在foreach循环中
编制报表和绑定参数准备好的语句是一种用于高效地重复执行相同(或类似)SQL语句的功能。
准备好的报表基本上是这样工作的:
准备:创建一个SQL语句模板并将其发送到数据库某些值未指定,称为参数(标有"?")。示例:INSERT INTO MyGuests VALUES(?,?,?)数据库对SQL进行解析、编译和查询优化语句模板,并在不执行结果的情况下存储结果执行:稍后,应用程序将值绑定到参数,数据库执行该语句。应用程序可以使用不同的值