Ir para conteúdo

POWERED BY:

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

AnthraxisBR

Consultas no MySQL com PHP demoram demais

Recommended Posts

Não estou com um problema em si, mas estou querendo melhorar o desempenho, no seguinte exemplo, eu faço a consulta na tabela empresas, e com base nas empresas, eu consulto quantos parcelamentos essa empresa possui, mas, além disso, além de contar quantos parcelamentos a empresa possui, eu preciso contar o total de parcelas, então são três camadas de tabelas:

Lista empresas -

 

Empresa -> Parcelamentos -> Parcelas por parcelamento

 

OBS: a mesma empresa pode ter vários parcelamentos.

OBS 2: são quantidades enormes de parcelas, e as parcelas não tem dados fixos, então cada parcela é uma linha no banco de dados.

 

Com o código abaixo, eu faço isso descrito acima (funciona):

 

<?php
            $sql = "SELECT id,cod,empresa_rel,qntd_parc FROM control_parc_fiscal";
            $parcs = Components::get_rs_array_stmt_PDO($sql);
            $emps_cod = array_column($parcs, 'empresa_rel');
            $SQL_emp_cod = implode("','", $emps_cod);
            if ($nivel <= 3) {
                $sql_2 = "SELECT id,cod,nome,cnpj,insc_estadual,tributacao FROM empresas_base WHERE cod IN ('$SQL_emp_cod') AND responsavel = '$user' GROUP BY id ORDER BY id";
            } else {
                $sql_2 = "SELECT id,cod,nome,cnpj,insc_estadual,tributacao FROM empresas_base WHERE cod IN ('$SQL_emp_cod')";
            }
            $emps = Components::get_rs_array_stmt_PDO($sql_2);
            $countParcs = Components::get_count_reg_mysql_PDO($sql_2);
            $i = 0;
            foreach ($emps as $emps_loop) {
                $emps_data[$i]['id'] = $emps_loop['id'];
                $emps_data[$i]['cod'] = $emps_loop['cod'];
                $emps_data[$i]['nome'] = $emps_loop['nome'];
                $emps_data[$i]['cnpj'] = $emps_loop['cnpj'];
                $emps_data[$i]['insc_estadual'] = $emps_loop['insc_estadual'];
                $b = 0;
                foreach ($parcs as $parc) {
                    $cod[$b] = $parc['empresa_rel'];
                    $control_cod[$b] = $parc['cod'];
                    if ($emps_data[$i]['cod'] == $cod[$b]) {
                        $a = 0;
                        while ($a <= $b) {
                            if ($emps_data[$i]['cod'] == $cod[$b]) {


                                $emps_data[$i]['parcs'][$b] = Array(
                                    "parc_cod" => $parc['cod'],
                                    "qntd_parc" => $parc['qntd_parc'],
                                    "count_venc" => $countVencParcs
                                );
                            } else {
                                $emps_data[$i]['parcs'] = NULL;
                            }
                            $a++;
                        }
                        $b++;
                    }
                }
                //echo $countVencParcs;
                $i++;
            }

            for ($p = 0; $p < count($emps); $p++) {
                $empresaID = $emps_data[$p]['id'];
                $empresaCod = $emps_data[$p]['cod'];
                $empresa = utf8_encode($emps_data[$p]['nome']);
                $cnpj = $emps_data[$p]['cnpj'];
                $insc_est = $emps_data[$p]['insc_estadual'];
                $grupo_easy = $emps_data[$p]['grupo_easy'];
                $regime_trib = $emps_data[$p]['tributacao'];

                $countParcs = count($emps_data[$p]['parcs']);
               //O que está fazendo a consulta ficar lenta é apenas está função
                $qntdVencidas = fiscalModel::get_count_parc_venc_parcelamento_fiscal_por_emp($empresaCod);
                if ($qntdVencidas['qntd_parcs_venc'] > 0) {
                    $tr_classes = 'danger';
                    $vnctMsg = " - Esta empresa possui " . $qntdVencidas['qntd_parcs_venc'] . " parcela(s) vencida(s)";
                } else{
                    $vnctMsg = "";
                    $tr_classes = 'default';
                }
                ?>
            <tr class="<?php echo $tr_classes ?>">
                    <td>
                        <a href="#" class='select_emp_parcelamento_fiscal' data-empresa="<?php echo $empresaCod ?>"><i class="fa fa-plus-circle"></i></a>
                    </td>
                    <td class='td_empresa'>
                        <?php
                        echo $empresa;
                        echo $vnctMsg;
                        ?>
                    </td>
                    <td>
                        <?php echo $cnpj ?>
                    </td>
                    <td>
                        <?php echo $insc_est ?>
                    </td>
                    <td>
                        <?php
                        echo $countParcs;
                        ?>
                    </td>
                </tr>
                <?php
            }
            ?>

Esta linha :

 

Citar

$qntdVencidas = fiscalModel::get_count_parc_venc_parcelamento_fiscal_por_emp($empresaCod);

 

É responsável por contar as parcelas vencidas, ela faz uma consulta, em média 5 vezes por empresa, em cerca de 60 campos, para cada uma das 311 empresas cadastradas até o momento, ou seja, são 90.000 registros.

 

Esse código abaixo, é o código executado por essa função

<?php
public static function get_count_parc_venc_parcelamento_fiscal_por_emp($emp) {

        $query = "SELECT control_rel,data_venc FROM control_parc_fiscal_parcelas WHERE data_pgto = ''";
        $rs = Components::get_rs_consulta_query($query);
        $countVencParcs = 0;
        $i = 0;
        $hoje = date('Y-m-d');
        while ($row = mysqli_fetch_array($rs)) {

            $control_rel = $row['control_rel'];
            $vnct = $row['data_venc'];
            $data_inicial = Components::exec_format_date($vnct, 'd/m/Y', 'Y-m-d');
            $verif_venc = Components::calc_diff_date_f($data_inicial, $hoje);
            $sql_2 = "SELECT cod,empresa_rel FROM control_parc_fiscal WHERE cod = '$control_rel' AND empresa_rel = '$emp'";
            $rs_2 = Components::get_rs_consulta_query($sql_2);
            $row_2 = mysqli_fetch_array($rs_2);
            $empresa = $row_2['empresa_rel'];
            $cod = $row_2['cod'];
            if ($control_rel == $cod) {
                if ($verif_venc > 0) {
                    $countVencParcs += 1;
                } else {
                    $countVencParcs += 0;
                }
            } else {
                
            }
            $i++;
        }

        $return = Array(
            "empresa" => $empresa,
            "qntd_parcs_venc" => $countVencParcs
        );

        return $return;
    }
?>

 

Não tenho ideia de como agilizar isso, se alguém tiver uma luz do que eu deva pesquisar, sabendo que esse é o famoso problema do 'n+1', mas não consigo achar outra solução, ou seria realmente viável armazenar todos esses dados em um array ?

 

Obrigado

Compartilhar este post


Link para o post
Compartilhar em outros sites

Chegou a dar uma lida nisso?

http://rberaldo.com.br/o-problema-do-n-mais-1/

 

Você poderia armazenar todos as chaves q vai usar e efectuar apenas uma carga.

Atacar o problema com VIEWS para abstrair consultas complexas.

 

As vezes pode ser índice na tabela tmb (estamos falando de qnt registros?)

 

 

 

Compartilhar este post


Link para o post
Compartilhar em outros sites
Agora, gabrieldarezzo disse:

Chegou a dar uma lida nisso?

http://rberaldo.com.br/o-problema-do-n-mais-1/

 

Você poderia armazenar todos as chaves q vai usar e efectuar apenas uma carga.

Atacar o problema com VIEWS para abstrair consultas complexas.

 

As vezes pode ser índice na tabela tmb (estamos falando de qnt registros?)

 

 

 

 

Sim sim, pode ver que a primeira parte das consultas está livre desse problema.

 

Estamos falando de 90.000 registros agora, mas a quantidade é crescente e em larga escala.

 

No caso, seria viável eu criar mais um indice de array pras parcelas ?

 

Seria algo tipo


 

<?php

$array[0] = Array(

    'empresa' => 'empresa 1',
	'parcelamentos da empresa 1' => Array(
        [0] => Array(
    		'parcelamento 1' => 'Parcelamento 1 da empresa 1',
      			'parcelas' => Array(
        		[0] => Array(
                  'parcela' => 'parcela 1, do parcelamento 1, da empresa 1',  
                  )
                [1] => Array(
                  'parcela' => 'parcela 2, do parcelamento 1, da empresa 1',  
                  )
                )	
    		),
       [1] => Array(
    		'parcelamento 2' => 'Parcelamento 2 da empresa 1',
      			'parcelas' => Array(
        		        [0] => Array(
                  			'parcela' => 'parcela 1, do parcelamento 2, da empresa 1',  
                  		)
                		[1] => Array(
                  			'parcela' => 'parcela 2, do parcelamento 2, da empresa 1',  
                  		)
        		 )	
    		)
      )	
)
?>

Mas não teria perigo de crashar o navegador com tantos dados ?, estou tentando de tudo

Compartilhar este post


Link para o post
Compartilhar em outros sites

O navegador provavelmente vai travar se tentar exibir os 90k de 1 vez.

 

Eu fui seco no titulo 'mysql lento'

Mas a lentidão não está no servidor e sim na renderização?

 

Ou realmente oq está lento é o navegador?

Nesse caso você pode tentar abordagens como paginação de resultados.

 

Outro fator, qnd citei indice seria indice do MYSQL:

http://www.linhadecodigo.com.br/artigo/3620/indices-mysql-otimizacao-de-consultas.aspx

https://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html

 

 

 

Compartilhar este post


Link para o post
Compartilhar em outros sites
16 minutos atrás, gabrieldarezzo disse:

O navegador provavelmente vai travar se tentar exibir os 90k de 1 vez.

 

Eu fui seco no titulo 'mysql lento'

Mas a lentidão não está no servidor e sim na renderização?

 

Ou realmente oq está lento é o navegador?

Nesse caso você pode tentar abordagens como paginação de resultados.

 

Outro fator, qnd citei indice seria indice do MYSQL:

http://www.linhadecodigo.com.br/artigo/3620/indices-mysql-otimizacao-de-consultas.aspx

https://dev.mysql.com/doc/refman/5.5/en/mysql-indexes.html

 

 

 

 

A lentidão está sim no servidor, ele está em uma função especifica, que precisa contar quantas parcelas estão vencidas, só pra saber quais estão vencidas, estou passando pelos 90.000 registros em todas as vezes que eu passo por um parcelamento, e quando está vencida, somo +1 e acrescento ao parcelamento relacionado.

 

No caso, é essa função:

 

<?php
public static function get_count_parc_venc_parcelamento_fiscal_por_emp($emp) {

        $query = "SELECT control_rel,data_venc FROM control_parc_fiscal_parcelas WHERE data_pgto = ''";
        $rs = Components::get_rs_consulta_query($query);
        $countVencParcs = 0;
        $i = 0;
        $hoje = date('Y-m-d');
        while ($row = mysqli_fetch_array($rs)) {

            $control_rel = $row['control_rel'];
            $vnct = $row['data_venc'];
            $data_inicial = Components::exec_format_date($vnct, 'd/m/Y', 'Y-m-d');
            $verif_venc = Components::calc_diff_date_f($data_inicial, $hoje);
            $sql_2 = "SELECT cod,empresa_rel FROM control_parc_fiscal WHERE cod = '$control_rel' AND empresa_rel = '$emp'";
            $rs_2 = Components::get_rs_consulta_query($sql_2);
            $row_2 = mysqli_fetch_array($rs_2);
            $empresa = $row_2['empresa_rel'];
            $cod = $row_2['cod'];
            if ($control_rel == $cod) {
                if ($verif_venc > 0) {
                    $countVencParcs += 1;
                } else {
                    $countVencParcs += 0;
                }
            } else {
                
            }
            $i++;
        }

        $return = Array(
            "empresa" => $empresa,
            "qntd_parcs_venc" => $countVencParcs
        );

        return $return;
    }
?>

Essa função está dentro do problema do 'N + 1' porque ela é chamada dentro do loop dos parcelamentos da array, ou seja, ela é chamada uma quantidade gigantesca de vezes, e essa função que é executada 90.000 vezes fazendo a consulta ficar lenta (e cada dia aumenta mais a quantidade de registros pois tem gente utilizando o sistema).

 

Com essa função o tempo de resposta é 12347ms.

Sem essa função o tempo de resposta é 32ms.

 

Mas eu preciso do famigerado dado que ela me traz.

 

Obs: As 3 tabelas relacionadas estão indexadas, mudar de banco de dados seria uma solução plausível, sabendo que provavelmente ao longo o tempo boa parte do sistema pode ir entrando nesse mesmo problema?

Ou o MySQL com PHP comportam, desde que programados corretamente, uma quantidade massiva de dados e consultas gigantescas (mantendo um bom desempenho)?

 

PS:

 

@gabrieldarezzoValeu por responder quase todas as minhas perguntas kkkk

 

 

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

Bom olhando com calma

Independente de pensar em N+1 ou Joins 

 

Tem coisas redundantes no seu código sem a necessidade.

 

Ex, a função get_count_parc_venc_parcelamento_fiscal_por_emp():

 

Atualmente ela executa isso aqui:

$query = "SELECT control_rel,data_venc FROM control_parc_fiscal_parcelas WHERE data_pgto = ''";

Sendo que os dados nunca se alteram.


Você poderia executar eles fora dos loops e apenas enviar o resultado dela como parâmetro, algo +/- assim:

public static function get_count_parc_venc_parcelamento_fiscal_por_emp($emp, $control_parc_fiscal_parcelas) {
    
    ...
    $hoje = date('Y-m-d');
    foreach($control_parc_fiscal_parcelas as $row){
      .....
 

Ps: Nomear as variáveis ajuda você e terceiros e você mesmo a ter um código mais simples....

Ex: oq significa $row? É a linha... ok mas linha do que? preferia algo como:

foreach($parcelas as $parcela)

 

 

 

Falando em Joins você sabe o conceito?
Olhei por cima... mas acho q da pra fazer quase tudo com Joins ;)

https://www.w3schools.com/sql/sql_join_inner.asp

 

Posta a estrutura das tabelas se tiver tentando e estiver com dificuldade.

 

Compartilhar este post


Link para o post
Compartilhar em outros sites
18 minutos atrás, gabrieldarezzo disse:

Bom olhando com calma

Independente de pensar em N+1 ou Joins 

 

Tem coisas redundantes no seu código sem a necessidade.

 

Ex, a função get_count_parc_venc_parcelamento_fiscal_por_emp():

 

Atualmente ela executa isso aqui:


$query = "SELECT control_rel,data_venc FROM control_parc_fiscal_parcelas WHERE data_pgto = ''";

Sendo que os dados nunca se alteram.


Você poderia executar eles fora dos loops e apenas enviar o resultado dela como parâmetro, algo +/- assim:


public static function get_count_parc_venc_parcelamento_fiscal_por_emp($emp, $control_parc_fiscal_parcelas) {
    
    ...
    $hoje = date('Y-m-d');
    foreach($control_parc_fiscal_parcelas as $row){
      .....
 

Ps: Nomear as variáveis ajuda 3° e você mesmo se enteder... oq significa $row é a linha... ok mas linha do que?

 

Falando em Joins você sabe o conceito, to com a cabeça meia quente... mas acho q da pra fazer quase tudo com Joins ;)

https://www.w3schools.com/sql/sql_join_inner.asp

 

Irmão, na hora que você respondeu, eu consegui resolver, usando IN, e associando arrays, o problema estava em que:

 

Onde chamava a função, era na view do sistema, ou seja, ele chama um vez pra cada linha que era printada na tela, bom, isso não existe mais, e essa função não existe mais, o que eu fiz foi transformar a quantidade de parcelas vencidas em um atributo do parcelamento, como isso? (achei muito burro da minha parte não ter pensado nisso antes)

 

<?php
//Esse dado ja vinha do banco em outra consulta
$parcs_cod = array_column($parcs,'cod');
$SQL_parc_cod = implode("','",$parcs_cod);
$hoje = date('Y-m-d');
$sql_3 = "SELECT control_rel,data_venc FROM control_parc_fiscal_parcelas WHERE control_rel IN ("$SQL_parc_cod") AND data_venc < '$hoje'";

$parcs_arr = Components::get_rs_array_stmt_PDO($sql_3);

Isso foi incluso na função que prepara a array com os dados dos parcelamentos, ai dentro dessa mesa função, no momento em que eu abro a array com parcelamento e associo os parcelamentos a uma empresa, eu crio essa outra comparação:

<?php
$f = 0;
foreach($parcs_arr as $parc_diss){
  	//Isso foi trazido pela consulta da query '$sql_3'
	$control_vnct = $parc_diss['control_rel'];
  	//Se o codigo do controle trazida da query '$sql_3' for igual, ao controle que ja está sendo tratado
  	if($ctrl_vnct == $control_cod[$b]){
     	$f += 1; 
    }else{
      $f += 0;
    }
}

Depois disso apenas relaciono o valor de $f para cada relacionamento, e na view, onde eu exibo os dados, somo a quantidade de parcelas vencidas pra cada parcelamento, e pra cada parcelamento somo para as empresas.

 

A consulta reduziu de incríveis mais de 12 segundos, pra 40/60ms. 

 

Realmente...

 

Novamente obrigado @gabrieldarezzo

Compartilhar este post


Link para o post
Compartilhar em outros sites

Boaaa!!!!
Mandou muito.

 

Pra fechar com chave de ouro 
Da uma lida sobre LEFT/INNER joins, as vezes simplifica ainda mais o seu código e não esquece de nomear as variáveis de maneira auto explicativas.

 

Abraços

 

Compartilhar este post


Link para o post
Compartilhar em outros sites

  • Conteúdo Similar

    • Por violin101
      Caros amigos, saudações.
       
      Por favor, me permita tirar uma dúvida com os amigos.

      Tenho um Formulário onde o Usuário digita todos os Dados necessários.

      Minha dúvida:
      --> como faço após o usuário digitar os dados e salvar, o Sistema chamar uma Modal ou mensagem perguntando se deseja imprimir agora ?

      Grato,
       
      Cesar
    • Por Carcleo
      Tenho uma abela de usuarios e uma tabela de administradores e clientes.
      Gostaria de uma ajuda para implementar um cadastro
       
      users -> name, login, passord (pronta) admins -> user_id, registratiom, etc.. client -> user_id, registratiom, etc...
      Queria ajuda para extender de user as classes Admin e Client
      Olhem como estáAdmin
      <?php namespace App\Models; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class Admin extends User {     use HasFactory;            protected $fillable = [         'name',         'email',         'password',         'registration'     ];      private string $registration;     public function create(         string $name,          string $email,          string $password,         string $registration     )     {         //parent::create(['name'=>$name, 'email'=>$email, 'password'=>$password]);         parent::$name = $name;         parent::$email = $email;         parent::$password = $password;         $this->registration = $registration;     } } User
      <?php namespace App\Models; // use Illuminate\Contracts\Auth\MustVerifyEmail; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Foundation\Auth\User as Authenticatable; use Illuminate\Notifications\Notifiable; use Illuminate\Database\Eloquent\Relations\BelongsToMany; class User extends Authenticatable {     /** @use HasFactory<\Database\Factories\UserFactory> */     use HasFactory, Notifiable;     static string $name;     static string $email;     static string $password;     /**      * The attributes that are mass assignable.      *      * @var list<string>      */     protected $fillable = [         'name',         'email',         'password',     ];          /**      * The attributes that should be hidden for serialization.      *      * @var list<string>      */     protected $hidden = [         'remember_token',     ];     /**      * Get the attributes that should be cast.      *      * @return array<string, string>      */     protected function casts(): array     {         return [             'email_verified_at' => 'datetime',             'password' => 'hashed',         ];     }          public function roles() : BelongsToMany {         return $this->belongsToMany(Role::class);     }       public function hasHole(Array $roleName): bool     {                 foreach ($this->roles as $role) {             if ($role->name === $roleName) {                 return true;             }         }         return false;     }         public function hasHoles(Array $rolesName): bool     {                 foreach ($this->roles as $role) {             foreach ($rolesName as $rolee) {             if ($role->name === $rolee) {                 return true;             }          }         }         return false;     }         public function hasAbility(string $ability): bool     {         foreach ($this->roles as $role) {             if ($role->abilities->contains('name', $ability)) {                 return true;             }         }         return false;     }     } Como gravar um Admin na tabela admins sendo que ele é um User por extensão?
      Tentei assim mas é claro que está errado...
      public function store(Request $request, Admin $adminModel) {         $dados = $request->validate([             "name" => "required",             "email" => "required|email",             "password" => "required",             "registration" => "required"         ]);         $dados["password"] =  Hash::make($dados["password"]);                  $admin = Admin::where("registration",  $dados["registration"])->first();                  if ($admin)              return                    redirect()->route("admin.new")                             ->withErrors([                                 'fail' => 'Administrador já cadastrados<br>, favor verificar!'                   ]);                            $newAdmin = $adminModel->create(                                    $dados['name'],                                    $dados['email'],                                    $dados['password'],                                    $dados['registration']                                 );         dd($newAdmin);         $adminModel->save();         //$adminModel::create($admin);                  return redirect()->route("admin.new")->with("success",'Cadastrado com sucesso');     }  
    • Por violin101
      Caros amigos, saudações.
       
      Gostaria de tirar uma dúvida com os amigos, referente a PDV.
       
      Estou escrevendo um Sistema com Ponto de Vendas, a minha dúvida é o seguinte, referente ao procedimento mais correto.

      Conforme o caixa vai efetuando a venda, o Sistema de PDV já realiza:
      a baixa direto dos produtos no estoque
      ou
      somente após concretizar a venda o sistema baixa os produtos do estoque ?
       
      Grato,
       
      Cesar
       
    • Por violin101
      Caros amigos do grupo, saudações e um feliz 2025.
       
      Estou com uma pequena dúvida referente a Teclas de Atalho.

      Quando o Caps Lock está ativado o Comando da Tecla de Atalho não funciona.
      ou seja:
      se estiver para letra minúscula ====> funciona
      se estiver para letra maiúscula ====> não funciona
       
      Como consigo evitar essa falha, tanto para Letra Maiúscula quanto Minúscula ?

      o Código está assim:
      document.addEventListener( 'keydown', evt => { if (!evt.ctrlKey || evt.key !== 'r' ) return;// Não é Ctrl+r, portanto interrompemos o script evt.preventDefault(); });  
      Grato,
       
      Cesar
    • Por ILR master
      Fala galera, tudo certo?
       
      Seguinte: No servidor A estou tentando fazer uma consulta com o servidor B, mas está dando erro.
      Estou usando o mesmo código de conexão do servidor B que funciona perfeitamente, mas no servidor A, dá erro.
      Segue código:
       
      $host = 'servidor B';
      $user = 'user';
      $pass = '********';
      $db   = 'banco';
       
      // conexão e seleção do banco de dados
      $conexao = mysqlI_connect($host, $user, $pass, $db);
      mysqlI_set_charset($conexao,"utf8");
      //print "Conexão rodando e OK!"; 
      //mysqlI_close($conexao);
       
      Alguém pode me ajudar?
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.