Optimising dataTable to load quicker Ajax / PHP

Optimising dataTable to load quicker Ajax / PHP

WebCodexWebCodex Posts: 63Questions: 11Answers: 3

Link to test case: https://vccalc.vapingcommunity.co.uk/public
Debugger code (debug.datatables.net): N/A
Error messages shown: N/A
Description of problem: I'm reading through tons of documentation here and trying to find the best way to optimise my dataTable to load as fast as it can but everything I try just doesn't seem to impact it. The data for the dataTable is being returned by a PHP function (PDO) via a controller as seen below

Current Returned Rows: 371

Queued: 2.27 sStarted: 2.28 sDownloaded: 2.39 s

I can see one possible bottleneck and that is where I am rebuilding an array at the controller, I did this because I need to do some checks for the image being returned.

Any suggestions on improving this setup are greatly appreciated, thank you.

PHP Controller:

/**
    POPULATE THE PUBLIC RECIPE TABLE
*/

if(isset($_POST['getPublicRecipes'])) {
    if($_SERVER['HTTP_X_REQUESTED_WITH'] == 'XMLHttpRequest' && isset($_SESSION['usertoken']) 
        && $_SESSION['usertoken'] === $_SESSION['token']){
        $newarray = array();
        $allrecipes = $Recipes->getAllRecipes();

        foreach($allrecipes  as $recipe) {
            $recipeid = $recipe['recipe_id'];
            $recipename = $recipe['recipe_name'];
            $recipeadded = $recipe['recipe_added'];
            $recipetimestamp = $recipe['recipe_timestamp'];
            $recipeflavours = $recipe['recipe_flavours'];
            $recipenotes = $recipe['recipe_notes'];
            $recipeviews = $recipe['recipe_views'];
            $recipemixed = $recipe['recipe_mixed'];
            $recipedescription = $recipe['recipe_description'];
            $recipepublic = $recipe['recipe_public'];
            $recipethumbnail = $recipe['recipe_thumbnail'];
            $recipeavatar = $recipe['user_avatar'];
            $recipeusername = $recipe['user_name'];
            $recipeeid = $recipe['external_id'];
            $recipeupvote = $recipe['upvote'];
            $recipeprofile = $recipe['recipe_profile'];

            if(empty($recipethumbnail) || $recipethumbnail == 'images/noRecipeImage.png' || $recipethumbnail == '/images/noRecipeImage.png') {
                $background_colors = array('lightblue', 'lightgreen', 'lightyellow', 'lightcoral', 'lightcyan', 'lightgoldenrodyellow', 'lightpink','lightsalmon', 'lightseagreen', 'lightskyblue', 'lightsteelblue', 'lightyellow');
                $rand_background = $background_colors[array_rand($background_colors)];
                $recipethumbnail = '<img src="images/noRecipeImage.png" style="background-color: '.$rand_background.'; width: 50px; height: 50px; margin-right: 5px;"></img>';
            } else {
                $recipethumbnail = '<img src="'.$recipethumbnail.'" style="width: 50px; height: 50px; margin-right: 5px;"></img>';
            }

            $newarray[] = [
                'recipe_id' => $recipeid,
                'recipe_name' => $recipename,
                'recipe_added' => $recipeadded,
                'recipe_timestamp' => $recipetimestamp,
                'recipe_flavours' => $recipeflavours,
                'recipe_notes' => $recipenotes,
                'recipe_views' => $recipeviews,
                'recipe_mixed' => $recipemixed ,
                'recipe_description' => $recipedescription,
                'recipe_public' => $recipepublic,
                'recipe_thumbnail' => $recipethumbnail,
                'user_avatar' => $recipeavatar,
                'user_name' => $recipeusername,
                'external_id' => $recipeeid,
                'upvote' => $recipeupvote,
                'recipe_profile' => $recipeprofile,

            ];

        }

        echo json_encode($newarray);
        $vcCalc->token();
    }
    else {
        echo json_encode('Invalid Token');
    }
}

PHP FUNCTION:

/**
    GET ALL RECIPES FOR RECIPE TABLE
*/

public function getAllRecipes() {
    $query = 'SELECT userrecipes.external_id, userrecipes.recipe_id, userrecipes.recipe_name, userrecipes.recipe_added, userrecipes.recipe_timestamp, userrecipes.recipe_flavours, userrecipes.recipe_notes, userrecipes.upvote, userrecipes.recipe_views, userrecipes.recipe_mixed, userrecipes.recipe_description, userrecipes.recipe_public, userrecipes.recipe_thumbnail, userrecipes.recipe_profile, users.user_avatar, users.user_name FROM userrecipes 
    LEFT JOIN users ON users.external_id = userrecipes.external_id WHERE userrecipes.public_recipe=1 ORDER BY userrecipes.recipe_added';
    $stmt = $this->queryIt($query);
return $this->resultset();
}

Answers

  • WebCodexWebCodex Posts: 63Questions: 11Answers: 3

    Tried to edit the post but couldn't so here:

    https://debug.datatables.net/uyiset

  • colincolin Posts: 9,856Questions: 0Answers: 1,635

    This section of the FAQ should help, it discusses various techniques to improve performance,

    Cheers,

    Colin

  • WebCodexWebCodex Posts: 63Questions: 11Answers: 3

    Thanks Colin, I'll take a read through, try some of the options and come back if I find anything, keep up the great work!

  • WebCodexWebCodex Posts: 63Questions: 11Answers: 3
    edited September 18

    @colin Thank you! I got server side working with my PDO by hacking the SSP class, even got multiple custom filters working using the WHERE option in complex, once it's live I'll post a link to it, I hope I've done it correctly, tables seem to load a lot quicker now as you can see below, I'm happy :)

    1st Table (has heavy content including images)
    Original Load Time: 187.48ms
    New Load Time: 51.51ms
    392 Entries

    2nd Table
    Original Load Time: 325.27ms
    New Load Time: 55.00ms
    12,227 Entries

  • allanallan Posts: 52,724Questions: 1Answers: 8,070 Site admin

    That's a big improvement - nice one!

    Allan

  • WebCodexWebCodex Posts: 63Questions: 11Answers: 3

    Hi @allan

    I've got the dataTable's working how I want them but sometimes they are very slow to load, could it be the images in this one?

    https://vccalc.vapingcommunity.co.uk/public

    If so, is there anything I can do to speed it up?

    You can see it working here too, these are the only 2 public dataTables I have, the rest are behind a login but use the same server side scripting.

    https://vccalc.vapingcommunity.co.uk/flavours

    If you have a second to take a look that would be great

    Many Thanks, keep up the great work! :)

Sign In or Register to comment.