Mysql: consulta lenta

[cjp]

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:

Código: Selecionar todos

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?

[alxsts]

Olá!

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.

Código: Selecionar todos

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.

[alxsts]

Olá!

Veja: MySQL Date and Time Functions

[AutomoSistema]

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

[JoséQuintas]

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'

[cjp]

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.

[JoséQuintas]

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.

[alxsts]

- 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.

[JoséQuintas]

O comando é que está confuso mesmo.

Código: Selecionar todos

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éQuintas]

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.

[cjp]

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?

[JoséQuintas]

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

Código: Selecionar todos

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.

[alxsts]

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?

[JoséQuintas]

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.

[cjp]

Testei teu exemplo, Quintas, e deu erro. Veja o anexo.
Como não o entendi, não sei consertá-lo.