Olá Galera...
Eu queria saber se posso melhorar o desempenho dessa sp que eu criei. Não há nada de errado na sua estrutura, apenas gostaria de saber se voces podem me auxiliar a melhorar o desempenho dela.
Mtu Obrigado.
CREATE PROCEDURE spSelect_Report_Staff_Summary
@CorpID varchar(50),
@DateBegin datetime,
@DateEnd datetime
AS
Set nocount ON
Declare @DateFrom datetime, @DateTo datetime
Begin
If @DateBegin = ''
Set @DateFrom = '1900-1-1'
else
Set @DateFrom = @DateBegin
If @DateEnd = ''
Set @DateTo = '2099-1-1'
else
Set @DateTo = @DateEnd
SELECT
M.Member_Guid
, (M.Member_Min + ' ' + M.Member_Max) "Member_Name"
, (SELECT Round(AVG(A.Project_Member_Review_Score),2)FROM tblProject_Member_Review A (NOLOCK)WHERE M.Member_Guid = A.Member_Guid)
, (SELECT ROUND(SUM(T.Time_Hours),2) FROM tblTime T (NOLOCK) WHERE M.Member_Guid = T.Member_Guid AND T.Time_Date Between @DateFrom AND @DateTo)"Hours"
, (SELECT ROUND(SUM(T.Time_Hours),2) FROM tblTime T (NOLOCK) WHERE M.Member_Guid = T.Member_Guid AND T.Time_Rate > 0 AND T.Time_Date Between @DateFrom AND @DateTo)"Billable_Hours"
, (SELECT ROUND(SUM(T.Time_Hours),2) FROM tblTime T (NOLOCK) WHERE M.Member_Guid = T.Member_Guid AND (T.Time_Rate = 0 OR T.Time_Rate IS NULL) AND T.Time_Date Between @DateFrom AND @DateTo)"Non_Billable_Hours"
, 0 "OT"
, (SELECT SUM(T.Time_Hours)FROM tblTime T (NOLOCK)WHERE T.Time_Date Between @DateFrom AND @DateTo AND T.Member_Guid = M.Member_Guid)
/
(SELECT SUM(T.Time_Hours)FROM tblTime T (NOLOCK)WHERE T.Time_Date Between @DateFrom AND @DateTo AND T.Time_Rate>0 AND T.Member_Guid = M.Member_Guid) "Member_Efficiency"
, (CASE WHEN(SELECT SUM(T.Time_Hours*T.Time_Rate) FROM tblTime T (NOLOCK)WHERE M.Member_Guid = T.Member_Guid AND T.Time_Date Between @DateFrom AND @DateTo)IS NULL THEN 0 ELSE (SELECT ROUND(SUM(T.Time_Hours*T.Time_Rate),2) FROM tblTime T (NOLOCK)WHERE M.Member_Guid = T.Member_Guid AND T.Time_Date Between @DateFrom AND @DateTo) END) "Staff_Revenue"
, (CASE WHEN(SELECT SUM(T.Time_Hours*T.Time_Rate -(T.Time_Hours*T.Time_Cost)) FROM tblTime T (NOLOCK)WHERE M.Member_Guid = T.Member_Guid AND T.Time_Date Between @DateFrom AND @DateTo)IS NULL THEN 0 ELSE (SELECT ROUND(SUM(T.Time_Hours*T.Time_Rate -(T.Time_Hours*T.Time_Cost)),2) FROM tblTime T (NOLOCK)WHERE M.Member_Guid = T.Member_Guid AND T.Time_Date Between @DateFrom AND @DateTo) END) "Project_Margin"
FROM tblMember M (NOLOCK)
WHERE M.Corp_Guid = @CorpID
END
GO
