Fixed Unnecessary hundreds query

Affected version
2.1.3

0815

New member
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
Screen_20190831030500.png

XF2
Screen_20190831030510.png

This query is executed countless times!
Code:
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!
 
Last edited:

Xon

Well-known member
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

XenForo bug fixer bot
Staff member
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.
 

0815

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

Alfa1

Well-known member
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.
 

0815

New member
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

XenForo bug fixer bot
Staff member
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.
 
Top