none
Un procedimiento lento y al alterar rapido RRS feed

  • Pregunta

  • Tengo un procedimiento almacenado que me genera cierta informacion (select y cursores) cada cierto tiempo esa consulta se queda pegada (muuyy lentaa) pero cuando cogemos el procedimiento y lo alteramos (un alter sin modificar nada en absoluto) la consulta vuelve a funcionar perfecto, rápida.

    Que le podria mirar ?? indices, cursores... no se

    Por favor, ayudenme

    Muchas gracias

    jueves, 22 de noviembre de 2012 13:50

Respuestas

  • Lo que comentas parece un claro ejemplo de que el plan de ejecución del procedimiento que se generó al principio ha quedado desfasado con los valores de los parámetros que se le van pasando (la parte mala del "parameter sniffing")

    Aunque no sea una práctica muy recomendable (habría que analizar más en detalle), puedes probar a forzar a que se recompile la instrucción en cada ejecución (aunque eso supone un sobrecoste a nivel de CPU), para asegurarte que el plan de ejecución que se genera es el más adecuado para los valores de los parámetros. Es decir, tu procedimiento quedaría como sigue:

    ALTER PROCEDURE [dbo].[RPT_COLILLA_CONCEPTOS_POS_DARSER]
     @pr_dtmFechaCruce AS DATETIME,
     @pr_intCodigoUsuario as INT
     
    
    AS
     BEGIN
     
    SELECT DISTINCT tblSERVicio.strNombre_SERV [Servicio], tblClasificacionCargos.strDescripcion_ClaCar [Clasificación],
     tblHORA.strSigla_HORA [Tipo Hora], tblDetalleTurnos.dcmVrBruto_DETU  / tblDetalleTurnos.intCantidad_DETU /*tblDEtalleHOras.dcmValorHora*/ [Valor] ,tblDetalleTurnos.intCantidad_DETU[Cantidad],
     tblDetalleTurnos.dcmVrHora_DETU * tblDetalleTurnos.intCantidad_DETU [Total]
         FROM tblPagoCompesacion 
     INNER JOIN tblCOMPensacion ON tblPagoCompesacion.intCodigo_COMP = tblCOMPensacion.intCodigo_COMPensacion 
     INNER JOIN tblDetalleTurnos ON tblDetalleTurnos.intCodigo_DETU = tblCOMPensacion.intCodigo_DETU 
     INNER JOIN tblHORA ON tblDetalleTurnos.intCodigo_HORA = tblHORA.intCodigo_HORA 
     INNER JOIN tblDEtalleMOdelo ON tblCOMPensacion.intCodigo_DEMO = tblDEtalleMOdelo.intCodigo_DEMO 
     INNER JOIN tblSERVicio ON tblDEtalleMOdelo.intCodigo_SERV = tblSERVicio.intCodigo_SERV
     INNER JOIN tblDEtalleHOras ON tblSERVicio.intCodigo_SERV = tblDEtalleHOras.intCodigo_SERV AND tblDetalleTurnos.intCodigo_HORA = tblDEtalleHOras.intCodigo_HORA
     INNER JOIN tblClasificacionCargos ON tblSERVicio.intCodigo_ClaCar = tblClasificacionCargos.intCodigo_ClaCar
     INNER JOIN tblTURNos ON tblDetalleTurnos.intCodigo_TURNos = tblTURNos.intCodigo_TURNos
     WHERE tblPagoCompesacion.dtmFechaCruce = @pr_dtmFechaCruce /*'31/05/2012'*/ AND tblDetalleTurnos.intCodigo_USUA = @pr_intCodigoUsuario /*3615*/ 
     AND tblDEtalleMOdelo.blnOrdinario = 1 AND (tblDEtalleMOdelo.blnAuxilioTransp = 0 or tblDEtalleMOdelo.blnAuxilioTransp is null)
     AND tblDEtalleMOdelo.blnNaturaleza_DEMO = 1
    OPTION (RECOMPILE)

    jueves, 22 de noviembre de 2012 17:52

Todas las respuestas

  • Hola Talito, comenzando con el hecho de usar cursores no es muy buena idea si de rendimiento estamos hablando, también deberías observar si utilizas tablas temporales y si las estás limpiando cada que se ejecuta el procedimiento, además de revisar algunas otras cosas como por ejemplo cantidad de información que manejas, está información crece día a día, etc...

    Si puedes postear tu procedimiento y nos explicas con un poco más a detalle tal vez te podamos ayudar.

    SALUDOS!


    Sergio Sánchez Arias

    jueves, 22 de noviembre de 2012 13:58
  • Perdon, no tiene cursores, ni usa tabla temporales

    este es el script, genera informacion para una colilla de pago

    Cada cierto tiempo se vuelve lenta, y cuando se altera vuelve a estar rapida

    todos los días se ingresan datos que son consultadas por el script

    ALTER PROCEDURE [dbo].[RPT_COLILLA_CONCEPTOS_POS_DARSER]
    @pr_dtmFechaCruce AS DATETIME,
    @pr_intCodigoUsuario as INT


    AS
    BEGIN

    SELECT DISTINCT tblSERVicio.strNombre_SERV [Servicio], tblClasificacionCargos.strDescripcion_ClaCar [Clasificación],
    tblHORA.strSigla_HORA [Tipo Hora], tblDetalleTurnos.dcmVrBruto_DETU  / tblDetalleTurnos.intCantidad_DETU /*tblDEtalleHOras.dcmValorHora*/ [Valor] ,tblDetalleTurnos.intCantidad_DETU[Cantidad],
    tblDetalleTurnos.dcmVrHora_DETU * tblDetalleTurnos.intCantidad_DETU [Total]
        FROM tblPagoCompesacion 
    INNER JOIN tblCOMPensacion ON tblPagoCompesacion.intCodigo_COMP = tblCOMPensacion.intCodigo_COMPensacion 
    INNER JOIN tblDetalleTurnos ON tblDetalleTurnos.intCodigo_DETU = tblCOMPensacion.intCodigo_DETU 
    INNER JOIN tblHORA ON tblDetalleTurnos.intCodigo_HORA = tblHORA.intCodigo_HORA 
    INNER JOIN tblDEtalleMOdelo ON tblCOMPensacion.intCodigo_DEMO = tblDEtalleMOdelo.intCodigo_DEMO 
    INNER JOIN tblSERVicio ON tblDEtalleMOdelo.intCodigo_SERV = tblSERVicio.intCodigo_SERV
    INNER JOIN tblDEtalleHOras ON tblSERVicio.intCodigo_SERV = tblDEtalleHOras.intCodigo_SERV AND tblDetalleTurnos.intCodigo_HORA = tblDEtalleHOras.intCodigo_HORA
    INNER JOIN tblClasificacionCargos ON tblSERVicio.intCodigo_ClaCar = tblClasificacionCargos.intCodigo_ClaCar
    INNER JOIN tblTURNos ON tblDetalleTurnos.intCodigo_TURNos = tblTURNos.intCodigo_TURNos
    WHERE tblPagoCompesacion.dtmFechaCruce = @pr_dtmFechaCruce /*'31/05/2012'*/ AND tblDetalleTurnos.intCodigo_USUA = @pr_intCodigoUsuario /*3615*/ 
    AND tblDEtalleMOdelo.blnOrdinario = 1 AND (tblDEtalleMOdelo.blnAuxilioTransp = 0 or tblDEtalleMOdelo.blnAuxilioTransp is null)
    AND tblDEtalleMOdelo.blnNaturaleza_DEMO = 1

    END

    jueves, 22 de noviembre de 2012 14:45
  • Hola buenos días Talito,

    te puedo recomendar dos cosas que podrían ayudarte a mejorar el rendimiento de la consulta, a mi me funciono (En Oracle) y me optimizo una consulta reduciendo el tiempo considerablemente.

    Lo primero seria que revisaras la cardinalidad de las tablas, es decir, podrías tomar entre las tablas que están involucradas en la consulta, cual es la que contiene mas datos y llevarla así sucesivamente hasta llegar a la que menos datos tenga.

    como segunda medida y complementando la primera opción podrías tener el uso de las condiciones directamente sobre el INNER JOIN, y evitando usar el WHERE, ya que a medida que la consulta va analizando los datos que llegan le aplica el filtro es decir, no es lo mismo aplicarle el filtro en el momento que esta trayendo los datos (AND en el JOIN), a aplicárselo después de que la consulta traiga todos los datos (este seria el WHERE).

    Utilice tu consulta para hacer el ejemplo, en el cual notaras que el WHERE se redujo y las condiciones se llevarón a un AND después del JOIN. de esta forma puedes basarte para mejorar el rendimiento de tu consulta.

    NOTA: al usar solo la segunda opción también reduce significativamente el tiempo, pero para el mejor rendimiento podrías evaluar la cardinalidad.

    ALTER PROCEDURE [dbo].[RPT_COLILLA_CONCEPTOS_POS_DARSER]
    @pr_dtmFechaCruce AS DATETIME,
    @pr_intCodigoUsuario as INT
    
    
    AS
    BEGIN
    
    SELECT DISTINCT tblSERVicio.strNombre_SERV [Servicio], tblClasificacionCargos.strDescripcion_ClaCar [Clasificación],
    tblHORA.strSigla_HORA [Tipo Hora], tblDetalleTurnos.dcmVrBruto_DETU  / tblDetalleTurnos.intCantidad_DETU /*tblDEtalleHOras.dcmValorHora*/ [Valor] ,tblDetalleTurnos.intCantidad_DETU[Cantidad],
    tblDetalleTurnos.dcmVrHora_DETU * tblDetalleTurnos.intCantidad_DETU [Total]
        FROM tblPagoCompesacion 
    INNER JOIN tblCOMPensacion ON tblPagoCompesacion.intCodigo_COMP = tblCOMPensacion.intCodigo_COMPensacion AND tblPagoCompesacion.dtmFechaCruce = @pr_dtmFechaCruce /*'31/05/2012'*/ 
    INNER JOIN tblDetalleTurnos ON tblDetalleTurnos.intCodigo_DETU = tblCOMPensacion.intCodigo_DETU AND tblDetalleTurnos.intCodigo_USUA = @pr_intCodigoUsuario /*3615*/ 
    INNER JOIN tblHORA ON tblDetalleTurnos.intCodigo_HORA = tblHORA.intCodigo_HORA 
    INNER JOIN tblDEtalleMOdelo ON tblCOMPensacion.intCodigo_DEMO = tblDEtalleMOdelo.intCodigo_DEMO AND tblDEtalleMOdelo.blnOrdinario = 1 AND tblDEtalleMOdelo.blnNaturaleza_DEMO = 1
    INNER JOIN tblSERVicio ON tblDEtalleMOdelo.intCodigo_SERV = tblSERVicio.intCodigo_SERV
    INNER JOIN tblDEtalleHOras ON tblSERVicio.intCodigo_SERV = tblDEtalleHOras.intCodigo_SERV AND tblDetalleTurnos.intCodigo_HORA = tblDEtalleHOras.intCodigo_HORA
    INNER JOIN tblClasificacionCargos ON tblSERVicio.intCodigo_ClaCar = tblClasificacionCargos.intCodigo_ClaCar
    INNER JOIN tblTURNos ON tblDetalleTurnos.intCodigo_TURNos = tblTURNos.intCodigo_TURNos
    WHERE  (tblDEtalleMOdelo.blnAuxilioTransp = 0 or tblDEtalleMOdelo.blnAuxilioTransp is null)

    Espero sea de tu ayuda, y obtengas un resultado satisfactorio... que tengas un buen día...

    jueves, 22 de noviembre de 2012 16:25
  • Lo que comentas parece un claro ejemplo de que el plan de ejecución del procedimiento que se generó al principio ha quedado desfasado con los valores de los parámetros que se le van pasando (la parte mala del "parameter sniffing")

    Aunque no sea una práctica muy recomendable (habría que analizar más en detalle), puedes probar a forzar a que se recompile la instrucción en cada ejecución (aunque eso supone un sobrecoste a nivel de CPU), para asegurarte que el plan de ejecución que se genera es el más adecuado para los valores de los parámetros. Es decir, tu procedimiento quedaría como sigue:

    ALTER PROCEDURE [dbo].[RPT_COLILLA_CONCEPTOS_POS_DARSER]
     @pr_dtmFechaCruce AS DATETIME,
     @pr_intCodigoUsuario as INT
     
    
    AS
     BEGIN
     
    SELECT DISTINCT tblSERVicio.strNombre_SERV [Servicio], tblClasificacionCargos.strDescripcion_ClaCar [Clasificación],
     tblHORA.strSigla_HORA [Tipo Hora], tblDetalleTurnos.dcmVrBruto_DETU  / tblDetalleTurnos.intCantidad_DETU /*tblDEtalleHOras.dcmValorHora*/ [Valor] ,tblDetalleTurnos.intCantidad_DETU[Cantidad],
     tblDetalleTurnos.dcmVrHora_DETU * tblDetalleTurnos.intCantidad_DETU [Total]
         FROM tblPagoCompesacion 
     INNER JOIN tblCOMPensacion ON tblPagoCompesacion.intCodigo_COMP = tblCOMPensacion.intCodigo_COMPensacion 
     INNER JOIN tblDetalleTurnos ON tblDetalleTurnos.intCodigo_DETU = tblCOMPensacion.intCodigo_DETU 
     INNER JOIN tblHORA ON tblDetalleTurnos.intCodigo_HORA = tblHORA.intCodigo_HORA 
     INNER JOIN tblDEtalleMOdelo ON tblCOMPensacion.intCodigo_DEMO = tblDEtalleMOdelo.intCodigo_DEMO 
     INNER JOIN tblSERVicio ON tblDEtalleMOdelo.intCodigo_SERV = tblSERVicio.intCodigo_SERV
     INNER JOIN tblDEtalleHOras ON tblSERVicio.intCodigo_SERV = tblDEtalleHOras.intCodigo_SERV AND tblDetalleTurnos.intCodigo_HORA = tblDEtalleHOras.intCodigo_HORA
     INNER JOIN tblClasificacionCargos ON tblSERVicio.intCodigo_ClaCar = tblClasificacionCargos.intCodigo_ClaCar
     INNER JOIN tblTURNos ON tblDetalleTurnos.intCodigo_TURNos = tblTURNos.intCodigo_TURNos
     WHERE tblPagoCompesacion.dtmFechaCruce = @pr_dtmFechaCruce /*'31/05/2012'*/ AND tblDetalleTurnos.intCodigo_USUA = @pr_intCodigoUsuario /*3615*/ 
     AND tblDEtalleMOdelo.blnOrdinario = 1 AND (tblDEtalleMOdelo.blnAuxilioTransp = 0 or tblDEtalleMOdelo.blnAuxilioTransp is null)
     AND tblDEtalleMOdelo.blnNaturaleza_DEMO = 1
    OPTION (RECOMPILE)

    jueves, 22 de noviembre de 2012 17:52