Reducing time spent on retrieving forums data in getForumList()

[TSP]

I was a bit torn on where to post this, in IP.Board support, feedback or here. But I guess the best place to start is here.

I've spent some time figuring out what the board spend time on.

On Monday I was able cut down load time on all pages by 30 milliseconds because some custom code retrieved some information that were never used. I also was able to cut down 130 milliseconds on the frontpage in our custom skins, since IP.Board loaded some information for the board index that we never display there.

It seems that since we have 370 forums, a lot of the server response time is spent on loading the forums, obviously.

Since all the forums is loaded into $this->registry->class_forums->forum_by_id this is something that is done on every page load in the forums app.

The entire method getForumList runs in approximately 0.15 seconds on average. Which is 150 milliseconds. Just 50 milliseconds below the recommended server response time from Google.

I also took the time for how long each individual forum use inside that method in www/admin/applications/forums/sources/classes/forums/class_forums.php

        foreach( $tempForums as $posData => $f )
        {
            $timer_2 = microtime(true);
...
            /* Add... */
            $forums_list[ $fr['id'] ] = $fr;
            $end_time = round(microtime(true)-$timer_2, 5);

            if($f['id'] == 290) { var_dump($f); }

            echo "{$end_time} used time on {$f['id']}<br>";
        }

The average time for this seems to be approximately 0,00035 seconds. When that is multiplied with 370, it is 0,13 seconds (or 130 milliseconds)

I'm trying to figure out whether there would be a way to make this faster, without sacrificing the data. I thought about using memcache, but I guess caching the data would require it to be updated for every new post written.

Anyone have any ideas on how to improve this? And is it something maybe IPS should try to look into improving in terms of execution time?

[Grumpy]

As a person who used to have 10k forums, fixing that was the first priority.

I reprogrammed the forum fetching section so it only loads relevant parts. But in areas like mod/admin, it still loads the full 10k. It just wasn't worth it for me to build up cases where it doesn't need to load the full in mod/admin areas. So, those areas got pretty slow. My solution isn't simple or perfect. So, I don't really want to share the code. It basically drills down what you are doing and what you need. If you're looking at a post, you need all your parents (like your parent's parent, and that parent's parent. etc.). If you're looking at the main forum, you need top forums and their direct child forums. etc. etc. It attempts to cater what you are doing separately and build separate rules of what forums it needs. But it fails in certain features like mark this forum as read because my forum has a big tree structure. To click that from the top of the tree would necessitate loading of all those forums. And that's unacceptable. Although, mark entire forum as read is still fine.

But I do agree that IPB really should be solving this kind of problem at more fundamental level and I hope it gets addressed in IPB4.

PS. Similar goes for permission matrices...

PPS. As far as I know, every other major forum software has the identical problem of loading too many forums.

[Birched]

I don't have a solution to this. I just want to chime in to say that this is a very important issue to me.

[TSP]

I have tried to dig some further, I'll be away for some hours now.

Most of the time seems to be spent on the permissions.

$fr = array_merge( $fr, $this->registry->permissions->parse( $f ) );

It seems to spend 0,00020 seconds on it.

There is also spent 0,00010 seconds on this:

$f = $this->registry->classItemMarking->setFromSqlJoin( $f, 'forums' );

If there would be a way to optimize these, I guess we could be able to improve this. But I will have to take a look inside those methods first.

[Grumpy]

The main problem is this code:

public function getForumList() @ /admin/applications/forums/sources/classes/forums/class_forums.php

/* Get the forums */
$this->DB->build( array( 'select' => 'f.*',
'from' => array( 'forums' => 'f' ),
'add_join' => array( array( 'select' => 'p.*',
'from' => array( 'permission_index' => 'p' ),
'where' => "p.perm_type='forum' AND p.app='forums' AND p.perm_type_id=f.id",
'type' => 'left' ),
$this->registry->classItemMarking->getSqlJoin( array( 'item_app_key_1' => 'f.id' ) ) ) ) );

You can see it just calls for ALL forums and then join the permission on it.

[TSP]

For me that query isn't that bad. It only runs once and takes me 0.015. But I guess it's worse if you're talking thousands of forums.

By the way, the following, which can take around 0,00010 seconds for each forum seems to be of no use for guests

$f = $this->registry->classItemMarking->setFromSqlJoin( $f, 'forums' );

I'm pretty sure it's safe to add an if ( $this->memberData['member_id'] ) around it, which saves me on average for 45 milliseconds on every page load.

But that would just be for guests of course, for members it would still have to use those 45 milliseconds.

[Grumpy]

The query to call forums is big, but it's effects are what takes more time, not the query itself. It's a very simple query in terms of difficulty. The fact that every time it processes something to do with a forum, working with a big forum set is lot harder than working with a smaller subset. Whether that be later checking if user has permission for something, whether it's creating additional, harder queries based on it, etc.

On item markers, I think guests do need it. Because even guests have division of read/unread/new based on session.

[TSP]

Our setting for guest marking is off.

[Matt]

This is no longer an issue in 4.0 as we don't load all forums on every single page.

[Birched]

This is no longer an issue in 4.0 as we don't load all forums on every single page.

Great news. Sadly, I'm getting to the point where I'm not interested in hearing anything else about 4.0 until it's ready. I do understand the desire to not put it out until it's done right, and actually appreciate that an attempt is being made to put out a good product and not have some kind of vB 5.0 alpha fiasco.

/ end of off topic; what is needed here is a 3.x solution, unless 4.0 is available