事务提交大量sql与合并提交大量sql效率对比
约 723 字 预计阅读 2 分钟
次阅读
- 事务提交5000条update，耗时：13.79 秒，内存占用：17.88 MB（大量时间消耗在Query生成sql语句上）
1
2
3
4
5
6
7
8
9
10
11
12
13
|
$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
\Yii::error("耗时：$runtime 秒，内存占用：$usedMemory MB");
|
- 事务提交5000条update，耗时：9.59 秒，内存占用：16.66 MB（只计算生成sql语句执行时间）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$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
\Yii::error("耗时：$runtime 秒，内存占用：$usedMemory MB");
|
- 合并sql提交5000条update，耗时：13.05 秒，内存占用：0.37 MB（主要时间都耗在Query生成sql语句上了）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$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
\Yii::error("耗时：$runtime 秒，内存占用：$usedMemory MB");
|
- 合并sql提交5000条update，耗时：4.84 秒，内存占用：0.00 MB（只计算生成sql语句执行时间 ）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
$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
\Yii::error("耗时：$runtime 秒，内存占用：$usedMemory MB");
|