Slow datatable when loading many results

Question

I have a database with about 500 records with system orders, however I need to display these records in Datatable for better organization and research.

Loading the page takes too long to display the data in the Datatable.

PHP:

 <div class="table-responsive">
     <table id="pedidosTable" class="table table-striped table-bordered display">
     <thead>
        <tr> 
          <th>Nº</th> 
          <th>Nome do Cliente</th> 
          <th class="d-none">Telefone </th> 
          <th class="d-none">Email</th> 
          <th>D. Inicial</th> 
          <th>D. atuali.</th> 
          <th>Situação</th> 
          <th>Responsavel</th>
          <th>Valor</th> 
          </tr>
          </thead>
          <tbody>
          <?php  while($rowsPedidos = mysqli_fetch_assoc($queryPedidos)){ 
               $grade = $rowsPedidos['grade'];
               $situacao = $rowsPedidos['situacao'];
               $gradeCorrigida = str_replace(array('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'), '', $grade); 

              $sqlCliente = $conn->query("SELECT * FROM clientes WHERE id_cliente = '$rowsPedidos[id_cliente]'");
              $sqlCliente = $conn->fetchAssoc($sqlCliente);

              $idPedido = $rowsPedidos['id'];
              $situacao = $rowsPedidos['situacao'];

              if($rowsPedidos['data_inicial'] != "0000-00-00"){
                  $dataInicial  = str_replace("-", "/", $rowsPedidos['data_inicial']);
                  $dataInicial = date('d/m/Y', strtotime($dataInicial));
              }else{
                  $dataInicial = "";
             }

             if($rowsPedidos['data'] != "0000-00-00"){
                $data  = str_replace("-", "/", $rowsPedidos['data']);
                $data = date('d/m/Y', strtotime($data));
             }else{
                $data = "";
             }

      ?>

         <tr onclick="location.href = 'detalhesPedido?id=<?php echo $rowsPedidos['id']; ?>';" style="cursor:pointer;">
         <td><?php echo $rowsPedidos['id']; ?></td>
         <td><?php echo $rowsPedidos['nome_cliente']; ?></td>
         <td class="d-none"><?php echo $sqlCliente['telefone']; ?></td>
         <td class="d-none"><?php echo $sqlCliente['email']; ?></td>
         <td><?php echo $dataInicial; ?></td>
         <td><?php echo $data; ?></td>
         <td><?php if ($rowsPedidos['situacao'] == 1){
           if ($rowsPedidos['situacao_orcamento'] == "") {
              echo '<span class="badge badge-warning"><i class="fa fa-clock-o" aria-hidden="true"></i> Orçamento Recebido</span>';
           }else { if($rowsPedidos['situacao_contato'] == "1"){
              echo '<span class="badge badge-info"><i class="fa fa-comments-o" aria-hidden="true"></i> Contatou (Orçamento e layout)</span>';
           }else{ echo '<span class="badge badge-info"><i class="fa fa-file-text-o" aria-hidden="true"></i> (PDF) Orçamento Gerado</span>';
           }
          }
       }elseif ($rowsPedidos['situacao'] == 2){ 
         if($rowsPedidos['situacao_contato'] == "1"){
           echo '<span class="badge badge-info"><i class="fa fa-comments-o" aria-hidden="true"></i> Contatou (Orçamento e layout)</span>';
         }else{
            echo '<span class="badge badge-info"><i class="fa fa-thumbs-up" aria-hidden="true"></i> Orçamento e layout</span>';
        }  }elseif ($rowsPedidos['situacao'] == 3){
           echo '<span class="badge badge-primary"><i class="fa fa-table" aria-hidden="true"></i> Grade</span>';
        }elseif ($rowsPedidos['situacao'] == 4){
            echo '<span class="badge badge-success"><i class="fa fa-exchange" aria-hidden="true"></i> Aprovação de Pagamento</span>';
            }elseif ($rowsPedidos['situacao'] == 5){
               echo '<span class="badge badge-success"><i class="fa fa-fighter-jet"></i> Aguardando Codigo de Rastreio</span>';
           }elseif ($rowsPedidos['situacao'] == 6){
              echo '<span class="badge badge-success"><i class="fa fa-check-circle"></i> Finalizado</span>';
             }

         ?></td>

        <?php //if ($nivel == 2){
           $responsavelGrade = $rowsPedidos['responsavel'];
           $queryResponsavel = $conn->query("SELECT * FROM usuarios WHERE id = '$responsavelGrade'");
           $dadosResponsavel = $conn->fetchAssoc($queryResponsavel);
           $nomeResponsavel = $dadosResponsavel['nome'];
           echo "<td>$nomeResponsavel</td>";
           //} ?>
           <td><?php if ($rowsPedidos['valorPedido']) {
              echo "R$ ". number_format($rowsPedidos['valorPedido'], 2, ',','.') ;
            }else {
              echo "";
            } ?></td>
           </tr>

JS:

 $('#pedidosTable').DataTable({
order: [
    [0, 'desc']
],
dom: 'Bfrtip',
buttons: [
    'copy', 'csv', 'excel', 'pdf', 'print'
]
});
$('.buttons-copy, .buttons-csv, .buttons-print, .buttons-pdf, .buttons-excel').addClass('btn btn-primary mr-1');

Answer 1

Your querypedidos loop performs other query internally I suggest you first change to return the required data in just one query for example:

SELECT * FROM 
pedidos p 
inner join 
clientes c on (p.id_cliente = c.id_cliente)
inner join 
usuarios u on (p.responsavel = u.id)

BS.: I don’t know your table database structure but recommend replace select'*' with the required columns for example, since the id_client with the same name is present in the order and client table, use only one in select: p.id_client

and the data accessed by the variable $sqlClient and $dataResponsavel can be accessed through the only $rowsPedidos

That already improves the slowness, If you wish, I also recommend using the datatable configured for server-side processing: It’s worth a read: https://datatables.net/reference/option/serverSide

https://datatables.net/manual/server-side

"serverSide": true, // for process server side
            "ajax": {
                "url": "suaurldosdados.php",
                "type": "POST",
                "datatype": "json"
            },