Lentidão na consulta

fabio.aurelio · Janeiro 29, 2017

Olá, pessoal!

Estou gerando uma consulta numa query, mas ela esta demorando uns 50 segundos para retornar os registros na tela.

São em media 8.000 registros, e já criei a paginação, mas mesmo assim, demora este tempo todo. Tentei usar um Inner join, mas não consegui progresso.

Vocês tem alguma ideia de como posso melhorar?

Segue trecho do código responsável pela consulta:

 $configs = mysql_query("SELECT * FROM empresa WHERE id = '1'");
    $config = mysql_fetch_array($configs);
    
    if(isset($_POST["filtrar"])){
        $mes = $_POST["mes"];
        $ano = $_POST["ano"];
        $sit = $_POST["situ"];

        $q_faturas = "SELECT * FROM financeiro WHERE MONTH(vencimento) = '$mes' AND YEAR(vencimento) = '$ano'";

        if($sit != ""){
            $q_faturas .= " AND situacao = '$sit'";
        }else{
            $q_faturas .= " AND (situacao = 'N' OR situacao = 'P')";
        }

        $q_faturas .= " ORDER BY vencimento DESC";
        $consultas = mysql_query($q_faturas);
    }else{
        $consultas = mysql_query("SELECT * FROM financeiro WHERE situacao = 'N' order by vencimento DESC"); /*  Esta consulta faz o select principal pra trazer os dados */
    }
   
    while($campo = mysql_fetch_array($consultas)){ 
    $idc = $campo['cliente'];
    $status = $campo['situacao'];
    $clientes = mysql_query("SELECT * FROM clientes WHERE id = '$idc'");
    $cliente = mysql_fetch_array($clientes);

    $id_boleto  = $campo["boleto"];
    $q_boleto   = "SELECT banco FROM carnes WHERE numero_documento = '$id_boleto'";
    $banco_nome = mysql_query($q_boleto);
    $banco_nome = mysql_fetch_array($banco_nome);
    $banco_nome = $banco_nome["banco"];

    $data_inicial = date('Y-m-d');
    $data_final = $campo['vencimento'];
    $time_inicial = strtotime($data_inicial);
    $time_final = strtotime($data_final);
    $diferenca = $time_final - $time_inicial; 
    $dias = (int)floor( $diferenca / (60 * 60 * 24)); 

    $status = ($status == "N")?"Em aberto":"Pago";

Editado Janeiro 29, 2017 por fabio.aurelio

wmsilva191 · Janeiro 29, 2017

Olá,

Explique o que quer fazer e se possível me informe quais as tabelas você está utilizando e qual a ligação entre elas, dessa forma consigo criar a sql para você com inner join p\ que evite fazer várias consultas;

Para performance busque sobre indices em bancos de dados;

Pode explicar também que calculo é esse que você faz nas datas no PHP, dependendo dá p\ colocar o calculo até na SQL.

fabio.aurelio · Janeiro 30, 2017

4 horas atrás, wmsilva191 disse:

Olá,

Explique o que quer fazer e se possível me informe quais as tabelas você está utilizando e qual a ligação entre elas, dessa forma consigo criar a sql para você com inner join p\ que evite fazer várias consultas;

Para performance busque sobre indices em bancos de dados;

Pode explicar também que calculo é esse que você faz nas datas no PHP, dependendo dá p\ colocar o calculo até na SQL.

Então,

As tabelas são : clientes, financeiro e carnes. Elas não estão interligadas(relacionadas).

O calculo que voce perguntou, verifica (calcula) o número de dias em atraso de um boleto ou número de dias para o próximo vencto.

Meu problema é a demora em trazer os dados para a tela, pois assim que entro nesta tela, ele demora uns 50 segundos para mostrar os dados.

O select principal é esse:

       $consultas = mysql_query("SELECT * FROM financeiro WHERE situacao = 'N' order by vencimento DESC"); /*  Esta consulta faz o select principal pra trazer os dados *

Editado Janeiro 30, 2017 por fabio.aurelio

wmsilva191 · Janeiro 30, 2017

Fábio, bom dia.

O primeiro teste será tirar o order by da select , e divulgue o resultado.

SELECT * FROM financeiro WHERE situacao = 'N'

1 - Esse código é algum produto já em produção, ou é estudo?

Pelo que lí em seu código a tabela financeiro se relaciona com Cliente; bom de qualquer forma, se for possível, coloque o nome das Tabelas e o nome de todas as colunas para eu entender; Se você tiver um print do sistema coloque aqui também; Se tiver do banco de dados ( MER ) , coloque também;

Quantos registros você tem em cada uma dessas tabelas?

2 - Se for para estudo, ou maleável a adaptação, eu vou te ajudando a modelar o banco de dados de forma que não ocorra nenhuma falha na normalização.

Esse código é uma rotina para calcular os valores e datas e salvar no banco de dados novamente?
- Se sim, podemos tentar uma Tarefa do Mysq ; para realizar o calculo de forma automatizada, sem necessidade de PHP, Apenas SQL;
Esse código é uma lista de boletos exibidos na tela do sistema
- Se sim, tente utilizar LIMIT na SQL; para exibir de poucos de uma vez;

fabio.aurelio · Janeiro 30, 2017

2 horas atrás, wmsilva191 disse:
Fábio, bom dia.

O primeiro teste será tirar o order by da select , e divulgue o resultado.
SELECT * FROM financeiro WHERE situacao = 'N'
1 - Esse código é algum produto já em produção, ou é estudo?

Pelo que lí em seu código a tabela financeiro se relaciona com Cliente; bom de qualquer forma, se for possível, coloque o nome das Tabelas e o nome de todas as colunas para eu entender; Se você tiver um print do sistema coloque aqui também; Se tiver do banco de dados ( MER ) , coloque também;

Quantos registros você tem em cada uma dessas tabelas?

2 - Se for para estudo, ou maleável a adaptação, eu vou te ajudando a modelar o banco de dados de forma que não ocorra nenhuma falha na normalização.

Esse código é uma rotina para calcular os valores e datas e salvar no banco de dados novamente?
Se sim, podemos tentar uma Tarefa do Mysq ; para realizar o calculo de forma automatizada, sem necessidade de PHP, Apenas SQL;

Esse código é uma lista de boletos exibidos na tela do sistema
Se sim, tente utilizar LIMIT na SQL; para exibir de poucos de uma vez;

Olá, Bom dia!!

Obrigado pelas dicas e ajuda.

- Tirei o order by do select, mas não influenciou no resultado

- O código esta em produção

- A rotina do calculo de dias que faltam(ou vencidos) não é salvo no banco é calculado na hora e mostrado na tela somente, não há necessidade de salvar no banco

- Os registros já estao sendo paginados quando exibidos(na tela em anexo esta mostrando).

Quando eu uso a clausula LIMIT, ele mostra somente 10 registros na tela(por exemplo), só que quando tento buscar algum cliente, ele não acha, visto que ele só buscou estes "10", entendeu?

Quantidade de registros:

Tabela Carnes: 378
Tabela clientes: 20142
Tabela Financeiro: 8278

Em anexo tela do sistema e tabelas

Editado Janeiro 30, 2017 por fabio.aurelio
Faltou informações

wmsilva191 · Janeiro 30, 2017

Espero ter sido claro na documentação abaixo; em caso de dúvidas pode postar aqui;
Lentidão pode problema de memória RAM que afeta PHP e Mysql; Pode ser o PHP que está realizando recursos desnecessários; É bom avaliar para ver se está tudo em ordens; Pode ser também relacionado a grande quantidade de informações sendo armazenados na tabela (Javascript, jquery, sei lá), o processamento em sí, para isso vamos testar primeiro a SQL;

1. Onde está rodando o Servidor da Aplicação (PHP) e Servidor de Mysql ? ( qual a configuração, CPU , MEMÓRIA ) ? verifique com a aplicação em execução se os recursos estão sobrecarregados;

1.a Precisamos ver se é sua aplicação que está apresentando lentidão ou se é o banco, para isso vamos ignorar o PHP por agora e trabalhar apenas com SQL;

2- Utilize sua ferramenta de preferência, ( MENOS PHPMYADMIN ) eu gosto da HeidiSQL, mas também tem o ToadMysql;

2.a Pelo que ví nas imagens e em seu código eu fiz esse código abaixo, e se possível, teste ele no banco de dados, pode ser em seu ambiente de testes;

2.a.1 - Você teve dificuldades em interpretar o código?

2.a.1 - Se o campo diferenca_dias ficar negativo ( onde precisa ser positivo) inverte o now() e o financeiro.vencimento ;

2.a.2 - Se der erro na execução da SQL confira se os campos estão de forma correta;

2.a.3 - Adicione Filtros 'WHERE' da forma que precisar e teste situações diferentes.

SELECT
financeiro.id,
financeiro.pedido,
financeiro.valor,
   
clientes.nome,
clientes.rgcliente,
clientes.cep,
financeiro.status,

carnes.fatura,
carnes.data_vencimento,
	
	DATEDIFF(financeiro.vencimento,now()) diferenca_dias
FROM financeiro
INNER JOIN clientes ON clientes.id=financeiro.cliente
INNER JOIN carne ON carnes.numero_documento = financeiro.boleto

WHERE
    MONTH(financeiro.vencimento) = '01'
AND YEAR(financeiro.vencimento) = '2017'
AND clientes.nome LIKE '%%' 
LIMIT 0,10

OU esta SQL, caso a de cima apresente problemas devido aos tipos dos campos serem diferentes UTILIZE a SQL abaixo;

3 - OBSERVAÇÕES: O campo carnes.numero_documento é varchar(50) enquanto o financeiro.boleto é varchar(30) ou seja,

3.1 - SE EXISTE carnes.numero_documento COM 50 de tamanho e for quebrado para 30, não quer dizer que seja igual a financeiro.boleto, o que vai mostrar BOLETOS repetidos;

3.2 - Foi preciso também converter o tipo clientes.id que é Inteiro em CHAR(11) que é o mesmo tamanho da tabela financeiro.cliente;




SELECT
   
financeiro.id,
financeiro.pedido,
financeiro.valor,
   
clientes.nome,
clientes.rgcliente,
clientes.cep,
financeiro.status,

carnes.fatura,
carnes.data_vencimento,
	
DATEDIFF(financeiro.vencimento,now()) diferenca_dias
FROM financeiro
INNER JOIN clientes ON cast(clientes.id, CHAR(11))=CAST(financeiro.cliente, CHAR(11))
INNER JOIN carne ON cast(carnes.numero_documento, CHAR(30)) = CAST(financeiro.boleto, CHAR(30))

WHERE
    MONTH(financeiro.vencimento) = '01'
AND YEAR(financeiro.vencimento) = '2017'
AND clientes.nome LIKE '%%' 
LIMIT 0,10

fabio.aurelio · Janeiro 30, 2017

2 horas atrás, wmsilva191 disse:
Espero ter sido claro na documentação abaixo; em caso de dúvidas pode postar aqui;
Lentidão pode problema de memória RAM que afeta PHP e Mysql; Pode ser o PHP que está realizando recursos desnecessários; É bom avaliar para ver se está tudo em ordens; Pode ser também relacionado a grande quantidade de informações sendo armazenados na tabela (Javascript, jquery, sei lá), o processamento em sí, para isso vamos testar primeiro a SQL;

1.    Onde está rodando o Servidor da Aplicação (PHP) e Servidor de Mysql ? ( qual a configuração, CPU , MEMÓRIA ) ? verifique com a aplicação em execução se os recursos estão sobrecarregados;

1.a   Precisamos ver se é sua aplicação que está apresentando lentidão ou se é o banco, para isso vamos ignorar o PHP por agora e trabalhar apenas com SQL;

2-    Utilize sua ferramenta de preferência, ( MENOS PHPMYADMIN ) eu gosto da HeidiSQL, mas também tem o ToadMysql;

2.a Pelo que ví nas imagens e em seu código eu fiz esse código abaixo, e se possível, teste ele no banco de dados, pode ser em seu ambiente de testes;

2.a.1 - Você teve dificuldades em interpretar o código?

2.a.1 - Se o campo diferenca_dias ficar negativo ( onde precisa ser positivo) inverte o now() e o financeiro.vencimento ;

2.a.2 - Se der erro na execução da SQL confira se os campos estão de forma correta;

2.a.3 - Adicione Filtros 'WHERE' da forma que precisar e teste situações diferentes.
SELECT
financeiro.id,
financeiro.pedido,
financeiro.valor,
   
clientes.nome,
clientes.rgcliente,
clientes.cep,
financeiro.status,

carnes.fatura,
carnes.data_vencimento,
	
	DATEDIFF(financeiro.vencimento,now()) diferenca_dias
FROM financeiro
INNER JOIN clientes ON clientes.id=financeiro.cliente
INNER JOIN carne ON carnes.numero_documento = financeiro.boleto

WHERE
    MONTH(financeiro.vencimento) = '01'
AND YEAR(financeiro.vencimento) = '2017'
AND clientes.nome LIKE '%%' 
LIMIT 0,10
OU esta SQL, caso a de cima apresente problemas devido aos tipos dos campos serem diferentes UTILIZE a SQL abaixo;

3 - OBSERVAÇÕES: O campo carnes.numero_documento é varchar(50) enquanto o financeiro.boleto é varchar(30) ou seja,

3.1 - SE EXISTE carnes.numero_documento COM 50 de tamanho e for quebrado para 30, não quer dizer que seja igual a financeiro.boleto, o que vai mostrar BOLETOS repetidos;

3.2 - Foi preciso também converter o tipo clientes.id que é Inteiro em CHAR(11) que é o mesmo tamanho da tabela financeiro.cliente;
SELECT
   
financeiro.id,
financeiro.pedido,
financeiro.valor,
   
clientes.nome,
clientes.rgcliente,
clientes.cep,
financeiro.status,

carnes.fatura,
carnes.data_vencimento,
	
DATEDIFF(financeiro.vencimento,now()) diferenca_dias
FROM financeiro
INNER JOIN clientes ON cast(clientes.id, CHAR(11))=CAST(financeiro.cliente, CHAR(11))
INNER JOIN carne ON cast(carnes.numero_documento, CHAR(30)) = CAST(financeiro.boleto, CHAR(30))

WHERE
    MONTH(financeiro.vencimento) = '01'
AND YEAR(financeiro.vencimento) = '2017'
AND clientes.nome LIKE '%%' 
LIMIT 0,10

Então,

1 . A aplicação esta rodando num server na kinghost. verifiquei o consumo e esta ok.

1.a. Já andei verificando uns logs do mysql, e acusou que precisa ser melhorado a questão das consultas, pois esta havendo uma lentidão por conta disso....(estou te enviando a tela anexo disto)

2. Minha ferramente é o Navicat Premium, me atende bem, muito boa para manipular bancos

2.1 não tenho dificuldades..

2.a 1 - ok

2.a 1- ok

2.a.2 - Deu o erro abaixo(creio que pelas linhass que o join esta lendo)

[Err] 1104 - The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

2.a.3 - ok

3. Ok

3.1 - Ok

3.2 - Ok

Da um olhada nos anexos, ali esta falando claramente que o problema são as consultas/querys

não creio que seja de performance ou quantidade de registros, visto que em outro modulo, em 40.000 registros e não demora nem 5 segundos para trazer o resultado...

Editado Janeiro 30, 2017 por fabio.aurelio

wmsilva191 · Janeiro 30, 2017

As dicas do relatório são criar condições para que o banco de dados indexe os resultados de forma que fique mais rápida a busca; Veja como criar indices;

Agora, eu sinceramente não sei como posso te ajudar, a não ser você fazer consultas nas tabelas, analisar os logs, se der poste eles aqui para ver se consigo em algo ( na imagem ele tem um link que penso que vai para os logs, clica no link e veja se é) ; Teste os campos que você mais usa, veja períodos diferentes;

Agora o certo seria testar e analisar mesmo, mesmo;

Execute as Querys separadamente e veja se há diferenças

 SELECT * FROM financeiro;

SELECT * 
FROM 
	financeiro 
WHERE 
	vencimento BETWEEN '2016-01-01' AND '2016-01-31'

fabio.aurelio · Janeiro 31, 2017

13 horas atrás, wmsilva191 disse:
As dicas do relatório são criar condições para que o banco de dados indexe os resultados de forma que fique mais rápida a busca; Veja como criar indices;

Agora, eu sinceramente não sei como posso te ajudar, a não ser você fazer consultas nas tabelas, analisar os logs, se der poste eles aqui para ver se consigo em algo ( na imagem ele tem um link que penso que vai para os logs, clica no link e veja se é) ; Teste os campos que você mais usa, veja períodos diferentes;

Agora o certo seria testar e analisar mesmo, mesmo;

Execute as Querys separadamente e veja se há diferenças
 SELECT * FROM financeiro;
SELECT * 
FROM 
	financeiro 
WHERE 
	vencimento BETWEEN '2016-01-01' AND '2016-01-31'

Bom dia, tudo beleza?

Passei mais algumas horas vendo isso rs! Então,

Testei seu script no sql direto: deu diferença de milésimos, quando usei direto no no código, piorou o tempo.

Eu analisei os logs que acusa lentidão, aparece que o problema pode ser pelo fato das consultas estar dentro de um loop(while), então,elas estão se repetindo dezenas de vezes..

Eu tentei tirar elas de dentro, mas sempre fica limitado e não mostra nada.

Você tem alguma ideia de como tirar estas querys de dentro do While?

Editado Janeiro 31, 2017 por fabio.aurelio

wmsilva191 · Janeiro 31, 2017

Na sql do Join está ocorrendo a repetição de linhas? Se sim logo após o select coloque o DISTINCT ( ou analise as repetições e corrija os relacionamentos; )

Neste caso a melhor solução é você replicar o banco e aplicação para outro servidor e crie assim um ambiente de testes;

No ambiente de testes você ajusta os campos de data para data, ID de cliente para INT, ( conforme as suas regras ) ; padronize os tipos certos para os campos corretos tipo data em data, tipo INT em códigos sequenciais, (Podem ocorrer erros durante os ajustes, principalmente nos campos de data, pois se você faz a conversão dela em texto é gravado de uma forma já em data é armazenada de outra forma; etc..)

Criação de Índices nas colunas que mais utilizar; Criação de relacionamento entre as tabelas; tudo isso vai aumentar a performance;

Sem o índice, por exemplo, quando você busca o cliente 40000, o banco vai olhar todos os clientes até que ele me encontre; se você precisar consultar várias vezes a mesma informação o banco "vai executar" a busca até encontra o cliente.

O Índice vai otimizar a busca, pois quando você buscar por determinado cliente o banco já vai 'saber' onde procurar, sem precisar passar por todos novamente;

Para retirar do While depende de como sua aplicação está montada, ai é mais arriscado de falar; eu acho que você precisa focar no banco de dados até conseguir obter a consulta de forma rápida; Se está lento na conexão direta com o banco também vai ficar na aplicação ;

O bom de instalar o sistema na sua maquina e testar é que você também verifica se a sua conexão até o servidor também não influencia no resultado;

fabio.aurelio · Janeiro 31, 2017

2 horas atrás, wmsilva191 disse:

Na sql do Join está ocorrendo a repetição de linhas? Se sim logo após o select coloque o DISTINCT ( ou analise as repetições e corrija os relacionamentos; )

Neste caso a melhor solução é você replicar o banco e aplicação para outro servidor e crie assim um ambiente de testes;

No ambiente de testes você ajusta os campos de data para data, ID de cliente para INT, ( conforme as suas regras ) ; padronize os tipos certos para os campos corretos tipo data em data, tipo INT em códigos sequenciais, (Podem ocorrer erros durante os ajustes, principalmente nos campos de data, pois se você faz a conversão dela em texto é gravado de uma forma já em data é armazenada de outra forma; etc..)

Criação de Índices nas colunas que mais utilizar; Criação de relacionamento entre as tabelas; tudo isso vai aumentar a performance;

Sem o índice, por exemplo, quando você busca o cliente 40000, o banco vai olhar todos os clientes até que ele me encontre; se você precisar consultar várias vezes a mesma informação o banco "vai executar" a busca até encontra o cliente.

O Índice vai otimizar a busca, pois quando você buscar por determinado cliente o banco já vai 'saber' onde procurar, sem precisar passar por todos novamente;

Para retirar do While depende de como sua aplicação está montada, ai é mais arriscado de falar; eu acho que você precisa focar no banco de dados até conseguir obter a consulta de forma rápida; Se está lento na conexão direta com o banco também vai ficar na aplicação ;

O bom de instalar o sistema na sua maquina e testar é que você também verifica se a sua conexão até o servidor também não influencia no resultado;

então,

Eu sempre faço backup de tudo que é alteração(banco e script), o ambiente é de produção, mas no fundo ainda estamos nos testes ainda. Quanto a isto sem problemas,

Vou tentar focar nesta questão dos indices, só queria saber como funciona a questão das consultas sem o while, tudo esta concentrado no trecho que enviei la em cima..

De toda forma, muito obrigado pela ajuda !!

wmsilva191 · Janeiro 31, 2017

Para retirar o while, no seu caso, é preciso organizar seu banco antes; fazer relacionamentos com inner join, e apartir dai com uma consulta apenas você faz o que você pretende;

Cada campo precisa estar no que é sua responsabilidade, data é data, numero é int, valores é FLOAT ; DECIMAL, ETC,

Crie relacionamentos entre suas tabelas; automaticamente terá os indices;

Crie os índices nas colunas que precisar fazer filtros;

Por nada, desculpa não poder ter sido efetivo.

Até a próxima.

fabio.aurelio · Fevereiro 1, 2017

13 horas atrás, wmsilva191 disse:

Para retirar o while, no seu caso, é preciso organizar seu banco antes; fazer relacionamentos com inner join, e apartir dai com uma consulta apenas você faz o que você pretende;

Cada campo precisa estar no que é sua responsabilidade, data é data, numero é int, valores é FLOAT ; DECIMAL, ETC,

Crie relacionamentos entre suas tabelas; automaticamente terá os indices;

Crie os índices nas colunas que precisar fazer filtros;

Por nada, desculpa não poder ter sido efetivo.

Até a próxima.

Que isso, voce me ajudou demais!

Vlw mesmo!

Entrar

Lentidão na consulta

Pergunta

Link para o comentário

Compartilhar em outros sites

12 respostass a esta questão

Posts Recomendados

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Link para o comentário

Compartilhar em outros sites

Participe da discussão

Estatísticas dos Fóruns