Slow display of the main menu page

Topics: User Forum
Sep 4, 2006 at 12:18 PM
When firing up the main menu page, it is very slow - around 30 seconds.

Here are the stats from our site at the moment:-

There are 50 posts in 23 topics in 40 forums.
We have 318 registered members.
Please welcome our newest member Lemasonry, Paul PR.

Powered by SharePoint Forums version 1.2.0.0
Copyright © 2006 by Bil Simser. All rights reserved.
This page was generated in 28.975 seconds.

Sep 4, 2006 at 2:22 PM
I'm not sure, but it does seem to be increasing - either with posts or number of users?

Here is the latest stats:-

There are 59 posts in 24 topics in 40 forums.
We have 323 registered members.
Please welcome our newest member Lawson, Robert RA.


Powered by SharePoint Forums version 1.2.0.0
Copyright © 2006 by Bil Simser. All rights reserved.
This page was generated in 37.221 seconds

Sep 7, 2006 at 11:08 AM
It's grinding to a halt now - is this only me?

I wonder if this is connected to my problem with all these errors? Might is be the database timing out?

Or is the slowness being caused by something that is also causing the errors?

Any moment now, I'm going to have to withdraw this service from my users, saying it is broken. Please help me find a solution.... :-(

********************************************
There are 81 posts in 25 topics in 40 forums.
We have 365 registered members.
Please welcome our newest member Dunnet, Alex AG.

Powered by SharePoint Forums version 1.2.0.0
Copyright © 2006 by Bil Simser. All rights reserved.
This page was generated in 82.913 seconds.



Coordinator
Sep 9, 2006 at 4:42 PM
Very odd as in 1.2 the performance issue was fixed. I have forums (and have seen them from users) with hundreds and even thousands of posts and no page takes more than 3-5 seconds to load.
Sep 11, 2006 at 7:38 AM
Thanks Bil.
It may be a problem caused by all the errors we are getting. I guess best to tackle them first and see it the slow display issue reamins when the errors have gone (I suspect not).
Cheers

Eric

Jan 3, 2007 at 12:51 AM
I am facing the same issue with version 1.2 also. The main page takes >90 seconds to load and getting slower everyday. My statistics:

There are 655 posts in 28 topics in 10 forums.
We have 648 registered members.

Powered by SharePoint Forums version 1.2.0.0
Copyright © 2007 by Bil Simser. All rights reserved.
This page was generated in 90.380 seconds.

Has there been any solution for this?

Jan 11, 2007 at 1:56 AM
Just to add, now the forum webpart is giving a Request Timeout and is unable to load completely :(

I ran SQL Profiler on the database and found that it was running this statement repeatedly:

EXEC spexecutesql N'SELECT TOP 2147483648 UserData.datetime1,UserData.tpCreated,UserData.tp_ID,t1.tp_Email AS
c0c2,UserData.tp_Version,UserData.tp_InstanceID,UserData.tp_ItemOrder,UserData.tp_GUID,UserData.float2,UserData.bit1,UserData.tp_ID,UserData.nvarchar1,UserData.tp_Created,UserData.tp_Author,t2.tp_Email
AS c3c2,UserData.tp_Editor,UserData.tp_ModerationStatus,UserData.tp_Version,t1.tp_Title AS
c0c1,UserData.tp_HasAttachment,UserData.ntext1,UserData.nvarchar2,UserData.tp_Modified,t2.tp_Title AS c3c1,UserData.float1 FROM UserData LEFT OUTER JOIN
UserInfo AS t1 ON (UserData.tp_Author=t1.tp_ID AND t1.tpSiteId = @L1 AND UserData.tpListId = @L2) LEFT OUTER JOIN UserInfo AS t2 ON
(UserData.tp_Editor=t2.tp_ID AND t2.tpSiteId = @L1 AND UserData.tpListId = @L2) WHERE UserData.tp_ListID=@L2 ORDER BY UserData.tp_ID Asc', N'@L1
uniqueidentifier,@L2 uniqueidentifier', '10F23211-E19B-4C3E-9354-35342B74732C','D23A2DDA-FC97-483A-AFEA-73C6EBE53DAF'

Anyone know if this is going anywhere?
Jan 11, 2007 at 2:03 AM
Just realised that that query is actually the one to get the list of users in the forum. The list that it is querying corresponds to the custom list spforums_users.

Seems like there may be an infinite loop going on whereby it keeps querying for the list of users. :\
Jan 11, 2007 at 3:29 AM
After analysing the sql profile results more, I guess that the algorithm when loading the main page is something like this:

1. get current user's details
2. get the permission groups that he has
3. get the categories and his permission for each of the categories
4. get the forums and his permission for each of the forums
5. get the list of users (x number of times where x is the number of users)

The part that I don't understand is why does the forum webpart need to query the database to get the list of users so many times. I have 714 users registered in the forums now and SQL Profiler is showing me that sps is running that same query 1438 times. This should be the reason why the forum is timing out. Bil, in your earlier post you mentioned that you have tested with thousands of posts but I think the problem is actually with volume of users; not the volume of posts.

Can you please try to fix this up in the next release? I feel that it's a very critical issue. Or is it still only my installation that is facing this problem?



Coordinator
Jan 11, 2007 at 5:27 AM
Thanks for the detailed investigation. I have to dig into the code (which I also need to release which will help diagnose problems) to see how close you are in guessing the sequence of events, but I think it's close. Note that I don't actually make any SQL calls, I'm just calling the SharePoint object model to get me lists of things (items from the spforums_users list for example). I think I can do some simple caching of that information which will reduce the number of queries against SharePoint which will cut down the SQL calls in the long run.
Coordinator
Jan 11, 2007 at 5:28 AM
This discussion has been copied to Work Item 7202. You may wish to continue further discussion there.
Jan 12, 2007 at 3:40 AM
Bil, thanks for confirming the issue. I look forward to the patch/fix. Hopefully my users will be able to hold their fire breaths until the release date. :)