SUM agilizando tempo de SELECT

[bnobre]

Olá a todos,

Estou executando o seguinte select em meu MySQL:

SELECT _TBL_LIBERAR_ITENS.id_produtos, _TBL_PRODUTOS.codigo, _TBL_PRODUTOS.descricao, 
(SELECT IFNULL(SUM(LI2.quantidade),0) FROM _TBL_LIBERAR_ITENS LI2 
LEFT OUTER JOIN _TBL_LIBERAR ON LI2.id_liberar = _TBL_LIBERAR.id_liberar 
WHERE LI2.id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND _TBL_LIBERAR.data < "2018/09/01" AND 
_TBL_LIBERAR.id_tecnicos IS NOT NULL AND _TBL_LIBERAR.empresa = "1") - 

(SELECT IFNULL(SUM(_TBL_BAIXAR_ITENS.quantidade),0) FROM _TBL_BAIXAR_ITENS 
LEFT OUTER JOIN _TBL_BAIXAR ON _TBL_BAIXAR_ITENS.id_baixar = _TBL_BAIXAR.id_baixar 
WHERE _TBL_BAIXAR.data < "2018/09/01" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND 
_TBL_BAIXAR.id_tecnicos IS NOT NULL AND _TBL_BAIXAR.empresa = "1") - 

(SELECT IFNULL(SUM(_TBL_DEVOLVER_ITENS.quantidade),0) FROM _TBL_DEVOLVER_ITENS 
LEFT OUTER JOIN _TBL_DEVOLVER ON _TBL_DEVOLVER_ITENS.id_devolver = _TBL_DEVOLVER.id_devolver 
WHERE _TBL_DEVOLVER.data < "2018/09/01" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND 
_TBL_DEVOLVER.id_tecnicos IS NOT NULL AND _TBL_DEVOLVER.empresa = "1") AS saldoanterior,

SUM(_TBL_LIBERAR_ITENS.quantidade) AS qtdeliberada, 

(SELECT IFNULL(SUM(_TBL_BAIXAR_ITENS.quantidade),0) FROM _TBL_BAIXAR_ITENS 
LEFT OUTER JOIN _TBL_BAIXAR ON _TBL_BAIXAR_ITENS.id_baixar = _TBL_BAIXAR.id_baixar 
WHERE _TBL_BAIXAR.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND _TBL_BAIXAR.id_tecnicos 
IS NOT NULL AND _TBL_BAIXAR.empresa = "1") AS qtdebaixada, 

(SELECT IFNULL(SUM(_TBL_DEVOLVER_ITENS.quantidade),0) FROM _TBL_DEVOLVER_ITENS 
LEFT OUTER JOIN _TBL_DEVOLVER ON _TBL_DEVOLVER_ITENS.id_devolver = _TBL_DEVOLVER.id_devolver 
WHERE _TBL_DEVOLVER.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND _TBL_DEVOLVER.id_tecnicos 
IS NOT NULL AND _TBL_DEVOLVER.empresa = "1") AS qtdedevolvida 

FROM _TBL_LIBERAR_ITENS 
LEFT OUTER JOIN _TBL_PRODUTOS ON _TBL_LIBERAR_ITENS.id_produtos = _TBL_PRODUTOS.id_produtos 
LEFT OUTER JOIN _TBL_LIBERAR ON _TBL_LIBERAR_ITENS.id_liberar = _TBL_LIBERAR.id_liberar 
WHERE _TBL_LIBERAR.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND _TBL_LIBERAR_ITENS.id_produtos IS NOT NULL AND _TBL_LIBERAR.id_tecnicos 
IS NOT NULL AND _TBL_LIBERAR.empresa = "1" 
GROUP BY _TBL_LIBERAR_ITENS.id_produtos ORDER BY _TBL_PRODUTOS.descricao

O mesmo retorna o resultado em 0,791 segundos, em média.

Eu não preciso mais da coluna qtdeliberada, gerada pela linha "SUM(_TBL_LIBERAR_ITENS.quantidade) AS qtdeliberada". E ao retirar a mesma, descobri que o SELECT passa a levar em média 30,894 segundos.

Alguém poderia me dizer porque esse SUM está tornando esse SELECT tão mais rapido???

[EMBarbosa]

13 horas atrás, doidopb disse:

Eu não preciso mais da coluna qtdeliberada, gerada pela linha "SUM(_TBL_LIBERAR_ITENS.quantidade) AS qtdeliberada". E ao retirar a mesma, descobri que o SELECT passa a levar em média 30,894 segundos.

Alguém poderia me dizer porque esse SUM está tornando esse SELECT tão mais rapido??? 

Meu MySQL não é tão bom, mas eu investigaria a possibilidade de mudar totalmente seu SQL se você for remover essa coluna.  Me parece que essa era a única coluna da tabela principal de sua query (_TBL_LIBERAR_ITENS ) que era agrupada pelo group by.

Analise os plans das duas "queries".

[bnobre]

5 horas atrás, EMBarbosa disse:

Meu MySQL não é tão bom, mas eu investigaria a possibilidade de mudar totalmente seu SQL se você for remover essa coluna.  Me parece que essa era a única coluna da tabela principal de sua query (_TBL_LIBERAR_ITENS ) que era agrupada pelo group by.

Analise os plans das duas "queries".

Olá EMBarbosa, irei analisar o posto o resultado aqui para conhecimento de todos...

Abraços

[bnobre]

Olá EMBarbosa, tudo bom?

Conforme você demonstrou, após retirar essa coluna fica mais interessante eu pegar esses dados da tabela _TBL_PRODUTOS. O fiz como no código abaixo:

 

SELECT _TBL_PRODUTOS.id_produtos, _TBL_PRODUTOS.codigo, _TBL_PRODUTOS.descricao, 
(SELECT IFNULL(SUM(LI2.quantidade),0) FROM _TBL_LIBERAR_ITENS LI2 
LEFT OUTER JOIN _TBL_LIBERAR ON LI2.id_liberar = _TBL_LIBERAR.id_liberar 
WHERE LI2.id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND _TBL_LIBERAR.data < "2018/09/01" AND 
_TBL_LIBERAR.id_tecnicos IS NOT NULL AND _TBL_LIBERAR.empresa = "1") - 

(SELECT IFNULL(SUM(_TBL_BAIXAR_ITENS.quantidade),0) FROM _TBL_BAIXAR_ITENS 
LEFT OUTER JOIN _TBL_BAIXAR ON _TBL_BAIXAR_ITENS.id_baixar = _TBL_BAIXAR.id_baixar 
WHERE _TBL_BAIXAR.data < "2018/09/01" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND 
_TBL_BAIXAR.id_tecnicos IS NOT NULL AND _TBL_BAIXAR.empresa = "1") - 

(SELECT IFNULL(SUM(_TBL_DEVOLVER_ITENS.quantidade),0) FROM _TBL_DEVOLVER_ITENS 
LEFT OUTER JOIN _TBL_DEVOLVER ON _TBL_DEVOLVER_ITENS.id_devolver = _TBL_DEVOLVER.id_devolver 
WHERE _TBL_DEVOLVER.data < "2018/09/01" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND 
_TBL_DEVOLVER.id_tecnicos IS NOT NULL AND _TBL_DEVOLVER.empresa = "1") AS saldoanterior,

SUM(_TBL_PRODUTOS.quantidade) AS qtdeliberada, 

(SELECT IFNULL(SUM(_TBL_BAIXAR_ITENS.quantidade),0) FROM _TBL_BAIXAR_ITENS 
LEFT OUTER JOIN _TBL_BAIXAR ON _TBL_BAIXAR_ITENS.id_baixar = _TBL_BAIXAR.id_baixar 
WHERE _TBL_BAIXAR.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND _TBL_BAIXAR.id_tecnicos 
IS NOT NULL AND _TBL_BAIXAR.empresa = "1") AS qtdebaixada, 

(SELECT IFNULL(SUM(_TBL_DEVOLVER_ITENS.quantidade),0) FROM _TBL_DEVOLVER_ITENS 
LEFT OUTER JOIN _TBL_DEVOLVER ON _TBL_DEVOLVER_ITENS.id_devolver = _TBL_DEVOLVER.id_devolver 
WHERE _TBL_DEVOLVER.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND id_produtos = _TBL_LIBERAR_ITENS.id_produtos AND _TBL_DEVOLVER.id_tecnicos 
IS NOT NULL AND _TBL_DEVOLVER.empresa = "1") AS qtdedevolvida 

FROM _TBL_PRODUTOS 
LEFT OUTER JOIN _TBL_LIBERAR_ITENS ON _TBL_PRODUTOS.id_produtos = _TBL_LIBERAR_ITENS.id_produtos 
LEFT OUTER JOIN _TBL_LIBERAR ON _TBL_LIBERAR_ITENS.id_liberar = _TBL_LIBERAR.id_liberar 
WHERE _TBL_LIBERAR.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND _TBL_LIBERAR_ITENS.id_produtos IS NOT NULL AND _TBL_LIBERAR.id_tecnicos 
IS NOT NULL AND _TBL_LIBERAR.empresa = "1" 
GROUP BY _TBL_PRODUTOS.id_produtos ORDER BY _TBL_PRODUTOS.descricao

O grande mistério é que ainda estou com a problemática do SELECT ficar rápido só com a presença da linha do SUM.

Porque será que remover esse SUM deixa o SELECT tão mais lento?

 

[bnobre]

Descobri algo interessante...

Dado o grande tamanho do SELECT, seria bem provável que eu tivesse um SUBSELECT tentando puxar de uma tabela externa, então eu tentei usar ALIASes em todas as tabelas para evitar esse problema... Então apliquei isso no primeiro SELECT, ficando assim:

SELECT LI1.id_produtos, P1.codigo, P1.descricao, 
(SELECT IFNULL(SUM(LI2.quantidade),0) FROM _TBL_LIBERAR_ITENS LI2 
LEFT OUTER JOIN _TBL_LIBERAR L2 ON LI2.id_liberar = L2.id_liberar 
WHERE LI2.id_produtos = LI1.id_produtos AND L2.data < "2018/09/01" AND 
L2.id_tecnicos IS NOT NULL AND L2.empresa = "1") - 

(SELECT IFNULL(SUM(BI1.quantidade),0) FROM _TBL_BAIXAR_ITENS BI1 
LEFT OUTER JOIN _TBL_BAIXAR B1 ON BI1.id_baixar = B1.id_baixar 
WHERE B1.data < "2018/09/01" AND BI1.id_produtos = LI1.id_produtos AND 
B1.id_tecnicos IS NOT NULL AND B1.empresa = "1") - 

(SELECT IFNULL(SUM(DI1.quantidade),0) FROM _TBL_DEVOLVER_ITENS DI1 
LEFT OUTER JOIN _TBL_DEVOLVER D1 ON DI1.id_devolver = D1.id_devolver 
WHERE D1.data < "2018/09/01" AND DI1.id_produtos = LI1.id_produtos AND 
D1.id_tecnicos IS NOT NULL AND D1.empresa = "1") AS saldoanterior,

SUM(LI1.quantidade) AS qtdeliberada, 

(SELECT IFNULL(SUM(BI2.quantidade),0) FROM _TBL_BAIXAR_ITENS BI2 
LEFT OUTER JOIN _TBL_BAIXAR B2 ON BI2.id_baixar = B2.id_baixar 
WHERE B2.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND BI2.id_produtos = LI1.id_produtos AND B2.id_tecnicos 
IS NOT NULL AND B2.empresa = "1") AS qtdebaixada, 

(SELECT IFNULL(SUM(DI2.quantidade),0) FROM _TBL_DEVOLVER_ITENS DI2 
LEFT OUTER JOIN _TBL_DEVOLVER D2 ON DI2.id_devolver = D2.id_devolver 
WHERE D2.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND DI2.id_produtos = LI1.id_produtos AND D2.id_tecnicos 
IS NOT NULL AND D2.empresa = "1") AS qtdedevolvida 

FROM _TBL_LIBERAR_ITENS LI1 
LEFT OUTER JOIN _TBL_PRODUTOS P1 ON LI1.id_produtos = P1.id_produtos 
LEFT OUTER JOIN _TBL_LIBERAR L1 ON LI1.id_liberar = L1.id_liberar 
WHERE L1.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND LI1.id_produtos IS NOT NULL AND L1.id_tecnicos 
IS NOT NULL AND L1.empresa = "1" 
GROUP BY LI1.id_produtos ORDER BY P1.descricao

Mesmo fazendo isso, o SELECT continuou demorado se eu retirasse o SUM.

Daí apliquei essa mesma ideia dos ALIASes no segundo SELECT que fiz, dado a dica do EMBarbosa, ficou assim:

SELECT P1.id_produtos, P1.codigo, P1.descricao, 
(SELECT IFNULL(SUM(LI2.quantidade),0) FROM _TBL_LIBERAR_ITENS LI2 
LEFT OUTER JOIN _TBL_LIBERAR L2 ON LI2.id_liberar = L2.id_liberar 
WHERE LI2.id_produtos = LI1.id_produtos AND L2.data < "2018/09/01" AND 
L2.id_tecnicos IS NOT NULL AND L2.empresa = "1") - 

(SELECT IFNULL(SUM(BI1.quantidade),0) FROM _TBL_BAIXAR_ITENS BI1 
LEFT OUTER JOIN _TBL_BAIXAR B1 ON BI1.id_baixar = B1.id_baixar 
WHERE B1.data < "2018/09/01" AND BI1.id_produtos = LI1.id_produtos AND 
B1.id_tecnicos IS NOT NULL AND B1.empresa = "1") - 

(SELECT IFNULL(SUM(DI1.quantidade),0) FROM _TBL_DEVOLVER_ITENS DI1 
LEFT OUTER JOIN _TBL_DEVOLVER D1 ON DI1.id_devolver = D1.id_devolver 
WHERE D1.data < "2018/09/01" AND DI1.id_produtos = LI1.id_produtos AND 
D1.id_tecnicos IS NOT NULL AND D1.empresa = "1") AS saldoanterior,

(SELECT IFNULL(SUM(LI3.quantidade),0) FROM _TBL_LIBERAR_ITENS LI3 
LEFT OUTER JOIN _TBL_LIBERAR L3 ON LI3.id_liberar = L3.id_liberar 
WHERE LI3.id_produtos = LI1.id_produtos AND L3.data BETWEEN "2018/09/01" AND "2018/09/31" AND 
L3.id_tecnicos IS NOT NULL AND L3.empresa = "1") AS qtdeliberada, 

(SELECT IFNULL(SUM(BI2.quantidade),0) FROM _TBL_BAIXAR_ITENS BI2 
LEFT OUTER JOIN _TBL_BAIXAR B2 ON BI2.id_baixar = B2.id_baixar 
WHERE B2.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND BI2.id_produtos = LI1.id_produtos AND B2.id_tecnicos 
IS NOT NULL AND B2.empresa = "1") AS qtdebaixada, 

(SELECT IFNULL(SUM(DI2.quantidade),0) FROM _TBL_DEVOLVER_ITENS DI2 
LEFT OUTER JOIN _TBL_DEVOLVER D2 ON DI2.id_devolver = D2.id_devolver 
WHERE D2.data BETWEEN "2018/09/01" AND 
"2018/09/30" AND DI2.id_produtos = LI1.id_produtos AND D2.id_tecnicos 
IS NOT NULL AND D2.empresa = "1") AS qtdedevolvida 

FROM _TBL_PRODUTOS P1 
LEFT OUTER JOIN _TBL_LIBERAR_ITENS LI1 ON P1.id_produtos = LI1.id_produtos 
LEFT OUTER JOIN _TBL_LIBERAR L1 ON LI1.id_liberar = L1.id_liberar 
WHERE LI1.id_produtos IS NOT NULL AND L1.id_tecnicos 
IS NOT NULL AND L1.empresa = "1" 
GROUP BY P1.id_produtos ORDER BY P1.descricao

Finalmente com esse SELECT reformulado e COM O USO de ALIASes, o SELECT ficou rápido com ou sem o uso do SUM.

Com isso eu concluo que o SELECT antes de mostrar o resultado estava tentando decidir qual tabela usar (a do SUBSELECT ou a EXTERNA) e daí ocorria a perda de tempo, fora a questão levantada pelo EMBarbosa sobre a estrutura errada.

Creio que é isso, se alguém tiver algo a acrescentar fique a vontade.

Abraços

[EMBarbosa]

4 horas atrás, doidopb disse:

Com isso eu concluo que o SELECT antes de mostrar o resultado estava tentando decidir qual tabela usar (a do SUBSELECT ou a EXTERNA) e daí ocorria a perda de tempo, fora a questão levantada pelo EMBarbosa sobre a estrutura errada. 

Você poderia ter certeza disso analisando qual plan foi gerada para cada query.

4 horas atrás, doidopb disse:

FROM _TBL_PRODUTOS P1 
LEFT OUTER JOIN _TBL_LIBERAR_ITENS LI1 ON P1.id_produtos = LI1.id_produtos 
LEFT OUTER JOIN _TBL_LIBERAR L1 ON LI1.id_liberar = L1.id_liberar 
WHERE LI1.id_produtos IS NOT NULL AND L1.id_tecnicos IS NOT NULL AND L1.empresa = "1"

 

É um pouco difícil sugerir alguma coisa sem entender realmente a modelagem de dados e o que o SQL tem como objetivo.

Mesmo assim, me parece que essa parte está ao contrário. Aparentemente, deveria começar pela tabela _TBL_LIBERAR. Ou talvez eu utilizaria uma CTE ou converteria isso tudo em uma stored procedure.

Outra coisa, algo que eu sempre tento fazer é diminuir o número de Left joins, sempre que possível. Geralmente é melhor trabalhar com inner joins.