Clipper On Line • Ver Tópico - Otimizar query

Otimizar query

Discussão sobre SQL

Moderador: Moderadores

 

Otimizar query

Mensagempor JoséQuintas » 26 Abr 2022 21:17

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

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é M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor JoséQuintas » 26 Abr 2022 21:24

Tentei com isto, mas mesmo problema:

   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é M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor JoséQuintas » 27 Abr 2022 10:13

Isto é rápido, menos de 1 segundo

SELECT produto, funcao(produto) FROM JPITEM


isto é rápido

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.

SELECT esproduto, esdata FROM JPESTOQUE
INNER JOIN ( SELECT produto, funcao(produto) FROM JPITEM ) AS ultval
   on estoque.esproduto = ultval.produto
José M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor JoséQuintas » 27 Abr 2022 10:31

Quase resolvido.
Na query original o FROM está errado, está pra produtos e não pro estoque.
José M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor JoséQuintas » 27 Abr 2022 14:24

JoséQuintas escreveu: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é M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor JoséQuintas » 27 Abr 2022 18:24

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:

   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é M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor JoséQuintas » 27 Abr 2022 18:32

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.
José M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes

Otimizar query

Mensagempor Itamar M. Lins Jr. » 27 Abr 2022 19:16

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.
Avatar de usuário

Itamar M. Lins Jr.
Colaborador

Colaborador
 
Mensagens: 6007
Data de registro: 30 Mai 2007 11:31
Cidade/Estado: Ilheus Bahia
Curtiu: 241 vezes
Mens.Curtidas: 358 vezes

Otimizar query

Mensagempor JoséQuintas » 28 Abr 2022 11:27

Itamar M. Lins Jr. escreveu: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.
José M. C. Quintas
Harbour 3.4, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, hbnetio, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"
Avatar de usuário

JoséQuintas
Colaborador

Colaborador
 
Mensagens: 18915
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 14 vezes
Mens.Curtidas: 1013 vezes




Retornar para SQL

Quem está online

Usuários vendo este fórum: Nenhum usuário registrado online e 3 visitantes


Ola Amigo, espero que meu site e forum tem lhe beneficiado, com exemplos e dicas de programacao.
Entao divulgue o link da Doacao abaixo para seus amigos e redes sociais ou faça uma doacao para o site forum...
MUITO OBRIGADO PELA SUA DOACAO!
Faça uma doação para o forum
cron
v
Olá visitante, seja bem-vindo ao Fórum Clipper On Line!
Efetue o seu login ou faça o seu Registro