Optimize script

nico077

Hello Allan,

In my database, i've got a lot of records (more 50 000) but i want to display 500.

In my script I use
// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'budget' )
    ->field( 
        Field::inst( 'budget.id' ),
        Field::inst( 'budget.nni_auteur' ),
        Field::inst( 'budget.id_ccs' ),
        Field::inst( 'budget.id_arret' )
               .......
        )
    ->leftJoin( 'nature_de_frais',     'budget.id','=', 'nature_de_frais.id_budget' )
    ->leftJoin( 'activite',     'budget.id_activite', '=', 'activite.id' )
    ->leftJoin( 'referentiel_atb',     'referentiel_atb.id',          '=', 'activite.id_atb' )
    ->leftJoin( 'referentiel_groupe_atb',   'referentiel_groupe_atb.id',    '=',    'referentiel_atb.id_groupe_atb')
    ->leftJoin( 'arret_tranche',     'arret_tranche.id',          '=', 'budget.id_arret' )
    ->leftJoin( 'datarrep',     'datarrep.id_arret_tranche',          '=', 'arret_tranche.id' )
    ->leftJoin( 'referentiel_type_at',     'referentiel_type_at.id',          '=', 'datarrep.id_type_at' )
    ->leftJoin( 'ccs',     'budget.id_ccs',          '=', 'ccs.id_pgi' )
    ->leftJoin( 'service',     'ccs.id_service',          '=', 'service.id' )
     ->where( 'budget.suppression', '0', '=' )
     ->where( function ( $q ) {
    $q->where( 'service.id_unite', $_SESSION['unite_v1_recettage'], '=' )
      ->where( function ( $r ) {
            if(!empty($_SESSION['ccs_v1_recettage']) and $_SESSION['droit_v1_recettage'] !='DGP'){
                foreach ($_SESSION['ccs_v1_recettage'] as $ccs){
                    $r->or_where( 'ccs.id_pgi', $ccs, '=' );
                }
            }else{
                $r->or_where( 'ccs.id_pgi', '', '!=' );
            }
        } );
    } )

The load is too long. I use a lot of leftJoin.

When i use sql, I use the LEFT JOIN after my where condition.

Can I optimize my scritps ?

Thank you for your response.

Best regards,
Nicolas

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan

Hi Nicolas,

Thanks for your code. The order of the where and leftJoin statements make no difference to how the SQL is actually executed, so just putting the where statements first won't help.

How long does that query take to execute? Have you tried enabling server-side processing?

Allan

nico077

When i've got 500 records in my database (table budget),
The load take two minutes...

Why is it taking so long ?

I try wth your exemple.

Thank you very much,

Best regards,
Nicolas

allan

Why is it taking so long ?

Without being able to see it, it is really difficult to say. I would suggest what you could do is have a look in the Database/Drivers/Mysql/Query.php file (assuming you are using MySQL) and you will find a commented out line for file_put_contents. Enable that line and update the output path to be suitable for your server. That will put the SQL queries that Editor runs into the file. You could then try running them against the server drivers to understand what is going on.

Allan

nico077

Hello Allan,

Thank you very much for your quick response,

I'm going to try use INNER JOIN instead of LEFT JOIN.

I'll keep you in touch.

Best regards,
Nicolas

nico077

This morning, i do test on my database.

I greatly improve when i add an index on nature_de_frais.id_budget.

When i use the SQL request
<code>
SELECT *
FROM(

SELECT budget.* FROM budget
LEFT JOIN ccs on budget.id_ccs = ccs.id_pgi
LEFT JOIN service on ccs.id_service = service.id
WHERE suppression = '0'
and service.id_unite = 'EM5370'

) as t1

LEFT JOIN nature_de_frais on t1.id = nature_de_frais.id_budget
LEFT JOIN activite on t1.id_activite = activite.id
LEFT JOIN referentiel_atb on activite.id_atb = referentiel_atb.id
LEFT JOIN referentiel_groupe_atb on referentiel_atb.id_groupe_atb = referentiel_groupe_atb.id
LEFT JOIN arret_tranche on t1.id_arret = arret_tranche.id
LEFT JOIN datarrep on arret_tranche.id = datarrep.id_arret_tranche
LEFT JOIN referentiel_type_at on datarrep.id_type_at = referentiel_type_at.id

</code>

The response is 0.1293 sec on PHPMyAdmin.

But when i use in Datatable, I've got a JSON error and in my console, the message
<b>Fatal error</b>: Allowed memory size of 134217728 bytes exhausted

I've got 20 000 elements in the budget table and nature_de_frais table.

Thanks again for all of your help

Nicolas

allan

Without being able to see the code you are using to execute that SQL I can't really comment, but my guess is you are loading the whole data structure into memory, hence the error.

You might need to either stream the output so it doesn't get dumped into memory or use server-side processing. I think phpMyAdmin does a default LIMIT on SQL statements which might be how they are working around the memory issue.

Allan

nico077

I find a solution with your help.

It's work.

Thank you for your help. Datatable is wonderfull !!

Nico