|
|
Posted by Xu, Wei on 12/09/05 11:11
Hi,
I have wrote the following sql sentence.Do you have comments to improve
the performance.I have created all the indexed. But it's still very
slow.Thanks
The primary key is proj_ID and Task_UID.
SELECT PR.PROJ_NAME AS PRName, PR.PROJ_ID As PRProjID, PR.TASK_UID As
PRTaskUID, 'Dev' AS GroupType,
Feat.PROJ_ID As FeatProjID, Feat.TASK_UID As FeatTaskUID, Feat.FeatureID AS
FeatureID,
dbo.CreateFIDSort(Feat.FeatureID) as FIDSort, Feat.FeatureName AS
FeatureName, Feat.Entity AS Entity,
Feat.CmtStatus AS CmtStatus, SE.AttrName AS SE, SE.ValueVariant AS SEHRID,
NetworkElement.ValueVariant As NetworkElement,
Pers.FirstName as FirstName, Pers.MiddleInitial as Middle, Pers.LastName as
LastName,
CASE WHEN Priority.ValueVariant Is Null Then 9999 When Priority.ValueVariant
= 0 Then 9999
Else Priority.ValueVariant END AS Priority, 'Doc' AS DocType, Doc.PROJ_ID AS
DocProjID, Doc.TASK_UID AS DocTaskUID,
Doc.ID_Code AS DocNum, Doc.Entity As DocEnt, DocName.Task_Name AS DocName,
DocNotes.Task_RTF_Notes AS DocNotes,
DocDetails.Proj_ID AS DateProjID, DocDetails.Task_UID AS DateTaskUID,
DocDetails.Task_Type
AS DetailTaskType, DocDetDates.TASK_FINISH_DATE AS CWVFinish,
DocDetDates.TASK_BASE_FINISH
AS BasFinish, DocDetDates.TASK_ACT_FINISH AS ActFinish, DocDetDur.TASK_DUR
AS TotalDur, DocDetDur.TASK_REM_DUR AS RemDur,
DocDetDates.TASK_ACT_START AS ActStart, Sortnum.DocNum As DocSortNum,
PR.PROJ_NAME AS PR1Name, 'Disp' As FeatDisp
FROM CPR_enum_ReltoProj Rel WITH (nolock)
INNER JOIN CPR_PATH ReltoFeat with (nolock) ON Rel.PROJ_ID =
ReltoFeat.PRED_PROJ_ID
AND Rel.TASK_UID = ReltoFeat.PRED_Task_UID AND Rel.PROJ_NAME LIKE 'R26.0'
AND ReltoFeat.EDGE_ID = 1
INNER JOIN CPR_TASK_FeatCmtStat Feat WITH (nolock) ON ReltoFeat.SUCC_PROJ_ID
= Feat.PROJ_ID
AND ReltoFeat.SUCC_Task_UID = Feat.TASK_UID AND Feat.CmtStatus <> 'Concept'
AND Feat.CmtStatus <> 'Identified'
AND Feat.CmtStatus Is Not Null
LEFT JOIN ( CPR_PATH FeattoPR WITH (nolock)
INNER JOIN CPR_ENUM_PRtoProj PR WITH (nolock)
ON FeattoPR.PRED_PROJ_ID = PR.PROJ_ID AND FeattoPR.PRED_TASK_UID =
PR.TASK_UID )
ON Feat.PROJ_ID = FeattoPR.SUCC_PROJ_ID AND Feat.TASK_UID =
FeattoPR.SUCC_TASK_UID AND FeattoPR.EDGE_ID = 1
LEFT JOIN CPR_ContainerAttr SE WITH (nolock) ON Feat.PROJ_ID = SE.PROJ_ID
AND Feat.TASK_UID = SE.TASK_UID
AND SE.AttrName in ('SEM','SEA')
LEFT JOIN CPR_Person Pers WITH (nolock) ON Pers.HRID = SE.ValueVariant
LEFT JOIN CPR_ContainerAttr NetworkElement WITH (nolock) ON Feat.PROJ_ID =
NetworkElement.PROJ_ID
AND Feat.TASK_UID = NetworkElement.TASK_UID AND NetworkElement.AttrName =
'NetElem'
LEFT JOIN CPR_ContainerAttr Priority WITH (nolock) ON Feat.PROJ_ID =
Priority.PROJ_ID
AND Feat.TASK_UID = Priority.TASK_UID AND Priority.AttrName = 'FPA'
LEFT JOIN ( CPR_PATH FeattoDoc WITH (nolock)
INNER JOIN CPR_ENUM_AllDocs Doc WITH (nolock)
ON FeattoDoc.SUCC_PROJ_ID = Doc.PROJ_ID AND FeattoDoc.SUCC_TASK_UID =
Doc.TASK_UID AND FeattoDoc.EDGE_ID = 1
AND Doc.ID_Code NOT LIKE '[<]%' AND Doc.Entity in
('FDD','SRD','SRAD','FFRD','VRAD')
LEFT JOIN CPR_DOCSORTNUM Sortnum WITH (nolock) ON Doc.Entity =
Sortnum.DocEnt
INNER JOIN MSP_TASKS DocName WITH (nolock) ON Doc.PROJ_ID = DocName.PROJ_ID
AND Doc.TASK_UID = DocName.TASK_UID
INNER JOIN CPR_ENUM_Task_RTF_Notes DocNotes WITH (nolock) ON Doc.PROJ_ID =
DocNotes.PROJ_ID
AND Doc.TASK_UID = DocNotes.TASK_UID
LEFT JOIN ( CPR_PATH DoctoDet WITH (nolock)
INNER JOIN CPR_ENUM_TASK_Task_Type DocDetails WITH (nolock) ON
(DoctoDet.SUCC_PROJ_ID = DocDetails.PROJ_ID
AND DoctoDet.SUCC_TASK_UID = DocDetails.TASK_UID AND DocDetails.Task_Type
In ('WriteRev', 'RwkRFA', 'PubLive', 'Waived','Review','RFA', 'CustRev'))
LEFT JOIN MSP_TASKS DocDetDates WITH (nolock) ON
(DocDetails.PROJ_ID = DocDetDates.PROJ_ID AND DocDetails.TASK_UID =
DocDetDates.TASK_UID)
LEFT JOIN CPR_ENUM_TASK_Durations DocDetDur WITH (nolock)
ON ( DocDetails.PROJ_ID = DocDetDur.PROJ_ID AND DocDetails.TASK_UID =
DocDetDur.TASK_UID))
ON ( DocName.PROJ_ID = DoctoDet.PRED_PROJ_ID AND DocName.TASK_UID =
DoctoDet.PRED_TASK_UID AND DoctoDet.EDGE_ID = 1))
ON (Feat.PROJ_ID=FeattoDoc.PRED_PROJ_ID AND
Feat.TASK_UID=FeattoDoc.PRED_TASK_UID AND FeattoDoc.EDGE_ID = 1)
WHERE Feat.FeatureID NOT LIKE '[<]%'
ORDER BY PRName, FIDSort, FeatureID, DocSortNum, DocProjID,DocTaskUID,
DateProjID, DateTaskUID
Navigation:
[Reply to this message]
|