Performance with saving large amounts of pages

[gebeer]

Hi,

just wanted to share my experience working with larger data sets in PW.

For a recent project I had to import rather large data sets (between 200 and 5000 rows) from Excel sheets. The import files got processed in chunks and I had to save only one value to one float field on a page. There were about 15.000 pages of that type in the system and another 1800 pages of a different type.

The process of saving each page got really slow when looping through hundreds or thousands of pages. Inside the loop I retrieved every page with a $pages->get() call. This was really fast. But saving got very slow. It took about 4 minutes to process 2500 pages on my dev docker machine and about 2 minutes on the live server with 16 virtual cores and 80GB of RAM. There was one hook running on change of the page field that I saved the values to that did a simple calculation and saved the result to a different float field on the same page. And I guess that was one reason for slowing the process down. After changing the logic and doing the calculation in the import loop, things got a little better. But not much. So I wonder if PW is just not designed to handle large amounts of page saves in an efficient way? I would have thought otherwise.

What I ended up doing is writing the values directly to the field in the DB with a simple method

	public function dbSetData($field, $id, $value)
	{
		$db = $this->wire->database;
		$statement = "UPDATE `{$field}` SET `data` = {$value} WHERE `pages_id` = {$id};";
		$query = $db->prepare($statement);
		if ($db->execute($query)) return true;
		return false;
	}

and also getting the required value for the simple calculation directly from the DB

	public function dbGetData($field, $id)
	{
		$db = $this->wire->database;
		$statement = "SELECT `data` FROM `{$field}` WHERE `pages_id` = {$id};";
		/** @var WireDatabasePDOStatement $query */
		$query = $db->prepare($statement);
		if ($db->execute($query)) {
			$resultStr = $query->fetchColumn();
			if (is_string($resultStr)) $result = $resultStr + 0;
			return $result;
		} else {
			return false;
		}
	}

And that drastically dropped execution time to about 8 seconds for 2500 rows compared to 2 minutes with $pages->get() and $pages->save().

I guess if I need to setup a scenario like this again, I will not use pages for storing those values but rather use a Pro Fields Table field or write directly to the DB.

[elabx]

Did you try using transactions ?? That worked for me to save an indefinite number of pages without running out of memory. Thanks for sharing!

[gebeer]

2 hours ago, elabx said:

Did you try using transactions ?? That worked for me to save an indefinite number of pages without running out of memory.  In any chance, thanks for sharing!

Unfortunately we where not on InnoDB. But the problem was not so much memory consumption but rather long execution time for saving pages. Total mem consumption after saving values to 2500 pages was around 45MB only.