Optimising dataTable to load quicker Ajax / PHP

WebCodex

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();
}

WebCodex

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

https://debug.datatables.net/uyiset

colin

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

Cheers,

Colin

WebCodex

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!

WebCodex

@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

allan

That's a big improvement - nice one!

Allan

WebCodex

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!

andrewteg

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.