¿Cuando usar procedimientos almacenados?

[mariano_donati]

Tengo una duda. ¿Cuando conviene utilizar procedimientos almacenados?. Estoy con un proyecto de un foro en php y existen muchas consultas a ejecutar. Mi pregunta es si es conveniente crear para cada consulta que se repita muchas veces (por ejemplo, seleccionar la lista de temas correspondientes a un subforo en particular) un procedimiento almacenado. ¿Aumentaría esto el rendimiento?.
Saludos.

[gnzsoloyo]

Existe un número de hechos ante los cuales pensar en usar sp, o por lo menos donde conviene usarlos, esto te puede dar una idea:

1. Si quieres aumentar la seguridad. Como los SP reciben parámetros específicos y cantidades exactas o de lo contrario fallan, son un buen método de evitar a ataques de SQL injection.
2. SI debes realizar consultas complejas con un conjunto acotado de parámetros, con muchos niveles de subconsultas, es mejor hacerlo por SP.
3. Si existen varias alternativas posibles de sentencias a ejecutar, pero no puedes establecer a priori cual hacerlo porque depende de la respuesta a otras consultas, puede ser conveniente hacerlo en un SP.
4. Si debes hacer un barrido de registros en una tabla, puede convenir un SP.
5. Si el proceso es normalizar tablas de datos obtenidas por cargas masivas, es mucho mejor hacerlo en un SP.
6. Cuando debes realizar un conjunto de operaciones de SQL y las respuestas de una serán las entradas de la otra, puede convenir hacerlo en SP.
7. Si debes obtener un conjuto de datos simples de una cadena de ejecuciones de SQL, es mejor hacer un SP.

No es funcional y resulta un desperdicio de recursos:
1. Hacer un SP para ejecutar un SELECT * FROM tabla..., por más complejo que sea el WHERE.
2. Resolver ecuaciones matemáticas o cálculos que requieran funciones que ya existen en los lenguajes de programación.
3. Realizar tareas de cualquier tipo que no incluyan consultas a las tablas.

Los programadores deben recordar que una base de datos es en esencia un almacén de datos con funciones específicas, y no un sustituto de las aplicaciones. No se le debe dar a la base de datos responsabilidades para las que no se diseño el motor...

El cómo, qué y cuando usar un SP y cuándo no, es tema de experiencia en el área. Muchas veces terminamos dándonos cuenta de que un conjunto X de SP que preparamos, luego son innecesarios, porque hay mejores caminos para lograr esas tareas.

[mariano_donati]

Muchas gracias por tu opinión. Me quedan algunas dudas luego de leerla. ¿Por qué no es una buena idea hacer un sp con una consulta select?. Y lo segundo que me gustaría preguntarte es si con "realizar tareas de cualquier tipo que no incluyan consultas a las tablas" también eso incluye los comandos de modificación e inserción, o es solamente para comandos de crear tablas, modificar una columna, etc.?.
Siempre voy a lamentar no haberle dedicado más tiempo al manejo de los motores de base de datos para sacar todo el juego de ellos, así que pido disculpas por si mis preguntas demuestran cierto grado de ignorancia.
Saludos.

[GatorV]

Porque si vas a hacer solamente un SELECT de nada te sirve darle la sobrecarga y complejidad a la BDD de hacer un SP solamente para un select.

Para tu otra duda, se refiere a cosas que no hagan nada, por ejemplo calcular el IVA de algo, o un calculo especifico, esas cosas no son tareas de la BDD.

Saludos.

[mariano_donati]

Pero si es un SELECT que se repite muchas veces, como el ejemplo que puse al inicio de este tema, devolver los posts de un foro en particular?

[gnzsoloyo]

Seguiría siendo una ejecución del SP por cada consulta que hagas, porque no se encadenan...
En definitiva sería lo mismo que enviar N veces un "SELECT * FROM tabla" en una query. ¿Para qué necesitas un SP para hacer eso, si cada vez que lo haces debes enviar los parámetros de seleccion de todos modos?
Lo único que harías sería enviar, por ejemplo, esto:

Código:

CALL SPSELECT1('paramentro1', 'parametro2)

En vez de esto:

Código:

SELECT a, b, c, d FROM tabla where x = 'parametro1' AND y = 'parametro2'

¿Qué te ahorras? Escribir un poco de texto. Bueno para eso mejor haces una función dentro de tu aplicación que arme esa misma sentencia con variables... ¿Para qué hacer que la base de datos tenga un SP para eso?
En otras palabras, si no vas a hacer algo dentro del SP, carece de sentido crearlo.
En el caso puntual de tu ejemplo, de todos modos eso se puede hacer con una sentencia sola, aunque tal vez implique crearla con subconsultas anidadas. Pero de todos modos, no necesitas un SP para eso.

[mariano_donati]

Bien, ahora voy entendiendo un poco más acerca de esto. Muchas gracias por la info.

De todas formas, ¿estaríamos sobrecargando tanto el motor de base de datos como para rechazar la opción de crear un store procedure para este tipo de tareas con el fin de darle más seguridad al sitio?

Pude solucionarlo utilizando mysqli. ¿Alguien sabe por que puede ocurrir esto?

[GatorV]

De hecho para eso si lo estas haciendo en PHP tienes los prepared statements, para solamente cambiar los parámetros y ejecutar las consultas, esto es mucho mas optimo que los stored procedures, para realizar diferentes selects.

¿Solucionar que?

Saludos.

[Genetix]

Mysqli es un biblioteca usada por php que aprovecha mejor las funcionalidades de mysql 4.x hacia adelante. En la siguiente versión de PHP saldrá la exención mysqlnd que promete ser mejor que Mysqli a aprovecha mas las nuevas versiones de mysql

Saludos!

[mariano_donati]

Me confundí de post gatorv. Tenía varias ventanas abiertas al mismo tiempo, y edite un post que no era el correcto. Disculpas por eso.
En cuanto a los prepared statements, me puse a buscar info al respecto y me parece que es una buena solución. Gracias por sus respuestas.

[kurroman]

Yo discrepo con alguna opinión que te han dado o al menos con las razones para no usarlos.

Si el motor de la base de datos debe servir sólo como almacén de datos, ¿entonces también almacenamos los campos calculados? Evidentemente no, pero delegamos ese cálculo a la sentencia SQL, por tanto también podemos delegar otras tareas a los procedimientos según ese mismo planteamiento.

Dudo que un foro sea una aplicación crítica para un motor de base de datos.

Además imagina que quieres hacer otra aplicación de monitorización del foro o cualquier otra tarea relacionada, esa aplicación también podría lanzar procedimientos que ya tengas creado y podrías alojarla en otra maquina (escalabilidad del sistema, mejor mantenimiento).

Otra ventaja es que descargas el tráfico de red entre el cliente/visitante y la interfaz de tu aplicación.

Por tanto, el único caso en el que te recomendaría no usarlo, sería si quisieras evitar a toda costa o tuvieras que minimizar la carga del motor de la base de datos.

[gnzsoloyo]

Vamos al punto: Mis palabras fueron

almacén de datos con funciones específicas

Tal vez el error, para su mejor comprensión es qa qué me refería con "funciones específicas", cosa que incluye los store procedures, functions, triggers, restricciones, sentencias DCL, sentencias DDL, y un amplio grupo de cosas que conocemos todos los que estamos en este asunto.
Simplemente no quería entrar en detalles.

Por otro lado, las funciones agregadas tienen uso estadístico y matemático, necesarias para la resolución de consultas. Si se almacenan o no los resultados depende de cierto tipo de consideraciones tales como la generación de cubos y reportes, donde sí e almacena información ya consolidada. De modo que cierta desnormalización es posible por razones de eficiencia para el usuario.

Que los SP recargan la tarea en ciertos niveles, es cierto, si realizamos un testeo de performance de ciertos SP, hemos de descubrir que algunos de ellos son ineficientes comparados contra una consulta compleja (me ha sucedido en varias ocasiones), por lo que finalmente es mejor desecharlos, pero a priori no se puede decir que todos recargen a la base. Hay que ver de qué SP estamos hablando, en qué base y con qué datos.

Sobre que los SP pueden disminuir la carga de trafico entre el cliente y el servidor, eso es cierto pero sólo bajo un criterio: cuando la tarea se realice off-line. Si usas SP para tareas en que el cliente (la aplicación) espera respuesta o resultado, generas una sobrecarga de tráfico hasta que la tarea del SP se termine y los resultados retornen.

Además, debes considerar que si se realizarán UPDATE, DELETE o INSERT dentro del SP, estos requieren transacciones y bloqueos de tablas que no se liberan hasta que el SP no lo hace..., por lo que los SP aumentan el riesgo de deadlocks si no están bien programados y las tareas son largas.

Aún con todas estas cosas, son uno de los medios más eficientes para realizar tareas que no se resuelven con un SELECT * FROM...

[kurroman]

Algunas puntualizaciones:

Iniciado por gnzsoloyo

De modo que cierta desnormalización es posible por razones de eficiencia para el usuario.

Y por facilidad de interacción con los datos almacenados también.

Iniciado por gnzsoloyo

Hay que ver de qué SP estamos hablando, en qué base y con qué datos.

Amén.

Iniciado por gnzsoloyo

Sobre que los SP pueden disminuir la carga de trafico entre el cliente y el servidor, eso es cierto pero sólo bajo un criterio: cuando la tarea se realice off-line. Si usas SP para tareas en que el cliente (la aplicación) espera respuesta o resultado, generas una sobrecarga de tráfico hasta que la tarea del SP se termine y los resultados retornen.

Yo creo (y es una opinión) que vas a tener más problema por la conexión que tengas entre las máquinas que por la duración del SP, así que más problemas tendrás si te dedicas a enviar varias senticias SQL que un SP. En esa frase que dije, pongamos tráfico de red + ahorros de recursos de la máquina que gestiona la interfaz para completarla y cobre aún mas sentido.

Iniciado por gnzsoloyo

Además, debes considerar que si se realizarán UPDATE, DELETE o INSERT dentro del SP, estos requieren transacciones y bloqueos de tablas que no se liberan hasta que el SP no lo hace..., por lo que los SP aumentan el riesgo de deadlocks si no están bien programados y las tareas son largas.

Pero si haces lo mismo en código, ¿también usarás transacciones no? No obstante en InnoDB los bloqueos son a nivel de registro, no de tablas y un SP de por sí ya es una unidad. (Igual no te he entendido bien en este punto)

Iniciado por gnzsoloyo

Aún con todas estas cosas, son uno de los medios más eficientes para realizar tareas que no se resuelven con un SELECT * FROM...

Mas eficientes, mas mantenibles, te dan mayor escalabilidad del sistema y te permite separar mejor las tareas entre grupos de trabajo.

Así que yo recomiendo usarlos.

[GatorV]

Claro yo creo como bien dice gnzsoloyo, el no esta diciendo no los uses, simplemente estaba exponiendo cuando no y porque no usarlos, y estoy 100% de acuerdo con él, hay veces que complicamos la logica de un programa, cuando no es necesario hacerlo mas complejo, a veces un SP ayuda para unas cosas, a veces complica la tarea.

Creo esa era la pregunta del tema y todos han aportado buenas ideas de los pros y contras de porque y cuando usar SP.

Saludos.

[mariano_donati]

Claro, esa era la cuestión. Entender el contexto bajo el cual es recomendable utilizarlos, y sin duda que me ha servido de mucho todas sus respuestas.
Estoy comenzando a usarlos y más allá del rendimiento de la aplicación (aspecto sobre el cual no puedo decir nada, ya que no estoy ni siquiera en un entorno de prueba) puedo decir que simplifica el código php y me permite separar el trabajo que pertenece a la base de datos del que corresponde al servidor. Me parece que es un aspecto muy importante también para mantener un sistema.
Estoy muy agradecido por sus respuestas.
Saludos.