Procedure muito lenta

[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!

[fulvio]

Bom dia Bonamim,

O problema não está no LEFT JOIN, mas sim nos subselects e temporárias que está utilizando.

Não sei a lógica nem os relacionamentos entre as tabelas, mas a princípio terá que:

- Diminuir ao máximo as tabelas temporárias (criá-las só quando não tiver jeito mesmo). As tabelas temporárias além de consumir muita memória, não possui formas "boas" de indexação. Se na temporária tiver muitos registros e ainda fizer JOIN's com outras temporárias, seu tempo de resposta vai nas alturas.

- Nunca utilizar subselects (eles mais que duplicam o tempo de execução, pois o sql aguarda o resultado de um select para executar o outro).

Analise os relacionamentos entre as tabelas e procure extrair os dados em um sql "limpo". Esta será a melhor forma de otimizar o resultado.

OBS.: 5 mil registros é fichinha pro sql. Manipulamos 30 milhões de registros com resultados satisfatórios.