Procedimientos Almacenados lentos en SQL Server

Si trabajas con SQL Server es muy posible que te haya ocurrido que un Procedimiento Almacenado te tarda muchísimo, pero si ejecutas la misma consulta, y en las mismas condiciones de estado en el servidor de base de datos, en el Analizador tarda sólo segundos. ¿Por qué?. La respuesta es el Parameter Sniffing, que ocurre con los Procedimientos Almacenados que tienen Parámetros y que utilizan dichos Parámetros en sus consultas. El motor de base de datos recoge los valores de los parámetros y los entrega al Optimizador de Consultas, y ya éste genera el Plan de Ejecución más apropiado, siempre que usemos los parámetros en sentencias 'where' por ejemplo. El Plan de Ejecución no se genera con la creación del Procedimiento Almacenado, sino que por el contrario, se crea y se cachea en la primera ejecución del Procedimiento Almacenado.

¿Hay alguna solución?. Sí, por supuesto, y como casi siempre suele ser de lo más simple: crear variables locales al Procedimiento Almacenado que recojan los valores de los parámetros, y usar dichas variables locales en las sentencias where. Un ejemplo:
CREATE PROCEDURE dbo.miprocedimiento @Id AS VARCHAR(5) AS
DECLARE @pId VARCHAR(5)
SET @pId = @Id
.......
SELECT ... FROM ... WHERE ... (campo=@pId)

Hace pocos días conocí un caso de un procedimiento almacenado que tardaba más de 30 minutos en completarse y con este "truco" los resultados se obtuvieron en 3 segundos. ¿Interesante, verdad?. Pues mañana más, jejejeje. Nos vemos.

18 comentarios :

  1. Muchas gracias por este articulo, no sabes como me ayudo.

    Muy Agradecido.
    Jorge.

    ResponderEliminar
  2. Gracias a tí por visitar mi Blog y me alegro que te haya sido de utilidad el consejo. Por aquí estaremos para seguir ayudando en lo que se pueda.

    ResponderEliminar
  3. sirvira esto en oracle? probare por si funciona...

    ResponderEliminar
  4. Hola Michea, gracias por visitar éste tu Blog. Creo que en Oracle existe un problema parecido con el "Bind variable peeking"... ¡Saludos!

    ResponderEliminar
  5. Infinitas gracias, me ayudo muchisimo.

    ResponderEliminar
  6. gracias por el truco, se optimizó mucho el tiempo de respuesta del SP

    ResponderEliminar
  7. Salve viejo, que desgraciado
    el procedimiento original 03:20
    con la modificacion 00:01
    Que buena informacion

    ResponderEliminar
  8. eres un hijo de tu mama, esta opcion me funciona chevere al segundo se nota la diferencia, anteriormente me estaba tardando un ku.... pero ahora corre de maravilla sos un campeon tio

    ResponderEliminar
  9. Excelente aporte hermano gracias y saludos!!!

    ResponderEliminar
  10. De verdad muchisimas gracias por tu aporte

    ResponderEliminar
  11. Gracias!! Pro el articulo, Me ayudo muchisimo...

    ResponderEliminar
  12. Perfecto! me ha aclarado bastante

    ResponderEliminar
  13. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  14. "Etselente...!!!" muy buena explicacion (Y)

    ResponderEliminar
  15. ERES UN BARBARO GRACIASSSSSSSSSSSS

    ResponderEliminar
  16. Muchas Gracias Pasa de tiempo de espera agotado (>30 seg) a menos de 1 segundo.

    ResponderEliminar

Buscar en el Blog: