Clipper On Line • Ver Tópico - Mysql: consulta lenta

Mysql: consulta lenta

Discussão sobre SQL

Moderador: Moderadores

 

Mysql: consulta lenta

Mensagempor cjp » 23 Abr 2021 01:09

Pessoal, tenho recebido do meu provedor um relatório de consultas lentas em meu banco de dados.

A consulta que está gerando esse relatório é a seguinte:

select codigo from acoes e where marca='V' and hrvisto<>91234 and e.codigo not IN (select codigo from acoes f where compvenda=0 and dtcotacao='2021-04-22' and (substr(hrcotacao,1,2)*3600)+(substr(hrcotacao,4,2)*60)>= 38903.25 group by codigo) group by codigo order by marca DESC, max(dtcotacao)


Gostaria de saber se alguém conseguiria me ajudar para tornar essa consulta mais rápida.

A ideia deste select é pegar na tabela acoes os códigos dos registros que tenham marca=V e hrvisto#91234 e que não tenham tido nenhum registro nos últimos minutos.

A tabela acoes tem 945.634 registros e está com índice no campo codigo.

Não sei se estou fazendo algo errado, essa foi a melhor forma que consegui de fazer o select.

Detalhe: essa consulta é feita centenas de vezes por dia no meu sistema. Se ela está gerando consultas lentas, isso realmente será problema no provedor.

Alguém pode me ajudar?
Nota de Moderação:
Itamar M. Lins Jr.: O presente tópico foi movido da seção Harbour, uma vez que seu conteúdo não tem relação com os objetivos daquela seção, onde só podem constar dúvidas técnicas de programação diretamente relacionadas com o [x]Harbour.
Inacio de Carvalho Neto
cjp
Usuário Nível 6

Usuário Nível 6
 
Mensagens: 1527
Data de registro: 19 Nov 2010 21:29
Cidade/Estado: paraná
Curtiu: 10 vezes
Mens.Curtidas: 20 vezes

Mysql: consulta lenta

Mensagempor alxsts » 23 Abr 2021 02:50

Olá!
cjp escreveu:Gostaria de saber se alguém conseguiria me ajudar para tornar essa consulta mais rápida.

Podemos tentar...

Não entendi:
- Se marca é sempre "V", por que ordenar por marca, e ainda mais DESC?
- max(dtcotacao) o que pretende com isto no order by?
- Explique melhor isto: "não tenham tido nenhum registro nos últimos minutos"
- o motivo da subquery...

Sugestão:
- Disse que tem índice pelo campo codigo. Reveja este índice ou crie um outro com codigo + marca + dtcotacao (ou na ordem que for preciso)
- Tente fazer uma query única
- Notei que o campo hora é separado do campo data e do tipo char. Em tabelas futuras, procure trabalhar com campos datetime. facilita muito cálculos de intervalo.
select codigo 
  from acoes e
where marca='V'
  and hrvisto<>91234
  and compvenda=0
  and dtcotacao='2021-04-22'
  and (substr(hrcotacao,1,2)*3600)+(substr(hrcotacao,4,2)*60)>= 38903.25 ===> Explicar melhor
order by codigo (verifique a ordem que precisa)


Procure abrir tópicos novos no respectivo fórum. Este tópico deveria estar na seção SQL ou Banco de Dados.
[]´s
Alexandre Santos (AlxSts)
alxsts
Colaborador

Colaborador
 
Mensagens: 2943
Data de registro: 12 Ago 2008 15:50
Cidade/Estado: São Paulo-SP-Brasil
Curtiu: 21 vezes
Mens.Curtidas: 248 vezes

Mysql: consulta lenta

Mensagempor alxsts » 23 Abr 2021 02:55

[]´s
Alexandre Santos (AlxSts)
alxsts
Colaborador

Colaborador
 
Mensagens: 2943
Data de registro: 12 Ago 2008 15:50
Cidade/Estado: São Paulo-SP-Brasil
Curtiu: 21 vezes
Mens.Curtidas: 248 vezes

Mysql: consulta lenta

Mensagempor AutomoSistema » 23 Abr 2021 03:48

Boa noite, cria um arquivo de index da coluna marca : create index marca_cdx on acoes( marca );. Pode resolver.
AutomoSistema
Usuário Nível 2

Usuário Nível 2
 
Mensagens: 87
Data de registro: 27 Nov 2015 21:09
Cidade/Estado: Jardim-MS
Curtiu: 0 vez
Mens.Curtidas: 4 vezes

Mysql: consulta lenta

Mensagempor JoséQuintas » 23 Abr 2021 09:56

Se está pesquisando uma determinada data, indice por data.

Para o horário, colocar na pesquisa o horário já convertido, senão tem que fazer a conta com cada um pra saber qual atende à pesquisa.
Aliás, porque não horario > '10:00:00'
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18013
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Mysql: consulta lenta

Mensagempor cjp » 23 Abr 2021 10:41

Vou tentar explicar melhor:

- Se marca é sempre "V", por que ordenar por marca, e ainda mais DESC?


Na tabela a marca não é sempre V, tem várias; nessa consulta que eu coloquei de exemplo, a marca é V, mas também consulto por outras marcas, e às vezes por mais de uma ao mesmo tempo, razão pela qual coloquei order by marca desc.

- max(dtcotacao) o que pretende com isto no order by?


Aqui também, às vezes a consulta retorna dtcotacao diferentes, razão pela qual coloquei order by max(dtcotacao), para ordenar pela data.

- Explique melhor isto: "não tenham tido nenhum registro nos últimos minutos"


É assim: meu sistema está a todo momento inserindo registros na tabela, referente a códigos diversos, com data e hora da inserção. Então, esta consulta precisa retornar os códigos que não tiveram inserção de registros nos últimos minutos (neste caso), ou nas últimas horas ou nos últimos dias (em consultas semelhantes). Pra isso é que eu fiz a subquery, pra primeiro verificar quais códigos teve inserção de registros nos últimos minutos, e depois verificar os códigos que não estão entre estes últimos.

- o motivo da subquery...


O motivo é justamente este explicado acima: verificar primeiro os códigos que tiveram inserção de registros nos últimos minutos, e daí extrair os códigos que não tiveram inserção.

- Disse que tem índice pelo campo codigo. Reveja este índice ou crie um outro com codigo + marca + dtcotacao (ou na ordem que for preciso)

Boa noite, cria um arquivo de index da coluna marca : create index marca_cdx on acoes( marca );. Pode resolver.

Se está pesquisando uma determinada data, indice por data.


Realmente, faltava esse índice. Criei agora.

Uma dúvida que sempre tenho: é preciso colocar "use index" na consulta? Faz diferença na velocidade da consulta?

- Tente fazer uma query única


Esta eu não entendi. A query já não é única?

- Notei que o campo hora é separado do campo data e do tipo char. Em tabelas futuras, procure trabalhar com campos datetime. facilita muito cálculos de intervalo.

Veja: MySQL Date and Time Functions


Inicialmente eu tinha colocado com datetime, mas tive bastante dificuldade em lidar com ele, por falta de conhecimento, por isso separei os campos. Terei que estudar melhor sobre isso para aprender a lidar bem com esse campo. Farei isso.

Procure abrir tópicos novos no respectivo fórum. Este tópico deveria estar na seção SQL ou Banco de Dados.


Desculpe, realmente errei.

Para o horário, colocar na pesquisa o horário já convertido, senão tem que fazer a conta com cada um pra saber qual atende à pesquisa.
Aliás, porque não horario > '10:00:00'


Isso funcionaria? Em harbour acho que não funciona. Mas vou testar assim.
Inacio de Carvalho Neto
cjp
Usuário Nível 6

Usuário Nível 6
 
Mensagens: 1527
Data de registro: 19 Nov 2010 21:29
Cidade/Estado: paraná
Curtiu: 10 vezes
Mens.Curtidas: 20 vezes

Mysql: consulta lenta

Mensagempor JoséQuintas » 23 Abr 2021 11:05

Ordem de data decrescente é DtCotacao DESC

No horário, se o campo é string, é só colocar Horario > '10:00' ou o horário que quiser, nem entendi porque faz esse cálculo.

O SELECT em geral tá confuso, vai selecionar tudo que existe menos uma data, é provável que venha 1 milhão de registros.
E é justamente por isso que está lento.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18013
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Mysql: consulta lenta

Mensagempor alxsts » 23 Abr 2021 11:32

- Tente fazer uma query única

Neste caso você tem uma query com outra (subquery). Cada uma processa a tabela de um milhão de registros uma vez. Tente fazer como mostrei, uma única consulta, colocando todas as condições na cláusula where.
[]´s
Alexandre Santos (AlxSts)
alxsts
Colaborador

Colaborador
 
Mensagens: 2943
Data de registro: 12 Ago 2008 15:50
Cidade/Estado: São Paulo-SP-Brasil
Curtiu: 21 vezes
Mens.Curtidas: 248 vezes

Mysql: consulta lenta

Mensagempor JoséQuintas » 23 Abr 2021 12:29

O comando é que está confuso mesmo.

select codigo from acoes f where compvenda=0 and dtcotacao='2021-04-22' and (substr(hrcotacao,1,2)*3600)+(substr(hrcotacao,4,2)*60)>= 38903.25 group by codigo


Pra trazer os códigos não é o group by, é o distinct.
group by é pra somar, o que exige pegar tudo, enquanto distinct já pega um de cada, o que só precisa de um de cada.

Mas ainda não é esse o maior problema
Não faço a menor idéia do que é hrvisto <> 91234, dá a impressão de que está salvando horário em formato numérico, e se quer isso específico, também um índice por isso poderia ajudar, dependendo do que significa isso.

e novamente repete o group by no segundo select

Já falamos de organizar melhor essas bases de dados, senão tá sempre processando porrilhões de informação.
Isso de misturar movimentação com cadastro não dá certo, e vai piorar cada vez mais.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18013
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Mysql: consulta lenta

Mensagempor JoséQuintas » 23 Abr 2021 12:33

Será que precisa mesmo todas as cotações de todos os tempos?
Cotações antigas só vão servir como perda de tempo, deveria limitar data também.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18013
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Mysql: consulta lenta

Mensagempor cjp » 23 Abr 2021 16:10

Neste caso você tem uma query com outra (subquery). Cada uma processa a tabela de um milhão de registros uma vez. Tente fazer como mostrei, uma única consulta, colocando todas as condições na cláusula where.


A query que vc postou não funcionaria como desejo, pois ela não verifica se teve registro recente. Veja no anexo como fica o retorno dessa query que vc postou, sendo que a query que eu postei, neste mesmo momento, está retornando sem nenhum registro.

Pra trazer os códigos não é o group by, é o distinct.
group by é pra somar, o que exige pegar tudo, enquanto distinct já pega um de cada, o que só precisa de um de cada.


Vou testar com distinct.

Não faço a menor idéia do que é hrvisto <> 91234, dá a impressão de que está salvando horário em formato numérico, e se quer isso específico, também um índice por isso poderia ajudar, dependendo do que significa isso.


O campo hrvisto é realmente para salvar a hora em formato numérico. Mas neste caso (<>91234) é só um código para excluir determinados códigos.

Isso de misturar movimentação com cadastro não dá certo, e vai piorar cada vez mais.


Esta parte eu não entendi. Como assim misturar movimentação com cadastro?

Será que precisa mesmo todas as cotações de todos os tempos?
Cotações antigas só vão servir como perda de tempo, deveria limitar data também.


De fato, eu não preciso de todas as cotações de todos os tempos, e pra isso que eu preciso de ajuda, pois não estou sabendo limitar corretamente.

O que eu realmente preciso: uma lista dos códigos da tabela que não tiveram cotação registrada nos últimos minutos. Pra isso, na minha cabeça (mas posso estar errado, e por isso estou pedindo ajuda), preciso listar primeiro todos os códigos que tiveram cotação registrada nos últimos minutos, e depois pegar os códigos que não estão nesta lista. Por isso eu fiz um select com os que tiveram cotação registrada nos último minutos (select codigo from acoes f where compvenda=0 and dtcotacao='2021-04-22' and (substr(hrcotacao,1,2)*3600)+(substr(hrcotacao,4,2)*60)>= 38903.25 group by codigo); note que neste select eu pego só as cotações com a data de hoje e a hora de alguns minutos pra cá (portanto, não são todos de todos os tempos). Daí depois eu faço outro select pesquisando todos que não estão nesta lista (select codigo from acoes e where marca='V' and hrvisto<>91234 and e.codigo not IN...).

A questão é: tem jeito melhor de fazer isso? Certamente deve ter, mas ainda não sei como. Já entendi a questão dos índices faltantes, já fiz. E também já entendi que a forma de tratar com a hora, fruto da minha lógica Harbour, está errada; isso eu vou consertar ainda. Mas creio que ainda esteja errado, que ainda dê pra melhorar esse select. Como?
Inacio de Carvalho Neto
cjp
Usuário Nível 6

Usuário Nível 6
 
Mensagens: 1527
Data de registro: 19 Nov 2010 21:29
Cidade/Estado: paraná
Curtiu: 10 vezes
Mens.Curtidas: 20 vezes

Mysql: consulta lenta

Mensagempor JoséQuintas » 23 Abr 2021 17:16

Tá muito doido, nem sei como não dá erro.
olhe com atenção.

select codigo 
from acoes e
where marca='V' and hrvisto<>91234
and e.codigo not IN (select codigo
                              from acoes f
                             where compvenda=0 and dtcotacao='2021-04-22' and (substr(hrcotacao,1,2)*3600)+(substr(hrcotacao,4,2)*60)>= 38903.25
                            group by codigo)
group by codigo
order by marca DESC, max(dtcotacao)


Está pegando somente o código, e colocando em ordem de.... mas se só tem código, como aceita ordem em outros campos?
Não tem um cadastro de códigos? se não tem, tá misturando cadastro com movimento, o que exige processar tudo só pra pegar a lista de códigos.

Poderia ter o cadastro de códigos, "talvez" gravar nele a data da última cotação, e a informação estaria disponível no cadastro, sem precisar olhar a movimentação.

o JOIN também permite relacionar a partir de uma data, por exemplo, não precisa ser o campo exato, o que agilizaria isso.

SELECT from acoes
left join cotacoes on cotacoes.codigo = acoes.codigo and dtcotacao > '2021-01-01'
where cotacoes.dtcotacao is not null

algo do tipo acima.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18013
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Mysql: consulta lenta

Mensagempor alxsts » 23 Abr 2021 20:13

Olá!
Você informou que estes minutos podem ser horas ou até mesmo dias. Na query você filtra por uma data. Desta forma, temos um período com data e hora de início e fim. Como é obtido este período? Vem de uma tela onde o usuário informa?
[]´s
Alexandre Santos (AlxSts)
alxsts
Colaborador

Colaborador
 
Mensagens: 2943
Data de registro: 12 Ago 2008 15:50
Cidade/Estado: São Paulo-SP-Brasil
Curtiu: 21 vezes
Mens.Curtidas: 248 vezes

Mysql: consulta lenta

Mensagempor JoséQuintas » 23 Abr 2021 21:27

alxsts escreveu:Você informou que estes minutos podem ser horas ou até mesmo dias. Na query você filtra por uma data. Desta forma, temos um período com data e hora de início e fim. Como é obtido este período? Vem de uma tela onde o usuário informa?


Aí que está.
A data/hora é pra ter o que NÃO SAI.
Não saem os códigos com movimento naquela data/hora, mas o resto sai tudo.
José M. C. Quintas
Harbour 3.2, mingw, gtwvg, multithread, dbfcdx, ADO+MySql, PNotepad
"The world is full of kings and queens, who blind our eyes and steal our dreams Its Heaven and Hell"

https://github.com/JoseQuintas/
Avatar de usuário

JoséQuintas
Membro Master

Membro Master
 
Mensagens: 18013
Data de registro: 26 Fev 2007 11:59
Cidade/Estado: São Paulo-SP
Curtiu: 15 vezes
Mens.Curtidas: 1206 vezes

Mysql: consulta lenta

Mensagempor cjp » 23 Abr 2021 23:50

Testei teu exemplo, Quintas, e deu erro. Veja o anexo.
Como não o entendi, não sei consertá-lo.
Anexos
join.png
Inacio de Carvalho Neto
cjp
Usuário Nível 6

Usuário Nível 6
 
Mensagens: 1527
Data de registro: 19 Nov 2010 21:29
Cidade/Estado: paraná
Curtiu: 10 vezes
Mens.Curtidas: 20 vezes

Próximo



Retornar para SQL

Quem está online

Usuários vendo este fórum: Google [Bot] e 6 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