Procedure muito lenta

[Bonamim 0]

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!

[advaldomesquita 93]

oque diz o plano de execução?