Procedure muito lenta

Olá Pessoal,

Estou ajustando uma procedure que esta muito lenta em nosso banco de dados, porém ao tentar melhora-la ficou ainda mais lenta.

Segue procedure antiga:

DECLARE
@FROMDATE DATETIME,
@TODATE DATETIME,
@EMPLOYEEFILTERID INT,
@TIMEZONEID VARCHAR(50)

SET @FROMDATE = '2010-01-01 00:00:00'
SET @TODATE = '2010-12-31 23:59:59'
SET @EMPLOYEEFILTERID = 62054
SET @TIMEZONEID = 'AMERICA/SAO_PAULO'

--AS BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SET ARITHIGNORE ON

-------------------------------
---------AJUSTE BIAS-----------
-------------------------------

DECLARE @BIAS INT

SELECT @BIAS=BIAS FROM TIMEZONEAM
WHERE TIMEZONEAM.TIMEZONE = @TIMEZONEID

AND NOT((TIMEZONEAM.ENDTIME <= @FROMDATE) OR
(TIMEZONEAM.STARTTIME > @TODATE))

/*

SELECT ORGANIZACAO,
 EMPLOYEENUMBER,
 OPERADOR,
 SUPERVISOR,
 COLORCODE,
 ATIVIDADE,
 STP,
 ETP,
 [STR],
 ETR,
 1-(CAST(EX AS FLOAT)/CAST(PLA AS FLOAT)) ACERTIVIDADE,
 [EX],
 PLA,
 REA
 FROM (SELECT ORGA.ORGANIZACAO,
 ISNULL(EMP2.LASTNAME,'SEM SUPERVISOR') SUPERVISOR,
 EMP.LASTNAME OPERADOR,
 EMP.EMPLOYEENUMBER,
 PLA.COLORCODE,
 PLA.ATIVIDADE,
 PLA.STARTTIME STP,
 PLA.ENDTIME ETP,
 ACT.STARTTIME [STR],
 ACT.ENDTIME ETR,
 ISNULL(DATEDIFF(SECOND,PLA.STARTTIME,PLA.ENDTIME),0)[PLA],
 ISNULL(DATEDIFF(SECOND,ACT.STARTTIME,ACT.ENDTIME),0)[REA],
 ISNULL(EXPECTION,0)[EX]
 FROM

*/

SELECT EMP.*, EMP2.LASTNAME
 FROM

------------------------------- ---------EMPLOYEEAM------------ -------------------------------

 (SELECT EMPLOYEEAM.ID,
 EMPLOYEENUMBER,
 PERSON.LASTNAME
 FROM EMPLOYEEAM
 JOIN PERSON ON PERSON.ID = EMPLOYEEAM.PERSONID

)EMP ------------------------------- ----------FILTRO--------------- -------------------------------

 JOIN (SELECT EMPLOYEEFILTERWORKRESOURCE.WORKRESOURCEID,
 EMPLOYEEFILTER.NAME
 FROM EMPLOYEEFILTERWORKRESOURCE
 JOIN EMPLOYEEFILTER ON EMPLOYEEFILTER.ID = EMPLOYEEFILTERWORKRESOURCE.EMPLOYEEFILTERID
WHERE EMPLOYEEFILTER.ID=@EMPLOYEEFILTERID

)EMPFWRS ON EMPFWRS.WORKRESOURCEID = EMP.ID ------------------------------- ----------PLANEJADO------------ ------------------------------- JOIN (SELECT DISTINCT PLANE.ID, SHAB.WORKRESOURCEID, CONVERT(VARCHAR(10),DATEADD(MINUTE,@BIAS,SHAB.STARTTIME),105)DATA, ATV.COLORCODE, ATV.NAME ATIVIDADE, DATEADD(MINUTE,@BIAS,PLANE.STARTTIME)STARTTIME, DATEADD(MINUTE,@BIAS,PLANE.ENDTIME)ENDTIME FROM SHIFTASSIGNMENTPUB SHAB JOIN PLANNEDEVENTTIMELINE PLANE ON PLANE.WORKRESOURCEID = SHAB.WORKRESOURCEID AND PLANE.ISUNPUBLISHED = 0 AND DATEADD(HOUR,-1,DATEADD(MINUTE,@BIAS,SHAB.STARTTIME)) < DATEADD(MINUTE,@BIAS,PLANE.STARTTIME) AND DATEADD(HOUR,1,DATEADD(MINUTE,@BIAS,SHAB.ENDTIME)) > DATEADD(MINUTE,@BIAS,PLANE.ENDTIME) JOIN ACTIVITY ATV ON ATV.ID = PLANE.ACTIVITYID AND ATV.COLORCODE IN ('PALA','PADE') LEFT JOIN ACTIVITYMAPPING ATVM ON ATVM.ACTIVITYID = ATV.ID AND ATVM.MAPPEDACTIVITYID = -4001 WHERE ATV.ID <> -4001 AND ATVM.MAPPEDACTIVITYID IS NULL AND PLANE.ISUNPUBLISHED = 0 AND DATEADD(MINUTE,@BIAS,SHAB.STARTTIME) BETWEEN @FROMDATE AND @TODATE )PLA ON PLA.WORKRESOURCEID = EMP.ID

-------------------------------
-----EXCEPTION-----------------
-------------------------------

 JOIN (SELECT EMPLOYEEID,
 PLANNEDEVENTTIMELINEID,
 DATEDIFF(SECOND,DATEADD(MINUTE,@BIAS,STARTTIME),DATEADD(MINUTE,@BIAS,ENDTIME))EXPECTION
 FROM ADHERENCEEXCEPTION

)EXC ON PLA.ID=EXC.PLANNEDEVENTTIMELINEID
-------------------------------
----------SUPERVISOR-----------
-------------------------------
LEFT JOIN (SELECT DISTINCT EMPLOYEEID,
SUPERVISOREMPLOYEEID,
DATEADD(MINUTE,@BIAS,STARTTIME) STARTTIME,
DATEADD(MINUTE,@BIAS,ISNULL(ENDTIME,GETDATE())) ENDTIME
FROM SUPERVISOR
)SPV ON SPV.EMPLOYEEID = EMP.ID
AND SPV.ENDTIME > PLA.STARTTIME
AND SPV.STARTTIME < PLA.ENDTIME

 LEFT JOIN (SELECT EMPLOYEEAM.ID,
 PERSON.LASTNAME
 FROM EMPLOYEEAM
 JOIN PERSON ON PERSON.ID = EMPLOYEEAM.PERSONID

)EMP2 ON EMP2.ID = SPV.SUPERVISOREMPLOYEEID
-------------------------------
-----TEMPO REALIZADO-----------
-------------------------------
LEFT JOIN (SELECT DISTINCT EMPLOYEEID,
ACTIVITY.COLORCODE,
CONVERT(VARCHAR(10),DATEADD(MINUTE,@BIAS,SAB.STARTTIME),105) DATA,
DATEADD(MINUTE,@BIAS,ACTE.STARTTIME) STARTTIME,
DATEADD(MINUTE,@BIAS,ACTE.ENDTIME) ENDTIME--,
FROM ACTUALEVENTTIMELINE ACTE
JOIN ACTIVITY ON ACTIVITY.ID = ACTE.ACTIVITYID
JOIN SHIFTASSIGNMENTPUB SAB ON SAB.WORKRESOURCEID = ACTE.EMPLOYEEID
AND DATEADD(MINUTE,@BIAS,ACTE.STARTTIME) >= DATEADD(HOUR,-9,DATEADD(MINUTE,@BIAS,SAB.STARTTIME))
AND DATEADD(MINUTE,@BIAS,ACTE.ENDTIME) <= DATEADD(HOUR,9,DATEADD(MINUTE,@BIAS,SAB.ENDTIME))
WHERE ACTE.ACTIVITYID <> -4001
AND ACTIVITY.NAME<>'PERDA DE CONEXÃO'
AND DATEADD(MINUTE,@BIAS,SAB.STARTTIME) BETWEEN @FROMDATE AND @TODATE
)ACT ON ACT.EMPLOYEEID = PLA.WORKRESOURCEID
AND PLA.DATA = ACT.DATA
AND ACT.COLORCODE=PLA.COLORCODE
-------------------------------
--------ORGANIZACAO------------
-------------------------------
LEFT JOIN (SELECT DISTINCT ORGANIZATIONID,
WORKRESOURCEID,
DATEADD(MINUTE,TMZ2.BIAS,WRO.STARTTIME) STARTTIME,
ISNULL(DATEADD(MINUTE,TMZ.BIAS-1,WRO.ENDTIME),GETDATE()) ENDTIME,
ORG.NAME ORGANIZACAO
FROM WORKRESOURCEORGANIZATION WRO
JOIN TIMEZONEAM TMZ ON DATEADD(DAY,-1,ISNULL(WRO.ENDTIME,GETDATE())) < TMZ.ENDTIME
AND ISNULL(WRO.ENDTIME,GETDATE()) > TMZ.STARTTIME
JOIN TIMEZONEAM TMZ2 ON WRO.STARTTIME < TMZ2.ENDTIME
AND DATEADD(DAY,1,WRO.STARTTIME) > TMZ2.STARTTIME
JOIN ORGANIZATION ORG ON ORG.ID=WRO.ORGANIZATIONID
WHERE TMZ.TIMEZONE=@TIMEZONEID
AND TMZ2.TIMEZONE=@TIMEZONEID
)ORGA ON ORGA.WORKRESOURCEID = PLA.WORKRESOURCEID
AND ORGA.ENDTIME > PLA.STARTTIME
AND ORGA.STARTTIME < PLA.ENDTIME
/*
)FINAL
ORDER BY EMPLOYEENUMBER,STP,ETP
*/

Segue procedure nova:

DECLARE
@FROMDATE DATETIME,
@TODATE DATETIME,
@EMPLOYEEFILTERID INT,
@TIMEZONEID VARCHAR(50)

SET @FROMDATE = '2010-01-01 00:00:00'
SET @TODATE = '2010-12-31 23:59:59'
SET @EMPLOYEEFILTERID = 62054
SET @TIMEZONEID = 'AMERICA/SAO_PAULO'

--AS BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SET ARITHIGNORE ON

-------------------------------
--AJUSTE BIAS
-------------------------------
DECLARE @BIAS INT

SELECT @BIAS=BIAS
 FROM TIMEZONEAM
 WHERE TIMEZONEAM.TIMEZONE = @TIMEZONEID
 AND NOT((TIMEZONEAM.ENDTIME <= @FROMDATE)
 OR (TIMEZONEAM.STARTTIME > @TODATE))

SET @FROMDATE = DATEADD(MINUTE,ABS(@BIAS),@FROMDATE)
SET @TODATE = DATEADD(MINUTE,ABS(@BIAS),@TODATE)

-------------------------------
--EMPLOYEEFILTER
-------------------------------
DECLARE @EMPLOYEEFILTER TABLE
(
ID INT PRIMARY KEY,
NAME VARCHAR(50)
)

INSERT INTO @EMPLOYEEFILTER
SELECT ID,

NAME FROM EMPLOYEEFILTER WHERE ID = @EMPLOYEEFILTERID

-------------------------------
--EMPLOYEEFILTERWORKRESOURCE
-------------------------------
DECLARE @EMPLOYEEFILTERWORKRESOURCE TABLE
(
WORKRESOURCEID INT PRIMARY KEY,
NAME VARCHAR(50)
)

INSERT INTO @EMPLOYEEFILTERWORKRESOURCE
SELECT EFW.WORKRESOURCEID,
 EF.NAME
 FROM EMPLOYEEFILTERWORKRESOURCE EFW
 JOIN @EMPLOYEEFILTER EF ON EF.ID = EFW.EMPLOYEEFILTERID

-------------------------------
--EMPLOYEEAM
-------------------------------
DECLARE @EMPLOYEEAM TABLE
(
ID INT PRIMARY KEY,
EMPLOYEENUMBER VARCHAR(20),
LASTNAME VARCHAR(50)
)

INSERT INTO @EMPLOYEEAM
SELECT E.ID,

E.EMPLOYEENUMBER, P.LASTNAME FROM EMPLOYEEAM E JOIN @EMPLOYEEFILTERWORKRESOURCE EFW ON EFW.WORKRESOURCEID = E.ID JOIN PERSON P ON P.ID = E.PERSONID

-------------------------------
--PLANNEDEVENTTIMELINE
-------------------------------
DECLARE @PLANNEDEVENTTIMELINE TABLE
(
ID INT,
ACTIVITYID INT,
WORKRESOURCEID INT,
STARTTIME DATETIME,
ENDTIME DATETIME
PRIMARY KEY(ID, ACTIVITYID, WORKRESOURCEID, STARTTIME, ENDTIME)
)

INSERT INTO @PLANNEDEVENTTIMELINE
SELECT DISTINCT PLANE.ID,

PLANE.ACTIVITYID,
PLANE.WORKRESOURCEID,
PLANE.STARTTIME,
PLANE.ENDTIME
FROM PLANNEDEVENTTIMELINE PLANE
JOIN @EMPLOYEEFILTERWORKRESOURCE EFW ON EFW.WORKRESOURCEID = PLANE.WORKRESOURCEID
WHERE PLANE.STARTTIME BETWEEN @FROMDATE AND @TODATE
AND PLANE.ISUNPUBLISHED = 0

-------------------------------
--ACTUALEVENTTIMELINE
-------------------------------
DECLARE @ACTUALEVENTTIMELINE TABLE
(
ACTIVITYID INT,
EMPLOYEEID INT,
STARTTIME DATETIME,
ENDTIME DATETIME
PRIMARY KEY(ACTIVITYID, EMPLOYEEID, STARTTIME, ENDTIME)
)

INSERT INTO @ACTUALEVENTTIMELINE
SELECT DISTINCT ACTE.ACTIVITYID,

ACTE.EMPLOYEEID,
ACTE.STARTTIME,
ACTE.ENDTIME
FROM ACTUALEVENTTIMELINE ACTE
JOIN @EMPLOYEEFILTERWORKRESOURCE EFW ON EFW.WORKRESOURCEID = ACTE.EMPLOYEEID
WHERE ACTE.STARTTIME BETWEEN @FROMDATE AND @TODATE

-------------------------------
--SHIFTASSIGNMENTPUB
-------------------------------
DECLARE @SHIFTASSIGNMENTPUB TABLE
(
WORKRESOURCEID INT,
STARTTIME DATETIME,
ENDTIME DATETIME
PRIMARY KEY(WORKRESOURCEID, STARTTIME, ENDTIME)
)

INSERT INTO @SHIFTASSIGNMENTPUB
SELECT DISTINCT SAP.WORKRESOURCEID,

SAP.STARTTIME,
SAP.ENDTIME
FROM SHIFTASSIGNMENTPUB SAP
JOIN @EMPLOYEEFILTERWORKRESOURCE EFW ON EFW.WORKRESOURCEID = SAP.WORKRESOURCEID
WHERE SAP.STARTTIME BETWEEN @FROMDATE AND @TODATE

-------------------------------
--ADHERENCEEXCEPTION
-------------------------------
DECLARE @ADHERENCEEXCEPTION TABLE
(
ID INT,
EMPLOYEEID INT,
PLANNEDEVENTTIMELINEID INT,
EXPECTION DATETIME
PRIMARY KEY(ID, PLANNEDEVENTTIMELINEID)
)

INSERT INTO @ADHERENCEEXCEPTION
SELECT DISTINCT AE.ID,

AE.EMPLOYEEID,
AE.PLANNEDEVENTTIMELINEID,
DATEDIFF(SECOND, AE.STARTTIME, AE.ENDTIME) EXPECTION
FROM ADHERENCEEXCEPTION AE
JOIN @PLANNEDEVENTTIMELINE PLA ON PLA.ID = AE.PLANNEDEVENTTIMELINEID

-------------------------------
--SUPERVISOR
-------------------------------
DECLARE @SUPERVISOR TABLE
(
EMPLOYEEID INT,
LASTNAME VARCHAR(50),
STARTTIME DATETIME,
ENDTIME DATETIME
PRIMARY KEY(EMPLOYEEID, LASTNAME, STARTTIME, ENDTIME)
)

INSERT INTO @SUPERVISOR
SELECT DISTINCT SPV.EMPLOYEEID,

P.LASTNAME,
SPV.STARTTIME,
ISNULL(SPV.ENDTIME,GETDATE()) ENDTIME
FROM @EMPLOYEEAM E
LEFT JOIN SUPERVISOR SPV ON E.ID = SPV.EMPLOYEEID
LEFT JOIN EMPLOYEEAM ESPV ON SPV.SUPERVISOREMPLOYEEID = ESPV.ID
JOIN PERSON P ON P.ID = ESPV.PERSONID

/*

SELECT ORGANIZACAO,
 EMPLOYEENUMBER,
 OPERADOR,
 SUPERVISOR,
 COLORCODE,
 ATIVIDADE,
 STP,
 ETP,
 [STR],
 ETR,
 1-(CAST(EX AS FLOAT)/CAST(PLA AS FLOAT)) ACERTIVIDADE,
 [EX],
 PLA,
 REA
 FROM (SELECT ORGA.ORGANIZACAO,
 ISNULL(EMP2.LASTNAME,'SEM SUPERVISOR') SUPERVISOR,
 EMP.LASTNAME OPERADOR,
 EMP.EMPLOYEENUMBER,
 PLA.COLORCODE,
 PLA.ATIVIDADE,
 PLA.STARTTIME STP,
 PLA.ENDTIME ETP,
 ACT.STARTTIME [STR],
 ACT.ENDTIME ETR,
 ISNULL(DATEDIFF(SECOND,PLA.STARTTIME,PLA.ENDTIME),0)[PLA],
 ISNULL(DATEDIFF(SECOND,ACT.STARTTIME,ACT.ENDTIME),0)[REA],
 ISNULL(EXPECTION,0)[EX]
 FROM

*/

SELECT EMP.*, SPV.LASTNAME
 FROM @EMPLOYEEAM EMP
INNER JOIN (SELECT DISTINCT PLA.ID,

SAP.WORKRESOURCEID, CONVERT(VARCHAR(10), SAP.STARTTIME, 105)DATA, ATV.COLORCODE, ATV.NAME ATIVIDADE, PLA.STARTTIME STARTTIME, PLA.ENDTIME ENDTIME FROM @SHIFTASSIGNMENTPUB SAP INNER JOIN @PLANNEDEVENTTIMELINE PLA ON SAP.WORKRESOURCEID = PLA.WORKRESOURCEID AND DATEADD(HOUR, -1, SAP.STARTTIME) < PLA.STARTTIME AND DATEADD(HOUR, 1, SAP.ENDTIME) > PLA.ENDTIME INNER JOIN ACTIVITY ATV ON ATV.ID = PLA.ACTIVITYID AND ATV.COLORCODE IN ('PALA','PADE') LEFT JOIN ACTIVITYMAPPING ATVM ON ATVM.ACTIVITYID = ATV.ID AND ATVM.MAPPEDACTIVITYID = -4001 WHERE ATV.ID <> -4001 AND ATVM.MAPPEDACTIVITYID IS NULL) PLA ON PLA.WORKRESOURCEID = EMP.ID INNER JOIN @ADHERENCEEXCEPTION EXC ON PLA.ID = EXC.PLANNEDEVENTTIMELINEID LEFT JOIN @SUPERVISOR SPV ON SPV.EMPLOYEEID = EMP.ID AND SPV.ENDTIME > PLA.STARTTIME AND SPV.STARTTIME < PLA.ENDTIME LEFT JOIN (SELECT DISTINCT ACT.EMPLOYEEID, CONVERT(VARCHAR(10), SAP.STARTTIME, 105)DATA, ATV.COLORCODE, ACT.STARTTIME STARTTIME, ACT.ENDTIME ENDTIME FROM @SHIFTASSIGNMENTPUB SAP INNER JOIN @ACTUALEVENTTIMELINE ACT ON SAP.WORKRESOURCEID = ACT.EMPLOYEEID AND ACT.STARTTIME >= DATEADD(HOUR, -9, SAP.STARTTIME) AND ACT.ENDTIME <= DATEADD(HOUR, 9, SAP.ENDTIME) INNER JOIN ACTIVITY ATV ON ATV.ID = ACT.ACTIVITYID WHERE ATV.ID <> -4001 AND ATV.NAME <> 'PERDA DE CONEXÃO') ACT ON PLA.WORKRESOURCEID = ACT.EMPLOYEEID AND PLA.DATA = ACT.DATA AND PLA.COLORCODE = ACT.COLORCODE LEFT JOIN (SELECT DISTINCT ORGANIZATIONID, WORKRESOURCEID, DATEADD(MINUTE,TMZ2.BIAS,WRO.STARTTIME) STARTTIME, ISNULL(DATEADD(MINUTE,TMZ.BIAS-1,WRO.ENDTIME),GETDATE()) ENDTIME, ORG.NAME ORGANIZACAO FROM WORKRESOURCEORGANIZATION WRO INNER JOIN TIMEZONEAM TMZ ON DATEADD(DAY,-1,ISNULL(WRO.ENDTIME,GETDATE())) < TMZ.ENDTIME AND ISNULL(WRO.ENDTIME,GETDATE()) > TMZ.STARTTIME INNER JOIN TIMEZONEAM TMZ2 ON WRO.STARTTIME < TMZ2.ENDTIME AND DATEADD(DAY,1,WRO.STARTTIME) > TMZ2.STARTTIME INNER JOIN ORGANIZATION ORG ON ORG.ID=WRO.ORGANIZATIONID WHERE TMZ.TIMEZONE = @TIMEZONEID AND TMZ2.TIMEZONE = @TIMEZONEID) ORGA ON PLA.WORKRESOURCEID = ORGA.WORKRESOURCEID AND ORGA.ENDTIME > DATEADD(MINUTE,@BIAS,PLA.STARTTIME) AND ORGA.STARTTIME < DATEADD(MINUTE,@BIAS,PLA.ENDTIME) /* )FINAL ORDER BY EMPLOYEENUMBER,STP,ETP */

O problema da lentidão eu já vi que é na hora do LEFT JOIN com a tabela @ACTUALEVENTTIMELINE, porém não sei como ajustar, nesta tabela existem mais de 5.000 registros.

Alguém poderia da uma ajudar?

NOTA: Estou usando SQL Server 2008.

Muito obrigado!