事务提交5000条update,耗时:13.79 秒,内存占用:17.88 MB(大量时间消耗在Query生成sql语句上)
$startTime = microtime(true);
$startMemory = memory_get_usage();
$transaction = \Yii::$app->db->beginTransaction();
for($i=0; $i< 5000; ++$i) {
\Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->execute();
}
$transaction->commit();
$endTime = microtime(true);
$runtime = number_format($endTime - $startTime, 2); //秒
$endMemory = memory_get_usage();
$usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2); //MB
echo("耗时:$runtime 秒,内存占用:$usedMemory MB");
事务提交5000条update,耗时:9.59 秒,内存占用:16.66 MB(一次性生成sql语句)
$transaction = \Yii::$app->db->beginTransaction(); $sql = \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->getRawSql().';'; $startTime = microtime(true); $startMemory = memory_get_usage(); for($i=0; $i< 5000; ++$i) { \Yii::$app->db->createCommand($sql)->execute(); } $transaction->commit(); $endTime = microtime(true); $runtime = number_format($endTime - $startTime, 2); //秒 $endMemory = memory_get_usage(); $usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2); //MB
echo("耗时:$runtime 秒,内存占用:$usedMemory MB");
合并sql提交5000条update,耗时:13.05 秒,内存占用:0.37 MB(主要时间都耗在Query生成sql语句上了)
$startTime = microtime(true); $startMemory = memory_get_usage(); $transaction = \Yii::$app->db->beginTransaction(); $sql = ''; for($i=0; $i< 5000; ++$i) { $sql .= \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->getRawSql().';'; } \Yii::$app->db->createCommand($sql)->execute(); $transaction->commit(); $endTime = microtime(true); $runtime = number_format($endTime - $startTime, 2); //秒 $endMemory = memory_get_usage(); $usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2); //MB
echo("耗时:$runtime 秒,内存占用:$usedMemory MB");
合并sql提交5000条update,耗时:4.84 秒,内存占用:0.00 MB(一次性生成sql语句)
$transaction = \Yii::$app->db->beginTransaction(); $sql = ''; for($i=0; $i< 5000; ++$i) { $sql .= \Yii::$app->db->createCommand()->update(UserMenu::tableName(), ['menu_id' => 4], ['user_id' => $id, 'menu_id' => 3])->getRawSql().';'; } $startTime = microtime(true); $startMemory = memory_get_usage(); \Yii::$app->db->createCommand($sql)->execute(); $transaction->commit(); $endTime = microtime(true); $runtime = number_format($endTime - $startTime, 2); //秒 $endMemory = memory_get_usage(); $usedMemory = number_format(($endMemory - $startMemory) / 1024 / 1024, 2); //MB
echo("耗时:$runtime 秒,内存占用:$usedMemory MB");