Fixed Unnecessary hundreds query

[Hawk1980]

Affected version

2.1.3

The problems occur under "admin.php?moderators/". in XF1 this was not so!

We plan to switch to XF2! Therefore I have both forums on the same server.
Same number of users/threads/posts/moderators/admins etc.


XF1


XF2


This query is executed countless times!

SELECT `xf_node`.*
FROM `xf_node`


ORDER BY `xf_node`.`lft` ASC

I hope that this will be improved!

Generally that you test your software with extremely large forums. There is a lot of potential! With more than 500 nodes also the start page becomes unbearably sluggish. yes, sluggish than with XF1!


Thank you



Edit: no Add-Ons installed!

 

[Xon]

This only happens with content moderators;

1. XF\Db\Mysqli\Statement->execute() in src/XF/Db/AbstractAdapter.php at line 94
2. XF\Db\AbstractAdapter->query() in src/XF/Mvc/Entity/Finder.php at line 1294
3. XF\Mvc\Entity\Finder->fetch() in src/XF/Repository/Node.php at line 36
4. XF\Repository\Node->getFullNodeList() in src/XF/Moderator/NodeModerator.php at line 35
5. XF\Moderator\NodeModerator->getContentTitles() in src/XF/Moderator/AbstractModerator.php at line 46
6. XF\Moderator\AbstractModerator->getContentTitle() in src/XF/Entity/ModeratorContent.php at line 36
7. XF\Entity\ModeratorContent->getContentTitle()
8. call_user_func_array() in src/XF/Template/Templater.php at line 999
9. XF\Template\Templater->method() in internal_data/code_cache/templates/l1/s0/admin/moderator_list.php at line 111
10. XF\Template\Templater->{closure}() in src/XF/Template/Templater.php at line 1315
11. XF\Template\Templater->renderTemplate() in src/XF/Template/Template.php at line 24
12. XF\Template\Template->render() in src/XF/Mvc/Renderer/Html.php at line 48
13. XF\Mvc\Renderer\Html->renderView() in src/XF/Mvc/Dispatcher.php at line 418
14. XF\Mvc\Dispatcher->renderView() in src/XF/Mvc/Dispatcher.php at line 400
15. XF\Mvc\Dispatcher->renderReply() in src/XF/Mvc/Dispatcher.php at line 360
16. XF\Mvc\Dispatcher->render() in src/XF/Mvc/Dispatcher.php at line 53
17. XF\Mvc\Dispatcher->run() in src/XF/App.php at line 2178
18. XF\App->run() in src/XF.php at line 390
19. XF::runApp() in admin.php at line 13

Essentially getFullNodeList is being called for each content moderator without any caching, resulting in unexpected N+1 query behaviour.

 

[XF Bug Bot]

Thank you for reporting this issue. It has now been resolved and we are aiming to include it in a future XF release (2.1.4).

Change log:

Fix n+1 queries on moderator list

Any changes made as a result of this issue being resolved may not be rolled out here until later.

 

[Hawk1980]

no fixed! No Add-Ons!

 

[kick]

Is the number of requests reduced? Reduced by 53

 

[Hawk1980]

Oh yeah, it's actually a few less!
Believe however it comes from the fact that a moderator was deleted! Even if not - compared to XF1 it's 315 too many!

 

[Alpha1]

I get 556 queries on that page. Its not a big deal as the page is very rarely loaded. It would be a different matter if it would be a from end page that millions of users were hitting. It still seems a lot of queries.

 

[Hawk1980]

That's not quite right!

Sure it's backend! But in our forum - almost 10000000 posts and thousands of users and many moderators the site very important for us.

It concerns me the principle. If I had phpBB - ok!
If it would have been like this in XF1 - ok!
But none of that! And currently we are worsening with XF2 instead of improving! AND that's what it's all about.


You write:

It would be a different matter if it would be a from end page that millions of users were hitting. It still seems a lot of queries.

well also this has become worse by XF2! Under XF1 the start page was approx. 0.2 seconds with XF2 (data amount exactly the same) 0.6 secons.
XF2 is simply overwhelmed with large amounts of data - ok XF1 too. But under XF1 one could compensate it.


Oh yes the server is very large dimensioned.
Percona 8, nginx, 40 cores and 256 GB RAM which are almost completely available to the DB.

 

[XF Bug Bot]

Thank you for reporting this issue. It has now been resolved and we are aiming to include it in a future XF release (2.1.5).

Change log:

Created a `Repository/Node->$nodeListCache` into which calls to `getFullNodeList()` can opt with a named cache-key `getFullNodeList($withinNode = null, $with = null, $cacheKey = null)` in order to quickly return a result that we are confident we have fetched before, as in the case of `getContentTitle()` for content moderators.

Any changes made as a result of this issue being resolved may not be rolled out here until later.