¿Qué es mejor: consultas ad hoc o procedimientos almacenados? [cerrado]

No puedo hablar con nada que no sea SQL Server, pero el argumento de rendimiento es no 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.

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 propia base de datos se puede cambiar sin afectar el código compilado y desplegado, solo para que las entradas y salidas del procedimiento sigan siendo las mismas.

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

Por la misma razón, tampoco es una buena práctica simplemente crear procedimientos almacenados que son solo consultas CRUD contra cada tabla en su base de datos, ya que esto sigue siendo un acoplamiento apretado. Los procedimientos en su lugar deben ser voluminosos, operaciones de grano grueso.

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

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 ajustar o administrar de manera efectiva. ¿Cómo puedo saber qué efecto tendrá un cambio en el esquema? Además, no creo que los usuarios deberían tener acceso directo a las tablas de la base de datos por razones de seguridad (y no solo significa ataques de inyección SQL, pero también porque es un control interno básico para no permitir derechos directos y requerir que todos los usuarios utilicen solo los procs diseñados para la aplicación. Esto es para evitar posibles fraudes. Cualquier sistema financiero que permita insertar, actualizar o eliminar directamente los derechos de las tablas 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 una base de datos perspectiva.

Nuestros desarrolladores nos dicen que están contentos de que todo nuestro acceso a la base de datos sea a través de procs porque hace que sea mucho más rápido corregir un error centrado en datos y luego simplemente ejecutar el proc 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 subversion, por lo que el control de código fuente no es un problema en absoluto. Si no está en Subversion, periódicamente será eliminado por los dba, por lo que no hay resistencia a usando Control de Fuente.

Los procedimientos almacenados son definitivamente el camino a seguir...están compilados, tienen un plan de ejecución antes de la mano y usted podría hacer la gestión de derechos en ellos.

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

Siempre comienza con a .archivo sql que es el origen del procedimiento almacenado. Ponlo en control de versiones una vez que hayas escrito tu código. La próxima vez que quieras editar procedimiento almacenado obtenerlo de su control de código fuente que de su base de datos. Si sigue esto, tendrá un control de código fuente tan bueno como su código.

Me gustaría citar a Tom Kyte de Oracle aquí...Aquí está su regla sobre dónde escribir 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 algo no se puede hacer usando el procedimiento almacenado en PL/SQL, use el procedimiento almacenado en Java.
3. Si crees que algo no puede se hace usando el procedimiento almacenado de Java, considere Pro * c.
4. Si crees que no puedes lograr algo usando Pro*C, es posible que quieras repensar lo que necesitas hacer.

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:

• tenga fácilmente todas las consultas bajo control de versiones
• para hacer los cambios que se requieren en 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 necesita procedimientos almacenados allí. Esto es en cierto modo un camino intermedio entre las consultas ad hoc y los procs almacenados.

Mi respuesta de un post diferente: Los procedimientos almacenados son MÁS mantenibles porque:

• No tienes que recompilar tu aplicación de C # cada vez que quieras cambiar algún SQL
• Terminas reutilizando código SQL.

La repetición de código es la peor lo que puedes hacer cuando estás tratando de construir una aplicación mantenible!

¿Qué sucede cuando encuentra un error lógico que necesita ser corregido en varios lugares? Eres más propensa a olvidar para cambiar el último lugar donde copió y pegó su código.

En mi opinión, las ganancias de rendimiento y seguridad son una ventaja adicional. Todavía puede escribir procedimientos almacenados SQL inseguros/ineficientes.

Es más fácil portar a otro DB - no hay procs a portar

No es muy difícil escribir todos los procedimientos almacenados para su creación en otra base de datos. De hecho, es más fácil que exportar sus tablas porque no hay claves primarias/foráneas de las que preocuparse sobre.

Hay argumentos persuasivos para ambos: todos los procedimientos almacenados están ubicados en un repositorio central, pero son (potencialmente) difíciles de migrar y las consultas ad hoc son más fáciles de depurar como lo son con su 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 se sostiene. texto de enlace

Hacer un procedimiento almacenado de Google vs Consulta Dinámica mostrará argumentos decentes de cualquier manera y probablemente mejor para que tomes tu propia decisión...

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

Considere una consulta que se ejecuta muy bien bajo una carga media, pero una vez que entra en producción a tiempo completo, su consulta mal optimizada martillea el sistema y lo lleva a un rastreo. En la mayoría de los servidores SQL no es la única aplicación / servicio que lo está utilizando. Su la aplicación ahora ha traído un montón de gente enojada a su puerta.

Si tiene sus consultas en SPROCs, 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!

EDIT: alguien dijo que recompilar es una excusa perezosa! sí, vamos a ver lo perezoso que se siente cuando tiene que recompilar e implementar su aplicación a 1000 de escritorios, todo porque el DBA le ha dicho que su consulta ad-hoc está consumiendo demasiado tiempo del servidor!

Algunas cosas para pensar aquí: ¿Quién Necesita Procedimientos Almacenados, De todos Modos?

Claramente es una cuestión de sus propias necesidades y preferencias, pero una cosa muy importante para pensar cuando se utilizan consultas ad hoc en un entorno de cara al público es la seguridad. Siempre parametrízalos y ten cuidado con las vulnerabilidades típicas como ataques de inyección SQL.

Los procedimientos almacenados son excelentes porque se pueden cambiar sin una recompilación. Trataría de usarlos tan a menudo como fuera posible.

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

Procs por las razones mencionadas por otros y también es más fácil afinar un proc con profiler o partes de un proc. De esta manera, no tiene que decirle a alguien que ejecute su aplicación para averiguar qué se está enviando a SQL server

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

SQL parametizado o SPROC...no importa desde un punto de vista de rendimiento...puede consultar optimizar cualquiera de los dos.

Para mí, el último beneficio restante de un SPROC es que puedo eliminar mucho la administración de derechos SQL solo otorgando mis derechos de inicio de sesión para ejecutar sprocs...si utiliza SQL parametizado, el login con su cadena de conexión tiene muchos más derechos (escribiendo cualquier tipo de instrucción select en una de las tablas a las que tienen acceso también, por ejemplo).

Todavía prefiero SQL parametizado sin embargo...

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

Alguien dijo que recompilar es una excusa perezosa! sí, vamos a ver lo perezoso que se siente cuando tiene que recompilar e implementar su aplicación en 1000 de escritorios, todo porque el DBA le ha dicho que su consulta ad-hoc está consumiendo demasiado tiempo del servidor!

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

El argumento de rendimiento de sproc es discutible: los 3 RDBMs principales usan el almacenamiento en caché del plan de consulta y lo han hecho por un tiempo. Está documentado... ¿O es 1995 todavía?

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

Si una aplicación puede comenzar desde cero con un OR (las aplicaciones greenfield están lejos y pocas entre ellas!) es una gran opción ya que su modelo de clase impulsa su modelo DB y ahorra mucho tiempo.

Si un framework OR no está disponible, hemos tomado un enfoque híbrido de crear un archivo XML de recursos SQL para buscar cadenas SQL según las necesitemos (luego son almacenadas en caché por el framework de recursos). Si el SQL necesita alguna manipulación menor, se hace en código; si se necesita una manipulación mayor de cadenas SQL, repensamos el enfoque.

Este enfoque híbrido se presta a una fácil gestión por parte de los desarrolladores (tal vez somos la minoría, ya que mi equipo es lo suficientemente inteligente como para leer un plan de consulta) y la implementación es un simple pago desde SVN. Además, facilita el cambio de RDBMs: simplemente intercambia el archivo de recursos SQL (no es tan fácil como una herramienta OR, por supuesto, pero conectarse a sistemas heredados o bases de datos no soportadas funciona)

Depende de cuál sea tu objetivo. Si desea recuperar una lista de elementos y ocurre una vez durante toda la ejecución de su 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 toma un tiempo (relativamente) largo para ejecutarse es un candidato excelente para el almacenamiento de bases de datos, ya que el rendimiento será mejor.

Si su aplicación vive casi en su totalidad dentro de la base de datos, los procedimientos almacenados son una obviedad. Si está escribiendo una aplicación de escritorio para 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 recompilar su aplicación para hacer modificaciones hace que los procedimientos almacenados sean una mejor opción no es un arranque. Puede haber razones para elegir procedimientos almacenados en lugar de consultas ad-hoc, pero en ausencia de cualquier otra cosa convincente, la compilación el problema parece pereza más que una razón real.

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 debe generarse de alguna manera 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 desee agregar columna a la tabla (propiedad al objeto) debe modificar solo un archivo.

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

No, obviamente no, es tal vez un mal ejemplo, pero creo que el punto que estaba tratando de hacer es claro, su DBA cuida de su infraestructura de base de datos esto es donde su experiencia es, relleno SQL en código bloquea la puerta a ellos y su experiencia.

Prefiero mantener toda la lógica data access en el código del programa, en el que la capa data access ejecuta consultas SQL directas. Por otro lado, la lógica de gestión de datos la pongo en la base de datos en forma de disparadores, procedimientos almacenados, funciones personalizadas y demás. Un ejemplo de algo que considero digno de la base de datos-ifying es la generación de datos - asumir que nuestro cliente tiene un nombre y un apellido. Ahora, la interfaz de usuario necesita un DisplayName, que se deriva de algunos no triviales lógica. Para esta generación, creo un procedimiento almacenado que luego se ejecuta mediante un disparador cada vez que se actualiza la fila (u otros datos de origen).

Parece haber un malentendido algo común de que la capa de acceso a los datos ES la base de datos y todo sobre los datos y el acceso a los datos va allí "solo porque". Esto es simplemente incorrecto, pero veo muchos diseños que se derivan de esta idea. Quizás este es un fenómeno local, sin embargo.

Puede que me apague la idea de SPs después de ver tantos mal diseñados. Por ejemplo, un proyecto en el que participé usó un conjunto de procedimientos almacenados CRUD para cada tabla y cada consulta posible que encontraron. Al hacerlo, simplemente agregaron otra capa completamente inútil. Es doloroso, incluso pensar en tales cosas.

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

Una de las razones principales es que los procedimientos almacenados no funcionan tan bien con los mapeadores O mapeadores.

En estos días creo que necesita una muy buena razón para escribir una aplicación de negocios / sistema de información que no utiliza algún tipo de O mapeador.

El procedimiento almacenado funciona como bloque de código, por lo que en lugar de la consulta adhoc funciona rápidamente. Otra cosa es el procedimiento almacenado dar recompile opción que la mejor parte de SQL usted acaba de utilizar esto para los procedimientos almacenados nada como esto en la consulta adhoc.

Algunos resultados en la consulta y el procedimiento almacenado son diferentes, eso es mi exp personal. Utilice la función cast y covert para comprobar esto.

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

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

Así que utilice solo procedimientos para cualquier transacción.