ejercicios - procedimientos almacenados insertar modificar eliminar sql c#

Consultas ad hoc vs procedimientos almacenados vs SQL dinámico (6)

Consultas ad hoc vs procedimientos almacenados vs SQL dinámico. ¿Alguien puede decir pros y contras?

Los procedimientos almacenados IMHO deben ser evitados como la plaga. Aquí hay diez buenas razones por las cuales no debería usarlas (se aplica a todas las bases de datos):

El lenguaje PL / SQL está orientado a manejar datos en tablas, filas y columnas. Es una mala elección para expresar la lógica empresarial. Puedes codificar cualquier cosa en cualquier idioma, eso no significa que debas
La mayoría de las bases de datos carecen de un IDE decente para ayudar con la sintaxis y el enlace a otros procedimientos existentes (por ejemplo, como Eclipse hace para Java)
Los recursos humanos son más difíciles de encontrar para escribir y mantener procedimientos almacenados, simplemente son mucho más raros y, por lo tanto, más caros
Los procedimientos almacenados no son portátiles entre las bases de datos, porque a) no existe un estándar de la industria para PL / SQL b) incluso si existiera un estándar, por lo general, se termina usando la funcionalidad específica de la base de datos / sql dentro de sus procesos almacenados. Si alguna vez tiene que mover dbs, está buscando una reescritura completa de su lógica empresarial
La mayoría de las bases de datos no ofrecen ningún soporte para la depuración de procedimientos almacenados: debe insertar filas en una tabla de registro o similar para lograr el registro de la depuración. Muy feo.
Para probar el procedimiento almacenado necesita una instancia de base de datos real. Esto hace que las pruebas unitarias de los procesos almacenados sean difíciles (tiene que implementarlos en una base de datos de desarrollo para ejecutarlos)
Para implementar procs almacenados, debe actualizar la base de datos (eliminar y luego crear el procedimiento almacenado). Si se descubre un error, no puede simplemente retroceder a una versión binaria anterior como puede hacerlo con el código de la aplicación. En su lugar, debe encontrar el código antiguo, eliminar el nuevo proceso almacenado y (re) crear el anterior. Este es un cambio en la parte superior de un cambio , no un retroceso
Está aumentando las demandas de procesamiento del servidor de la base de datos, en lugar de distribuir la lógica empresarial a otros servidores (de aplicaciones). Dado que la base de datos suele ser un singleton, esto es muy malo, porque la única forma de aumentar la capacidad es comprar mejor hardware (no comprar más hardware o usar la nube).
No son mucho más rápidas que las consultas bien escritas que usan declaraciones preparadas, porque existe una compensación entre el aumento de la demanda de procesamiento en el servidor de la base de datos y la eficiencia de su uso. Además de la velocidad, no lo es todo (siempre que sea aceptable): la capacidad de mantenimiento, la capacidad de depuración, la idoneidad de PL / SQL, etc. son tan importantes si no más
Los lenguajes proc almacenados tienen bibliotecas limitadas (si las hay) a las que recurrir, por lo que termina escribiendo muchos códigos de bajo valor. Esto es diferente a los lenguajes de aplicaciones, que tienen muchas bibliotecas que puede usar para todas las cosas que necesita la lógica de negocios.

Solo hay un lugar donde autorizaría su uso: Para una funcionalidad de base de datos muy específica: tal vez una verificación de clave o una conversión de tipo de datos o algo similar, tal vez dentro de un activador, que sea tan importante que justifique su existencia y que probablemente nunca lo hará. cambio una vez escrito.

En general, debe ejecutar gritando desde procedimientos almacenados!

Procedimientos almacenados

Pro: Permiso de acciones sin necesidad de otorgar más derechos fundamentales a nivel de tabla.
Pro: Discreto y versionable.
Pro: Le permite aislar su esquema de su código de acceso a datos.
Contras: Puede ser tedioso codificar procedimientos de CRUD
Contras: deben mantenerse en línea con el esquema subyacente

Ad hoc y dinámico - vea las respuestas y comentarios de Bill Paetzke.

Además, no olvide patrones como la inserción masiva para SQL, que no está en su lista pero que aún debe ser considerada.

Procedimientos almacenados PROs:

Compilado Esto significa que es más rápido de ejecutar y tiene un impacto positivo en la CPU de su servidor de base de datos debido a que se omite la etapa de optimización / compilación para todas las ejecuciones, excepto la primera.
Permita el control limpio de permisos sobre las consultas complejas de lectura y escritura.
Proporcione una API reutilizable que permita una implementación BUENA y eficiente, en lugar de un grupo de Yahoos en una variedad de plataformas desde una variedad de aplicaciones que reimplementan las consultas de samke y se arriesgan a obtener implementaciones ineficientes
Al igual que cualquier API, proporcionar la capa de abstracción. Puede cambiar la implementación subyacente (esquema) sin cambiar ningún código que llame al SP. Esa es una ventaja extremadamente grande cuando hay cientos de aplicaciones en todas las plataformas que utilizan la consulta.

Procedimientos almacenados CONs:

Lógica difícil de codificar en comparación con el SQL dinámico.
Tener una versión precompilada puede llevar a una ejecución menos eficiente a medida que cambian los datos y las opciones del optimizador cambian. Esto es fácil de mejorar re-compilando de vez en cuando.

RDBMS? Esta respuesta es específica para oráculo mayor.

En la versión anterior de Oracle <11, el sql dinámico no reutiliza los planes de texto SGA sql existentes, crea una nueva entrada para cada plan de ejecución que el analizador necesita. Con una gran cantidad de llamadas a un sql dinámico, el área de texto sql se vacía lo suficientemente rápido como para que la reutilización de la consulta sea mucho menor y el rendimiento lo siga hacia abajo.

Una ventaja adicional es más fácil: "Sin actualizaciones de tiempo de inactividad" (para las actualizaciones importantes, es posible que aún tenga tiempo de inactividad).

Si todo el acceso a los datos se realiza a través de procedimientos almacenados, puede hacer que v1 y v2 de los procedimientos almacenados se coloquen uno al lado del otro fácilmente.

ahora puede tener binarios / lógica de aplicación de v1 y v2 ejecutándose lado a lado, cada uno llamando a su propia versión de procedimientos almacenados.

no se logra tiempo de inactividad a través de 1, bloqueando la aplicación v1 en modo de solo lectura (si corresponde), 2, implementando cambios de db. 3, que habilita el acceso normal a la aplicación v1, 4, que implementa la aplicación v2 en paralelo, les dice a los nuevos usuarios que usen nuevos binarios. 6. cierre los archivos binarios antiguos cuando no haya más usuarios que estén utilizando los archivos binarios antiguos.

Procedimientos almacenados

Pro: bueno para consultas cortas y simples (también conocido como OLTP, es decir, agregar, actualizar, eliminar, ver registros)
Pro: Mantiene la lógica de la base de datos separada de la lógica de negocios.
Pro: fácil de solucionar
Pro: fácil de mantener
Pro: Menos bits transferidos a través de la red (es decir, solo el nombre de proc y los parámetros)
Pro: compilado en base de datos
Pro: Mejor seguridad (los usuarios no necesitan acceso directo a la tabla)
Pro: excelente almacenamiento en caché del plan de consultas ( bueno para consultas OLTP - beneficios de la reutilización del plan)
Contras: excelente almacenamiento en caché de planes de consulta ( malo para consultas OLAP - beneficios de planes únicos)
Con: te hace atar a ese proveedor de SQL

SQL dinámico (es decir, utiliza el comando exec dentro de un procedimiento almacenado)

Pro: bueno para consultas cortas y simples (también conocido como OLTP)
Pro: Mantiene la lógica de la base de datos separada de la lógica de negocios.
Pro: Menos bits transferidos a través de la red (es decir, solo el nombre de proc y los parámetros)
Pro: permite hacer referencia a cualquier tabla, base de datos o columna
Pro: permite agregar / eliminar predicados (en la cláusula WHERE) según los parámetros
Pro: Buen almacenamiento en caché del plan de consultas (mediocre a bueno para consultas tanto OLTP como OLAP)
Con: Solo se pueden compilar los elementos estáticos del proc.
Con: te hace atar a ese proveedor de SQL
Con: Más difícil de solucionar
Con: Más vulnerable a los ataques de inyección SQL

Ad Hoc SQL (es decir, creado en su código de negocio)

Pro: bueno para largas, complejas quieres (también conocido como OLAP, es decir, informes o análisis)
Pro: acceso flexible a los datos
Pro: el uso de ORM es posible; se puede compilar / probar en código (es decir, Linq-to-Sql o SqlAlchemy)
Pro: pobre almacenamiento en caché del plan de consulta ( bueno para consultas OLAP - se beneficia de planes únicos)
Con: pobre almacenamiento en caché del plan de consultas ( malo para las consultas OLTP - beneficios de la reutilización del plan)
Con: Más bits transferidos a través de la red (es decir, toda la consulta y parámetros)
Contras: más difícil de mantener, si no usa un ORM
Contras: más difícil de solucionar, si no utiliza un ORM
Con: Más vulnerable a los ataques de inyección SQL

Nota: siempre parametrice su SQL ad hoc.

Para OLAP ad hoc SQL: solo parametrizar datos de cadena. Esto satisface dos condiciones. Previene el ataque de inyección SQL. Y hace que las consultas se vean más exclusivas de la base de datos. Sí, obtendrá un índice de aciertos de caché de planes de consulta deficiente. Pero eso es deseable para las consultas OLAP. Se benefician de la generación de planes únicos, ya que sus conjuntos de datos y los planes más eficientes varían enormemente entre los parámetros dados.