From db6927ee0530d2e352725dc0f6d75e8db8723587 Mon Sep 17 00:00:00 2001
From: Carl Schwan <carl@carlschwan.eu>
Date: Wed, 16 Feb 2022 18:18:56 +0100
Subject: Improve performance when converting between DBs

Use a transaction when doing multiple inserts increase the writing
speed.

Ref: https://stackoverflow.com/a/68495887

Signed-off-by: Carl Schwan <carl@carlschwan.eu>
---
 core/Command/Db/ConvertType.php | 40 +++++++++++++++++++++++++---------------
 1 file changed, 25 insertions(+), 15 deletions(-)

diff --git a/core/Command/Db/ConvertType.php b/core/Command/Db/ConvertType.php
index 27f3147b81d..754cbd08812 100644
--- a/core/Command/Db/ConvertType.php
+++ b/core/Command/Db/ConvertType.php
@@ -353,27 +353,37 @@ class ConvertType extends Command implements CompletionAwareInterface {
 
 			$result = $query->execute();
 
-			while ($row = $result->fetch()) {
-				$progress->advance();
-				if (!$parametersCreated) {
-					foreach ($row as $key => $value) {
-						$insertQuery->setValue($key, $insertQuery->createParameter($key));
+			try {
+				$toDB->beginTransaction();
+
+				while ($row = $result->fetch()) {
+					$progress->advance();
+					if (!$parametersCreated) {
+						foreach ($row as $key => $value) {
+							$insertQuery->setValue($key, $insertQuery->createParameter($key));
+						}
+						$parametersCreated = true;
 					}
-					$parametersCreated = true;
-				}
 
-				foreach ($row as $key => $value) {
-					$type = $this->getColumnType($table, $key);
-					if ($type !== false) {
-						$insertQuery->setParameter($key, $value, $type);
-					} else {
-						$insertQuery->setParameter($key, $value);
+					foreach ($row as $key => $value) {
+						$type = $this->getColumnType($table, $key);
+						if ($type !== false) {
+							$insertQuery->setParameter($key, $value, $type);
+						} else {
+							$insertQuery->setParameter($key, $value);
+						}
 					}
+					$insertQuery->execute();
 				}
-				$insertQuery->execute();
+				$result->closeCursor();
+
+				$toDB->commit();
+			} catch (\Throwable $e) {
+				$toDB->rollBack();
+				throw $e;
 			}
-			$result->closeCursor();
 		}
+
 		$progress->finish();
 		$output->writeln('');
 	}
-- 
cgit v1.2.3