TransWikia.com

Optimizar y crear bucle sql

Stack Overflow en español Asked by Nacherasg on December 27, 2021

Hola buenas a todos soy nuevo en el foro y no se me da muy bien msql, necesitaría alguna ayuda por favor.

Me han dicho que tengo que optimizar esta consulta ya que siempre que se ejecuta el servidor se cae porque no da abasto. También me han que debo crear un bucle para ahorrar lineas.

En el bucle había pensando en un while para que se ejecute los 12 meses del año con un if para que cuando llegue al mes 12 se reinicie a 1. Ya que si ejecutamos la consulta en noviembre empezara por el mes 11, continuara por el mes 12 y tiene que pasar al mes 1, Pero siempre un total de 12 meses.

Esta seria la consulta sql:

Muchas gracias de antemano

SELECT clientes.codigo, clientes.denominacionFiscal,
 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes082019.horaFacturables)))) AS 'FAC082019',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes082019.horaReportadas)))) AS 'REP082019',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes092019.horaFacturables)))) AS 'FAC092019',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes092019.horaReportadas)))) AS 'REP092019',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes102019.horaFacturables)))) AS 'FAC102019',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes102019.horaReportadas)))) AS 'REP102019',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes112019.horaFacturables)))) AS 'FAC112019',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes112019.horaReportadas)))) AS 'REP112019',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes122019.horaFacturables)))) AS 'FAC122019',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes122019.horaReportadas)))) AS 'REP122019',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes012020.horaFacturables)))) AS 'FAC012020',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes012020.horaReportadas)))) AS 'REP012020',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes022020.horaFacturables)))) AS 'FAC022020',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes022020.horaReportadas)))) AS 'REP022020',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes032020.horaFacturables)))) AS 'FAC032020',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes032020.horaReportadas)))) AS 'REP032020',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes042020.horaFacturables)))) AS 'FAC042020',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes042020.horaReportadas)))) AS 'REP042020',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes052020.horaFacturables)))) AS 'FAC052020',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes052020.horaReportadas)))) AS 'REP052020',

 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes062020.horaFacturables)))) AS 'FAC062020',
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes062020.horaReportadas)))) AS 'REP062020',
 (SEC_TO_TIME(SUM(TIME_TO_SEC(mes072020.horaFacturables)))) AS 'FAC072020', 
(SEC_TO_TIME(SUM(TIME_TO_SEC(mes072020.horaReportadas)))) AS 'REP072020', 

 usuarios.nombreCompleto AS nombreCompleto, clientes.localidad AS localidad, clientes.idprovincias AS provincia
FROM clientes
INNER JOIN usuarios ON usuarios.id= clientes.idConsultor
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 08 AND YEAR(fecha)= 2019) mes082019 ON clientes.codigo = mes082019.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 09 AND YEAR(fecha)= 2019) mes092019 ON clientes.codigo = mes082019.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 10 AND YEAR(fecha)= 2019) mes102019 ON clientes.codigo = mes102019.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 11 AND YEAR(fecha)= 2019) mes112019 ON clientes.codigo = mes112019.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 12 AND YEAR(fecha)= 2019) mes122019 ON clientes.codigo = mes122019.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 01 AND YEAR(fecha)= 2020) mes012020 ON clientes.codigo = mes012020.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 02 AND YEAR(fecha)= 2020) mes022020 ON clientes.codigo = mes022020.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 03 AND YEAR(fecha)= 2020) mes032020 ON clientes.codigo = mes032020.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 04 AND YEAR(fecha)= 2020) mes042020 ON clientes.codigo = mes042020.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 05 AND YEAR(fecha)= 2020) mes052020 ON clientes.codigo = mes052020.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 06 AND YEAR(fecha)= 2020) mes062020 ON clientes.codigo = mes062020.cliente
LEFT JOIN (
SELECT id_cust,customer.cliente,horaFacturables, horaReportadas
FROM customer
WHERE gender in (1,2) AND MONTH(fecha) = 07 AND YEAR(fecha)= 2020) mes072020 ON clientes.codigo = mes072020.cliente
WHERE clientes.estatus='Alta'
GROUP BY clientes.codigo
base de datosmysqlsql

One Answer

Voy a ampliar un poco más, el comentario de Gonzalo. El problema que tienes, aún sin tener datos de los índices o los "query plan" del servidor, se puede llegar a saber por como tienes construida la consulta. Lo que se ve son 12 consultas a la tabla customer dónde, además, por las columnas, me imagino no tienen índices, por lo que hay una alta probabilidad que el motor esté haciendo un escaneo completo de la tabla, en este caso 12 veces. Como regla de trabajo, si tenemos que hacer un escaneo completo de una tabla, hagámoslo una sola vez. Por lo que la solución que podría proponerte es resolver en primer lugar los acumulados por clientes, con un GROUP BY y un SUM condicional. Algo así:

SELECT cliente,
       SUM(TIME_TO_SEC(CASE WHEN MONTH(fecha) = 08 AND YEAR(fecha)= 2019 THEN horaFacturables ELSE 0 END)) as FAC082019, 
       SUM(TIME_TO_SEC(CASE WHEN MONTH(fecha) = 08 AND YEAR(fecha)= 2019 THEN horaReportadas ELSE 0 END))  as REP082019,
       SUM(TIME_TO_SEC(CASE WHEN MONTH(fecha) = 09 AND YEAR(fecha)= 2019 THEN horaFacturables ELSE 0 END)) as FAC092019, 
       SUMTIME_TO_SEC((CASE WHEN MONTH(fecha) = 09 AND YEAR(fecha)= 2019 THEN horaReportadas ELSE 0 END))  as REP092019
       FROM customer
       WHERE gender in (1,2)
       GROUP BY cliente

Lo armé solo para los dos primeros meses, para no hacer tan larga la respuesta, te tocá a tí agregar lo meses que faltan. Espero que se entienda la idea: agrupamos los clientes y hacemos 24 sumarizaciones que estarán condicionadas al mes y año. Como puedes ver, con esto resolvimos el problema de fondo haciendo una sola consulta a customer

Luego simplemente deberemos integrar esta consulta a la consulta anterior:

SELECT C.codigo, 
       C.denominacionFiscal,
       SEC_TO_TIME(T.FAC082019) AS 'FAC082019',
       SEC_TO_TIME(T.REP082019) AS 'REP082019',
       SEC_TO_TIME(T.FAC092019) AS 'FAC092019',
       SEC_TO_TIME(T.REP092019) AS 'REP092019'
       FROM clientes C
       INNER JOIN usuarios U
             ON U.id= C.idConsultor
       LEFT JOIN (

             <Aquí va la consulta anterior>

       ) T
              ON C.codigo = T.cliente

Answered by Patricio Moracho on December 27, 2021

Add your own answers!

Ask a Question

Get help from others!

Recent Questions

Recent Answers

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP