Query lenta....

[kryta]

Buenas gente,

Hacía bastante que no me pasaba por el foro y os saludo de nuevo :)

Os comento, porque no se si lo estoy haciendo del todo correcto o de la manera más eficiente, es un poco complejo de explicar:

La query en cuestión es la siguiente:

Código:

select proveidors_treballadors.id_prov_treb, nom, coalesce(fac,0) from proveidors_treballadors
left join (select id_prov_treb, ROUND((sum(materials+stock)),2) as fac from albarans 
left join (select id_albara, sum(quantitat*multiplicatiu) as materials from comandes_albarans_families group by id_albara)
    T1 on (albarans.id_albara=T1.id_albara)
left join (select id_albara, sum(quantitat*multiplicatiu) as stock from albarans_families group by id_albara)
    T2 on (albarans.id_albara=T2.id_albara) 
group by id_prov_treb)
T3 on (proveidors_treballadors.id_prov_treb=T3.id_prov_treb)
where tipus_prov_treb = 'proveidor';

Tarda unos 33.1319 segundos :(

El Explain es el siguiente:



El diseño de las tablas indicadas, en amarillo:



Cualquier ayuda es bienvenida. No se porqué hice el diseño en catalán pero bueno....

Gracias y saludos.

[jurena]

Deberías aclararnos qué buscas, qué son esas familias de albaranes y esas comandes_families_albarans, y qué quieres sacar exactamente (me refiero a esos sumatorios). Observo demasiados LEFT JOIN innecesarios y un campo innecesario en albarans, el campo tipus_prov_treb, aunque luego relacionas con él y con el id_prov_treb. Eso es repetir datos de manera innecesaria, aunque no tiene por qué causarte problemas y ahora no podrás tocarlo, claro. Opino que partiendo de la tabla albarans puedes usar luego INNER JOIN al menos cuando una de las tablas relacionadas sean albarans. Pero dinos algo más sobre lo que tienes y quieres.
En otro orden de cosas y en lo que se refiere al diseño en catalán, ¿por qué no si esa es tu lengua? Si tenemos alguna duda te preguntaremos, pero se entiende bien.

[kryta]

Iniciado por jurena

Deberías aclararnos qué buscas, qué son esas familias de albaranes y esas comandes_families_albarans, y qué quieres sacar exactamente (me refiero a esos sumatorios). Observo demasiados LEFT JOIN innecesarios y un campo innecesario en albarans, el campo tipus_prov_treb, aunque luego relacionas con él y con el id_prov_treb. Eso es repetir datos de manera innecesaria, aunque no tiene por qué causarte problemas y ahora no podrás tocarlo, claro. Opino que partiendo de la tabla albarans puedes usar luego INNER JOIN al menos cuando una de las tablas relacionadas sean albarans. Pero dinos algo más sobre lo que tienes y quieres.
En otro orden de cosas y en lo que se refiere al diseño en catalán, ¿por qué no si esa es tu lengua? Si tenemos alguna duda te preguntaremos, pero se entiende bien.

Hola jurena, gracias por contestar, te comento:

El campo tipus_prov_treb en la tabla de albarans es para poder realizar la relación con la tabla proveidors_treballadors ya que en esta la clave primaria está formada por (id_prov_treb, tipus_prov_treb). Lo hice así porque tuve que migrar una base de datos en la que los trabajadores y proveedores estaban en tablas distintas y al ponerlos todos en una misma tabla me podían coincidir los ids. Tenía que respetar los ids ya que también migraba los pedidos,......

Comento un poco que es cada tabla:

proveidors_treballadors -> No cal mucha explicación, aquí hay los trabajadores y proveedores

albarans -> Los albaranes con una relación de uno a muchos con la tabla anterior.

comandes_albarans_families -> las líneas de albaranes que van hacia un pedido. Relación de uno a muchos con la tabla albarans

albarans_families -> las líneas de albaranes que no van hacia ningún pedido, es decir el stock. Relación de uno a muchos con la tabla albarans

Pues lo que quiero conseguir con esa query es la suma de todo lo que se ha comprado a cada proveedor, es decir sumar todas las lineas de albaranes (comandes_albarans_families y albarans_families) agrupado por proveedor.

Espero haberme explicado.

Gracias y saludos.

[kryta]

Buenas.

Me parece que he dado con la tecla, ahora falta comprobar los resultados:

Código:

select proveidors_treballadors.id_prov_treb, proveidors_treballadors.tipus_prov_treb, nom, coalesce(fac,0) from proveidors_treballadors
left join (select id_prov_treb, tipus_prov_treb, ROUND(sum(comandes_albarans_families.quantitat*comandes_albarans_families.multiplicatiu)+sum(albarans_families.quantitat*albarans_families.multiplicatiu),2) as fac from albarans 
inner join comandes_albarans_families on (albarans.id_albara=comandes_albarans_families.id_albara)
inner join albarans_families on (albarans.id_albara=albarans_families.id_albara)
group by id_prov_treb, tipus_prov_treb)
T3 using (id_prov_treb, tipus_prov_treb)
where proveidors_treballadors.tipus_prov_treb = 'proveidor'

0.0538 seg

Saludos.

[jurena]

No sé si te habré entendido bien todo y lo que quieres es la suma de todas las líneas sean de pedidos con destino cliente o líneas con destino stock. No he probado nada y la consulta es solo es a título orientativo. Tampoco sé si será lo más eficiente hacer el where y el group by dentro de los elementos del UNION ALL o hacerlo solo fuera. Pero primero probamos la consulta para ver si da lo que quieres y luego hacemos pruebas para hacerla más eficiente.

Código MySQL:

SELECT
    pt.id_prov_treb,
   pt.nom,
   coalesce(pt.fac,0),
   pt.tipus_prov_treb,
   SUM(T1.tot) TOTAL 
FROM proveidors_treballadors pt 
   INNER JOIN  
      ((SELECT
          (ca.quantitat * ca.multiplicatiu) tot, 
           a.id_prov_treb,
           a.tipus_prov_treb 
         FROM albarans a 
            INNER JOIN comandes_albarans_families ca ON a.id_albara = ca.id_albar
         WHERE a.tipus_prov_treb = 'proveidor' 
           GROUP BY a.id_prov_treb )
      UNION ALL
           (SELECT 
               (af.quantitat * af.multiplicatiu),
                a.id_prov_treb,
                a.tipus_prov_treb
             FROM albarans a 
                  INNER JOIN albarans_families af ON a.id_albara = af.id_albara 
                     WHERE a.tipus_prov_treb = 'proveidor' 
                     GROUP BY a.id_prov_treb ))T1 
ON (pt.id_prov_treb = T1.id_prov_treb AND pt.tipus_prov_treb = T1.tipus_prov_treb) 
    GROUP BY pt.id_prov_treb, pt.tipus_prov_treb
 

[kryta]

Pego la query con código mysql así se ve mejor.

Código MySQL:

SELECT proveidors_treballadors.id_prov_treb, proveidors_treballadors.tipus_prov_treb, nom, coalesce( fac, 0 )
FROM proveidors_treballadors
LEFT JOIN (
       SELECT id_prov_treb, tipus_prov_treb, 
       ROUND( sum( comandes_albarans_families.quantitat * comandes_albarans_families.multiplicatiu ) + sum( albarans_families.quantitat *albarans_families.multiplicatiu ) , 2 ) AS fac
       FROM albarans
       INNER JOIN comandes_albarans_families ON ( albarans.id_albara = comandes_albarans_families.id_albara )
       INNER JOIN albarans_families ON ( albarans.id_albara = albarans_families.id_albara )
       GROUP BY id_prov_treb, tipus_prov_treb
)T3
USING ( id_prov_treb, tipus_prov_treb )
WHERE proveidors_treballadors.tipus_prov_treb = 'proveidor'
 

Saludos

[kryta]

Iniciado por jurena

No sé si te habré entendido bien todo y lo que quieres es la suma de todas las líneas sean de pedidos con destino cliente o líneas con destino stock. No he probado nada y la consulta es solo es a título orientativo. Tampoco sé si será lo más eficiente hacer el where y el group by dentro de los elementos del UNION ALL o hacerlo solo fuera. Pero primero probamos la consulta para ver si da lo que quieres y luego hacemos pruebas para hacerla más eficiente.

Código MySQL:

SELECT
    pt.id_prov_treb,
   pt.nom,
   coalesce(pt.fac,0),
   pt.tipus_prov_treb,
   SUM(T1.tot) TOTAL 
FROM proveidors_treballadors pt 
   INNER JOIN  
      ((SELECT
          (ca.quantitat * ca.multiplicatiu) tot, 
           a.id_prov_treb,
           a.tipus_prov_treb 
         FROM albarans a 
            INNER JOIN comandes_albarans_families ca ON a.id_albara = ca.id_albar
         WHERE a.tipus_prov_treb = 'proveidor' 
           GROUP BY a.id_prov_treb )
      UNION ALL
           (SELECT 
               (af.quantitat * af.multiplicatiu),
                a.id_prov_treb,
                a.tipus_prov_treb
             FROM albarans a 
                  INNER JOIN albarans_families af ON a.id_albara = af.id_albara 
                     WHERE a.tipus_prov_treb = 'proveidor' 
                     GROUP BY a.id_prov_treb ))T1 
ON (pt.id_prov_treb = T1.id_prov_treb AND pt.tipus_prov_treb = T1.tipus_prov_treb) 
    GROUP BY pt.id_prov_treb, pt.tipus_prov_treb
 

Hola Jurena.

Mírate la consulta que he posteado después de tu comentario, creo que en la tuya el group by se debería hacer sobre id_prov_treb y tipus_prov_treb que es la clave foránea y el índice, me refiero al group by de las interiores.... no se.... lo miro.

Gracias, voy a probar.

Saludos

[jurena]

Si la tuya funciona, creo que será más eficiente, pero en mi caso no es necesario hacer el group by por los dos campos, pues filtro por uno de ellos y los no proveedores quedarían fuera.

[kryta]

Iniciado por jurena

Si la tuya funciona, creo que será más eficiente, pero en mi caso no es necesario hacer el group by por los dos campos, pues filtro por uno de ellos y los no proveedores quedarían fuera.

Hola jurena.

En las querys donde multiplicas valores estas agrupando por id_prov_treb, piensa que una misma id puede estar repetida por eso hice el tipus_prov_treb para poder diferenciar entre trabajador y proveedor. En tu caso estarías calculando con valores de un trabajador y un proveedor a la vez.

Espero haberme explicado. Por cierto gracias y voy a probarlo todo con calma.

Saludos.

[kryta]

Hola, después de revisarlo con calma creo que he dado con el resultado:

Código MySQL:

select id_prov_treb, tipus_prov_treb, nom, ROUND(coalesce(materials,0)+coalesce(stock,0),2) as fac from proveidors_treballadors
left join (select id_prov_treb, tipus_prov_treb, 
   ROUND(sum(quantitat*multiplicatiu),2) as materials 
   from albarans 
   inner join comandes_albarans_families using (id_albara)
   where tipus_prov_treb = 'proveidor' group by id_prov_treb, tipus_prov_treb)
T1 using (id_prov_treb, tipus_prov_treb)
left join (select id_prov_treb, tipus_prov_treb, 
   ROUND(sum(quantitat*multiplicatiu),2) as stock 
   from albarans 
   inner join albarans_families using (id_albara)
   where tipus_prov_treb = 'proveidor' group by id_prov_treb, tipus_prov_treb)
T2 using (id_prov_treb, tipus_prov_treb)
where tipus_prov_treb = 'proveidor'
 

0.0463 seg :)

¿Qué os parece?

Saludos y gracias.