Moderate size data set loads very slow

CrimsonGT

I've spent the last few days trying to figure out the problem, however thus far I am at a loss. I am using Laravel as an MVC and pull all DB records in my Controller, pass these as an array to the view and then display them via the DataTable. As of now, I have around 550 entries which isn't exactly excessive given I have read here other people are using 100,000+.

The problem appears to be with me using scroll as opposed to pagination. The page loads, the table takes up the full height of the page and then a second later it gets placed intro a scrolling container. It isn't horrible but it is rather wonky looking.

http://rosterdrafts.com/lineup/draftkings

Is there anything that can be done to improve this? I read about Deferred Rendering and using Server Side Processing, however I can't imagine that this would improve much for my case. Any help would be greatly appreciated.

jLinux

Are you editing this live? I keep trying to look into it, but the error about initiating DT keeps showing and disappearing. I know you have the retrieve set to true, but you comment that out (sometimes).

Question, why do you initiate DataTables on the table with just the retrieve setting, but nothing else, then re-initi the table again right after that, in another $(document).ready ?

Heres your code (with other HTML removed)

$(document).ready(function()
{
    //var table = $('#dt-players-all').DataTable();
    var table = $('#dt-players-all').DataTable({"retrieve": true});

    $(".btn-add-player").click( function()
    {
        var row = $(this).closest("tr");
        console.log( table.row( row ).data() );
    });
});

$(window).load( function ()
{
    $('#dt-players-all').dataTable().fnAdjustColumnSizing( false );
    } );

    $(document).ready(function()
{
    $('a[data-toggle="tab"]').on( 'shown.bs.tab', function (e) {
        $( $.fn.dataTable.tables( true ) ).DataTable().columns.adjust();
    });

    $('table.table').DataTable({
    responsive: true,
    "order": [[ 7, "desc" ]],
    "scrollY":        "500px",
    "scrollCollapse": true,
    "paging":         false
    //"bInfo" : false
    });

    //$('#dt-players-all').DataTable().search( '' ).draw();
    });

I dont see any reason to not initialize DataTables with all the options in the first init..

The long "pause" here and here seem to be between the two DataTable initializations...

It looks like the table with all the rows gets initialized (with the first one, using retrieve), then the 2nd table is whats taking so long to create, THEN when thats done, the 2nd DT script is initialized. So im not positive that it has anything to do with the scrolling or paging settings, but rather just that theres a pause between the two DT Initializations..

Why do you have two $(document).ready's in your script? You only need one... Especially if they're in the same page/script.

P.S.

Is there anything that can be done to improve this? I read about Deferred Rendering and using Server Side Processing, however I can't imagine that this would improve much for my case. Any help would be greatly appreciated.

I dont believe that your problem would be solved by this, because I dont think the amount of rows is hte issue.
However, I know that the ajax is best for rows over like 10k, but I use it and I only have a few hundred, and I still think it helps, also because I like to have ANYTHING related to the table done via DataTable, so I poll all the rows via AJAX, then parse the content via the createdRow, as opposed to using PHP

P.S.S There really isnt much you can do (via DT) to initialize the loading of a table using the DOM as the source, Scroller, DeferRendering, DeferLoading are all for AJAX, JSON or ServerSide DataSources

jLinux

And I know Scroller is supposed to stop all rows from being rendered, but im actually having issues with that myself: http://datatables.net/forums/discussion/31084/scroller-on-ajax-sourced-dt-shouldnt-it-only-render-visible-rows#latest

CrimsonGT

I was in fact editing it live, been trying to fix a couple issues for hours with it that are driving me insane and not having any luck. I stopped though, sorry about that!

While it is the same page, I have the actual DataTable initialization code in my "footer.blade.php" and the click event code in my lineup.blade.php file, why its kind of split up.

---

In regards to initializing it with just retrieve, I am actually just trying to figure out how to properly get an existing DT instance (not create another one there). That code works but I receive a warning that I am re-initializing the table so I still haven't figured out how to properly do so. From what I found online on these forums and Stack Overflow...

• One person said that the update docs state .dataTable() is for initializing and .DataTable() is for getting an instance but didn't stop the warning message.
• The documentation here said that you could get an existing instance using retrieve but that also didn't stop the alert warning about re-initializing.
• Another person stated that you can use .dataTable().api() to get the DT instance but alas, this also still throws the same warning about re-initializing it.

---

As for the issue with the slow loading, I think this is in part to having 7 different DataTables, where each is in a tab. I create each of these individually in the HTML and assume this is probably drastically slowing things down. As 6 of the tabs are essentially just for filtering purposes though, I am trying to determine a way to perhaps reuse the existing table rather than have 7 individual ones.

---

Lastly, for the actual problem I was referring to in my OP, the table seems to all load in at 100% of the page height, only shrinking down to meet the "ScrollY" value once it has fully loaded. A better example of this would be http://rosterdrafts.com/players/draftkings where this only has a single DataTable (no tabs/etc) but still suffers from the problem.

Thanks for all of the feedback by the way. I have been trying to fix a small handful of problems with this for days and am absolutely tearing out my hair so any help is vastly appreciated.

jLinux

@CrimsonGT, I forgot to mention that when you use retrieve, it actually needs to be in the 2nd DT init, not the first..

Per the DT retrieve docs:

this parameter will cause DataTables to simply return the object that has already been set up - it will not take account of any changes you might have made to the initialisation object passed to DataTables (setting this parameter to true is an acknowledgement that you understand this!)

As far as the table taking a bit to load, that may just be because DT has to render all of the rows before it can initiate the Scroll extension. You can see on the DT Example here that it kinda does the same thing, and thats the client side DataSource, but in the Server Side Example, it works fine (So it should also be the same for ajax using deferRender. I would think that the issue is partly due to the DOM source, but that definitely cant be the reason its taking 11+ seconds, however when I visit your page now, it only takes around 4~5 seconds, so I would definitely attribute that to the DOM source

So basically, I recommend switching to an AJAX Data source, and use the retrieve properly, and moving all the JS into one single JS file, that way you can manage it properly, as opposed to having JS in different PHP files that mingle with other JS in other PHP files

Edit Also, I just remembered you said that you had multiple tables, so its more than just the 500 rows, its multiple tables. Since each of those tables are referenced by the table.table CSS selector, Im guessing DT has to load/render every row, in every table first, before it starts the Scroller extension. You stated you might do something like move them all to one table, I would recommend doing that, and just having buttons to filter the table. You could even have links above the table that will filter the table based on the content of a hidden column, which would be the way to go, especially since you have an All tab that loads a table with every record, then all the other tables consist of records that exist in that table..

CrimsonGT

I would just like to say thank you for all of the help. With your advice, I cut this down to a single DataTable and instead used the Bootstrap Tabs as filters instead which has drastically improved the performance. Factoring in some optimizations such as gzip compression and so fourth, it is much less noticeable now than before.

Tomorrow I am going to look at building the data on a 12 hour cron to feed in via AJAX where I do hope this will also further improve if not completely fix the entire issue originally described.

Just one further question if you have any thoughts on it. For my "FLEX" tab, this is actually comprised of 3 different categories. From reading various threads, I was under the impression these could be sorted by the "|" character, however it doesn't seem to be working in this case. Do you have any idea how to accomplish this?

    $('a[data-toggle="tab"]').on( 'shown.bs.tab', function (e)
    {
        // Get the DataTable instance
        var table = $('#tbl_players').DataTable();

        // Get the HTML Text of the Tab (Ex: QB) and filter column 0 (Position) by that
        var tab = $(e.target).html();

        switch (tab)
        {
            case "All":
            {
                table.columns( 0 ).search(  "" ).draw();
                break;
            }
            case "FLEX":
            {
                table.columns( 0 ).search(  "WR|RB|TE" ).draw();
                break;
            }
            default:
            {
                table.columns( 0 ).search(  tab ).draw();
                break;
            }
        }

    });

allan

I was under the impression these could be sorted by the "|" character

Yes, but that is a regular expression separator, so you need to tell the columns().search() method to treat it as regex rather than as a plain string. You should also disable DataTables built in smart search since that would just play havoc with your own regex:

table.columns( 0 ).search(  "WR|RB|TE", true, false ).draw();

See columns().search() for details.

Allan