How can I improve performance (24 cols, 18000 entries)? (and other questions)

How can I improve performance (24 cols, 18000 entries)? (and other questions)

extarysextarys Posts: 3Questions: 2Answers: 0

The window freeze for 20-30 seconds while the table renders and it seems that no matter the options I choose, nothing change.
I'm using node-webkit and the database request takes 0.5 seconds or less.

$('#'+drawer.currentView+'_'+drawer.currentContent).find('table').DataTable({
        columns: [
            { "title": "Title",             "data": "title",           "width": "250px" },
            { "title": "Album Artist",      "data": "albumartist",      "width": "250px",        "orderData": [1, 3, 2]  },
            { "title": "Artist",            "data": "artist",           "width": "250px",        "orderData": [2, 3] },
            { "title": "Album",             "data": "album",            "width": "250px" },
            { "title": "BPM",               "data": "bpm",                      "type": "num" },
            { "title": "Compilation",       "data": "compilation",              "visible": false },
            { "title": "Date",              "data": "date",                     "visible": false },
            { "title": "Year",              "data": "year",                     "type": "date" },
            { "title": "Disk",              "data": "disk",                     "render": function ( data, type, row, meta ) {
                return data.no;
            }},
            { "title": "Track",             "data": "track",                    "render": function ( data, type, row, meta ) {
                return data.no;
            }},
            { "title": "Genre",             "data": "genre"                    },
            { "title": "Bits Per Sample",   "data": "bitsPerSample",            "type": "num-fmt" },
            { "title": "Loseless",          "data": "loseless",                 "visible": false },
            { "title": "Channels",          "data": "numberOfChannels",         "visible": false },
            { "title": "Duration",          "data": "duration",                 "render": function ( data, type, row, meta ) {
                var min = Math.floor(data / 60);
                var sec = Math.floor(data - (min * 60));
                return min + ":" + sec;
            }},
            { "title": "Sample Rate",       "data": "sampleRate",               "type": "num-fmt" },
            { "title": "Label",             "data": "label" },
            { "title": "Media",             "data": "media" },
            { "title": "Barcode",           "data": "barcode" },
            { "title": "Catalog #",         "data": "catalognumber" },
            { "title": "Path",              "data": "src" },
            { "title": "Rating",            "data": "rating",                    "render": function ( data, type, row, meta ) {
                return '<div class="jrating" data-rating="'+data+'"></div>';
            }},
            { "title": "Play Count",            "data": "playcount",                    "render": function ( data, type, row, meta ) {
                return data;
            }},
            { "title": "Last played",            "data": "lasttimeplayed",                    "render": function ( data, type, row, meta ) {
                return data;
            }},

        ],
        paging: false,
        processing: true,
        deferRender:    true,
        info: false,
        bPaginate: false,
        searching: false,
        scrollX: true,
        scrollCollapse: true,
        scrollY:        '70vh',

        colReorder: true,
        rowReorder: false,


       //Disabled for now, doesnt seem to work when clicking on an artist
        // searchPane: {
        //     container: '#explore',
        //     insert: "prepend",
        //     columns: [2, 3]
        //     //threshold: 0.5
        // },

        data: tracks,

        "order": [[1, "asc"], [3, "asc"], [9, "asc"]],
    });

Also, 1) how can I make the table occupy all the available height? 2) any idea how I could make "server-side" rendering for sorting columns directly from the database on node-webkit? It probably should go through a function.

Thanks for tips and help!

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Enable paging and also the deferRender option. That should make a significant difference. At the moment you are drawing all 18000 rows into the document up front which is going to slow things done.

    Also, 1) how can I make the table occupy all the available height?

    See this blog post, or if you disable scrolling to improve performance use this one.

    2) any idea how I could make "server-side" rendering for sorting columns directly from the database on node-webkit?

    The ajax option can be given as a function. So as long as it handles the server-side processing request it should be possible to use server-side processing.

    Allan

  • extarysextarys Posts: 3Questions: 2Answers: 0

    deferRender is already set to true :/ but it doesn't seem to do a thing. I was thinking it would only render what was visible.

    Since I'm trying to mimic a music app, paging would break the desktop app feeling it is why I was relying on deferRender to solve the issue.

    Thanks for the other tips, a database call will be much faster to sort/search :)

    If you have any other tips for me go ahead :smile: and thanks for the hard work!

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    deferRender is already set to true :/ but it doesn't seem to do a thing. I was thinking it would only render what was visible.

    That is correct, but since you have disabled paging, every row is visible, thus it won't do anything. That's why I mentioned enabling paging.

    Allan

  • Loren MaxwellLoren Maxwell Posts: 382Questions: 93Answers: 10
    edited February 2018

    Have you looked at the Scroller extension?

    For 18,000 rows it seems Scroller with serverSide: true would be your best option.

    I use it for a table with 645,000 rows and it renders in just a couple of seconds.

This discussion has been closed.