Mysql: consulta lenta

por **JoséQuintas** » 27 Abr 2021 21:22

//STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();

Putz... achei lindo isso... tá igual meu uso de ADO.
E o uso de ponto e vírgula lembra as functions do MySQL.
Então, por usar ADO, já sei um pouco de MySQL em java !!!!

rs:Next() igual ADO
rs:Close() igual ADO
Conn:Close() igual ADO
Só esse mtmt:Close() que não sei do que se trata
E ao invés do Fields() do ADO, usa GetInt() e GetString(), nomes diferentes, mas do jeito que estou usando: :Number(), :String()

Pois é... acabou mostrando algo parecido com ADO e nem percebeu kkkkk

Itamar M. Lins Jr. escreveu:Veja que é exatamente o contrário do que vc está tetando passar, dizendo que SQLMIX faz a pessoa não aprender SQL. Mas é usando o ADO que faz com que o usuário não aprenda SQL.

Tem certeza?
Leu direito?

Não é necessário conhecimento de SQL para acessar um banco de dados ao usar o ADO, embora se possa usar o ADO para executar comandos SQL diretamente. A desvantagem do último é que ele introduz uma dependência sobre o tipo de banco de dados utilizado.

E aí?
Realmente concorda com o texto?
Diz que executar comandos SQL é desvantagem?
Se é para o ADO, também é para o SQLMIX.

Então....
o SQLMIX é DBF sem DBF
o meu ADO é ADO sem ADO
Agora tá igual .... igual mas diferente kkkkk

por **JoséQuintas** » 27 Abr 2021 21:32

   WITH OBJECT ::cnSQL
      :cSQL := "SELECT JPTABCATEGORIA.*, LICMIXLICENCA, LICENCANOME, LICENCAOBS " + ;
         " FROM JPTABCATEGORIA" + ;
         " LEFT JOIN JPTABLICMIX ON LICMIXCATEGORIA = IDCATEGORIA" + ;
         " LEFT JOIN JPTABLICENCA ON IDLICENCA = LICMIXLICENCA" + ;
         " WHERE IDCATEGORIA = " + NumberSQL( nIDCATEGORIA ) + ;
         " ORDER BY LICENCANOME"
      :Execute()
      cNome   := :String( "CATEGNOME", 80 )
      cObs    := :String( "CATEGOBS", 30 )
      cInfInc := :String( "CATEGINFINC", 80 )
      cInfAlt := :String( "CATEGINFALT", 80 )
      oTBrowse := { ;
         { "LICENCA", { || Str( :Number( "LICMIXLICENCA" ), 6 ) } }, ;
         { "NOME",    { || :String( "LICENCANOME", 50 ) } }, ;
         { "OBS",     { || :String( "CATEGOBS", 30 ) } } }
      ::ShowTabs()
      @ Row() + 1, 1 SAY "Código............:" GET nIdCategoria PICTURE "999999" WHEN .F.
      @ Row() + 2, 1 SAY "Nome..............:" GET cNome PICTURE "@!" VALID ! Empty( cNome )
      @ Row() + 1, 1 SAY "Obs para alerta...:" GET cObs PICTURE "@!"
      nLinTBrowse := Row() + 2
      KEYBOARD Chr( K_ESC )
      BrowseADORC( nLinTBrowse, 1, MaxRow() - 5, MaxCol(), ::cnSQL, oTBrowse, "LICENCANOME", { || "" } )
      @ MaxRow() - 4, 1 SAY "Inf.Inclusão......:" GET cInfInc  WHEN .F.
      @ MaxRow() - 3, 1 SAY "Inf.Alteração.....:" GET cInfAlt  WHEN .F.
      :CloseRecordset()
   ENDWITH

Uia quanta coisa do ADO.... tem o :Execute(), e o :CloseRecordset()
Ah não... esses são métodos da minha classe....

Tá com a cara do java kkkk

Itamar, você tem conhecimento baixo do ADO porque apenas está lendo postagens, do mesmo jeito que eu também tenho conhecimento baixo do SQLMIX.
Estou discordando sobre partes que você diz sobre o ADO, e você discordando de partes que eu digo sobre SQLMIX.
Não vamos chegar a lugar nenhum, nós dois estamos certos, do mesmo jeito que nós dois estamos errados, tudo depende do ponto de vista.

Com certeza, vale a pena usar qualquer um dos dois pra chegar no que interessa, acaba não sendo tão importante qual dos dois vai ser usado.

por **Itamar M. Lins Jr.** » 27 Abr 2021 22:10

Olá!

você tem conhecimento baixo do ADO porque apenas está lendo postagens

Tenho uma aplicação em VB que usa MySQL... Nem mexo mais nela, tá aqui os fontes...
Para vc ver que tudo é maravilhoso, na teoria.
Usando SQLMIX esses seus comandos e em JAVA, nem precisa.
Apenas isso:

cSQL := "SELECT JPTABCATEGORIA.*, LICMIXLICENCA, LICENCANOME, LICENCAOBS " + ;
         " FROM JPTABCATEGORIA" + ;
         " LEFT JOIN JPTABLICMIX ON LICMIXCATEGORIA = IDCATEGORIA" + ;
         " LEFT JOIN JPTABLICENCA ON IDLICENCA = LICMIXLICENCA" + ;
         " WHERE IDCATEGORIA = " + NumberSQL( nIDCATEGORIA ) + ;
         " ORDER BY LICENCANOME

O resto não precisa explicar pq todo mundo aqui do forum sabe manipular DBF.
No seu ADO precisa converter... E vc diz que é melhor.
Primeiro inventaram que SQLMIX só extraia os dados para DBF, depois essa que SQLMIX é DBF...
Mas quando vamos para o finalmente, se apresenta a diferença.
cNome := :String( "CATEGNOME", 80 )

SQLMIX nos poupa disso e ainda roda no Linux.
Nem pretendo ter conhecimento avançado de ADO, pq ADO não tem nada para aprender em relação ao DBF. Agora fazer as sentenças em SQL, ai sim, está o segredo e todo mundo que começa a aprender vai caindo a fixa... Poxa no DBF eu tinha, isso, e aquilo... Podia fazer assim e assado... Bem que "result set" podia ser em DBF.

Quantas horas qualquer aprendiz passa no HeidiSQL ou no PgAdmin, etc, só aprendendo SQL ? E vc quer misturar isso com ADO ? e desmerecer as outras ferramentas que funcionam e vc nem sabe como é.

Saudações,
Itamar M. Lins Jr.

por **JoséQuintas** » 27 Abr 2021 23:51

Itamar M. Lins Jr. escreveu:Mas quando vamos para o finalmente, se apresenta a diferença.
cNome := :String( "CATEGNOME", 80 )
SQLMIX nos poupa disso e ainda roda no Linux.

Tá bom, venceu pelo cansaço.

Em todo caso....

STATIC FUNCTION ADOSubFilter( cKey, aFieldList )

   LOCAL cField, cTxt := ""

   FOR EACH cField IN aFieldList
      IF ! Empty( cField )
         cTxt += iif( Len( cTxt ) == 0, "", " AND " ) + " " + cKey + " LIKE '%" + cField + "%'"
      ENDIF
   NEXT

   RETURN cTxt

ADO sozinho, sem nenhuma base de dados, só usar comandos SQL.
Tá bom assim?

por **alxsts** » 27 Abr 2021 23:55

Olá!

Bom... deixando a disputa de lado e retornando ao assunto do tópico, seguem alguns testes que fiz:

/* SQL para criação de tabela - modo tradicional */

USE test;
DROP TABLE IF EXISTS tbAcoes

CREATE TABLE tbAcoes (
   codigo INT(6) UNSIGNED PRIMARY KEY,
   marca VARCHAR(1) NOT NULL,
   hrvisto INT(6) NOT NULL,
   compvenda INT(6),
   dtcotacao DATE, 
   hrcotacao VARCHAR(10)
);

------

/* SQL para popular a tabela com 1000 registros, utilizando Common Table Expressions (CTE) 
   (Funciona em MySQL 8 e acima e MariaDB 10 e acima)
*/

INSERT INTO tbAcoes 
   WITH RECURSIVE my_cte( codigo, marca, hrvisto, compvenda, dtcotacao, hrcotacao) AS
   (
      SELECT
          1 as codigo,
          ' ' as marca,
          98989 as hrvisto,
          0 as compvenda,
          Current_Date() as dtcotacao,
          Left(cast(current_time() as varchar(10)),5) as hrcotacao
      UNION ALL
      SELECT
          codigo + 1 as codigo,
          CASE Mod( (codigo + 1), 2)
             WHEN 0 THEN ' '
          ELSE
             CASE
                WHEN Mod((codigo + 1), 3) = 0 THEN 'V'
             ELSE 'A'
             END
         END as marca,
         99999 - ( mod(codigo + 1, 2) + codigo / 2 ) * 100 as hrvisto,
         CASE
             WHEN Mod(codigo + 1,
             2) <> 0 then 1
             ELSE 0
         END as compvenda,
         CASE
            WHEN (codigo + 1) < 50 
               THEN Curdate()
            ELSE 
               Cast( Date_Sub( Current_Timestamp, INTERVAL (1000000 - ( mod(codigo + 1, 2) + codigo / 2 ) * 1000) SECOND) AS Date)
         END AS dtcotacao,
         Left(Cast( Date_Sub( Current_Time(), INTERVAL (codigo + 1 ) MINUTE) as VARCHAR(10)),5) AS hrcotacao
      FROM my_cte
      WHERE codigo < 1000 
   )
   SELECT *
     FROM my_cte;
------

/* SQL para criação de tabela e já popular com 1000 registros,
   utilizando Common Table Expressions (CTE) 
   (Funciona em MySQL 8 e acima e MariaDB 10 e acima) 
*/

USE test;
CREATE TABLE tbAcoes
   WITH RECURSIVE my_cte( codigo, marca, hrvisto, compvenda, dtcotacao, hrcotacao) AS
   (
      SELECT
          1 as codigo,
          ' ' as marca,
          98989 as hrvisto,
          0 as compvenda,
          Current_Date() as dtcotacao,
          Left(cast(current_time() as varchar(10)),5) as hrcotacao
      UNION ALL
      SELECT
          codigo + 1 as codigo,
          CASE Mod( (codigo + 1), 2)
             WHEN 0 THEN ' '
          ELSE
             CASE
                WHEN Mod((codigo + 1), 3) = 0 THEN 'V'
             ELSE 'A'
             END
         END as marca,
         99999 - ( mod(codigo + 1, 2) + codigo / 2 ) * 100 as hrvisto,
         CASE
             WHEN Mod(codigo + 1,
             2) <> 0 then 1
             ELSE 0
         END as compvenda,
         CASE
            WHEN (codigo + 1) < 50 
               THEN Curdate()
            ELSE 
               Cast( Date_Sub( Current_Timestamp, INTERVAL (1000000 - ( mod(codigo + 1, 2) + codigo / 2 ) * 1000) SECOND) AS Date)
         END AS dtcotacao,
         Left(Cast( Date_Sub( Current_Time(), INTERVAL (codigo + 1 ) MINUTE) as VARCHAR(10)),5) AS hrcotacao
      FROM my_cte
      WHERE codigo < 1000 
   )
   SELECT *
     FROM my_cte;
  
------

/* SQL para criação do indice */
CREATE INDEX idx_marca_data_hora_cotacao ON tbAcoes (marca,dtcotacao,hrcotacao);

Após criar a tabela, o índice e 1000 registros de teste, algumas consultas simples:

EXPLAIN
SELECT * 
  FROM tbacoes
 WHERE marca = ' ' 
   AND dtcotacao = '2021-04-22'
   AND hrcotacao = '05:34'

id|select_type|table  |type|possible_keys|key          |key_len|ref              |rows|Extra                |
--|-----------|-------|----|-------------|-------------|-------|-----------------|----|---------------------|
 1|SIMPLE     |tbacoes|ref |idx_dtcotacao|idx_dtcotacao|20     |const,const,const|1   |Using index condition|
 
 codigo|marca|hrvisto|compvenda|dtcotacao |hrcotacao|
------|-----|-------|---------|----------|---------|
   984|     |  50849|        0|2021-04-22|05:34    |
------

EXPLAIN
SELECT * 
  FROM tbacoes
 WHERE marca = ' ' 
   AND dtcotacao = '2021-04-22'
   AND hrcotacao <= '05:34'

id|select_type|table  |type |possible_keys|key          |key_len|ref|rows|Extra                |
--|-----------|-------|-----|-------------|-------------|-------|---|----|---------------------|
 1|SIMPLE     |tbacoes|range|idx_dtcotacao|idx_dtcotacao|20     |   |9   |Using index condition|
----------------------

codigo|marca|hrvisto|compvenda|dtcotacao |hrcotacao|
------|-----|-------|---------|----------|---------|
  1000|     |  50049|        0|2021-04-22|05:18    |
   998|     |  50149|        0|2021-04-22|05:20    |
   996|     |  50249|        0|2021-04-22|05:22    |
   994|     |  50349|        0|2021-04-22|05:24    |
   992|     |  50449|        0|2021-04-22|05:26    |
   990|     |  50549|        0|2021-04-22|05:28    |
   988|     |  50649|        0|2021-04-22|05:30    |
   986|     |  50749|        0|2021-04-22|05:32    |
   984|     |  50849|        0|2021-04-22|05:34    |
------

EXPLAIN
SELECT * 
  FROM tbacoes
 WHERE marca = ' ' 
   AND dtcotacao = '2021-04-22'
   AND hrcotacao >= '05:34'

id|select_type|table  |type |possible_keys|key          |key_len|ref|rows|Extra                |
--|-----------|-------|-----|-------------|-------------|-------|---|----|---------------------|
 1|SIMPLE     |tbacoes|range|idx_dtcotacao|idx_dtcotacao|20     |   |3   Using index condition|

 codigo|marca|hrvisto|compvenda|dtcotacao |hrcotacao|
------|-----|-------|---------|----------|---------|
   984|     |  50849|        0|2021-04-22|05:34    |
   982|     |  50949|        0|2021-04-22|05:36    |
   980|     |  51049|        0|2021-04-22|05:38    |

Todas as consultas estão usando o índice criado (Using index condition)

Nota: tanto no MySQL quanto no MariaDB, não são aceitas funções na criação de índices, o que é possível, pelo menos no PostgreSQL.

por **alxsts** » 30 Abr 2021 00:35

Olá!

CJP escreveu escreveu:Veja: são consultas diferentes, feitas em momentos diferentes, separadamente. Portanto, não é o caso de fazer uma só consulta para todas as situações. Quando a marca é V, a consulta leva em conta minutos; quando é A, horas; quando está em branco, dias.

Neste caso, uma stored procedure recebendo os parâmetros necessários pode ser o melhor caminho.

CREATE PROCEDURE spFiltrarAcoes (IN cMarca VARCHAR(1),
                                IN dtCotacao DATE, 
                                IN cHrCotacao VARCHAR(5), 
                                IN nIntervalo INT
)

BEGIN
   
   DECLARE dDataInicio DATE;
   DECLARE cHoraInicio VARCHAR(10); 
   DECLARE dDataFinal  DATE;
   DECLARE cHoraFinal  VARCHAR(10);
   DECLARE dTempData   DATETIME;
   DECLARE rs          INT;
   
   IF Coalesce(cMarca,'') = '' THEN
      SET cMarca = ' ';
   END IF;
   
   IF Coalesce(dtCotacao,'') = '' THEN
      SET dtCotacao = Current_Date();
   END IF;
   
   IF Coalesce(chrCotacao,'') = '' THEN
      SET chrCotacao = '00:00';
   END IF;

   IF Coalesce(nIntervalo,0) = 0 THEN
      SET nIntervalo = 1;
   END IF;

   SET dDataInicio = dtCotacao;
   SET cHoraInicio = cHrCotacao;

   /* Tipos de intervalo, conforme a marca: 'V' minutos, 'A' horas, ' ' dias */
   CASE
      WHEN cMarca = 'V' THEN
         SET dTempData = Date_Sub( Current_Timestamp(), INTERVAL nIntervalo MINUTE);
      WHEN cMarca = 'A' THEN
         SET dTempData = Date_Sub( Current_Timestamp(), INTERVAL nIntervalo HOUR);
      WHEN cMarca = ' ' THEN
         SET dTempData = Date_Sub( Current_Timestamp(), INTERVAL nIntervalo DAY);
   END CASE;

   SET dDataFinal = Cast( dTempData AS DATE );
   SET cHoraFinal = Cast( Date_Format(dTempData, '%H:%i') AS VARCHAR(5) );

   SELECT codigo 'Código', 
          marca   'Marca', 
          hrvisto 'Hr.Visto',
          compvenda 'Comp Venda',
          dtcotacao 'Dt.Cotação',
          hrcotacao 'Hr.Cotação'
     FROM tbAcoes a
    WHERE a.marca = cMarca
      AND hrvisto <> 91234
      AND compvenda = 0
      AND a.dtcotacao >= dtCotacao
      AND a.hrcotacao >= cHrCotacao
      AND a.dtcotacao <= dDataFinal
      AND a.hrcotacao <= cHoraFinal
 ORDER BY codigo;
END;

por **cjp** » 30 Abr 2021 01:42

Ainda não deu para analisar estes últimos exemplos, mas estou fazendo alguns testes, com base em tudo que foi falando aqui, tentando simplificar a consulta, mas me deparei com algumas inconsistências.

Exemplo:

Com este select:

select codigo,max(dtcotacao),max(hrcotacao) from acoes where marca='V' and hrvisto<>91234 group by codigo order by max(dtcotacao), max(hrcotacao)

Obtive agora o resultado constante do anexo tela1. Observem que nesse resultado constam 5 registros cuja max(dtcotacao) seria inferior à data atual (30/4).

Entretanto, consultando individualmente cada um desses códigos, todos têm cotações com data de 30/4 (vide prints em anexo).

Será que eu estou fazendo algo errado, ou há algum erro sistêmico?

por **alxsts** » 30 Abr 2021 04:11

Olá!

Tente assim:

  SELECT codigo, dtcotacao, hrcotacao
    FROM tbAcoes 
   WHERE marca='V' 
     AND hrvisto<>91234 
GROUP BY codigo 
ORDER BY dtcotacao DESC, hrcotacao  DESC;

Obs: se você puder formatar (indentar) o teu código antes de postar, vai ajudar bastante, principalmente em códigos mais extensos. Se você usa HeidiSQL, é só selecionar o código e dar um Ctrl + F8 ou menu Editar > Reformatar SQL.

por **alxsts** » 30 Abr 2021 04:21

Olá!

Faça um teste nesta stored procedure que postei anteriormente.
Crie ela no banco e depois vá executando, passando parâmetros variados.

-- Obs.: Onde está o nome da tabela (FROM tbAcoes), troque por FROM acoes, antes de criar a procedure.
Tudo isto no HeidiSQL.

Exemplo de execução:

CALL spFiltrarAcoes('V', '2021-04-30', '00:00' , 30)

por **cjp** » 30 Abr 2021 09:57

Fiz como vc disse, mas acho que não está certo assim. Veja a imagem anexa.

Não conhecia esse recurso de identação do Heidi; vou passar a usá-lo. Obrigado.

por **cjp** » 06 Mai 2021 01:33

Só para dar um retorno já, informo que estou ainda estudando os exemplos e sugestões dadas, mas creio que, só com os índices e as sugestões que já consegui implementar, o problema maior já foi resolvido. Parei de receber relatórios de consultas lentas do provedor.

por **cjp** » 14 Ago 2021 11:09

Pessoal, prosseguindo no tema deste tópico, mudei completamente todo o sistema, simplifiquei a query, criei os índices necessários, mas ainda assim estou frequentemente recebendo alertas de consultas lentas do provedor. Vejam:

Este banco já apresentou um total de 521 consultas lentas ontem.

Seguem abaixo as queries mais lentas por tipo:

Maior tempo de consulta: 00:00:36 (em segundos) gerada pela consulta:

select codigo,max(datahora) as data,nritem from acoes use index (coddt) where marca='V' and hrvisto<>91234 group by codigo order by data

Maior número de registros examinados/lidos: 1255218 (em número de linhas) gerada pela consulta:

select codigo,max(datahora) as data,nritem from acoes use index (coddt) where marca='V' and hrvisto<>91234 group by codigo order by data

Maior número de registros enviados: 1255218 (em número de linhas) gerada pela consulta:

select codigo,max(datahora) as data,nritem from acoes use index (coddt) where marca='V' and hrvisto<>91234 group by codigo order by data

Maior tempo de trava na tabela: 00:00:00 (em segundos) gerada pela consulta:

select codigo,max(datahora) as data,nritem from acoes use index (coddt) where marca='V' and hrvisto<>91234 group by codigo order by data

O que ainda é possível fazer para evitar isso?

por **sergiosouzalima** » 14 Ago 2021 11:32

Olá!

Se você já mudou completamente o sistema, simplificou a query, criou os índices necessários, no meu ponto de vista,
já podemos discutir o que são registros históricos e o que são registros transacionais (usados no dia a dia).

Nesses mais de 1 milhão de linhas da sua tabela (1255218), todos precisam ser considerados novamente?

Não tem como deixar parte dessa contagem já feita, preparada por assunto, por exemplo, por ano, por mês, por hrvisto ou marca?

Eu tentaria deixar tabelas históricas com valores já preparados por algum assunto.

Assim, quando precisar novamente desse max, parte da comparação já estaria preparada.

Só uma ideia.

Abraços.

por **cjp** » 14 Ago 2021 15:00

Realmente vc tem razão, não são todos que precisam ser considerados nesta consulta.
Fiz uma simples modificação na query, acrescendo um "AND datahora>'2021-07-01'" e vi que a consulta já ficou mais rápida.
Vou aprimorar isso e observar se para de dar mensagem de consulta lenta.
Obrigado por enquanto.

por **sergiosouzalima** » 14 Ago 2021 15:18

Olá, cjp.

Fico feliz em poder ajudar um pouco.

Essa discussão sobre o que são registros históricos e o que são registros transacionais é bem interessante.

Separar uma coisa de outra, é o início do que é usado para sistemas BI (Business intelligence) e Data warehouses.

E por serem conceitos, podem ser usados em qualquer tecnologia.

Se quiser mais informações é só entrar em contato.

Depois nos atualize sobre sua solução para deixar registrado aqui. Pode ajudar alguém.

Abraços.

Quem está online