Performance Issues - TSQL 2008 R2

JnavarroMc
JnavarroMc used Ask the Experts™
on
Hello!

I have some performance problems with the following query:

exec DBP_Obtener_Reporte_Productividad_Trabajador @pcodi_operacion=N'02',@pcodi_lugar=N'',@pfech_inicio=N'2017/04/01',@pfech_termino=N'2018/04/30',@codi_usuario=N'SISTEMAS'

Open in new window


USE [PACKING]
GO

/****** Object:  StoredProcedure [dbo].[DBP_Obtener_Reporte_Productividad_Trabajador]    Script Date: 05/23/2018 12:34:14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

  
 
  
CREATE PROCEDURE [dbo].[DBP_Obtener_Reporte_Productividad_Trabajador] (      
@PCODI_OPERACION   VARCHAR(3),      
@PCODI_LUGAR       VARCHAR(3),      
@PFECH_INICIO      VARCHAR(20),      
@PFECH_TERMINO     VARCHAR(20) ,      
@CODI_USUARIO      VARCHAR(100)    
)      
AS      
BEGIN       
    
SELECT A.*     
  INTO #DETALLE_ORDEN_PDT    
  FROM DETALLE_ORDEN_PDT  A  
    
    INNER JOIN  (    
    SELECT DISTINCT CODI_OPERACION,CODI_LUGAR  
    FROM BDGESTIONUSR.DBO.INFORMACION_USUARIO     
    WHERE  CODI_OPERACION = @PCODI_OPERACION    
    AND CODI_LUGAR    LIKE '%' + @PCODI_LUGAR +'%'       
    AND CODI_USUARIO  = @CODI_USUARIO ) USER_    
  ON A.CODI_OPERACION = USER_.CODI_OPERACION    
  AND A.CODI_LUGAR = USER_.CODI_LUGAR      
  WHERE CODI_COMPANIA IS NOT NULL AND     
 A.CODI_OPERACION =  @PCODI_OPERACION  AND     
 A.CODI_LUGAR  LIKE '%' + @PCODI_LUGAR +'%'      AND    
  CODI_SECTOR IS NOT NULL AND     
  LOTE_KEY IS NOT NULL AND     
  CODI_LINEA IS NOT NULL AND     
  CODI_TRABAJADOR IS NOT NULL AND     
  CODI_BARRA_TRABAJADOR IS NOT NULL AND     
  CODI_CALIBRE IS NOT NULL AND     
  CODI_BARRA_PRODUCTO IS NOT NULL AND     
  CODI_RESPONSABLE IS NOT NULL       
  AND FECH_EMPAQUE BETWEEN @PFECH_INICIO AND @PFECH_TERMINO        
    
SELECT @PFECH_INICIO,      
       @PFECH_TERMINO,      
       O.NOMB_OPERACION,      
       L.NOMB_LUGAR,      
       DBO.MID(D.FECH_COSECHA,1,10) AS FECH_COSECHA,      
       DBO.MID(D.FECH_EMPAQUE,1,10) AS FECH_EMPAQUE,      
       P.CODI_PRODUCTO,      
       P.NOMB_PRODUCTO,      
       C.NOMB_CORTO_CALIBRE AS CODI_CALIBRE,      
       D.CODI_TRABAJADOR,      
       T.NOMB_TRABAJADOR,      
       SUM(CANT_CAJA) AS CANT_CAJAS,      
       ROUND(SUM(CANT_CAJA * PR.CANT_KILOS / CU.CANT_KILOS_STANDARD),2) AS CANT_EQUIV      
   
         
   FROM #DETALLE_ORDEN_PDT D       
   LEFT JOIN TRABAJADOR T ON T.CODI_COMPANIA = D.CODI_COMPANIA 
                         AND T.CODI_OPERACION = D.CODI_OPERACION 
                         AND T.CODI_TRABAJADOR = D.CODI_TRABAJADOR      
   LEFT JOIN PRODUCTO   P ON P.CODI_PRODUCTO   = D.CODI_PRODUCTO      
   LEFT JOIN LUGAR      L ON L.CODI_LUGAR      = D.CODI_LUGAR      
   LEFT JOIN OPERACION  O ON O.CODI_COMPANIA=D.CODI_COMPANIA 
                         AND O.CODI_OPERACION  = D.CODI_OPERACION      
   LEFT JOIN CALIBRE    C ON C.CODI_CALIBRE    = D.CODI_CALIBRE
                         AND C.CODI_CULTIVO    = p.codi_cultivo				-- HCMM20170907 
   LEFT JOIN PRESENTACION PR ON PR.CODI_PRESENTACION = P.CODI_PRESENTACION      
   INNER JOIN CULTIVO CU ON CU.CODI_CULTIVO = P.CODI_CULTIVO    
  WHERE D.CODI_OPERACION = @PCODI_OPERACION       
 AND CASE WHEN LEN(@PCODI_LUGAR) = 0 THEN '' ELSE D.CODI_LUGAR END = @PCODI_LUGAR     
-- AND D.CODI_LUGAR LIKE '%'+ @PCODI_LUGAR +'%'    
    AND  CONVERT(VARCHAR(8), D.FECH_EMPAQUE,112) BETWEEN CONVERT(VARCHAR(10), CAST(@PFECH_INICIO AS DATE),112) AND CONVERT(VARCHAR(10), CAST(@PFECH_TERMINO AS DATE) ,112)    
 --    SP_HELPINDEX OPERACION    
    GROUP BY O.NOMB_OPERACION,      
             L.NOMB_LUGAR,      
             D.FECH_COSECHA,      
             D.FECH_EMPAQUE,      
             P.CODI_PRODUCTO,      
             P.NOMB_PRODUCTO,      
             C.NOMB_CORTO_CALIBRE,      
             D.CODI_TRABAJADOR,      
             T.NOMB_TRABAJADOR      
    ORDER BY P.CODI_PRODUCTO,T.NOMB_TRABAJADOR      
END 

GO

Open in new window


I tried to work with the RECOMPILE and OPTIMIZE FOR UNKNOWN option but I don't have results.

Can you help me or give me any feedback?

Thank you very much,
JN
PLAN2.sqlplan
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
My immediate response, before looking at the execution plans is to change this:
   AND CONVERT(varchar(8), d.FECH_EMPAQUE, 112) BETWEEN CONVERT(varchar(10), CAST(@PFECH_INICIO AS date), 112) AND CONVERT(varchar(10), CAST(@PFECH_TERMINO AS date), 112)

PLEASE do NOT convert DATA using functions in the where clause (this remove the use of indexes).

In addition, PLEASE, do NOT change date/time information into varchars (this makes it slower as well).

Using "between" is also a problem, and because it is a problem, you have chosen to convert that data instead of adjusting the parameters.

e.g.
@pfech_inicio=N'2017/04/01',@pfech_termino=N'2018/04/30'

what you want for that date rage is everything >= 2017/04/01 and less than 2018/05/01


  AND d.FECH_EMPAQUE >= CAST(@PFECH_INICIO AS date) AND d.FECH_EMPAQUE < dateadd(day,1,CAST(@PFECH_TERMINO AS date))

This avoids changing any data so the query can use indexes on that date column and the date range will be accurate.

by the way, it might be safer to pass the date literals as YYYYMMDD (ie. style 112), if you did this then you could do this:

@pfech_inicio=N'20170401',@pfech_termino=N'20180430'

  AND d.FECH_EMPAQUE >= @PFECH_INICIO AND d.FECH_EMPAQUE < dateadd(day,1,@PFECH_TERMINO)
Vitor MontalvãoMSSQL Senior Engineer
Distinguished Expert 2017
Commented:
tried to work with the RECOMPILE and OPTIMIZE FOR UNKNOWN option but I don't have results.
You went directly to change compilation options without even checking the query itself.
Besides Paul's useful comments, I can see other performance killer statements (DISTINCT, ORDER BY, LIKE '%'+...+'%') and a call to a function that we don't know what does (DBO.MID).
JnavarroMcIT Consultant

Author

Commented:
Sorry for the delay! I still haven't been able to connect with the client to make the changes.
JnavarroMcIT Consultant

Author

Commented:
Thank you!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Thanks for returning and closing it off - always appreciated. Cheers, Paul

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial