0

I have a webpage that utilizes Expression Engine for it's backend. One of the pages is taking 3-5 seconds to load because it is querying for all records in a channel and displaying them in a Datatable (for more info on this JS lib, see: https://datatables.net/).

How would I use Ajax to query for only 50 records at a time instead of having Datatables or EE query ALL of the records? I want to optimize this to bring down the load time and prevent it from getting worse as more records are added to the table.

As of now, this is the JS that creates the Datatable:

$('#server_table').DataTable( {
        "pageLength": 50,
        "lengthMenu": [[10, 50, 100, 500], [10, 50, 100, 500]],
        "order": [[ 0, "asc" ]]
}

Here is how EE constructs the table's body:

<tbody>  
   {exp:channel:entries channel="servers" limit="3000" status="Open|Closed"}
   <tr>
   {if status == "closed"}
   <td><a href="{url_title_path='site/server_detail'}" class="text-danger">{title} (Decomm'd)</a></td>
   {if:else}
   <td><a href="{url_title_path='site/server_detail'}" class="text-navy">{title}</a></td>
   {/if}
   <td>{server_ip_address}</td>
   <td>
{server_application}
    <a href="{siteurl}index.php/site/system_detail/{server_application:url_title}" class="text-navy">{server_application:title}</a>{if server_application:total_results > 1}<br>{/if}
{/server_application}
    </td>    
    <td>{server_environment}</td>
    <td>{server_operating_system}</td>
    <td>{server_data_center}</td>
    </tr>
    {/exp:channel:entries}
 </tbody>
0

To achieve the speed up you are looking for you ideally need to modify your use of datatables such that it pulls just the information it needs to render the currently visible 50 rows, rather than pulling the entire dataset before you render the table.

This issue is quite common, and Datatables has a rich feature set to support various strategies to enable this type of working: from defering the loading of the bulk of a large dataset until after the initial view of the table has been rendered, to multiple approaches for AJAX access to partial data sets.

How you configure datatables to deliver your required results is outside the scope of this forum (but there are very good support options to help you with this via the datatables site). But the EE configuration to support these options is disarmingly easy.

The simplest datatables solution to implement is probably deferred loading - which means you continue to dump the entire dataset out on first contact each time, but the bulk of the loading delay is hidden from the user. If this is what you are doing, you probably want to turn on EE's quite effective caching system - which may give you a useful speedup (for the hidden dataload). There are a few options for how you enable the caching options for EE, and a clearly written part of the EE documentation takes you through the options and their pros and cons.

Depending your server configuration it is possible that you can achieve quite impressive speed-ups by turning caching on: 3000 records is (quite possibly) a small amount of data compared to (say) a large picture, and so reading this from a file rather than your database server each time might be all you need to do to get OK performance - particularly if you couple it with deferred loading in the table. An example of what your channel:entries tag might look like with simple caching turned on would be:

{exp:channel:entries 
    channel="servers" 
    limit="3000" 
    status="Open|Closed" 
    cache="yes" 
    refresh="3000"}

As the documentation notes, another speed up might be able to use is to disable parts of the EE query using the disable parameter. With this your tag becomes something like:

{exp:channel:entries 
    channel="servers" 
    limit="3000" 
    status="Open|Closed" 
    cache="yes" 
    refresh="3000" 
    disable="categories|custom_fields|member_data|pagination"}

The better solution would be to change how your table rendering works so that datatables is fed with 'just enough' information for the table render the part of the dataset that is visible for the current page of the table (i.e. send just 50 records at a time). Providing the restricted data sets for this kind of operation from within EE itself is disarmingly easy - no more complex than adding two more parameters to the call: limit and offset. You are already using limit (to override the default limit on records returned). offset simply sets the index from which you want to retrieve records. What's more the same caching speedups we discuss above can be applied too...

So to return just 50 records from an arbitary point (e.g. in this case to return records 51-100) from your channel you could use a call like:

{exp:channel:entries 
    channel="servers" 
    limit="50" 
    offset="50"
    status="Open|Closed" 
    cache="yes" 
    refresh="3000" 
    disable="categories|custom_fields|member_data|pagination"}

To make this approach work you almost certainly need to generate this restricted dataset in response to AJAX calls. From the EE perspective this is not difficult to implement - simply create a specific template to deliver the table data in whatever format you require it for the datatables instance (for example JSON) and then set the offset and limit values in that template set via either URL segments, GET or POST data (it doesn't really matter which - since the URL is not one that anyone sees). For example if you used URL segments your AJAX url would perhaps look like this:

NB. assumes the ajax template is at /data-provider/ with offset first, limit second and that on this call you want the 50 records following the 250 entry ():

https://your-domain.com/data-provider/250/50

The matching channel:entries tag for this would look something like:

{exp:channel:entries 
    channel="servers" 
    limit="{segment_3}"
    offset="{segment_2}" 
    status="Open|Closed" 
    cache="yes" 
    refresh="3000" 
    disable="categories|custom_fields|member_data|pagination"}

Hope this helps with the EE side of the problem - if you are stuck on configuration of datatables, better to ask questions of their support community if you can.

2
  • Wow. You went in depth on this and I appreciate that. I will try this in a bit on our dev instance.
    – Alroy
    Mar 21 '19 at 17:06
  • 1
    Caching worked. Marking this as the solution.
    – Alroy
    Mar 21 '19 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.