问题描述
很多用户在PWA网站中点击“我的任务”的界面时,页面加载很长后,然后报“未知错误”
问题原因
页面加载很长,应该是数据库在执行相关存储过程时间太长,或者遇到了错误。利用SQL Server Profiler抓取到以下SQL语句:
exec sp_executesql N'SET NOCOUNT ON
SELECT
MAS.ASSN_UID ,
MAS.PROJ_UID
INTO #T0
FROM dbo.MSP_ASSIGNMENTS_SAVED AS MAS
INNER JOIN dbo.MSP_PROJECTS AS MP ON MP.PROJ_UID = MAS.PROJ_UID
INNER JOIN dbo.MSP_TASKS_SAVED AS T ON T.PROJ_UID = MAS.PROJ_UID AND T.TASK_UID = MAS.TASK_UID
INNER JOIN dbo.MSP_PROJECT_RESOURCES AS R ON R.PROJ_UID = MAS.PROJ_UID AND R.RES_UID = MAS.RES_UID
INNER JOIN dbo.MSP_RESOURCES AS R2 ON R2.RES_UID = R.RES_UID
LEFT JOIN dbo.MSP_RESOURCES AS R3 ON R3.RES_UID = MAS.RES_UID_OWNER
LEFT JOIN dbo.MSP_RESOURCES AS R4 ON R4.RES_UID = MAS.WRES_UID_MANAGER
WHERE MAS.WASSN_LOCKDOWN_BY_MANAGER = 0AND T.TASK_IS_ACTIVE = 1 AND dbo.MFN_UTILITY_RES_IS_GENERIC(R2.RES_TYPE)=0 AND ( (MAS.RES_UID_OWNER = @P0) )
CREATE CLUSTERED INDEX PK_#T0 ON #T0 (ASSN_UID, PROJ_UID)
SET NOCOUNT OFF
SELECT
MAS.ASSN_UID ,
MAS.PROJ_UID ,
T.TASK_ID ,
MAS.TASK_NAME ,
ASSN_ACT_START ,
ASSN_ACT_FINISH ,
MAS.ASSN_START_DATE ,
MAS.ASSN_FINISH_DATE ,
MAS.ASSN_PCT_WORK_COMPLETE ,
T.TASK_PCT_COMP ,
MAS.TASK_UID ,
PROJ_NAME ,
WASSN_IS_NEW_ASSN ,
ASSN_ACT_WORK ,
MAS.WASSN_REMOVED_BY_RES ,
case when exists ( select * from MSP_ASSIGNMENT_TRANSACTIONS t where MAS.ASSN_UID = t.ASSN_UID and t.ASSN_TRANS_STATE_ENUM = 1) then 1 else 0 end AS ASSN_RESPONSE_PENDING ,
MAS.WASSN_MGR_UPDATED ,
MAS.WASSN_ACTUALS_PENDING ,
MAS.WASSN_SEND_UPDATE_NEEDED ,
MAS.WASSN_LOCKDOWN_BY_MANAGER ,
MAS.WASSN_DELETED_IN_PROJ ,
MAS.WASSN_COMMENTS ,
R.RES_NAME ,
T.TASK_IS_MILESTONE ,
WASSN_UPDATE_TRACKING_MODE ,
T.TASK_NOTES ,
T.TASK_IS_MANUAL ,
T.TASK_HIERARCHY ,
T.TASK_DUR_TXT ,
T.TASK_FINISH_TXT ,
T.TASK_START_TXT ,
MAS.TASK_IS_SUMMARY ,
WO.WOBJ_UID ,
MP.PROJ_OPT_MINUTES_PER_DAY ,
MP.PROJ_OPT_MINUTES_PER_WEEK ,
MP.PROJ_OPT_DAYS_PER_MONTH ,
MP.PROJ_OPT_CURRENCY_CODE ,
MAS.ASSN_STOP_DATE ,
T.TASK_IS_SUBPROJ ,
T.TASK_IS_ACTIVE ,
T.TASK_START_DATE ,
T.TASK_FINISH_DATE ,
T.TASK_DUR ,
MAS.RES_UID ,
R.RES_MATERIAL_LABEL ,
R.RES_TYPE ,
ASSN_UPDATE_NEEDED
FROM dbo.MSP_ASSIGNMENTS_SAVED AS MAS
INNER JOIN dbo.MSP_PROJECTS AS MP ON MP.PROJ_UID = MAS.PROJ_UID
INNER JOIN dbo.MSP_TASKS_SAVED AS T ON T.PROJ_UID = MAS.PROJ_UID AND T.TASK_UID = MAS.TASK_UID
INNER JOIN dbo.MSP_PROJECT_RESOURCES AS R ON R.PROJ_UID = MAS.PROJ_UID AND R.RES_UID = MAS.RES_UID
INNER JOIN dbo.MSP_RESOURCES AS R2 ON R2.RES_UID = R.RES_UID
LEFT JOIN dbo.MSP_RESOURCES AS R3 ON R3.RES_UID = MAS.RES_UID_OWNER
LEFT JOIN dbo.MSP_RESOURCES AS R4 ON R4.RES_UID = MAS.WRES_UID_MANAGER
INNER JOIN #T0 AS keys ON keys.ASSN_UID = MAS.ASSN_UID AND keys.PROJ_UID = MAS.PROJ_UID
LEFT JOIN dbo.MSP_WEB_OBJECTS AS WO ON WO.WOBJ_PROJ_UID = T.PROJ_UID AND WO.WOBJ_TASK_UID = T.TASK_PUBLISHED_UID AND WO.WOBJ_TYPE = 2
WHERE MAS.WASSN_LOCKDOWN_BY_MANAGER = 0AND T.TASK_IS_ACTIVE = 1 AND dbo.MFN_UTILITY_RES_IS_GENERIC(R2.RES_TYPE)=0
DROP TABLE #T0;
',N'@ResUid uniqueidentifier,@ViewUid uniqueidentifier,@P0 uniqueidentifier',@ResUid='0D2FDCCC-486B-4D1F-AF89-A4A119E570E8',@ViewUid='A20480EF-C755-4DA7-A36F-5FE92FED0C68',@P0='0D2FDCCC-486B-4D1F-AF89-A4A119E570E8'
在SQL查询过程中,这段语句超过了6分钟,这就导致我的任务界面超过,所以报未知错误。
解决办法
为了缩短查询时间,就需要优化查询过程,在SQL的查询界面中,选中ProjectServer_Published数据库,执行以下存储过程:
EXEC SP_updatestats