Row limit for datatables?

cursor

I am using datatables to present CDR information from my PBX. The information is stored in a MariaDB database. Datatables works fine as long as I limit the number of rows I want to display. In my tests about 10,000 records will load fine after a few seconds. Any more than that and I get an error:
DataTables warning: table id=contenido_cdr - Ajax error. For more information about this error, please see http://datatables.net/tn/7

I can see a status of "500" in the response from my server whenever I try to load more thank 10K records and it comes almost immediately after the query is executed. With less thank 10K I get the expected "200" status. Is this a limit on datatables? How can I handle more records than that? 10K records is only about two weeks of calls and I need to display at least a whole month for the report to be useful. Is there a setting or parameter I need to change to be able to handle more rows? Thanks.

colin

Hi cursor,

I'm not aware of any limit, it may be the server-side causing the problems.

One solution though maybe to defer rendering, so data is only requested from the server when it needs to be displayed. This is discussed further in this example.

Hope that helps,

Cheers,

Colin

allan

Yes, as Colin says, there is no hard limit anywhere in DataTables itself. What I suspect is happening here is that you are hitting a memory limit in whatever is processing the server-side data access code. Checking the server's error logs would be the way to confirm if this is the case or not (that's always the first place to look when you get a 500 error - its an "internal server error" code).

I'd say that in this case you really want to use server-side processing. There is an example of it available here. That way you'll be able to handle millions of rows of data by leveraging the capabilities of the database where the data is stored.

Allan

madhavs

Hi, allan
me also have a same problem, it's load upto 5500 rows only. if i try to bind 6000 or more throwing same error.

colin

Hi @madhavs ,

Are you seeing server errors? If you could link to the page, we can take a look.

Cheers,

Colin

madhavs

Hi @colin,
here is my code: trying to load all data(more than 1 lakh) at once and searching, sorting at client side only, but i able to bind only 5500 rows please, suggest. thanks in advance.

jQuery331(document).ready(function ($) {

  $('#table-1 tfoot th').each(function () {
    var title = $(this).text();
    $(this).html('<input type="text" placeholder="search ' + title + '" />');
});

var table = $("#table-1").DataTable({

    "ajax": {
        "url": "/ProductManager/GetProducts",
        },
    "columns": [

               { "data": "Name" },
               { "data": "Sku" },
               { "data": "MasterSku" },
               { "data": "Category" },
               { "data": "Design" },
               { "data": "PrimaryColor" },
               { "data": "Availability" },
               { "data": "ReleaseDate" },
               { "data": "PrimaryImageName" },
               { "data": "Id" }
    ]

});
// Apply the search
table.columns().every(function () {
    var that = this;

    $('input', this.footer()).on('keyup change', function () {
        if (that.search() !== this.value) {
            that
                .search(this.value)
                .draw();
        }
    });
});

});

madhavs

this is the error which i getting:

DataTables warning: table id=table-1 - Ajax error. For more information about this error, please see http://datatables.net/tn/7

tangerine

The link provided in the error message is there for you to follow. It shows diagnostic steps to explain and resolve the problem.

colin

Hi @madhavs ,

As @tangerine says, use the info in that link, it'll help. Also, your table isn't using serverSide, so performance will be slow/troublesome with that many records. See this FAQ page here for information,

Cheers,

Colin

Axotla

same error here, ERR_CONNECTION_RESET in browser

colin

@Axotla That error wasn't mentioned in this thread. It wouldn't be generated by DataTables. It would be worth checking your server logs.

Colin

bobthecoder64

Using .NET server side, I was able to avoid the 500 error on by web server by changing the MaxJsonLength from the default to something bigger. Originally i was getting an error at about 700 rows of data. Making this change allow me to display/transfer thousands of rows to the browser.Avoiding Javascript Serializer Issues in .NET