John Davidson

Message:

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 !

---

My Answer:
Here are a few suggestions to optimize your slow SQL query and display process:

1. Use indexing: Make sure that the columns you are querying on are indexed in your database. This can significantly speed up the retrieval process.

2. Limit the number of rows retrieved: If you don't need to display all 36,848 entries at once, consider limiting the number of rows retrieved in your SQL query. You can use pagination to display a subset of the data at a time.

3. Use caching: If the data doesn't change frequently, consider caching the results of the query so that you don't have to retrieve it every time a user accesses the page.

4. Use asynchronous requests: Instead of waiting for the entire dataset to be retrieved before displaying it on the page, consider using AJAX to make asynchronous requests to fetch the data in chunks and display it as it becomes available.

5. Optimize your jQuery code: Make sure that your jQuery code is efficient and not causing any unnecessary delays. Avoid using complex selectors or performing unnecessary DOM manipulations.

By implementing these optimizations, you should be able to speed up the process of retrieving and displaying data on your web page.