Datatables encounters issues loading 70,000 records into the system

Question

Datatables encounters issues loading 70,000 records into the system

I have a jQuery datatable that is supposed to load over 70K records.

However, the datatable fails to display anything beyond 20K records.

Despite trying to use the deferRender option as a workaround, it doesn't seem to be effective.

$.ajax({
    url: 'api/portmbs.php',
    type: 'POST',
    data: data,
    dataType: 'html',
    success: function(data, textStatus, jqXHR)
    {
        var jsonObject = JSON.parse(data);

        var table = $('#example1').DataTable({
            "data": jsonObject,
            "columns": [
                {"data": "column_one"},
                {"data": "column_two"},
                // more columns...
            ],
            "iDisplayLength": 25,
            "order": [[ 1, "desc" ]],
            "paging": true,
            "scrollY": 550,
            "scrollX": true,
            "bDestroy": true,
            "stateSave": true,
            "autoWidth": true,
            "deferRender": true
        });
    },
    error: function(jqHHR, textStatus, errorThrown)
    {
        $('#loadingDiv').hide();
        $('#errorModal').modal('show');
        $('.message').text('There was an error conducting your search. Please try again.');
        return false;       
        console.log('fail: '+ errorThrown);
    }
});

The above code results in an error message:

Failed to load resource: the server responded with a status of 500 (Internal Server Error)

By imposing a limit of 10000 on the query generating the data, the datatable successfully loads.

What am I overlooking in order to make the deferRender option work seamlessly for loading 70K records?

jquery ajax datatables

Answer 1

Answer №1

A server error I encountered in the past was due to an overflow on the PHP memory_limit variable. The default value (found in the php.ini) is 128MB, which means that with over 70000 rows of data, this limit could easily be exceeded.

To temporarily resolve this issue, you can increase the memory limit by adjusting the configuration in the php.ini file on the server and then restarting it. Personally, I have set my memory limit to:

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
; XXX: Increased from 128 to 512.
memory_limit = 512M

For more information on this topic, please refer to the following links:

(1) http://php.net/manual/en/ini.core.php#ini.memory-limit

(2)

However, keep in mind that increasing the memory limit is only a temporary solution. To address these types of issues effectively, consider implementing server-side processing. This involves making server requests for actions like pagination, ordering, or filtering, and updating the displayed data accordingly in the datatable. You can find an example of server-side processing on the DataTables examples page at the following link:

(1) Server Side Processing Class Example

Answer 2

A server error I encountered in the past was due to an overflow on the PHP memory_limit variable. The default value (found in the php.ini) is 128MB, which means that with over 70000 rows of data, this limit could easily be exceeded.

To temporarily resolve this issue, you can increase the memory limit by adjusting the configuration in the php.ini file on the server and then restarting it. Personally, I have set my memory limit to:

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
; XXX: Increased from 128 to 512.
memory_limit = 512M

For more information on this topic, please refer to the following links:

(1) http://php.net/manual/en/ini.core.php#ini.memory-limit

(2)

However, keep in mind that increasing the memory limit is only a temporary solution. To address these types of issues effectively, consider implementing server-side processing. This involves making server requests for actions like pagination, ordering, or filtering, and updating the displayed data accordingly in the datatable. You can find an example of server-side processing on the DataTables examples page at the following link:

(1) Server Side Processing Class Example

Answer 3

Answer №2

$(document).ready(function() {
    $('#example').DataTable( {
        serverSide: true,
        ordering: false,
        searching: false,
        ajax: function ( data, callback, settings ) {
            var out = [];

            for ( var i=data.start, ien=data.start+data.length ; i<ien ; i++ ) {
                out.push( [ i+'-1', i+'-2', i+'-3', i+'-4', i+'-5' ] );
            }

            setTimeout( function () {
                callback( {
                    draw: data.draw,
                    data: out,
                    recordsTotal: 5000000,
                    recordsFiltered: 5000000
                } );
            }, 50 );
        },
        scrollY: 200,
        scroller: {
            loadingIndicator: true
        },
        stateSave: true
    } );
} );

Answer 4

$(document).ready(function() {
    $('#example').DataTable( {
        serverSide: true,
        ordering: false,
        searching: false,
        ajax: function ( data, callback, settings ) {
            var out = [];

            for ( var i=data.start, ien=data.start+data.length ; i<ien ; i++ ) {
                out.push( [ i+'-1', i+'-2', i+'-3', i+'-4', i+'-5' ] );
            }

            setTimeout( function () {
                callback( {
                    draw: data.draw,
                    data: out,
                    recordsTotal: 5000000,
                    recordsFiltered: 5000000
                } );
            }, 50 );
        },
        scrollY: 200,
        scroller: {
            loadingIndicator: true
        },
        stateSave: true
    } );
} );

Datatables encounters issues loading 70,000 records into the system

Answer №1

Answer №2

Similar questions

The loading GIF in jQuery is malfunctioning when displayed simultaneously on multiple div elements

Is it possible for me to utilize a validation function to display error messages within a specific span id tag?

Simple and quickest method for incorporating jQuery into Angular 2/4

I successfully set up ApacheOpenmeetings on a dedicated server. Now, I am wondering how to connect to its REST APIs from a different application

I am looking to conceal an element as soon as a list item is scrolled into view and becomes active

Why is the X-Requested-With header necessary?

Perform an Ajax call just one time

Is there a way to make jQuery Validate display errors within the form elements rather than outside of them?

Adjusting the size of a textarea using jQuery

Is there a way to modify AJAX response HTML and seamlessly proceed with replacement using jQuery?

Acquiring variables from a JQuery Ajax request while invoking a JavaScript file

Trigger a fixed bottom bar animation upon hover

Sending AJAX Responses as Properties to Child Component

Performing an AJAX call in Rails 4 to update a particular value

Is Valums Ajax file Upload capable of handling the uploaded file?

Enhance User Experience by Dynamically Updating Google Maps Markers with Custom Icons Using Django and JSON

Display Partial View in MVC 4 using ajax success callback

Tips for spinning HTML5 Canvas's background image and adjusting the zoom with a slider

Managing dynamically appearing elements in Ember: Executing a Javascript function on them

Implement necessary validation for the country code selection on the dropdown menu using the intl-tel-input jQuery plugin