Bonamim

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!