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!