Consulta de mais de 100.000 Linhas em tabela MySql usando PHP.

[Yan Gabriel 2]

Olá!

 

Minha tabela possui mais de 100 mil registros e preciso capturar todos para a geração de um arquivo do tipo xlsx.

O esquema atual consiste em capturar esses dados e armazená-los em um único array de objetos para poupar processamento enquanto a conexão está aberta.

Após o fechamento da conexão, eu trato essas informações separando-as em outros arrays de objetos para gerar as estatísticas e colocá-las no arquivo xlsx.

 

Observações:

1. Esse primeiro array de objetos armazena 16 campos da tabela para cada objeto.
2. A velocidade de captura desses dados está em torno de 40 segundos(o que não é tão ruim pois a intensão é de processar esses dados durante a madrugada com o uso do crontab e do wget e disponibilizar os arquivos pela manhã).
3. O grande problema está justamente no tratamento desse array porque preciso de vários laços para gerar as informações.
4. Já estou utilizando a função: set_time_limit(0) do PHP para tornar o processamento possível.
5. Por enquanto não estou usando a biblioteca PHPExcel, assim como nenhuma outra, porque quero testar o desempenho sem elas, tratar as informações que irão para o arquivo e... para não perder tempo à toa tendo um código maior para verificar.

 

Como faço para aumentar o desempenho do processamento?

Na última vez deixei processando por mais de 4 horas e disponibilizei 1GB de memória através do init_set('memory_limit', '1G') do PHP. E adivinhem? O processamento não ficou completo.

[Beraldo 864]

Jogar tudo pra memória pode ser o principal fator da lentidão. Se você puder processar as informações dentro do loop que busca os resultados, o uso de memória será bem menor.

 

Falando em código, em vez disto:

 

$dados = [];
// consulta
while ( fetch() )
{
    $dados[] = $dado_corrente;
}
 
// aqui $dados possui todos os registros

 

faça algo assim

 

while ( fetch() )
{
    // efetua os cálculos com o $dado_corrente;
}

 

Nesse segundo caso, não existiria $dados e todos os cálculos seriam feitos em um único loop, com menor uso de memória

 

A conexão ficará aberta por mais tempo, porém o uso de memória tende a ser bem menor que gerar um array com todos os dados

[Yan Gabriel 2]

Jogar tudo pra memória pode ser o principal fator da lentidão. Se você puder processar as informações dentro do loop que busca os resultados, o uso de memória será bem menor.

 

Falando em código, em vez disto:

$dados = [];
// consulta
while ( fetch() )
{
    $dados[] = $dado_corrente;
}
 
// aqui $dados possui todos os registros

faça algo assim

while ( fetch() )
{
    // efetua os cálculos com o $dado_corrente;
}

Nesse segundo caso, não existiria $dados e todos os cálculos seriam feitos em um único loop, com menor uso de memória

 

A conexão ficará aberta por mais tempo, porém o uso de memória tende a ser bem menor que gerar um array com todos os dados

 

Valeu! Mas isso não faria o banco de dados quebrar/interromper a conexão por causa do tempo?

[Beraldo 864]

Valeu! Mas isso não faria o banco de dados quebrar/interromper a conexão por causa do tempo?

É possível alterar o timeout do MySQL. Não lembro de cabeça qual é a diretiva, mas dá pra mudar, sim.

[Yan Gabriel 2]

Solução:

 

Resolvi o problema usando a linguagem C++ para manipulação dos dados afim de gerar os arquivos .xlsx com as estatísticas pois essa tarefa é muito massante para o PHP que não foi feito para isso(só serve para manipulações em pequenas escalas, como por exemplo, exibir relatórios em uma painel de controle conforme for sendo solicitado com o auxílio ajax ao invés de tudo de uma vez só).

 

Percebam que se você chegar ao ponto de utilizar funções como set_time_limit, init_set('memory_limit', '1G') e entre outras é porque seu script(PHP, SQL ou até mesmo Javascript) não está bem formulado ou a linguagem não é a ideal para tal tarefa como no meu caso. Utilizá-las só vai atrasar sua dor de cabeça que com o tempo se tornará pior.

 

 

 

Por quê?

 

Lembrem-se que o meu caso é diferente do painel com relatórios citado acima. Para gerar o arquivo é necessário todas, repetindo, TODAS, as informações que correspondem a minha pesquisa SQL porque é assim que se gera estatística. Necessitamos de todos os dados disponíveis para conceder algo útil. O C++ é perfeito porque posso gerenciar a memória assim como outros recursos.

 

 

Agradeço a ajuda!