uso una qué procedimientos funciones entre diferencia desventajas datos aporta almacenados sql stored-procedures

sql - una - ¿Qué es mejor: consultas ad hoc o procedimientos almacenados?



procedimientos almacenados sql (22)

Asumiendo que no puede usar Linq por cualquier razón, ¿es una mejor práctica colocar sus consultas en procedimientos almacenados, o es una buena práctica ejecutar consultas ad hoc contra la base de datos (por ejemplo, Servidor SQL por razones de argumento)?

¡alguien dijo que recompilar es una excusa perezosa! sí, veamos qué flojo se siente cuando tiene que recompilar e implementar su aplicación en miles de escritorios, ¡todo porque el DBA le ha dicho que su Consulta ad hoc está consumiendo demasiado tiempo del Servidor!

¿Es buena la arquitectura del sistema si permite conectar 1000 escritorios directamente a la base de datos?

¿Es buena la arquitectura del sistema si permite conectar 1000 escritorios directamente a la base de datos?

No, obviamente no, es quizás un mal ejemplo, pero creo que el punto que intento establecer es claro, su DBA cuida de su infraestructura de base de datos, esta es su experiencia, rellenar SQL en código les bloquea la puerta y su experiencia.

Creo que este es un conflicto básico entre las personas que deben mantener la base de datos y las personas que desarrollan las interfaces de usuario.

Como persona de datos, no consideraría trabajar con una base de datos a la que se accede a través de consultas adhoc porque son difíciles de sintonizar o administrar de manera efectiva. ¿Cómo puedo saber qué efecto tendrá un cambio en el esquema? Además, no creo que los usuarios deban tener acceso directo a las tablas de la base de datos por razones de seguridad (y no solo me refiero a ataques de inyección SQL, sino también porque es un control interno básico el no permitir derechos directos y requerir que todos los usuarios utilice solo los procesos diseñados para la aplicación. Esto es para evitar posibles fraudes. Cualquier sistema financiero que permita insertar, actualizar o eliminar derechos de tablas directamente tiene un gran riesgo de fraude. Esto es algo malo).

Las bases de datos no están orientadas a objetos y el código que parece bueno desde una perspectiva orientada a objetos puede ser extremadamente malo desde la perspectiva de una base de datos.

Nuestros desarrolladores nos dicen que están contentos de que todo nuestro acceso a datos sea a través de procs porque hace que sea mucho más rápido arreglar un error centrado en datos y luego simplemente ejecutar el proceso en el entorno de producción en lugar de crear una nueva rama del código y recompilar y recargar a producción Requerimos que todos nuestros procs estén en subversión, por lo que el control de la fuente no es un problema en absoluto. Si no está en Subversion, periódicamente se eliminará por el dbas, por lo que no hay resistencia al uso de Source Control.

Depende de cuál sea tu objetivo. Si desea recuperar una lista de elementos y ocurre una vez durante toda la ejecución de la aplicación, por ejemplo, probablemente no valga la pena el esfuerzo de usar un procedimiento almacenado. Por otro lado, una consulta que se ejecuta repetidamente y tarda (relativamente) mucho tiempo en ejecutarse es un candidato excelente para el almacenamiento de la base de datos, ya que el rendimiento será mejor.

Si su aplicación vive casi por completo dentro de la base de datos, los procedimientos almacenados son una obviedad. Si está escribiendo una aplicación de escritorio en la que la base de datos solo es tangencialmente importante, las consultas ad-hoc pueden ser una mejor opción, ya que mantiene todo su código en un solo lugar .

@Terrapin: creo que su afirmación de que el hecho de que no tenga que volver a compilar su aplicación para hacer modificaciones hace que los procedimientos almacenados sean una mejor opción si no se inicia. Puede haber razones para elegir procedimientos almacenados sobre consultas ad-hoc, pero a falta de algo más convincente, el problema de compilación parece más pereza que una razón real.

El argumento del rendimiento del sproc es irrelevante: los 3 mejores RDBM usan el caché del plan de consulta y lo han estado haciendo por un tiempo. Está documentado ... ¿O aún es 1995?

Sin embargo, incrustar SQL en su aplicación también es un diseño terrible. Para muchos, el mantenimiento del código parece ser un concepto que falta.

Si una aplicación puede comenzar desde el principio con un ORM (¡las aplicaciones en campo abierto son lejanas y pocas entre!) Es una gran elección ya que su modelo de clase maneja su modelo DB, y ahorra MUCHO tiempo.

Si una estructura ORM no está disponible, hemos adoptado un enfoque híbrido de creación de un archivo XML de recursos SQL para buscar cadenas de SQL a medida que las necesitamos (el marco de recursos las almacena en la memoria caché). Si el SQL necesita alguna manipulación menor, está hecho en código; si se necesita una gran manipulación de cadenas SQL, reconsideramos el enfoque.

Este enfoque híbrido se presta a una administración sencilla por parte de los desarrolladores (tal vez somos la minoría ya que mi equipo es lo suficientemente brillante como para leer un plan de consulta) y la implementación es una simple salida de SVN. Además, facilita el cambio de RDBM, simplemente intercambie el archivo de recursos SQL (no es tan fácil como una herramienta ORM, por supuesto, pero esto funciona si se conecta a sistemas heredados o una base de datos no compatible)

El procedimiento almacenado funciona como un bloque de código, por lo que en lugar de la consulta ad hoc funciona rápidamente. Otra cosa es que el procedimiento almacenado proporcione la opción de recompilación, que la mejor parte de SQL que acaba de usar es para los procedimientos almacenados, nada de esto en una consulta ad hoc.

Algunos resultados en la consulta y el procedimiento almacenado son diferentes que es mi exp personal. Use la función de conversión y encubierta para verificar esto.

Debe usar el procedimiento almacenado para grandes proyectos para mejorar el rendimiento.

Tenía 420 procedimientos en mi proyecto y funciona bien para mí. Trabajo durante los últimos 3 años en este proyecto.

Entonces use solo procedimientos para cualquier transacción.

En estos días casi nunca utilizo procedimientos almacenados. Solo los uso para consultas sql complicadas que no se pueden hacer fácilmente en el código.

Una de las principales razones es porque los procedimientos almacenados no funcionan tan bien con los correlacionadores OR.

En estos días, creo que necesita una muy buena razón para escribir un sistema de aplicaciones / información empresarial que no utilice algún tipo de asignador OR.

En mi experiencia escribiendo principalmente aplicaciones cliente / servidor de WinForms, estas son las simples conclusiones a las que he llegado:

Use procedimientos almacenados:

  1. Para cualquier trabajo de datos complejo. Si va a hacer algo que realmente requiera un cursor o tablas temporales, generalmente es más rápido hacerlo dentro de SQL Server.
  2. Cuando necesite bloquear el acceso a los datos. Si no le da acceso a la tabla a los usuarios (o rol o lo que sea), puede estar seguro de que la única forma de interactuar con los datos es a través de los SP que crea.

Use consultas ad-hoc:

  1. Para CRUD cuando no necesita restringir el acceso a datos (o lo está haciendo de otra manera).
  2. Para búsquedas simples. Crear SP para un grupo de criterios de búsqueda es un problema y es difícil de mantener. Si puede generar una consulta de búsqueda razonablemente rápida, use eso.

En la mayoría de mis aplicaciones he usado tanto SP como sql ad-hoc, aunque me parece que estoy usando SP cada vez menos, ya que terminan siendo código como C #, solo más difícil de controlar, probar y mantener. Recomendaría usar sql ad-hoc a menos que pueda encontrar una razón específica para no hacerlo.

En nuestra aplicación, hay una capa de código que proporciona el contenido de la consulta (y a veces es una llamada a un procedimiento almacenado). Esto nos permite:

  • tener fácilmente todas las consultas bajo control de versión
  • hacer los cambios que sean necesarios para cada consulta para diferentes servidores de bases de datos
  • elimina la repetición del mismo código de consulta a través de nuestro código

El control de acceso se implementa en la capa intermedia, en lugar de en la base de datos, por lo que no necesitamos procedimientos almacenados allí. Este es, en cierto modo, un camino intermedio entre las consultas ad hoc y los procesos almacenados.

Existen argumentos persuasivos para ambos: los procedimientos almacenados se encuentran en un repositorio central, pero son (potencialmente) difíciles de migrar y las consultas ad hoc son más fáciles de depurar que con el código, pero también pueden ser más difíciles de encontrar en el código.

El argumento de que los procedimientos almacenados son más eficientes ya no retiene el agua. Texto del enlace

Hacer un google para Stored Procedure vs Dynamic Query mostrará argumentos decentes de cualquier manera y probablemente lo mejor para que pueda tomar su propia decisión ...

Los procedimientos almacenados representan un contrato de software que encapsula las acciones tomadas contra la base de datos. El código en los procedimientos, e incluso el esquema de la base de datos en sí, se puede cambiar sin afectar el código compilado e implementado, solo para que las entradas y salidas del procedimiento permanezcan iguales.

Al incrustar consultas en su aplicación, está estrechamente vinculado a su modelo de datos.

Por la misma razón, tampoco es una buena práctica crear simplemente procedimientos almacenados que sean solo consultas CRUD contra todas las tablas en su base de datos, ya que esto todavía es un acoplamiento estricto. Los procedimientos deberían ser operaciones voluminosas de grano grueso.

Desde una perspectiva de seguridad, es una buena práctica anular db_datareader y db_datawriter de su aplicación y solo permitir el acceso a los procedimientos almacenados.

Los procedimientos almacenados son definitivamente el camino a seguir ... se compilan, tienen un plan de ejecución de antemano y se puede gestionar los derechos sobre ellos.

No entiendo todo este problema de control de fuente en el procedimiento almacenado. Definitivamente puedes controlarlos en origen, si solo eres un poco disciplinado.

Comience siempre con un archivo .sql que sea la fuente de su procedimiento almacenado. Ponlo en control de versión una vez que hayas escrito tu código. La próxima vez que desee editar su procedimiento almacenado, consígalo desde su control de origen que desde su base de datos. Si sigues esto, tendrás un control de fuente tan bueno como tu código.

Me gustaría citar a Tom Kyte de Oracle aquí ... Aquí está su regla sobre dónde escribir el código ... aunque un poco sin relación, pero es bueno saberlo, supongo.

  1. Comience con procedimientos almacenados en PL / SQL ...
  2. Si cree que no se puede hacer algo utilizando el procedimiento almacenado en PL / SQL, use el procedimiento Java almacenado.
  3. Si cree que no se puede hacer algo utilizando el procedimiento almacenado de Java, considere Pro * c.
  4. Si crees que no puedes lograr algo con Pro * C, tal vez quieras reconsiderar lo que debes hacer.

Los procedimientos almacenados son geniales porque se pueden cambiar sin una recompilación. Intentaría usarlos con la mayor frecuencia posible.

Solo uso ad-hoc para consultas que se generan dinámicamente en función de la entrada del usuario.

Los procedimientos de la tienda deben usarse tanto como sea posible, si su código SQL en el código ya está configurado para dolores de cabeza en el futuro. Se necesita aproximadamente el mismo tiempo para escribir un SPROC que para escribirlo en el código.

Considere la posibilidad de una consulta que funcione bien en una carga mediana, pero una vez que entre en producción a tiempo completo, su consulta mal optimizada martilleará el sistema y lo ralentizará. En la mayoría de los servidores SQL, usted no es la única aplicación / servicio que lo está usando. Su aplicación ahora ha traído a un grupo de personas enojadas a su puerta.

Si tiene sus consultas en SPROC, también permite que su DBA amigable administre y optimice sin recompilar o romper su aplicación. Recuerde que los DBA son expertos en este campo, saben qué hacer y qué no hacer. ¡Tiene sentido utilizar su mayor conocimiento!

EDITAR: alguien dijo que recompilar es una excusa floja! sí, veamos qué flojo se siente cuando tiene que recompilar e implementar su aplicación en miles de escritorios, ¡todo porque el DBA le ha dicho que su Consulta ad hoc está consumiendo demasiado tiempo del Servidor!

Mi experiencia es que el 90% de las consultas y / o procedimientos almacenados no deben escribirse en absoluto (al menos a mano).

El acceso a los datos se debe generar de forma automática. Puede decidir si desea generar procedimientos de forma estática en tiempo de compilación o dinámicamente en tiempo de ejecución, pero cuando desea agregar una columna a la tabla (propiedad del objeto) debe modificar solo un archivo.

Mi respuesta de una publicación diferente : los procedimientos almacenados son MÁS fáciles de mantener porque:

  • No tiene que volver a compilar su aplicación C # cada vez que desee cambiar algún SQL
  • Terminas reutilizando el código SQL.

La repetición de código es lo peor que puede hacer cuando intenta crear una aplicación que se puede mantener.

¿Qué sucede cuando encuentras un error de lógica que necesita ser corregido en múltiples lugares? Es más probable que te olvides de cambiar el último lugar donde copiaste y pegaste tu código.

En mi opinión, las ganancias de rendimiento y seguridad son un plus añadido. Todavía puede escribir procedimientos almacenados SQL inseguros / ineficientes.

Más fácil de transportar a otro DB - no hay procs al puerto

No es muy difícil escribir todos sus procedimientos almacenados para la creación en otro DB. De hecho, es más fácil que exportar sus tablas porque no hay claves primarias / externas de qué preocuparse.

No he encontrado ningún argumento convincente para usar consultas ad-hoc. Especialmente aquellos confundidos con su código C # / Java / PHP.

No puedo hablar con otra cosa que no sea SQL Server, pero el argumento de rendimiento no es significativamente válido allí a menos que esté en 6.5 o anterior. SQL Server ha estado almacenando en caché planes de ejecución ad-hoc durante aproximadamente una década.

Prefiero mantener toda la lógica de acceso a datos en el código del programa, en el que la capa de acceso a datos ejecuta consultas SQL directas. Por otro lado, la lógica de gestión de datos que puse en la base de datos en forma de desencadenantes, procedimientos almacenados, funciones personalizadas y otras cosas. Un ejemplo de algo que considero digno de la generación de bases de datos es la generación de datos: supongamos que nuestro cliente tiene un Nombre y un Apellido. Ahora, la interfaz de usuario necesita un DisplayName, que se deriva de alguna lógica no trivial. Para esta generación, creo un procedimiento almacenado que luego se ejecuta mediante un desencadenador cada vez que se actualiza la fila (u otros datos de origen).

Parece haber este malentendido bastante común de que la capa de acceso a los datos ES la base de datos y todo sobre el acceso a los datos y a los datos entra ahí "solo porque". Esto es simplemente incorrecto, pero veo muchos diseños que se derivan de esta idea. Sin embargo, quizás este sea un fenomonón local.

Es posible que simplemente se desactive la idea de los SP después de ver tantos diseños mal diseñados. Por ejemplo, un proyecto en el que participé utilizó un conjunto de procedimientos almacenados de CRUD para cada tabla y cada consulta posible que encontraron. Al hacerlo, simplemente agregaron otra capa completamente inútil. Es doloroso siquiera pensar en tales cosas.

Procs por las razones mencionadas por otros y también es más fácil sintonizar un proceso con profiler o partes de un proceso. De esta forma, no es necesario decirle a alguien que ejecute su aplicación para averiguar qué se envía al servidor SQL.

Si utiliza consultas ad-hoc, asegúrese de que estén parametrizadas

SQL parametrizado o SPROC ... no importa desde un punto de vista de rendimiento ... puede consultar optimizar uno u otro.

Para mí, el último beneficio que me queda de un SPROC es que puedo eliminar una gran cantidad de derechos SQL al otorgar mis derechos de inicio de sesión para ejecutar sprocs ... si usa SQL parametrizado, el inicio de sesión con su cadena de conexión tiene muchos más derechos (escribir CUALQUIER tipo de declaración seleccionada en una de las tablas a las que tienen acceso también, por ejemplo).

Todavía prefiero SQL parametrizado ...