Optimising dataTable to load quicker Ajax / PHP
Optimising dataTable to load quicker Ajax / PHP
WebCodex
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();
}
This discussion has been closed.
Answers
Tried to edit the post but couldn't so here:
https://debug.datatables.net/uyiset
This section of the FAQ should help, it discusses various techniques to improve performance,
Cheers,
Colin
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!
@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
That's a big improvement - nice one!
Allan
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!
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.