From b50fd065f955d69708a8b093ea2d22717288cc53 Mon Sep 17 00:00:00 2001
From: Matthias Schmidt <gravatronics@live.com>
Date: Sun, 23 Jun 2019 09:51:23 +0200
Subject: [PATCH] Optimize AbstractDatabaseObjectAction::update() for bulk
 processing

---
 .../AbstractDatabaseObjectAction.class.php    | 52 ++++++++++++++++---
 1 file changed, 46 insertions(+), 6 deletions(-)

diff --git a/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php b/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php
index 49d25159a3..b3ad8863a0 100644
--- a/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php
+++ b/wcfsetup/install/files/lib/data/AbstractDatabaseObjectAction.class.php
@@ -362,16 +362,56 @@ abstract class AbstractDatabaseObjectAction implements IDatabaseObjectAction, ID
 			$this->readObjects();
 		}
 		
-		if (isset($this->parameters['data'])) {
-			foreach ($this->getObjects() as $object) {
-				$object->update($this->parameters['data']);
+		$tableName = call_user_func([$this->className, 'getDatabaseTableName']);
+		$indexName = call_user_func([$this->className, 'getDatabaseTableIndexName']);
+		
+		// instead of executing one query per object id, execute queries
+		// for batches of up to 1000 object ids at once
+		$itemsPerLoop = 1000;
+		$batchCount = ceil(count($this->objectIDs) / $itemsPerLoop);
+		
+		if (!empty($this->parameters['data'])) {
+			$updateSQL = '';
+			$statementParameters = [];
+			foreach ($this->parameters['data'] as $key => $value) {
+				if (!empty($updateSQL)) $updateSQL .= ', ';
+				$updateSQL .= $key . ' = ?';
+				$statementParameters[] = $value;
+			}
+			
+			WCF::getDB()->beginTransaction();
+			for ($i = 0; $i < $batchCount; $i++) {
+				$batchObjectIDs = array_slice($this->objectIDs, $i * $itemsPerLoop, $itemsPerLoop);
+				
+				$sql = "UPDATE	" . $tableName . "
+					SET	" . $updateSQL . "
+					WHERE	" . $indexName . " IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")";
+				$statement = WCF::getDB()->prepareStatement($sql);
+				$statement->execute(array_merge($statementParameters, $batchObjectIDs));
 			}
+			WCF::getDB()->commitTransaction();
 		}
 		
-		if (isset($this->parameters['counters'])) {
-			foreach ($this->getObjects() as $object) {
-				$object->updateCounters($this->parameters['counters']);
+		if (!empty($this->parameters['counters'])) {
+			$updateSQL = '';
+			$statementParameters = [];
+			foreach ($this->parameters['counters'] as $key => $value) {
+				if (!empty($updateSQL)) $updateSQL .= ', ';
+				$updateSQL .= $key . ' = ' . $key . ' + ?';
+				$statementParameters[] = $value;
+			}
+			
+			WCF::getDB()->beginTransaction();
+			for ($i = 0; $i < $batchCount; $i++) {
+				$batchObjectIDs = array_slice($this->objectIDs, $i * $itemsPerLoop, $itemsPerLoop);
+				
+				$sql = "UPDATE	" . $tableName . "
+					SET	" . $updateSQL . "
+					WHERE	" . $indexName . " IN (?" . str_repeat(', ?', count($batchObjectIDs) - 1) . ")";
+				$statement = WCF::getDB()->prepareStatement($sql);
+				$statement->execute(array_merge($statementParameters, $batchObjectIDs));
 			}
+			WCF::getDB()->commitTransaction();
 		}
 	}
 	
-- 
2.20.1