usar tipos sintaxis qué pueden procedimientos procedimiento los ejemplos casos almacenados almacenado sql database stored-procedures

tipos - procedimientos almacenados sql sintaxis



¿Son los procedimientos almacenados más eficientes, en general, que las declaraciones en línea en los RDBMS modernos? (20)

TEN EN CUENTA que esta es una vista general de los procedimientos almacenados no regulados para un DBMS específico. Algunos DBMS (e incluso, diferentes versiones del mismo DBMS!) Pueden operar de manera contraria a esto, por lo que querrá verificar con su DBMS de destino antes de asumir que todo esto aún se mantiene.

He sido un DBA Sybase ASE, MySQL y SQL Server activado y desactivado desde hace casi una década (junto con el desarrollo de aplicaciones en C, PHP, PL / SQL, C # .NET y Ruby). Por lo tanto, no tengo un hacha particular para moler en esta (a veces) guerra santa.

El beneficio de rendimiento histórico de los procesos almacenados generalmente ha sido de los siguientes (sin ningún orden en particular):

  • SQL pre-analizado
  • Plan de ejecución de consulta pregenerado
  • Reducción de latencia de red
  • Posibles beneficios de caché

SQL pre-analizado : beneficios similares al código compilado frente a código interpretado, excepto en un nivel muy micro.

Todavía una ventaja? No es muy notable en la CPU moderna, pero si está enviando una única instrucción SQL que es MUY grande, mil millones de veces por segundo, la carga de análisis puede sumarse.

Plan de ejecución de consulta pregenerado . Si tiene muchas JOIN, las permutaciones pueden volverse bastante inmanejables (los optimizadores modernos tienen límites y límites por motivos de rendimiento). No es extraño que un SQL muy complicado sea distinto, medible (he visto una consulta complicada que lleva más de 10 segundos generar un plan, antes de ajustar el DBMS) debido al optimizador tratando de descubrir el "casi mejor" " plan de ejecución. Por lo general, los procedimientos almacenados almacenan esto en la memoria para que pueda evitar esta sobrecarga.

Todavía una ventaja? La mayoría de los DBMS (las últimas ediciones) almacenarán en caché los planes de consulta para las sentencias SQL INDIVIDUALES, reduciendo en gran medida el diferencial de rendimiento entre los procesos almacenados y el SQL ad hoc. Hay algunas advertencias y casos en los que este no es el caso, por lo que tendrá que probar en su DBMS de destino.

Además, cada vez más DBMS le permite proporcionar planes de ruta del optimizador (planes de consulta abstracta) para reducir significativamente el tiempo de optimización (tanto para el procedimiento ad hoc como para el procedimiento almacenado SQL !!).

ADVERTENCIA Los planes de consulta en caché no son una panacea de rendimiento. Ocasionalmente, el plan de consulta generado no es óptimo. Por ejemplo, si envía SELECT * FROM table WHERE id BETWEEN 1 AND 99999999 , el DBMS puede seleccionar un escaneo de tabla completa en lugar de un escaneo de índice porque está tomando cada fila en la tabla (por lo tanto, diga las estadísticas). Si esta es la versión almacenada en caché, entonces puede obtener un rendimiento deficiente cuando más adelante envíe SELECT * FROM table WHERE id BETWEEN 1 AND 2 . El razonamiento detrás de esto está fuera del alcance de esta publicación, pero para obtener más información, consulte: http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx y http://msdn.microsoft.com/en-us/library/ms181055.aspx y http://www.simple-talk.com/sql/performance/execution-plan-basics/

"En resumen, determinaron que suministrar algo distinto de los valores comunes cuando se realizaba una compilación o recompilación daba como resultado que el optimizador compilara y almacenara en caché el plan de consulta para ese valor particular. Sin embargo, cuando ese plan de consulta se reutilizó para posteriores ejecuciones del mismo consulta para los valores comunes (''M'', ''R'' o ''T''), resultó en un rendimiento subóptimo. Este problema de rendimiento subóptimo existió hasta que se recompilara la consulta. En ese punto, basado en el @ P1 el valor del parámetro proporcionado, la consulta puede o no tener un problema de rendimiento ".

Reducción de latencia de red A) Si está ejecutando el mismo SQL una y otra vez, y el SQL se suma a muchos KB de código, reemplazarlo con un simple "exe foobar" realmente puede sumar. B) Los procesos almacenados se pueden usar para mover el código de procedimiento al DBMS. Esto ahorra barajar grandes cantidades de datos al cliente solo para que le devuelva un chorrito de información (¡o nada en absoluto!). Análogamente a hacer un JOIN en el DBMS vs. en tu código (¡la WTF favorita de todos!)

Todavía una ventaja? A) ¡La moderna Ethernet de 1 Gb (y 10 Gb y superior!) Realmente lo hace insignificante. B) Depende de qué tan saturada esté tu red, ¿por qué meter y sacar varios megabytes de datos sin una buena razón?

Posibles beneficios de la memoria caché La realización de transformaciones de datos en el lado del servidor puede ser potencialmente más rápida si tiene suficiente memoria en el DBMS y los datos que necesita están en la memoria del servidor.

Todavía una ventaja? A menos que su aplicación tenga acceso de memoria compartida a los datos DBMS, el borde siempre será para los procesos almacenados.

Por supuesto, ninguna discusión sobre la optimización del Procedimiento Almacenado estaría completa sin una discusión de SQL parametrizado y ad hoc.

SQL parametrizado / preparado
Tipo de cruce entre procedimientos almacenados y SQL ad hoc, son sentencias SQL incorporadas en un lenguaje de host que usa "parámetros" para valores de consulta, por ejemplo:

SELECT .. FROM yourtable WHERE foo = ? AND bar = ?

Estos proporcionan una versión más generalizada de una consulta que los optimizadores de hoy en día pueden usar para almacenar en caché (y reutilizar) el plan de ejecución de consultas, lo que resulta en gran parte del beneficio de rendimiento de los procedimientos almacenados.

Ad hoc SQL Simplemente abra una ventana de consola a su DBMS y escriba una declaración de SQL. En el pasado, estos eran los "peores" intérpretes (en promedio) ya que el DBMS no tenía forma de preoptimizar las consultas como en el método de procuración parametrizado / almacenado.

Todavía una desventaja? No necesariamente. La mayoría de los DBMS tienen la capacidad de "abstraer" SQL ad hoc en versiones parametrizadas, anulando más o menos la diferencia entre los dos. Algunos lo hacen implícitamente o deben habilitarse con una configuración de comando (SQL server: http://msdn.microsoft.com/en-us/library/ms175037.aspx , Oracle: http://www.praetoriate.com/oracle_tips_cursor_sharing.htm ).

¿Lecciones aprendidas? La ley de Moore continúa avanzando y los optimizadores de DBMS, con cada lanzamiento, se vuelven más sofisticados. Claro, puedes ubicar cada declaración tonta de un pequeño SQL dentro de un proceso almacenado, pero solo debes saber que los programadores que trabajan en optimizadores son muy inteligentes y continuamente buscan formas de mejorar el rendimiento. Eventualmente (si no está aquí) el rendimiento SQL ad hoc se volverá indistinguible (en promedio) del rendimiento del procedimiento almacenado, por lo que cualquier tipo de procedimiento almacenado masivo ** solo por "razones de rendimiento" ** seguro me parece una optimización prematura .

De todos modos, creo que si evitas los casos extremos y tienes un SQL bastante simple, no notarás una diferencia entre los procedimientos ad hoc y almacenados.

Esta pregunta ya tiene una respuesta aquí:

La sabiduría convencional afirma que los procedimientos almacenados son siempre más rápidos. Entonces, como siempre son más rápidos, úselos TODO EL TIEMPO .

Estoy bastante seguro de que esto se basa en algún contexto histórico en el que esto fue una vez el caso. Ahora bien, no defiendo que los procesos almacenados no sean necesarios, pero quiero saber en qué casos los procesos almacenados son necesarios en bases de datos modernas como MySql, SqlServer, Oracle o. ¿Es excesivo tener acceso total a través de procs almacenados?

Darse cuenta de esto es un poco fuera de tema a la pregunta, pero si está utilizando una gran cantidad de procedimientos almacenados, asegúrese de que haya una manera consistente de ponerlos bajo algún tipo de control de fuente (por ejemplo, subversión o git) y ser capaz de migre las actualizaciones de su sistema de desarrollo al sistema de prueba para el sistema de producción.

Cuando esto se hace a mano, sin forma de auditar fácilmente qué código está donde, esto rápidamente se convierte en una pesadilla.

EN MI HUMILDE OPINIÓN...

Restringir las operaciones "C_UD" a procedimientos almacenados puede mantener la lógica de integridad de datos en un solo lugar. Esto también se puede hacer restringiendo las operaciones "C_UD" a una única capa de middle ware.

Las operaciones de lectura se pueden proporcionar a la aplicación para que puedan unirse solo a las tablas / columnas que necesitan.

El único tema que nadie ha mencionado aún como beneficio de los procedimientos almacenados es la seguridad. Si construye la aplicación exclusivamente con acceso a datos a través de procedimientos almacenados, puede bloquear la base de datos para que el ÚNICO acceso sea a través de esos procedimientos almacenados. Por lo tanto, incluso si alguien obtiene una ID de base de datos y una contraseña, estarán limitados en lo que pueden ver o hacer en contra de esa base de datos.

El uso de procedimientos almacenados para operaciones CRUD es probablemente excesivo, pero dependerá de las herramientas que se utilicen y de sus propias preferencias (o requisitos). Prefiero SQL en línea, pero me aseguro de utilizar consultas parametrizadas para evitar ataques de inyección SQL. Guardo una copia impresa de este comic xkcd como un recordatorio de lo que puede salir mal si no tienes cuidado.

Los procedimientos almacenados pueden tener beneficios reales de rendimiento cuando se trabaja con múltiples conjuntos de datos para devolver un solo conjunto de datos. Por lo general, es más eficiente procesar conjuntos de datos en el procedimiento almacenado que enviarlos a través del cable para ser procesados ​​en el extremo del cliente.

En 2007 estaba en un proyecto, donde usamos MS SQL Server a través de un ORM. Tuvimos 2 grandes y crecientes tablas que tomaron entre 7 y 8 segundos de tiempo de carga en SQL Server. Después de realizar 2 grandes procedimientos almacenados de SQL y optimizarlos desde el planificador de consultas, cada tiempo de carga de DB se redujo a menos de 20 milisegundos, por lo que es claro que aún existen razones de eficiencia para utilizar los procedimientos SQL almacenados.

Una vez dicho esto, descubrimos que el beneficio más importante de los procedimientos almacenados era la lógica de business-logic agregada de facilidad de mantenimiento, seguridad, integridad de datos y desacoplamiento de la lógica de middleware, que beneficiaba a todos los middleware-logic de la reutilización de los 2 procedimientos .

Nuestro proveedor de ORM hizo la afirmación habitual de que disparar muchas consultas SQL pequeñas sería más eficiente que obtener grandes conjuntos de datos unidos. Nuestra experiencia (para nuestra sorpresa) mostró algo más.

Esto, por supuesto, puede variar entre máquinas, redes, sistemas operativos, servidores SQL, marcos de aplicaciones, marcos ORM e implementaciones de lenguaje, así que mida cualquier beneficio, PIENSA que puede obtener al hacer otra cosa.

No fue hasta que evaluamos que descubrimos que el problema estaba entre el ORM y la base de datos que tomaba toda la carga.

En muchos casos, los procedimientos almacenados son en realidad más lentos porque están más genaralizados. Si bien los procedimientos almacenados pueden ser altamente ajustados, en mi experiencia hay suficiente desarrollo y fricción institucional que se dejan en su lugar una vez que funcionan, por lo que los procedimientos almacenados a menudo tienden a devolver muchas columnas "por si acaso", porque usted no desea implementar un nuevo procedimiento almacenado cada vez que cambie su aplicación. Un OR / M, por otro lado, solo solicita las columnas que usa la aplicación, lo que reduce el tráfico de la red, las uniones innecesarias, etc.

Es un debate que sigue y sigue (por ejemplo, aquí ).

Es tan fácil escribir malos procedimientos almacenados como escribir una lógica de acceso a datos incorrecta en su aplicación.

Mi preferencia es para Stored Procs, pero eso se debe a que normalmente trabajo con aplicaciones muy grandes y complejas en un entorno empresarial donde hay DBA dedicados que son responsables de mantener los servidores de bases de datos en funcionamiento.

En otras situaciones, me alegra que las tecnologías de acceso a datos como LINQ se encarguen de la optimización.

Sin embargo, el rendimiento puro no es la única consideración. Aspectos como la gestión de la seguridad y la configuración suelen ser al menos tan importantes.

Editar: Mientras que el artículo de Frans Bouma es realmente detallado, se pierde el punto con respecto a la seguridad por una milla. El hecho de que tenga 5 años tampoco ayuda a su relevancia.

Los procedimientos almacenados también se pueden utilizar en lugar de consultas parametrizadas (o consultas ad-hoc) para algunas otras ventajas también:

  • Si necesita corregir algo (un orden de clasificación, etc.), no necesita volver a compilar su aplicación
  • Puede denegar el acceso a todas las tablas para esa cuenta de usuario, otorgar acceso solo a procedimientos almacenados y enrutar todo el acceso a través de procedimientos almacenados. De esta forma, puede tener una validación personalizada de todas las entradas mucho más flexible que las restricciones de la tabla.

Los procesos almacenados son excelentes para los casos en los que el código SQL se ejecuta con frecuencia porque la base de datos lo almacena en la memoria. Si ejecuta repetidamente el mismo código fuera de un proceso almacenado, incurrirá en un golpe de rendimiento de la base de datos repasando el mismo código una y otra vez.

Por lo general, solía llamar al código como un proceso almacenado o como un objeto SqlCommand (.NET) y ejecutar tantas veces como fuera necesario.

No hay una diferencia de velocidad notable para los procedimientos almacenados frente a consultas parametrizadas o preparadas en la mayoría de las bases de datos modernas, porque la base de datos también almacenará en caché los planes de ejecución para esas consultas.

Tenga en cuenta que una consulta parametrizada no es lo mismo que ad hoc sql.

La razón principal por la cual todavía favorecen los procedimientos almacenados hoy tiene más que ver con la seguridad. Si utiliza procedimientos almacenados exclusivamente , puede desactivar los permisos INSERTAR, SELECCIONAR, ACTUALIZAR, ELIMINAR, ALTERAR, CAÍDAR y CREAR etc. para el usuario de su aplicación, solo dejándolo con EJECUTAR.

Esto proporciona un poco de protección adicional contra la inyección sql de segundo orden . Las consultas parametrizadas solo protegen contra la inyección de primer orden .

Obviamente, el rendimiento real debe medirse en casos individuales, no asumidos. Pero incluso en los casos en que el rendimiento está obstaculizado por un procedimiento almacenado, existen buenas razones para usarlos:

  1. Los desarrolladores de aplicaciones no siempre son los mejores codificadores de SQL. Los procedimientos almacenados ocultan SQL de la aplicación.

  2. Los procedimientos almacenados usan automáticamente variables de vinculación. Los desarrolladores de aplicaciones a menudo evitan las variables de vinculación porque parecen ser un código innecesario y muestran pocos beneficios en los sistemas de prueba pequeños. Más adelante, la falta de uso de variables de vinculación puede acelerar el rendimiento de RDBMS.

  3. Los procedimientos almacenados crean una capa de direccionamiento indirecto que podría ser útil más adelante. Es posible cambiar los detalles de implementación (incluida la estructura de la tabla) en el lado de la base de datos sin tocar el código de la aplicación.

  4. El ejercicio de crear procedimientos almacenados puede ser útil para documentar todas las interacciones de bases de datos para un sistema. Y es más fácil actualizar la documentación cuando las cosas cambian.

Dicho esto, normalmente incluyo SQL sin formato en mis aplicaciones para que yo pueda controlarlo. Depende de tu equipo de desarrollo y filosofía.

Para mí, una de las ventajas de los procedimientos almacenados es ser independiente del idioma del servidor: puede cambiar de una C, Python, PHP o cualquier otra aplicación a otro lenguaje de programación sin reescribir su código. Además, algunas características, como las operaciones masivas, mejoran realmente el rendimiento y no están fácilmente disponibles (¿no del todo?) En los lenguajes de los hosts.

Prefiero usar SP cuando tiene sentido usarlos. En SQL Server, de todos modos, no hay ventaja de rendimiento para los SP sobre una consulta parametrizada.

Sin embargo, en mi trabajo actual, mi jefe mencionó que estamos obligados a usar SP porque nuestros clientes lo requieren. Sienten que son más seguros. No he estado aquí el tiempo suficiente para ver si estamos implementando la seguridad basada en roles, pero tengo la sensación de que sí.

Entonces, los sentimientos del cliente prevalecen sobre todos los demás argumentos en este caso.

Razones para usar procedimientos almacenados:

  • Reduzca el tráfico de red : debe enviar la declaración SQL a través de la red. Con sprocs, puede ejecutar SQL en lotes, que también es más eficiente.
  • Plan de consulta de almacenamiento en caché : la primera vez que se ejecuta sproc, SQL Server crea un plan de ejecución, que se almacena en caché para su reutilización. Esto es particularmente eficaz para pequeñas consultas que se ejecutan con frecuencia.
  • Posibilidad de usar parámetros de salida : si envía SQL en línea que devuelve una fila, solo puede recuperar un conjunto de registros. Con sprocs puede recuperarlos como parámetros de salida, que es considerablemente más rápido.
  • Permisos : cuando envía SQL en línea, debe otorgar permisos en la (s) tabla (s) al usuario, lo que le otorga mucho más acceso que simplemente otorgar permiso para ejecutar un sproc
  • Separación de la lógica : elimine el código generador de SQL y segregalo en la base de datos.
  • Posibilidad de editar sin volver a compilar : esto puede ser controvertido. Puede editar el SQL en un sproc sin tener que volver a compilar la aplicación.
  • Encuentra dónde se usa una tabla : con sprocs, si quieres encontrar todas las declaraciones SQL que hagan referencia a una tabla en particular, puedes exportar el código sproc y buscarlo. Esto es mucho más fácil que tratar de encontrarlo en el código.
  • Optimización : para un administrador de bases de datos es más fácil optimizar el SQL y ajustar la base de datos cuando se utilizan los sprocs. Es más fácil encontrar índices faltantes y tal.
  • Ataques de inyección SQL : SQL en línea correctamente escrito puede defenderse de los ataques, pero los sprocs son mejores para esta protección.

Reducción del tráfico de red: SP generalmente es peor que Dynamic SQL. Debido a que las personas no crean un nuevo SP para cada selección, si solo necesita una columna, se le indica que use el SP que tenga las columnas que necesita e ignore el resto. Obtenga una columna adicional y cualquier uso de red menos que acaba de abandonar. También tiende a tener una gran cantidad de filtrado de clientes cuando se utilizan SP.

almacenamiento en caché: MS-SQL no los trata de manera diferente, ya que MS-SQL 2000 puede haber sido 7 pero no recuerdo.

Permisos: no es un problema, ya que casi todo lo que hago es web o tengo algún nivel intermedio de aplicación que hace todo el acceso a la base de datos. El único software con el que tengo acceso directo a la base de datos del cliente son productos de terceros que están diseñados para que los usuarios tengan acceso directo y se basan en otorgar permisos a los usuarios. ¡Y sí, el modelo de seguridad de permisos MS-SQL SUCKS! (aún no ha pasado tiempo en 2008) Como parte final de esto, me gustaría ver una encuesta sobre cuántas personas siguen haciendo programación directa de cliente / servidor vs web y programación de servidores de aplicaciones intermedias; y si están haciendo grandes proyectos, ¿por qué no hay ORM?

Separación: la gente se pregunta por qué está poniendo lógica de negocios fuera del nivel medio. Además, si busca separar el código de manejo de datos, hay formas de hacerlo sin ponerlo en la base de datos.

Posibilidad de editar: ¿de qué no tiene que preocuparse por las pruebas y el control de la versión? También solo un problema con el cliente / servidor, en el mundo web no hay problema.

Encuentre la tabla: solo si puede identificar el SP que la usa, se quedará con las herramientas del sistema de control de versiones, el agente de búsqueda o el estudio visual para buscar.

Optimización: su DBA debe usar las herramientas de la base de datos para encontrar las consultas que necesitan optimización. La base de datos le puede decir al DBA qué enunciados están generando la mayor cantidad de tiempo y recursos y pueden solucionarlos desde allí. Para las sentencias SQL complejas, los programadores deberían tener la instrucción de hablar con el DBA si las selecciones simples no se preocupan por ello.

Ataques de inyección SQL: SP no ofrece una mejor protección. Lo único que obtienen el visto bueno es que la mayoría de ellos enseñan el uso de parámetros vs SQL dinámico la mayoría de los ejemplos ignoran los parámetros.

Sí, son más rápidos la mayor parte del tiempo. La composición de SQL es un área de ajuste de rendimiento enorme también. Si estoy haciendo una aplicación de tipo back office, puedo omitirlos, pero cualquier cosa que enfrente la producción, los uso con seguridad por todas las razones por las que otros hablaron también ... a saber, seguridad.

Todo lo que puedo hablar es el servidor SQL. En esa plataforma, los procedimientos almacenados son encantadores porque el servidor almacena el plan de ejecución, que en la mayoría de los casos acelera un poco el rendimiento. Digo "en la mayoría de los casos", porque si el SP tiene rutas de ejecución muy variadas, es posible que obtenga un rendimiento por debajo del óptimo. Sin embargo, incluso en esos casos, algunas refactorizaciones ilustradas de los SP pueden acelerar las cosas.