Optimize performance of the pages loading by stopping using filesystem always

[Koc]

Is your feature request related to a problem? Please describe.
I profiled some endpoints, e.g. PublicPageController::index() and realized that we have a lot of more or less repeatable queries to database, mostly filecache table. For some of our collectives we have up to 500 queries per collective view. This happens because PageService::getPagesFromFolder() recursively loads all files from a folder and then loads pages for each file one by one. The more pages we have with deeper nesting level -> the bigger quantity of queries we got.

Describe the solution you'd like
As we already have collectives_pages table - let's use it to load all pages for collective in a single query. We need just add columns like collective_id, parent_id, etc.

Of course we need also somehow maintain this table for cases when users creating files via filesystem instead of using Collectives app. But this can be a background process.

WDYT guys?

[mejo-]

Thank for bringing this up @Koc! @max-nextcloud and me already discussed a similar approach some time back, but never found time to work on this yet.

As we already have collectives_pages table - let's use it to load all pages for collective in a single query.

Agreed, that would be a much cleaner way forward. Currently we lack some information in the DB though for populating the PageInfo object that we get from the node directly and that we would have to duplicate into our collectives_pages table as well in this case:

• title: needed, currently the file basename
• timestamp: needed (as last change timestamp)
• size: probably unneeded. I don't see us using this anywhere. Maybe I'm missing something, otherwise we could drop it.
• fileName, filePath: needed
• parentId: needed

Of course we need also somehow maintain this table for cases when users creating files via filesystem instead of using Collectives app. But this can be a background process.

We thought about using Node{Deleted,Renamed,Written}Listener for updating our table.

Would you like to work on this issue?

[max-nextcloud]

If we use the collectives_pages table as a starting point we have the file ids, right? So we could have a single query to the filecache table to get all the additional data from there.

[Koc]

yeah, I'd also prefer to not denormalize data too much.

The only one thing - loading multiple files in a same query not so easy: we need to use \OCP\Files\Folder::search(). For some of my attempts it produced even more queries than getDirectoryListing() 🙉 . But this is a separate topic.

I will try to start work on PR. But I will ask for your help guys 😃 .

[mejo-]

The only one thing - loading multiple files in a same query not so easy: we need to use \OCP\Files\Folder::search(). For some of my attempts it produced even more queries than getDirectoryListing() 🙉 . But this is a separate topic.

Why not get rid of uding \OCP\Files\* classes altogether when building the pages tree and instead do a database query on filecache table directly?

The solution I envision would look like this:

1. Add Node{Deleted,Renamed,Written} event listeners that add/update the page in collectives_pages if the node action doesn't originate from Collectives itself. See e.g. here for an example of such a listener. Similar to the DocumentService->isSaveFromText() we could implement PageService->fromCollectives() or something alike.
2. Add field collective_id to the collectives_pages table and a migration that populates this field. This is necessary for getting the pages by collective from this DB table.
3. Refactor PageService to always use collectives_pages as source of truth when compiling a list of pages per collective. As long as only meta information is needed (i.e. no file content), get rid of using the OCP\Files\ classes altogether and instead query collectives_pages and filecache tables directly.

What do you think about this approach @Koc @max-nextcloud?

I will try to start work on PR. But I will ask for your help guys 😃 .

Whoohoo, much appreciated 😍. And sure, feel free to ask anytime.

[Koc]

hey mates @mejo- @max-nextcloud ! Let's continue discussing in #2390 . I've created a draft, please check PR description, especially TODO section. There are some opened questions. Also check fixmes in the file diff

Feel free edit PR description if I miss any scenario and carefully push directly into the branch if you want.