none
Procedure muito lenta RRS feed

  • Pergunta

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


    Jefferson Bonamim - Estudante | Email: supersbonamim@hotmail.com | Caso esta sugestão for útil, por favor, classifique-a como 'Útil'. Se caso ela lhe ajudar a resolver o problema, por favor, clique em 'Marcar como Resposta'.

    terça-feira, 19 de fevereiro de 2013 00:57

Respostas

  • Jeferson, Bom dia!

    Apenas postando a sua procedure fica impossivel poder ajudar a um nivel bacana pois não conhecemos seu ambiente, memoria, processador, utilização, tabelas, indices, meios de acesso, enfim! muitas coisas podem influenciar na performance de uma procedure.

    Olhando a proc o que eu posso te dizer é:

    - Substitua as Variaveis de tabela (@Tabela) que tiverem muitos dados por tabelas temporarias, variaveis de tabela não possuem indice ou estatisticas o que faz com que sua performance na utilização seja bem restrita a tabelas pequenas (E olhe la....) alem de utilizar a base tempDB, que pode ser um gargalo caso esteja sendo muito utilizada como uma mão na roda em questão de performance pois normalmente esta apartada em discos exclusivos com melhores configurações para I/O(Como um RAID 1 por exemplo).

    - Evite negatividade, por exemplo, ao invez de colocar <> -4001 coloque IN (E todas as possiblidades possiveis), isso fara a utilização correta de uma estrutura segundaria de pesquisa (Indice) e passara a fazer um seek direto (Dado a variação do dado claro) enquanto a negatividade no melhor dos casos um Index Scan.

    - Cuidado com DATEADD(DAY, -1, Data) < GETDATE() [Ou qualquer variação disso], a partir do momento que voce coloca o DATEADD na expressão do campo de restrição voce mata todas as possibilidades de indexação, tente tratar este campo antes (Em uma tabela temporaria!) de coloca-lo no join desta maneira.


    Fabrizzio A. Caputo
    MCT
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    ITIL V3 Foundation
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com

    terça-feira, 19 de fevereiro de 2013 12:04
    Moderador