Performance issues for Active Topics?

Mar 4, 2013 at 4:18 AM
Does anyone else seem to have any performance issues with the ActiveTopics view?

Most of the time when I try to hit it on my converted site (DNNForum to AF 5.0.3) it times out.

Digging into the stored proc, just the ROWCOUNT at the top took me a minute and 5 seconds to execute the first time I tried it. Subsequent executions showed 1 second


Select Count(*)
from dbo.vw_activeforums_TopicsView as T INNER JOIN
dbo.activeforums_Functions_Split('2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;93;94;95;96;97;98;99;100;101;102;103;104;105;106;107;108;109;110;111;112;113;114',';') as ft 
    ON ft.id = t.ForumId INNER JOIN
dbo.activeforums_Forums as F on ft.id = F.ForumId INNER JOIN 
dbo.activeforums_Permissions as p on p.PermissionsId = f.PermissionsId
WHERE T.PortalId = 0
AND T.ModuleId = 519
AND DATEDIFF(mi,t.LastReplyDate,GetDate())<= 1440


I'm wondering if the permissions for my forums are all jacked from the conversion and maybe that is causing the problem?
Mar 4, 2013 at 12:08 PM
If this is something that "shows" in heavily populated Forums, then i can't say.

In my small forum i don't have this problem at all. It was a convertion from the old DNN forums (not AF).
Mar 4, 2013 at 3:03 PM
I really don't know what causes it. Sometimes it takes over a minute to run that query, sometimes it runs in under a second. I can't figure out why yet,.
Mar 4, 2013 at 3:35 PM
Wow, you have a lot of forums! I wonder if that might have something to do with it? I wouldn't think so, but I'm by no means a SQL guy.
Mar 4, 2013 at 10:18 PM
I don't have lot of forums but I see lot of time out errors on my What's New Module. I was told it uses lot of resources. Maybe same goes for the Active Topics? However, my forums have not timed out while using Active Topics on the main forum page. I assume, if we were using Active Topics as a different module on a different page we will see the time out errors a lot.
Mar 5, 2013 at 2:00 AM
I don't think that there's anything in the what's new module that makes it any more resource intensive than the rest of the forums.

I'm not sure what's going on here, but I don't think it's happening to everyone. I have a fairly busy forum with over 100K forum posts, and I don't see any performance issues with the what's new or the active topics.

Honestly, what we really need is someone working on this project that is really good at optimization of SQL tables, procs, indexes, etc. SQL is something that I don't do very often so it's certainly not my strong suite. Will is probably much better at it than I am, but I don't think he has much time to dedicate to this project right now either..

So... If there is a SQL Guru lurking around this project who's ever thought about lending a hand, I think it would be much appreciated.
Mar 8, 2013 at 3:04 AM
Here is my time out error:

DotNetNuke.Services.Exceptions.ModuleLoadException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Modules.ActiveForums.SqlDataProvider.UI_TopicView(Int32 PortalId, Int32 ModuleId, Int32 ForumId, Int32 TopicId, Int32 UserId, Int32 PageIndex, Int32 PageSize, Boolean IsSuper, String Sort) at DotNetNuke.Modules.ActiveForums.Controls.TopicView.LoadData(Int32 PageId) at DotNetNuke.Modules.ActiveForums.Controls.TopicView.OnLoad(EventArgs e) --- End of inner exception stack trace ---


and

Message: DotNetNuke.Services.Exceptions.ModuleLoadException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) at Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteDataset(String connectionString, String spName, Object[] parameterValues) at DotNetNuke.Modules.ActiveForums.SqlDataProvider.Search_Standard(Int32 PortalId, Int32 ModuleId, Int32 UserId, Int32 ForumId, Boolean IsSuper, Int32 RowIndex, Int32 MaxRows, String SearchString, Int32 MatchType, Int32 SearchField, Int32 Timespan, Int32 AuthorId, String Author, String Forums, String Tags, String ForumsAllowed) at DotNetNuke.Modules.ActiveForums.af_search_new.BindPosts(Int32 Column, String Sort) at DotNetNuke.Modules.ActiveForums.af_search_new.OnLoad(EventArgs e) --- End of inner exception stack trace ---
Mar 8, 2013 at 3:16 AM
Just out of curiosity, what are you using for a SQL server and how much memory does it have?
Mar 8, 2013 at 7:03 PM
2008 SQL Server with 4 GB RAM?