sintaxis - ¿Los procedimientos almacenados realmente mejoran el rendimiento en MS SQL/.NET?

procedimientos almacenados sql ejemplos

Question

Jeff Atwood escribió sobre esto here , y si bien entiendo el aumento de rendimiento teórico que podría ofrecer un procedimiento almacenado, parece un dolor tremendo.

¿Qué tipos de consultas vería que el mayor rendimiento aumenta con los procedimientos almacenados y qué tipo de consultas prefiere construir sobre la marcha?

Cualquier documentación de una manera u otra sería muy apreciada.

Answer 1

Como es común con el software, el problema del almacenamiento en caché de SQL es más sutil de lo que parece. Por ejemplo, veamos el almacenamiento en caché de una consulta SQL ad-hoc :

-- First, clear the cache
DBCC FREEPROCCACHE
-- Look at what executable plans are in cache
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = ''Executable Plan''
-- Execute the following statement
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE t.price = 19.99
-- Look at what executable plans are in cache and you''ll
-- find that there''s a plan for a NUMERIC(4,2)
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = ''Executable Plan''
-- If you execute the EXACT same statement with a 4,2
-- then you will get THAT plan. But if you execute with a 5,2
-- then you''ll get a new plan. Try this:
SELECT t.*
FROM pubs.dbo.titles AS t
WHERE price = 199.99
-- Look again at the cached executable plans, and you''ll see a NEW one...
SELECT sc.*
FROM master.dbo.syscacheobjects AS sc
WHERE sc.cacheobjtype = ''Executable Plan''

Sin embargo, puede usar sp_executesql para escribir los parámetros y obligar al plan a guardarse en caché. Todos los usos posteriores tendrán el mismo plan, pero a algunas personas no les gusta la oscuridad de este enfoque:

DECLARE @ExecStr nvarchar(4000)
SELECT @ExecStr = N''SELECT t.* FROM dbo.titles AS t WHERE t.price = @price''
EXEC sp_executesql @ExecStr, N''@price money'', 19.99

Ahora, si crea una consulta similar como un procedimiento almacenado con un parámetro para el precio, el plan se creará y almacenará en caché en la memoria (no en el disco) en la primera ejecución, y se reutilizará independientemente del valor del parámetro.

El hecho de que el plan de procedimiento almacenado esté almacenado en la memoria y no en el disco significa que se saldrá de la memoria caché al reiniciar el servidor o debido a una baja reutilización. También puede caerse de la memoria caché si los datos de los que depende el procedimiento cambian lo suficiente como para invalidar las estadísticas. Esto hace que SQL Server invalide el plan.

Answer 2

El argumento proc / no almacenado procs almacenado se ha convertido en un problema religioso. Para cada persona que enfatiza planes de ejecución optimizados para procs, otro señala que las consultas dinámicas comunes se almacenan en caché y se optimizan en la mayoría de los DBMS modernos. Para cualquiera que señale la seguridad que un proceso puede ofrecer, otro explica que las consultas dinámicas se pueden realizar igual de seguras. A algunos les gusta la flexibilidad de cambiar un proceso sin volver a compilar su aplicación, mientras que otros argumentan que las consultas se deben capturar en el código de la aplicación para que vivan y crezcan en la misma base de código.

Yo digo...

Has lo que quieras. Dudo que podamos encontrar la respuesta correcta. Si los procs son una molestia, no los use. Si parecen una buena idea, ve por ello. He trabajado con ambos modelos y sinceramente no tengo una preferencia. Soy productivo con o sin ellos.

Answer 3

Frans Bouma causó un gran revuelo con este tema.

En SQL Server, es más importante entender que cualquier aumento en el rendimiento se debe a que el plan de consulta está en caché. Un plan de consulta es bastante costoso de compilar, por lo que tiene sentido almacenarlos en caché. EDITAR: es menos probable que el SQL Ad-hoc coincida con un plan de consulta en caché. El SQL dinámico se puede almacenar en caché si se ejecuta llamando a sp_executesql.

Si usa EXEC para ejecutar SQL, por ejemplo,''EXEC @dynamic_sql '', almacenará en caché un plan pero no lo parametrizará (ni lo normalizará), lo que significa que a menos que se envíe exactamente la misma consulta, no volverá a utilizar el plan de consulta en caché.

Answer 4

Hemos encontrado que un beneficio de los Procedimientos almacenados es que, una vez creado inicialmente por el Desarrollador, se pueden entregar a los expertos en DBA o Ajuste de la base de datos para que se escriban mejor por razones de rendimiento, si es necesario.

Obviamente, los DBA no necesitan acceder al código de la aplicación para hacerlo, y pueden trabajar junto con una versión pendiente de la aplicación.

Por supuesto, lo mismo es cierto para las consultas SQL integradas o en línea, pero creo que los Procedimientos almacenados se prestan mejor a esta forma cooperativa y de equipo más grande.

Answer 5

La idea de que los procedimientos almacenados sean más rápidos es que pueden almacenar planes de consultas en caché, como precompilar el procedimiento almacenado. Entonces, si el procedimiento es muy complejo con muchas combinaciones entre muchas tablas, es posible que vea un beneficio de velocidad.

Creo que los procesos almacenados se desean más allá de su velocidad, ayudan a abstraer el esquema de la base de datos y ayudan a prevenir los ataques de inyección de sql.

Answer 6

Las mayores ventajas de los procedimientos almacenados, en mi opinión, son:

1 / Están centralizados en el sistema de gestión de la base de datos, por lo que los DBA saben exactamente lo que están haciendo y pueden optimizar la base de datos para que se adapte a ellos. Las consultas ad-hoc de los clientes son mucho más difíciles de hacer. He visto las bases de datos arrodilladas porque los DBA permitían consultas sin restricciones. También obligan a los clientes a pensar en lo que necesitan.

2 / Pueden hacer un procesamiento muy complicado en el servidor minimizando la cantidad de datos enviados a través del cable. Esto evita que las aplicaciones obtengan una gran cantidad de datos, decidan algo de ellos, luego obtengan más datos, y así sucesivamente.

De todos modos, permita cualquier consulta aleatoria durante el desarrollo pero, antes de ir a la producción, averigüe qué acciones de la base de datos se deben mover a los procesos almacenados.

Como DBA a tiempo parcial, creo que eso significa todos ellos, pero no es necesario siempre y cuando tenga un mínimo de control sobre las consultas a través de las cuales pueden enviar las solicitudes.

Answer 7

Los procedimientos almacenados que brindan un beneficio de rendimiento son un mito que se retiene de las versiones anteriores de SQL Server. SQL Server> versión 7 trata todas las consultas de la misma manera y guardará en caché los planes de ejecución para todas las consultas de uso común, sin importar su origen.

Me pregunto cuántos años más vivirá este mito.

Answer 8

Un procedimiento almacenado mal escrito no dará ningún impulso de rendimiento. Lo mismo que una mala consulta en línea tendrá un peor rendimiento que uno mal pensado.

Answer 9

Una cosa que la gente está omitiendo es que todos los procedimientos almacenados de CRUD están llenos de uniones y si las declaraciones son extremadamente lentas.

Si está utilizando sentencias parametrizadas frente a una coalescencia mediante el procedimiento almacenado, las declaraciones dinámicas van a ganar.

Answer 10

Una de las ideas detrás de un procedimiento almacenado es que le permite hacer varias cosas en un solo viaje a la base de datos.

MSSQL permite que los lotes de consulta se envíen en una sola solicitud; esto es más o menos equivalente.

Sin embargo, el principio es el mismo. En un sistema distribuido a gran escala, la latencia de la base de datos (en lugar del tiempo que tarda la consulta en el servidor) puede ser un problema de rendimiento. Emitir menos consultas puede ser beneficioso.

Entonces, usar lotes de consulta o procedimientos almacenados versus emitir consultas únicas es una ganancia de rendimiento potencial en un sistema distribuido.

Answer 11

En días pasados, se obtuvieron considerables beneficios de rendimiento al utilizar procedimientos almacenados, pero la reutilización del plan de consulta ahora es significativamente mejor, de modo que los dos son casi los mismos en muchos casos.

Si está creando SQL dinámico en el servidor, puede aumentar aún más la reutilización del plan de consulta (y la seguridad de inyección) utilizando sp_ExecuteSQL (en lugar de solo EXEC) para ejecutar el SQL.

El uso de procedimientos almacenados presenta algunas ventajas:

• Arreglan exactamente lo que sucederá en el DB
• Para escenarios de seguridad estrictos, puede controlar estrictamente el acceso al procedimiento almacenado (en lugar de a la tabla)
• Puede subdividir la consulta si el plan se ve extraño en algunos puntos (sniffing)

Sin embargo, también existen ventajas para SQL:

• Permite que las herramientas ORM (LINQ, por ejemplo) escriban consultas compilables (página 4, ordenadas por Foo, Bar) sobre la marcha
• Le permite escribir código dinámico en un lenguaje más expresivo (¡TSQL no está destinado a ser usado para escribir TSQL!)
• Generalmente tienes mejores herramientas

Algo en lo que definitivamente usaría un SP es (por ejemplo) un paso de migración de datos, es decir, para una operación masiva, podría usar SqlBulkCopy para insertar los datos en una tabla de etapas, luego un procedimiento almacenado para mover los datos. Más allá de eso, soy bastante flexible.