Skip to content
Advertisement

Slow SQL Query / Displaying (PHP / jQuery)

I have a request to get data in my mySQL database (36,848 entries). Then I format and append them with jQuery on my web page.

These two operations take 2 minutes and 30 seconds. During this time, no other operation is possible on the page.

Is there a way to optimize this to speed up the process? Like this, it’s unusable.

My PHP function

<?php
function get_my_customers() {
    $req = $bdd->prepare("SELECT * FROM clients ORDER BY raison_sociale");
    $req->execute();
    $customers = $req->fetchAll(PDO::FETCH_ASSOC);

    return $customers;
}

if(isset($_POST['ajax'])){
    $result = get_my_customers();
    echo json_encode($result);
}
?>

Data retrieval in jQuery (Ajax)

function get_my_customers(){
    var customers;
    $.ajax({
        url: "model/application/*******/customers/get_my_customers.php", 
        async: false,
        type: "POST",
        dataType: 'json',
        data: {ajax: 'true'},
        success: function(data)
        {
            customers = data;
        }
    });
    return customers;
}

var my_customers = get_my_customers();

$('#customers_list').append('<div class="list_card" card="customers_list"></div>');

$.each(my_customers, function(key, val){
    if(val.enseigne == null){
        var enseigne = '';
    }else{
        var enseigne = ',' + val.enseigne;
    }

    $('.list_card[card="customers_list"]').append(''+
        '<div class="list_card_element">'+
            '<div><b>'+ val.numero_client +'</b></div>'+
            '<div>'+
                '<b>'+ val.raison_sociale +'</b>' + enseigne +
                '<br>'+
                val.cp_livraison + ', ' + val.adresse_livraison +
            '</div>'+
        '</div>'
    );
});

Do you know what I can do to speed up processing time? I’ve tried limiting the SELECT query to only the fields I need but that doesn’t improve processing time.

I wonder if it’s not rather the jQuery layout that takes time rather than the SQL query.

Thank you !

Advertisement

Answer

I just passed from 2 minutes and 30 secondes to 14 secondes with this optimization.

var construct_customers = "";

$.each(my_customers, function(key, val){
    if(val.enseigne == null){
        var enseigne = '';
    }else{
        var enseigne = ',' + val.enseigne;
    }

    construct_customers += '<div class="list_card_element">'+'<div><b>'+ val.numero_client +'</b></div>'+'<div>'+'<b>'+ val.raison_sociale +'</b>' + enseigne +'<br>'+val.cp_livraison + ', ' + val.adresse_livraison +'</div>'+'</div>';

    //OLD CODE
    // $('.list_card[card="customers_list"]').append(''+
    //     '<div class="list_card_element">'+
    //         '<div><b>'+ val.numero_client +'</b></div>'+
    //         '<div>'+
    //             '<b>'+ val.raison_sociale +'</b>' + enseigne +
    //             '<br>'+
    //             val.cp_livraison + ', ' + val.adresse_livraison +
    //         '</div>'+
    //     '</div>'
    // );
});

$('.list_card[card="customers_list"]').append(construct_customers);

I’m impressed

9 People found this is helpful
Advertisement