Optimising dataTable to load quicker Ajax / PHP

Optimising dataTable to load quicker Ajax / PHP

WebCodexWebCodex Posts: 71Questions: 13Answers: 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: 71Questions: 13Answers: 3

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

    https://debug.datatables.net/uyiset

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

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

    Cheers,

    Colin

  • WebCodexWebCodex Posts: 71Questions: 13Answers: 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: 71Questions: 13Answers: 3
    edited September 2020

    @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: 61,440Questions: 1Answers: 10,053 Site admin

    That's a big improvement - nice one!

    Allan

  • WebCodexWebCodex Posts: 71Questions: 13Answers: 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! :)

  • andrewtegandrewteg Posts: 3Questions: 1Answers: 0

    Do you have details on how you got server side working with your PDO? I'd like to do the same and pass things better but haven't had any luck hacking the SSP class at this point.

This discussion has been closed.