El boletín gratuito de SQL Server de TechRepublic, que se entrega cada martes, contiene consejos prácticos que lo ayudarán a ser más experto con este poderoso sistema de administración de bases de datos relacionales. ¡Suscríbete automáticamente hoy!

La sabiduría generalmente aceptada sobre los procedimientos almacenados (o sprocs) es que debido a que SQL puede optimizarlos y compilarlos,se ejecutan más rápidamente que las sentencias SQL equivalentes ejecutadas desde QueryAnalyzer (o tal vez pasadas desde alguna aplicación de front-end, como una página web o un programa VB). Esto es cierto hasta cierto punto.

Primero, necesita saber qué hace SQL Server con un nuevo sproc. En el momento de la creación, comprueba la sintaxis. Si no encuentra ningún error, entonces agrega el sproc a las tablas del sistema: sysobjects, sysdepends y syscomments (este último almacena el cuerpo del sproc). Por defecto, no compila el sproc en el momento de la creación.

Tras la primera ejecución del sproc, SQL Server lo optimiza y compila. Esto es cuando SQL Server diseña un queryplan y lo almacena en su caché de procedimientos. En invocaciones posteriores, SQL busca primero en la caché, encuentra el sproc allí y no lo compila. Si el sproc no está en la caché,SQL Server lo compila y lo coloca en la caché.

Mi experiencia con la opción CON RECOMPILAR

Hace un tiempo, apoyaba una página de búsqueda que permitía a sus usuarios buscar por cualquiera de varias columnas. Luego, la página se llama sproc, pasando un parámetro para indicar qué columna buscar. Examiné el parámetro usando un bloque de mayúsculas y minúsculas, y luego ejecuté una de varias consultas, dependiendo de la columna a buscar.

Supe que algo estaba mal cuando comencé a probar mi procedimiento almacenado allegedlyclever. En teoría, el rendimiento de cada búsqueda debería al menos ser aproximadamente el mismo, pero eso no es lo que sucedió. Cuando realizaba múltiples búsquedas, independientemente del orden, la primera era rápida, y las búsquedas posteriores eran mucho más lentas.

Finalmente, me di cuenta de que la primera vez que se llamó al procedimiento, se ideó un plan de consulta y se almacenó en la caché. Mientras buscara en esa columna en particular, todo funcionaría como se esperaba. Sin embargo, en el momento en que cambié de columna, el rendimiento se desplomó. ¿Por qué pasó esto?

La primera búsqueda que realicé creó un plan de consulta y storedit en la caché. Por ejemplo, digamos que estaba buscando en la columna OrderDate. Si cambiaba la búsqueda a la columna CompanyName, SQL usaría ciegamente el queryplan en caché, buscando el nombre de la empresa de destino usando el OrderDateindex. No es de extrañar que el rendimiento se desplomara tan dramáticamente.

La solución es bastante simple. Ejecuté el suministro de la opción CON RECOMPILAR:

EXEC MySproc_Select '12/31/2004' WITH RECOMPILE

Esto le dice a SQL Server que deseche el plan de consulta existente y construya otro plan pero solo esta vez.

También puede agregar el RECOMPILADO WITH directamente al proceso almacenado justo antes de la palabra clave AS. Esto le indica a SQL Server que deseche el planquery en cada ejecución del sproc.

también Hay una tercera opción. Podría haber creado un sproc separado para cada método de búsqueda, y luego decidir cuál ejecutar dentro del bloque de CASOS. De esta manera,el plan de consulta asociado con los subesprocs permanece en la caché, donde SQL puede aprovecharlos. Dado que cada uno de los piñones buscó exactamente una columna, no hay necesidad de recompilar.

La capacidad de SQL Server para optimizar y compilar un proceso almacenado es excelente, pero, si no tiene cuidado, puede morderle cuando lo selecciona. Ahora que sabe cómo lidiar con el problema, tal vez haya algunas situaciones en su propia base de datos que tal vez desee revisar.