Active Topics

Well that was easier than I thought.
For those that are interested here's the SQL stored procedure. Note that rather that you have to pass the stored procedure a date and the Author_ID of the user. I'll be figuring out the date ASP side which will allow me to remove all the filters currently in the page. Note I also return all the category information so that I'm not making another call to the database later in order to get the category information.

CREATE PROCEDURE wwf_sp_ActiveTopics ( @AuthorID int, @dteMinimum datetime ) as declare @GroupID int set @GroupID = (select Group_ID from tblAuthorA where Author_ID=@AuthorID) SELECT     dbo.tblCategory.Cat_ID AS Cat_ID,dbo.tblCategory.Cat_name AS Cat_name, dbo.tblForum.Forum_name AS Forum_name, dbo.tblForum.Password AS Password,                           dbo.tblForum.Forum_code AS Forum_code, dbo.tblForum.[Read] AS [Read], dbo.tblTopic.* FROM        dbo.tblCategory RIGHT OUTER JOIN                           dbo.tblForum ON dbo.tblCategory.Cat_ID = dbo.tblForum.Cat_ID RIGHT OUTER JOIN                           dbo.tblTopic ON dbo.tblForum.Forum_ID = dbo.tblTopic.Forum_ID WHERE      (tblForum.[Read] = 1 OR      (tblTopic.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID = @GroupID and [Read]=1)))      AND tblTopic.Last_entry_date >=@dteMinimum ORDER BY      tblCategory.Cat_order ASC, tblForum.Forum_Order ASC, tblTopic.Last_entry_date DESC; GO

Maybe Borg will incorporate this into the active topics page in the next release (if he already hasn't).



Edited by ljamal

The biggest time hog for SQL users is the multiple trips to the database. It makes around 20 or so hits to the database, so if you can contract those 20 hits into fewer hits, that will drastically reduce your load speed. This is only something that should be tackled by someone with a fair amount of knowledge of stored procedures and ASP.

For the default page
I moved the common.asp stuff into a stored procedure with gets the user info and permissions and PM info and date formating. I hardcoded all the forum configuration information (because it's not going to change so I don't need the extra call to the database). That cut out about 4-5 calls to the database.
Moving to the page itself, I removed all the stats code from the bottom. I combined the forum and category query into one view of the forums with categories. I returned the permissions for the forum and the user's group and got the moderators and the last posts.
The first paragraph is stuck in one stored procedure and the second in another and they are both queried at one time. Those 2 stored procedures return 9 recordsets which are used to build the page.

ilnar wrote: ljamal wrote: WHERE      (tblForum.[Read] = 1 OR      (tblTopic.Forum_ID in (select Forum_ID from tblPermissions where Author_ID=@AuthorID or Group_ID = @GroupID and [Read]=1)))      AND tblTopic.Last_entry_date >=@dteMinimum Why "tblForum.[Read] = 1" - [Read] in this context not permissions attribute, IMHO. IMHO, right check "tblForum.[Hide] = 0"

You want to display only the topics the user can read, when tblForum.Hide is set to 1 that only tells the app to hide the forum IF tblForum.[Read] = 0 so that fact that Hide = 1 does not means that the user don't have access to the forum.