Performance problem main.aspx
For some people a timeout exception occurs on main.aspx I guess it's about the percentages in the green-boxes. Is it possible to turns this off. It doesn't contain any useful information.
thanks.
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() at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, Boolean autoDiscoverTypes, Boolean callable, RowSelection selection, ISessionImplementor session) at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
Here's the query. It seems quite complex. SET DATEFIRST 7 SELECT CAST(i.projectid AS INT) AS ProjectID, SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, getdate()) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW0, SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW1, SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW2, SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW3, SUM(CASE WHEN i.created > u.previouslogindate THEN 1 ELSE 0 END) AS CreatedSinceLastLogin, SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, getdate()) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW0, SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW1, SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW2, SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW3, SUM(CASE WHEN i.closeddate > u.previouslogindate THEN 1 ELSE 0 END) AS ClosedSinceLastLogin, MIN(ISNULL(c.CreatedW0, 0)) AS CommentCreatedW0, MIN(ISNULL(c.CreatedW1, 0)) AS CommentCreatedW1, MIN(ISNULL(c.CreatedW2, 0)) AS CommentCreatedW2, MIN(ISNULL(c.CreatedW3, 0)) AS CommentCreatedW3, MIN(ISNULL(c.CreatedSinceLastLogin, 0)) AS CommentCreatedSinceLastLogin, SUM(CASE WHEN s.isfinal = 0 THEN 1 ELSE 0 END) AS OpenIssues FROM geminiissues i LEFT JOIN ( SELECT ii.projectid, SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, getdate()) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW0, SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW1, SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW2, SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW3, SUM(CASE WHEN ic.created > uu.previouslogindate THEN 1 ELSE 0 END) AS CreatedSinceLastLogin FROM geminiissuecomments ic INNER JOIN geminiissues ii ON ii.issueid = ic.issueid INNER JOIN geminiusers uu ON uu.userid = 94 WHERE ((ii.visibilitymembertype=2 and 94 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = ii.visibility)) or (ii.visibilitymembertype=3 and 94 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = ii.visibility))) AND ((ic.visibilitymembertype=2 and 94 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = ic.visibility)) or (ic.visibilitymembertype=3 and 94 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = ic.visibility))) GROUP BY ii.projectid ) c ON i.projectid = c.projectid INNER JOIN geminiusers u ON u.userid = 94 INNER JOIN geminiissuestatus s ON s.statusid = i.issuestatusid
WHERE ((i.visibilitymembertype=2 and 94 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = i.visibility)) or (i.visibilitymembertype=3 and 94 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = i.visibility)))
AND i.projectid in (80,41,83,92,84)
GROUP BY i.projectid ]
[SQL: SET DATEFIRST 7
SELECT
CAST(i.projectid AS INT) AS ProjectID,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, getdate()) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW0,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW1,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW2,
SUM(CASE WHEN DATEPART(WEEK, i.created)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(i.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW3,
SUM(CASE WHEN i.created > u.previouslogindate THEN 1 ELSE 0 END) AS CreatedSinceLastLogin,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, getdate()) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW0,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW1,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW2,
SUM(CASE WHEN DATEPART(WEEK, i.closeddate)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(i.closeddate) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS ClosedW3,
SUM(CASE WHEN i.closeddate > u.previouslogindate THEN 1 ELSE 0 END) AS ClosedSinceLastLogin,
MIN(ISNULL(c.CreatedW0, 0)) AS CommentCreatedW0,
MIN(ISNULL(c.CreatedW1, 0)) AS CommentCreatedW1,
MIN(ISNULL(c.CreatedW2, 0)) AS CommentCreatedW2,
MIN(ISNULL(c.CreatedW3, 0)) AS CommentCreatedW3,
MIN(ISNULL(c.CreatedSinceLastLogin, 0)) AS CommentCreatedSinceLastLogin,
SUM(CASE WHEN s.isfinal = 0 THEN 1 ELSE 0 END) AS OpenIssues
FROM geminiissues i
LEFT JOIN (
SELECT
ii.projectid,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, getdate()) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW0,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -1, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW1,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -2, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW2,
SUM(CASE WHEN DATEPART(WEEK, ic.created)=DATEPART(WEEK, DATEADD(WEEK, -3, getdate())) AND YEAR(ic.created) = YEAR(GETDATE()) THEN 1 ELSE 0 END) AS CreatedW3,
SUM(CASE WHEN ic.created > uu.previouslogindate THEN 1 ELSE 0 END) AS CreatedSinceLastLogin
FROM geminiissuecomments ic INNER JOIN geminiissues ii ON ii.issueid = ic.issueid INNER JOIN geminiusers uu ON uu.userid = 94
WHERE ((ii.visibilitymembertype=2 and 94 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = ii.visibility)) or (ii.visibilitymembertype=3 and 94 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = ii.visibility)))
AND ((ic.visibilitymembertype=2 and 94 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = ic.visibility)) or (ic.visibilitymembertype=3 and 94 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = ic.visibility)))
GROUP BY ii.projectid
) c ON i.projectid = c.projectid
INNER JOIN geminiusers u ON u.userid = 94
INNER JOIN geminiissuestatus s ON s.statusid = i.issuestatusid
WHERE ((i.visibilitymembertype=2 and 94 in (select gg.userid from geminiglobalgroupmembership gg where gg.globalgroupid = i.visibility)) or (i.visibilitymembertype=3 and 94 in (select pg.userid from geminiprojectgroupmembership pg where pg.projectgroupid = i.visibility)))
AND i.projectid in (80,41,83,92,84)
GROUP BY i.projectid
RemcoDekkers
· 1 |
|
Thursday, April 19, 2012, 7:44:46 AM |