Procedimiento almacenado - ventajas

[redomir]

Buenas , me gustaría hacer una pregunta para los mas expertos del lugar.

En mi aplicacion tengo consultas que se repiten muchas veces y a su vez son un poco pesadas. ¿Hasta que punto mejoraria el rendimiento si llamo a estas consultas desde procedimientos? ¿Son los procedimientos tan ventajosos a la hora del rendimiento?

O las ventajas de los procedimientos basicamente es traspasar toda la logica de la aplicacion a la base de datos y en cuanto al rendimiento no cambia mucho

Muchas gracias.

[gnzsoloyo]

Vamos a empezar por corregirte una afirmación antes que caigas en un error calamitoso:

O las ventajas de los procedimientos basicamente es traspasar toda la logica de la aplicacion a la base de datos

NO, no y no. El objetivo de los SP NO es traspasar la lógica de la aplicación a la base de datos. Eso es un error de concepto muy usual entre los programadores.
En todo caso UNO de sus objetivos es sacar la lógica DE DATOS de la aplicación y dárselo a la base de datos EN AQUELLOS CASOS donde el procesamiento de los datos sea mas eficiente en la base.
Por ejemplo: No tiene ningún sentido que leas una tabla para luego recorrerla iterativamente en la aplicación a fin de realizar operaciones de sumatoria, calculo o agrupamiento y/u ordenamiento de los registros, así como otras consultas dependientes de ese resultado original, cuando todo eso lo puede hacer la propia base con un SP, devolviendote un resultado final.
¿Se entiende la idea?
Son los procesos hechos con datos de la base, para acciones que deriven en la base y que luego devuelva algún tipo de mensaje o resultados. Allí es donde tiene sentido usar SP. Esas acciones pueden ser simplemente una validación contra una tabla (que devuelva TRUE o FALSE), la lectura de una tabla o vista, o una secuencia muy larga de sentencias manipulando resultados y evaluaciones lógicas de acuerdo ellos para realizar otras.

Por otro lado, también posee ventajas a la hora de la segurizacion de la base, dado que 1) los SP son invulnerables al sql-injection, y 2) permiten utsar usuarios que no tengan permisos para hacer nada mas que ejecutar SP... con lo que esos mismos usuarios no pueden, por ejemplo, leer directamente una tabla si no es por medio de SP.

Finalmente, para hablar de eficiencia en performance, te lo ejemplifico en esta forma: Las aplicaciones que requieren mayor optimizacion y eficiencia son las bancarias, es decir aquellas que están relacionadas con movimientos de los bancos. Estas son tanto las de los cajeros automáticos como las de las transacciones de clearing y asientos masivos de todo tipo.
Bueno, NINGUNA de esas acciones y NINGUNO de los aplicativos de uso bancario utiliza directamente consultas SQL a las tablas, sino pura y exclusivamente stored procedures.

¿Se entiende bien esa idea?

[redomir]

Muchas gracias por la explicación y por tu tiempo.

Lo que no tengo tan claro es como saber cual limite de tiempo de ejecucion de una query es el correcto. Por ejemplo , una query de 9 segundos. Segun tengo entendido 9 segundos es mucho tiempo.

Pero claro , entiendo tambien, que segun la cantidad de los datos y el tipo de hardware , los tiempos cambian. Me refiero , ¿es normal que una query de 6 joins con SUM y COUNT pueda durar 9 segundos?

Con el comando explain entiendo que está optimizada para el uso de los indices.

¿Uso un SP para ejecutar una query?¿Bajaran los tiempos?

Muchas gracias.

[gnzsoloyo]

Los tiempos de ejecución varían incluso si se corre la misma query en forma secuencial, en la misma PC. Las razones para esto son variadas.
Respecto a los nueve segundos, puede ser excesivo o correcto dependiendo de Base, datos, sintaxis de la query, JOIN existentes, etc.
La optimización de una consulta se hace para cada caso. Muestranos la query y veremos.
Lo que si puedo decir es que una query con uso de funciones agregadas puede ser lenta por el GROUP BY que esté colocado.

[redomir]

Un ejemplo de query

Código MySQL:

SELECT comercial ,
       ID_Cliente,
       Cliente,
       Ciudad,
       Provincia,
       Sum(cantidad1 + cantidad2)  AS CantidadTotal,
       Count(DISTINCT( idventas )) AS Visitas
      FROM mov
      LEFT JOIN ventas as v on v.idventas = mov.idmov
      LEFT JOIN listado as inv on inv.id = v.idventas
      LEFT JOIN productos as p on p.idproducto = inv.idproducto 
      LEFT JOIN clientes as cli on cli.idcliente = v.idcliente
      LEFT JOIN direccionClientes as cdir on cdir.idDireccion = cli.idcliente
      LEFT JOIN comerciales as u on u.id = mov.idcomercial
      WHERE p.categoria = 'Zapatos'
      GROUP BY cli.idcliente
      ORDER BY comercial
 

[gnzsoloyo]

El LEFT JOIN es una de las fuentes de mayor confusión y bajas de performance, además de causar errores de datos cuando se usa con descuido.
Tienes que tener muy en cuenta que LEFT JOIN devuelve TODO lo que está en la tabla a su izquierda tenga O NO TENGA relación con lo que está en la tabla a su derecha. Eso tiene el potencial de levantar registros innecesarios luego, lo que reduce la performance general de la consulta. Y esto sin contar con la posibilidad de generar relaciones en estrella, las que causan productos cartesianos...
A diferencia de las matemáticas, el orden de los factores (tablas) SI modifica el producto (RESULTADO de la consulta). No es lo mismo poner las tablas en un orden que en el otro.
Para usar tu ejemplo, si pones a la izquierda las ventas y a la derecha los clientes, la query te devolverá todas las ventas de los clientes que hayan comprado, pero no te devolverá clientes que no posean compras del tipo buscado, lo que es una diferencia notoria para el reporte que planteas en este caso.
En los hechos ese LEFT JOIN opera como un INNER JOIN, solo que invoca el algoritmo equivocado. No es igual buscar con un INNER que con un LEFT. No para el DBMS.

¿Se va entendiendo la forna de analizar las cosas?

En tu caso, si lo que quieres es el listado completo de clientes que efectivamente hayan realizado una compra, no debes usar LEFT JOIN, asi como tampoco usarlo en la busqueda de los datos del cliente, o el producto. Esas son relaciones mandatorias, por lo que corresponde usar INNER JOIN.
Adicionalmente, es mejor poner las tablas en el mismo orden de los datos que se desean encontrar

Algo como:

Código MySQL:

SELECT comercial ,
       ID_Cliente,
       Cliente,
       Ciudad,
       Provincia,
       Sum(cantidad1 + cantidad2)  AS CantidadTotal,
       Count(DISTINCT( idventas )) AS Visitas
FROM clientes cli 
    INNER JOIN direccionClientes cdir on cdir.idDireccion = cli.idcliente
    LEFT JOIN ventas v ON  cli.idcliente = v.idcliente
    INNER JOIN mov ON  v.idventas = mov.idmov
    INNER JOIN listado inv on inv.id = v.idventas
    INNER JOIN productos p on p.idproducto = inv.idproducto 
    INNER JOIN comerciales u on u.id = mov.idcomercial
WHERE p.categoria = 'Zapatos'
GROUP BY cli.idcliente
ORDER BY comercial
 

Si solo quieres los clientes que compraron zapatos, entonces cambia el LEFT por el INNER en el segundo caso.

Nota: Esa query puede devolver lo mismo que la anterior, si interpreté todo correctamente,
En todo caso hay que verificar el funcionamiento genera de la performance.

[redomir]

Hola , he ejecutado la consulta y suele tardar lo mismo 9 o 10 segundos. Mostrando las mismas filas como resultado. Quizas no esté tan bien optimizado los indices como pensaba. Sigo pensando que 9 segundos , quizas sea mucho.

¿Sabrias decirme algo , si te pongo la salida del comando EXPLAIN ?

Muchas gracias, de verdad.

[redomir]

Iniciado por redomir

Hola , he ejecutado la consulta y suele tardar lo mismo 9 o 10 segundos. Mostrando las mismas filas como resultado. Quizas no esté tan bien optimizado los indices como pensaba. Sigo pensando que 9 segundos , quizas sea mucho.

¿Sabrias decirme algo , si te pongo la salida del comando EXPLAIN ?

Muchas gracias, de verdad.

He ejecutado el comando optimize table sobre las tablas en cuestion y ha bajado el tiempo de ejecucion a 3 segundos.

[gnzsoloyo]

La explicación de la mejora la puedes encontrar en el Manual de Referencia:

http://dev.mysql.com/doc/refman/5.7/...ize-table.html

Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table. This statement does not work with views.

La optimizacion de las tablas es una parte normal y cotidiana de la función de un DBA. Siempre se debe tener que hacerlo, en especial con BBDD con muchas ABM