Software used
- Project server 2010
- SharePoint 2010 RTM
- Sql Server 2008 R2
Steps taken
- new project server 2010 setup on a virtual machine
- importing/publishing 2 projects from projectcentral 2000 to project server 2010.
- a user has 122 tasks (in project central 2000 he only gets 15 tasks in his view)
- unable to view the detail of the tasks --> unkown error.
there is a sql time out in the log files, from the following query (when i run it in SQL management studio it takes > 5 minutes)
################
declare @ResUid UniqueIdentifier; set @ResUid = '9ffd71a7-c94a-44f5-b3b1-6d27eb17f541';
declare @ViewUid UniqueIdentifier; set @ViewUid = 'a20480ef-c755-4da7-a36f-5fe92fed0c68';
declare @P0 UniqueIdentifier; set @P0 = '9ffd71a7-c94a-44f5-b3b1-6d27eb17f541';
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_DEF_ASSN_OWNER
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 ( (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 , MAS.TASK_NAME , MAS.ASSN_START_DATE , MAS.ASSN_FINISH_DATE ,
ASSN_REM_WORK , MAS.ASSN_PCT_WORK_COMPLETE , ASSN_WORK , ASSN_ACT_WORK , MAS.TASK_UID , PROJ_NAME ,
WASSN_IS_NEW_ASSN , 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_DEF_ASSN_OWNER
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
DROP TABLE #T0;
#################
This query creates a tempory table -- when ran seperately = fast
The query does a selection joining the temporary table.
When running the second query without the join on the temp it takes 8 seconds and result in 826 records (all assignements in the db)
After looking at the join and where clauses used in the first and second query i see they are almost identical and with only adding "AND ( (MAS.RES_UID_OWNER = @P0) ) " and not using a temp table, I get the wanted result (122) only taking half
a second.
So
Suggestions how to fix, circumvent the problem?
