Table for Excel taking too long

Asked

Viewed 80 times

0

Good , I have a query in the database , that returns me to depend on this query 5k lines to 30k lines , I’m generating already in excel through the code below , however when brings me about 5k of information , it takes a long time to load . Could someone help regarding the code or other way to generate excel ?

excel.php:


<?php
   $file = 'planilha.xls';
   header ("Content-type: application/x-msexcel");
   header ("Content-Disposition: attachment; filename=\"{$file}\"" );
   header ("Content-Description: PHP Generated Data" );
?>
<?php
    define('DB_HOST'        , "");
    define('DB_USER'        , "");
    define('DB_PASSWORD'    , "");
    define('DB_NAME'        , "");
    define('DB_DRIVER'      , "sqlsrv");

    require_once "Conexao.php";

try{
  //inicia a conexao
   $Conexao     = Conexao::getConnection();

   $filial1 = $_POST[];
   $filial2 = $_POST[];
   $data_inicial = $_POST[_];
   $data_final = $_POST[];



  //faz a consulta na tabela procurando palavras do form
   $query       = $Conexao->query (" EXECUCAO DA MINHA PROCEDURE ");
   $teste       = $query->fetchAll();

  }catch(Exception $e){
        echo $e->getMessage();
        exit;
    }
?>
    <table border="1">
        <thead>
            <tr>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>
                <th>
                    <center><b></b></center>
                </th>

            </tr>
        </thead>
        <?php
        foreach($teste as $CLIENTE) {
    ?>
            <tr>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>

                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE['']; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>
                <td>
                    <center>
                        <?php echo $CLIENTE[]; ?>
                    </center>
                </td>

            </tr>
            <?php
        }
    ?>
    </table>
php html5 excel code-review

  • Usually code revisions need to be more specific and require more details.

  • In the case , I presented my code of how I am generating Excel , my doubt is in the part of how could improve this code in specific . I described my entire code and explained why you’re making a possible improvement .

1 answer

1


I will add some considerations, but it will hardly be a specific answer to your problem.

There are several ways to "attack" this problem, the first of which is to increase the machine’s resources so that the script manages the content faster, this solution is relatively easy, but hardly scalable, there will come a time when there will no longer be a single machine capable of generating content.

The second way is to schedule the creation of the table and inform the user when it is ready, ie the user instead of clicking generate the table and wait for the table to be ready, he would request (through a button or something) and your system would put the creation in the background to be executed in the time needed, when the file is ready you inform the user by email or some other mechanism.

If you need the content periodically, you can do a cronjob to run the script overnight or some time that the server receives less load, storing the result and providing the already computed file when prompted.

One last consideration, is the SQL query itself, which in the question was omitted, maybe it is nice you check how long it takes to run, maybe the problem is not really in PHP.

  • $query = $Connected->query ("EXEC Procedure '$x,'$y','$z','$Fw' "); this is my query .

Browser other questions tagged php html5 excel code-review

You are not signed in. Login or sign up in order to post.