Otimizar query

[JoséQuintas]

Não consigo enxergar aonde otimizar isto, apesar de já saber aonde deixa lento:

Código: Selecionar todos

SELECT 
JPESTOQUE.IDESTOQUE, JPESTOQUE.ESCADASTRO, JPESTOQUE.ESDATLAN, JPESTOQUE.ESPEDIDO, JPESTOQUE.ESNUMDOC, 
JPESTOQUE.ESOBS, JPESTOQUE.ESTIPLAN, JPESTOQUE.ESNUMDEP,  
JPESTOQUE.ESTRANSACAO,JPESTOQUE.ESQTDE * IF( JPITEM.IEQTDCOM < 1, 1, JPITEM.IEQTDCOM ) AS QTDE, 
JPESTOQUE.ESVALOR / IF( JPITEM.IEQTDCOM < 1, 1, JPITEM.IEQTDCOM ) AS VALOR,  JPITEM.IENOME, 
JPITEM.IEQTDCOM, JPITEM.IEPROLOC, JPITEM.IEPRODEP, JPITEM.IEPROGRU, JPITEM.IEPROSEC, JPITEM.IDPRODUTO, 
JPITEM.IETIPO, JPITEM.IEANP, 
ze_ProdutoUltimaEntradaValor( JPESTOQUE.ESPRODUTO ) AS ULTENTVAL, 
JPTRANSACAO.TRREACAO, IF( SUBSTR( ESCFOP, 2 ) IN ( '905', '663', '664', '906' ), 'S', 'N' ) AS TRANSFERENCIA, 
JPCADASTRO.CDNOME  FROM JPITEM 
INNER JOIN JPESTOQUE ON JPESTOQUE.ESPRODUTO = JPITEM.IDPRODUTO 
INNER JOIN JPCADASTRO ON ESCADASTRO = JPCADASTRO.IDCADASTRO 
INNER JOIN JPTRANSACAO ON ESTRANSACAO = JPTRANSACAO.IDTRANSACAO 
WHERE  1 = 1 AND DATE( JPESTOQUE.ESDATLAN ) <= DATE( '2022-04-25' ) 
ORDER BY IDPRODUTO, ESDATLAN, ESTIPLAN, ESNUMDOC DESC;

[JoséQuintas]

Tentei com isto, mas mesmo problema:

Código: Selecionar todos

   cSQLFrom += " LEFT JOIN ( SELECT JPITEM.IDPRODUTO AS ULTCOD, ze_ProdutoUltimaEntradaValor( JPITEM.IDPRODUTO ) AS ULTENTVAL FROM JPITEM )"
   cSQLFrom += " AS ULTENT ON JPESTOQUE.ESPRODUTO = ULTENT.ULTCOD"

Como teste, criei um temporário com o cálculo.

Essa query isolada demora 0.250 segundos, calculando pra todos os produtos.
Mesmo colocando desse jeito, no MySQL 5.7 causa estouro do limite de tempo.

Sem idéia sobre agilizar isso pra mer.da do MySQL 5.7
Por enquanto a solução vai ser retornar o 5.5, porque já estava lá, mas no MariaDB não acontece isso, nem no MySQL velho.

E futuramente, se isso não mudar, é nunca mais usar MySQL, somente MariaDB.

[JoséQuintas]

Isto é rápido, menos de 1 segundo

Código: Selecionar todos

SELECT produto, funcao(produto) FROM JPITEM

isto é rápido

Código: Selecionar todos

SELECT esproduto, esdata FROM JPESTOQUE

Combinando os dois, fica muito lento, no MySQL 5.7 até estoura o limite de tempo.
A última tentativa foi fazer um sub-select, pra calcular apenas uma vez por produto.

Código: Selecionar todos

SELECT esproduto, esdata FROM JPESTOQUE
INNER JOIN ( SELECT produto, funcao(produto) FROM JPITEM ) AS ultval
   on estoque.esproduto = ultval.produto

[JoséQuintas]

Quase resolvido.
Na query original o FROM está errado, está pra produtos e não pro estoque.

[JoséQuintas]

Quase resolvido.
Na query original o FROM está errado, está pra produtos e não pro estoque.

Alarme falso.
Na prática ficou até mais lento.

Em termos práticos:

A query pega a movimentação de estoque, relacionada com outras tabelas.
O último preço é obtido por uma function no servidor.

Se eu retirar o último preço ok.
Se eu pegar todos os produtos com o último preço ok.
Se eu juntar as duas..... fica lento.
Pensei que era por pegar o último preço de cada lançamento do estoque, então acrescentei a sub-query que é rápida, pra pegar por produto, mas não resolveu.
Depois vi o FROM que usava como base os produtos, ao invés da movimentação de estoque, alterei e não resolveu.

No MariaDB ou MySQL 5.5 demora 58 segundos.
No MySQL 5.7 é que o problema piorou, o processo é cancelado após 5 minutos.

Não reparei antes porque se trata da listagem completa do estoque, e achei que era normal demorar por ser muita informação.

Por enquanto não fiz novas tentativas.
Vou olhar com atenção os tipos dos campos, mas que eu me lembre são todos iguais.

Existe algum programa que ajude nisso? pra analisar queries ?
EXPLAIN não ajudou em nada.

[JoséQuintas]

Resolvido no chute.
Nem quero saber qual foi o FDP que inventou de definir collation por campo.
Apenas que ele vá se fud... tá bom.

Resolvido executando esta rotina:

Código: Selecionar todos

   LOCAL cnSQL := ADOLocal()

   cnSQL:cSQL := "SELECT concat('ALTER TABLE `', TABLE_SCHEMA, '`.`', table_name, '` CHARACTER SET latin1 COLLATE latin1_swedish_ci;')" + ;
      " AS MYCMD from information_schema.tables" + ;
      " where TABLE_SCHEMA like database();"
   cnSQL:Execute()
   DO WHILE ! cnSQL:Eof()
      SayScroll( cnSQL:String( "mycmd" ) )
      cnSQL:ExecuteNoReturn( cnSQL:String( "mycmd" ) )
      cnSQL:MoveNext()
   ENDDO
   cnSQL:CloseRecordset()

   RETURN

Reduziu o tempo pra 1 segundo.

Só descobri porque após trocar de MySQL 5.7 pra MariaDB 10.5, e depois de MariaDB 10.5 pra MySQL 5.6 apareceu erro na stored function.
Alguma coisa a ver com mix collation.

Só defino isso pra tabela, mas por algum motivo, é acrescentado em cada campo.
Alguma alteração manual aonde o default ficou diferente.

[JoséQuintas]

Acréscimo:

O erro paralizou a empresa a tarde toda.

Foi depois da migração pro servidor novo, com MySQL novo, e tudo novo, com provável default diferente.

Pelo menos agora resolveu de vez, mas vai ficar o MySQL VELHO mesmo.

[Itamar M. Lins Jr.]

Olá!

mas vai ficar o MySQL VELHO mesmo.

Se está funcionando, pra quê mudar ?
O pessoal pensa que tenho problema com xHarbour e outras libs... Já usei TODAS que pude, testei várias, IDEs então nem se fala. xMate, xIsso, xAquilo...
Tô nem ai, mudo mesmo se vejo alguma vantagem. Mas tem casos que o velho continua resolvendo e não precisa mudar.

Por exemplo, SQL é SQL, ai A,B,C resolvem adotar tal particularidade de tal LIB, tal SGDB, mas na frente encontrará alguns problemas com certeza porque aquela particularidade ficou lá estagnada em tal versão.

Já sabendo disso, desse problema a primeira coisa que vai ver é essa configuração e essa query quando mudar p/ outra versão ou outro SGBD.

Saudações,
Itamar M. Lins Jr.

[JoséQuintas]

Já sabendo disso, desse problema a primeira coisa que vai ver é essa configuração e essa query quando mudar p/ outra versão ou outro SGBD.

Vou tentar inclur alguma coisa no backup pra evitar isso.
Talvez o comando que usei pra resolver.

Na próxima troca de servidor, daqui há alguns anos, posso não lembrar disso, e já fica resolvido.