[Improvement][Perf]: WorkspaceHelper::isAllowed creating hundreds of database queries for listings

[dkarlovi]

Improvement description

WorkspaceHelper::isAllowed will run a DB query for each item in the listing, including relations:

data-hub/src/WorkspaceHelper.php

Lines 280 to 282 in f781127

	$db = Db::get();
	$sql = 'SELECT `' . $type . '` FROM plugin_datahub_workspaces_' . $elementType . ' WHERE cid IN (' . implode(',', $parentIds) . ') AND configuration = ' . $db->quote($configuration->getName()) . ' AND `' . $type . '`=1 ORDER BY LENGTH(cpath) DESC LIMIT 1';
	$permissionsParent = $db->fetchOne($sql);

In my test, fetching 50 products and related images will run the query 426 times, accounting for about 30% of the total runtime.

Looking at the code, it seems it should be possible to avoid these queries completely by creating a special in-memory (in cache) data structure optimized for this check and running the check as fast as possible on that specific structure.

Edit: when the listing is fully cached (all elements fully resolved from cache), the listing will still run 546 queries, but 426 of them are still this permission check, that's 80% of all the work done, accounting for 40% of total runtime.

[fashxp]

related to #300?

[dkarlovi]

@fashxp not sure what you're asking or if you're even asking me.

[fashxp]

@dkarlovi just linking potentially related issues - not sure though if this is the same topic.