(Resolvido) Perda de desempenho a cada select no mysql

[michelframos]

Boa noite,

tenho um sistema em Delphi com MySQL, parte do sistema é para gerenciar cadastro de empresas e seus pagamentos, tanto a tabela de empresas quanto a de pagamentos contém índices. Na tela de alteração do cadastro da empresa é possível consultar os pagamentos existentes para aquela empresa por ano e mês.

Conforme o sistema vai sendo utilizado e os pagamentos das empresas consultados na tela de alteração da empresa o desemprenho da consulta (select) cai drasticamente ao ponto de fazer o sistema travar.

Estou usando o Delphi XE7, com o componentes TDFConection, TDFQuery da Paleta FireDAC e o TDataSource e banco de dados MySQL.

[Jhonas]

por favor informe:

versão do seu MYSQL

o tipo da tabela MyISAM ou InnoDB ...etc

seus indices são chaves primarias ou não

as tabelas são relacionadas ou não

total de registros por consulta

mostre a select que esta usando

 

[michelframos]

Versão do MySQL: 5.6.17

Tipo da Tabela:  MyISAM

Estrutura da Tabela:

CREATE TABLE `pagamentos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empresa_id` int(11) NOT NULL,
  `ano` int(11) NOT NULL,
  `mes` int(11) NOT NULL,
  `valor` float(9,2) DEFAULT NULL,
  `data_prevista` date DEFAULT NULL,
  `data_pagamento` date DEFAULT NULL,
  `observacoes` text,
  `situacao` varchar(20) DEFAULT NULL,
  UNIQUE KEY `id` (`id`) USING BTREE,
  KEY `empresa_id` (`empresa_id`) USING BTREE,
  KEY `ano` (`ano`) USING BTREE,
  KEY `mes` (`mes`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=814573 DEFAULT CHARSET=latin1;

O select que estou usando: SELECT * FROM pagamentos WHERE empresa_id = :empresa_id AND ano = :ano

O total de registros dependerá da quantidade de anos que existem para aquela empresa, normalmente 5 anos, então serial 5*12 = 60 registros.

tenho cerca de 4000 empresas cadastradas então o total de registros na tabela de pagamento seriam em torno de 4000 * 5 * 12 = 240.000 registros.

[Jhonas]

o desemprenho da consulta (select) cai drasticamente ao ponto de fazer o sistema travar.

se voce estiver trabalhando em rede, o fluxo de registros trafegados na rede pode fazer isso acontecer.

para tabelas acima de 230.000 registros o ideal é usar apenas uma chave de indice...

na select voce pode limitar a quantidade de registros que vai ser mostrada

por exemplo: mostrar para o usuario apenas os 100 primeiros registros

SELECT * FROM pagamentos WHERE empresa_id = :empresa_id AND ano = :ano  Limit 100

em casos onde o processo vai ficando muito demorado, voce pode usar Threads, ou seja, voce pode deixar a select ser executada em segundo plano, sem travar o sistema

https://simasware.wordpress.com/2014/01/06/criando-threads-com-delphi/

outra forma de acelerar a pesquisa seria usar o indice dentro da propria select

Select COD_PRODUTO from PRODUTOS USE INDEX(CODPRODX)

abraço

 

 

[michelframos]

Mesmo rodando com banco de dados local o problema ocorre, tentei usar o índice direto no select como você sugeriu, mas sem sucesso, ainda vou tentar usar threads.

Seguem duas imagens para melhor explicar o funcionamento do sistema.

A imagem 'AlteraEmpresa' é a tela para alteração dos dados da empresa que contem uma aba para consultar as mensalidades, quando esta tela é mostrada os pagamento não o são imediatamente, somente quando a aba 'Mensalidades' é selecionada, daí então o select ' SELECT * FROM pagamentos WHERE empresa_id = :empresa_id AND ano = :ano' é disparado.

A segunda imagem é a aba de Mensalidades, que contem um combobox com os anos cadastrados para aquela empresa, toda vez que um ano é selecionado o mesmo select mencionado acima é disparado novamente.

Obs: cada vez que a tela de alteração é chamada o componente Query que está desativado é ativado, e quando a tela é fechada o componente torna a ser desativado. O mesmo acontece a cada consulta de pagamento na aba de Mensalidade.

Para a busca das mensalidade por ano estou usando a seguinte rotina:

procedure TF_AlteraEmpresa.buscaMensalidades;
begin

  DM.QManipulaPagamentos.Active := False;
  totalPago := 0;
  totalPendencias := 0;

  with dm.QManipulaPagamentos do
  begin

    Close;
    sql.Clear;
    SQL.Add('SELECT * FROM pagamentos USE INDEX(empresa_id) WHERE empresa_id = :empresa_id AND ano = :ano LIMIT 12');
    ParamByName('ano').Value := cbAnoAberto.Text;
    ParamByName('empresa_id').Value := id;
    Open();

    First;
    while not eof do
    begin

      if FieldByName('situacao').AsString = 'Quitado' then
      begin
        totalPago := totalPago + FieldByName('valor').AsFloat;
      end
      else if FieldByName('situacao').AsString = 'Pendente' then
      begin
        totalPendencias := totalPendencias + FieldByName('valor').AsFloat;
      end;
      Next;
    end;

    DM.QManipulaPagamentos.Active := True;
    LBTotalPago.Caption := FormatFloat('R$ , 0.00', totalPago);
    LBTotalPendente.Caption := FormatFloat('R$ , 0.00', totalPendencias);
    DBGPagamentos.AutoSizeRows(False, 1);
  end;

end;

[Jhonas]

veja que voce esta usando este form ( TF_AlteraEmpresa  )  mas esta chamando o Data Modulo ( DM.QManipulaPagamentos )  ....  algum tempo atrás eu tambem usava esse componente, mas parei de usar justamente por deixar lento o processamento de algumas rotinas.

de preferencia coloque os componentes para a sua tabela no mesmo form em que elas serão usadas.... além de facilitar a manutençao, deixa mas rapido o acesso as tabelas.

o seu data modulo não deveria ser desativado

DM.QManipulaPagamentos.Active := False;

experimente tambem trocar o While pelo For

tenho uma rotina muito parecida com essa que voce esta usando .... o acesso para 12 registros é menor que 1 segundo

abraço

 

[michelframos]

Obrigado pela atenção, têm sido bastante útil.

Mesmo chamando a mesma tabela em diversas telas diferentes é melhor usar os componentes para isso no próprio form ao invés de num Data Modulo ? Removo completamente o Data Modulo ou deixo apenas os componentes de conexão nele ?

[Jhonas]

se quiser usar o meu conselho, eu tenho os componentes de conexão com o MYSQL no meu Form Principal ( Menu )

os componentes de acesso as tabelas, eu coloco em cada form que eu vou utilizar essas tabelas

mesmo que eu vá usar outros forms, e tenha que usar as mesmas tabelas, coloco para cada uma delas os mesmos componentes ( como eu disse facilita a manutenção e fica restrito à aquele form ... deixei de usar o Data Modulo faz tempo... não compensa )

as minhas pesquisas com select  em arquivos com 300.000 registros é de no maximo 2 segundos

abraço

[michelframos]

Jhonas, vou experimentar seu conselho e observar se haverá ganho de performance e depois posto aqui o resultado.

Desde já muito obrigado pela atenção e orientações.

[Jhonas]

ok... qualquer outra coisa, posta ai ...

abraç

[michelframos]

Olá Jhonas, fiz os testes conforme suas orientações.

Ficou inacreditavelmente mais rápido!

Muito obrigado, salvou um projeto de vários meses.

[Jhonas]

ok... precisando tirar dúvidas é só postar ...

abraço