Handle Large Number of Rows

[supery0ung]

Hi,

I am new to the FooTable. It is great and I like it very much
But I experienced one problem, when the number of rows is large, maybe hundreds or thousands of them, FooTable is not be able to handle them. It crashes.

So I wonder how do you guys do if you have more than 1000 rows, since it has no pagination.

Thank you.

supery0ung

[bradvin]

Hi

We do not ever show that many rows in the table and handle all pagination on the server side. Do you have to show 1000 rows in the table? Will the visitor ever scroll through that many rows?

[supery0ung]

Thank you for replying, in most case I don't need to show that much rows.
But sometimes I need to show some experiment data, so the user may wants to have a glimpse of the whole dataset, even it is tedious to see all of them.

[supery0ung]

I think I will set the max row to 500, thanks.

[ricardozea]

I agree with bradvin, scrolling through that sheer amount of data isn't the best experience.

If the clients need to have a "glimpse" then showing them a table of 1000 rows or more is certainly not a "glimpse". A glimpse would be a pie chart of that data for example.

Your issue is a content issue, not a script issue. You may want to revise the approach you're taking to display that experiment data and try something more linear like some sort of graph/pie, there are plenty of jQuery based plugins and scripts out there:

• jqPlot
• Sparklines
• Sparklines demo

Stackoverflow:

• How to create a pie chart with jQuery
• Pie chart with jQuery

[supery0ung]

Thank you rzea for the resources.

[KimberleyMeyer]

Are there any plans to add a scroll bar to footable? I have the need to limit the grid to a certain space and allow scrolling of the table separate from the page it lives on.

[ricardozea]

You can always wrap the table in a DIV and give that DIV a specific height and then overflow-y:auto;

[KimberleyMeyer]

rzea - Thanks for the suggestion. That does add the scroll bar, but the headers are also scrolling. I'm going to need a scroll bar on just the rows so the headers remain in view. If I come up with a solution, I'll share it.

[bradvin]

@syn4k The pagination add-on you see in FooTable now allows for pagination, but it is only client side. If you are dealing with a large amount of data, you should probably do your processing on the server and only send a small subset of that data to the client. I am not sure what you want me to show you

[zacheryrodgers]

I am having trouble finding more info on using FooTable for very large datasets. I am wanting to load ~300K items, but I know that loading them all at once would kill any browser. What I would like to do is feed FooTable the number of rows I will need so it can prep the pagination and then make the sort/search option run an ajax query so I can return the rows it will need. Is there any solution already available for this or am I going to need to build my own? Thanks.

[bradvin]

@Tarnic There is nothing built to handle this out of the box at the moment, but was the reason behind me building the event interception demo - to cater for this exact scenario

[mackhankins]

Possibly server side processing would be useful. Has there been any progress made here?

[bradvin]

Again, footable was never meant to be used with thousands of rows, and I do not think it should be used like that. Showing 1000's of rows to the user is just not useful in my honest opinion. Server side processing should be used to handle this.

Having said that, it does not make sense then to use the pagination and sorting add-ons when using server-side processing. That "state" should be handled by the developer using hidden inputs perhaps (or similar).

Things we are going to be working on in the future:

• reworking the footable core so that it is more streamlined and faster (as a result, this will make it handle more rows)
• include a server side demo (which will either use the event interception I speak about in the above comments, or not use any add-ons and just use plain ol' html)

[aboomer]

I am interested in the server-side demo (that you mention) to handle large data-sets. Can you give me an approx eta for this demo...are we talking days/weeks/months? Also, what server-side language do you anticipate using?

[bradvin]

@aboomer will prob not have anything out before the end of Feb.
When I do a server side demo, I will probably do 2 - one in PHP and the other in C#

[schnubor]

Hey,

1st of all thanks for footable, it's awesome and like most of the people here I'd like to use it with larger datasets/rows (e.g. 5k+). Are there any updates on the server side demo or eventually the mentioned core update?

[bradvin]

@schnubor sorry no updates yet - we are planning on a V3 complete rewrite which is going to start this month (May). One of the items on top of the list is good performance with LOTS of data (1000+ rows)

[schnubor]

@bradvin thanks for the info :) Managed to set up the server side solution myself for now and it works great. Really looking forward to V3 though

[aboomer]

Hi @bradvin, thanks again for such a great product. I was wondering if you had a rough ETA for V3? I'm currently using FooTable for a ~400 row table and hoping to improve the loading time. If V3 is still a while off I might look into a server-side solution.

[mommaroodles]

I am also having the same problem. I want to show the data from a db table - 4000 rows and even with pagination loading time is very slow.

[bradvin]

@aboomer I am hoping to release a V3 branch to test with by the end of the month, but it is VERY raw and still has issues. Perhaps the community can help...

[bradvin]

@mommaroodles 4000 rows will cause slow loading times. You might need to implement server side pagination

[puffster]

@bradvin I just wanted to thank you for FooTables, I think they are amazing! I was just curious how v3 was coming along with regards to working with a large amount of records. I'll try to stay on point, but want to explain my background...I work for the public school system in Louisville, Ky and just started back into web development about a year ago (until then I'd mostly be working on the database side of things). My skills are rusty, and one of my first tasks was/is to create a website that will allow school admins to log on and review test results. Generally they will pick a single school and want to load all grades for that school, which averages around 1,200 records. At that point, they generally will a) search for a specific student's record or b) sort the records based on test score from highest to lowest so they can create various stats. I'm using a combination of asp.net (VS 2012), html5, and bootstrap and for the most part it works great. I stumbled onto FooTables when looking for a solution that will collapse my tables into a mobile or tablest view...and found like I hit the jackpot when I saw it's filtering & sorting capabilities as well as the awesome mobile views. I've implemented it and during my testing it worked great; however, when I recently published the site, that's when I discovered how people were going to be loading everything at once. Since I'm still getting my dev feet back underneath me, I never even realized there could be an issue with loading a large amount of records. In reading through the posts above, I now realize that FooTable might not be the solution I need. If you agree, could you help a brother out and point me to a solution that you think would be more in line with what I need?

Again, thanks for your efforts and sharing them with the rest of us!!

[bradvin]

@puffster you are in luck - the V3 branch of FooTable is available and in that branch we have a few demos handling 10,000 rows of data! Both via AJAX and inline - the plugin has basically been rewritten from the groud up and is gonna be amazing when it is officially released. Right now we are working on docs and demos etc, but it still works and you can try it out please and let me know how it goes

[bradvin]

Yes, you heard right - TEN THOUSAND ROWS of data!!!!

[puffster]

Brad!! This has been amazing!! I'm well into implementing it and I think it's going to work fantastically. From what I've discovered so far, the AJAX version seems to be much, much faster than the inline version if you need to go above 10,000 rows (my worst case scenario would be a district administrator that wants to pull in all high school students, about 29,000 students total). Inline seems to stall out after 10k -- I don't know the exact threshold where it chokes out, but it's somewhere between the 10k-29K figure. AJAX, however, seems to take the 29K, smiles, and asks for more. It's taking next to no time to come back.

This is slightly off topic, but you may be able to help me. I'm working on creating dynamic columns -- I give my users the option to choose which columns they want/need to see on the report. So a report could have anywhere from 8 up to 22 columns on it. I'm working off your FAjax example, where you have the columns hard-coded as a default value like this:

FAjax.defaults = {
    columns: {
        0: { name: 'lastName', title: 'Last Name' }, 
        1: { name: 'firstName', title: 'First Name' },
        2: { name: 'grade', title: 'Grade', type: 'number' }
    },

I've created a webmethod that queries my database for the needed columns, and I assumed I would just create these columns as a multidimensional array and return it as a JSON object. However, all my attempts to do this have not created a JSON object in the format you have. Have you tried creating the columns dynamically? Is there some other way I could/should be doing that? As I said I know it's a little off topic, but it never hurts to ask :)

[bradvin]

@puffster - thanks for the great feedback, but the credit goes to @steveush on V3. He should also be able to help with the dynamic columns questions you are asking

[puffster]

any help i could get from @steveush or anyone else would be very much appreciated. I've been working this over the past five days, and today I got it to where I thought I was recreating the hard-coded solution to the letter, but it's still not working.

I'm calling a web method that's generating my columns:

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]

public class eoc : System.Web.Services.WebService
{
    [WebMethod]
    public List<eocColumn> getEOCColumns()
    {
        formValues fv = new formValues();
        string pPersonID = fv.personID;
        List<eocColumn> eocCol = new List<eocColumn>();

        string mySQL = "exec get_EOCTableHeaders2 @pPersonID";
        string cf = System.Configuration.ConfigurationManager.ConnectionStrings["DistrictAssessmentDWConnectionString"].ConnectionString;

        SqlConnection connection = new SqlConnection(cf);
        SqlCommand command = new SqlCommand(mySQL, connection);

        command.Parameters.AddWithValue("@pPersonID", pPersonID);

        connection.Open();

        SqlDataReader dr = command.ExecuteReader();

        int ct = 0;
        while (dr.Read())
        {
            eocColumn eCol = new eocColumn();

            eCol.name = dr.GetValue(0).ToString();
            eCol.title = dr.GetValue(1).ToString();
            eCol.type = dr.GetValue(4).ToString();

            eocCol.Add(eCol);

            ct++;
        }

        connection.Close();

        return eocCol;
    }
}

}

On the client side, my aspx script looks like this - you can see that I've tried different things to get this to work, the uncommented "columns" is the closest so far:

<script>
    jQuery(function ($) {
        $('#table_1').footable({
            ajaxEnabled: true,
            ajax: FAjax.request,
            sorting: {
                enabled: true
            },
            paging: {
                enabled: true,
                total: FAjax.defaults.rows.length,
                size: 15,
                current: parseInt(FooTable.utils.getURLParameter('page')) || 1
            },
            filtering: {
                enabled: true
            },
            columns: FAjax.defaults.columns
            //columns: FAjax.defaults.dsCols().done(function (columnResults) {
            //    var loc = columnResults.d;
            //    var colIndex = new Object;
            //    var col = new Object();

            //    for (i in loc) {
            //        col = loc[i];
            //        colIndex[i] = col;
            //    };
            //}),
        });
    });
</script>

My modified FAjax.js looks like this:

FAjax.eocColumns = function () {
    return $.ajax({
        type: "POST",
        contentType: "application/json; charset=utf-8",
        url: "WebServices/eoc.asmx/getEOCColumns",
        data: "{}",
        dataType: "json"
    })
};

FAjax.defaults = {
    dataSource: FAjax.eocResults,
    rows: {},
    columns: {},
    dsCols: FAjax.eocColumns,
    //columns: {
    //    0: { "__type": "DMC.AE.WebServices.eocColumn", name: "locationAlias", title: "School Alias" },
    //    1: { "__type": "DMC.AE.WebServices.eocColumn", name: "lastName", title: "Last Name" },
    //    2: { "__type": "DMC.AE.WebServices.eocColumn", name: "firstName", title: "First Name" }
    //},
    //columns: FAjax.eocColumns().done(function (columnResults) {
    //    var loc = columnResults.d;
    //    var colIndex = new Object;
    //    var col = new Object();

    //    for (i in loc) {
    //        col = loc[i];
    //        colIndex[i] = col;
    //    };

    //    console.log(colIndex);
    //    FAjax.defaults.columns = colIndex;
    //    columnResults = colIndex;
    //}),
    sorters: {
        text: function (a, b) {
            if (typeof (a) === 'string') { a = a.toLowerCase(); }
            if (typeof (b) === 'string') { b = b.toLowerCase(); }
            if (a === b) return 0;
            if (a < b) return -1;
            return 1;
        },
        number: function (a, b) {
            return a - b;
        }
    }
};

FAjax.isFiltered = function (query, text) {
    var queries = query.split(' '), count = queries.length;
    for (var i = 0, len = queries.length; i < len; i++) {
        if (text.toUpperCase().indexOf(queries[i].toUpperCase()) >= 0) count--;
    }
    return count > 0;
};

FAjax.request = function (data) {
    return $.Deferred(function (d) {
        FAjax.defaults.dsCols().done(function (columnResults) {
            var loc = columnResults.d;
            var colIndex = new Object;
            var col = new Object();

            for (i in loc) {
                col = loc[i];
                colIndex[i] = col;
            };

            FAjax.defaults.columns = colIndex;

            console.log(FAjax.defaults.columns);

            FAjax.defaults.dataSource().done(function (rowResults) {
                FAjax.defaults.rows = rowResults;

                var rows = JSON.parse(JSON.stringify(rowResults.d));

                if (data.filtering && data.filtering.query) {
                    var i, text, len = rows.length, remove = [];
                    for (i = 0; i < len; i++) {
                        text = '';
                        for (var j = 0, column; j < data.filtering.columns.length; j++) {
                            column = data.filtering.columns[j];
                            text += ' ' + ($.isFunction(column.formatter) ? column.formatter(rows[i][column.name]) + '' : rows[i][column.name] + '');
                        }
                        if (FAjax.isFiltered(data.filtering.query, text)) {
                            remove.push(i);
                        }
                    }
                    remove.sort(function (a, b) { return a - b; });
                    len = remove.length - 1;
                    for (i = len; i >= 0; i--) {
                        rows.splice(remove[i], 1);
                    }
                }
                if (data.sorting && data.sorting.column && data.sorting.direction) {
                    var sorter = $.isFunction(FAjax.defaults.sorters[data.sorting.column.type]) ? FAjax.defaults.sorters[data.sorting.column.type] : FAjax.defaults.sorters.text;
                    rows.sort(function (a, b) {
                        return data.sorting.direction == 'ASC'
                            ? sorter(a[data.sorting.column.name], b[data.sorting.column.name])
                            : sorter(b[data.sorting.column.name], a[data.sorting.column.name]);
                    });
                }
                var total = rows.length, result = {};
                if (data.paging) {
                    var start = (data.paging.current - 1) * data.paging.size,
                        end = data.paging.current * data.paging.size > rows.length ? rows.length - 1 : data.paging.current * data.paging.size;

                    if (total > data.paging.size) rows = rows.slice(start, end);
                    result.paging = { total: total };
                }
                result.rows = rows;
                d.resolve(result);
            });
        });
    });
};

If you look at both objects in the console log, they appear to be identical.

The only guess I have, is if the column field on the .aspx page is being loaded with empty values before the FAjax.defaults.column field is getting loaded?

[bradvin]

@puffster why are you loading the columns with an ajax call? Why not output a columns variable to the page and then reference that variable from the footable init call?

If you want to keep the ajax call to get the columns, then I would suggest doing that first before you init footable, then on the ajax success, call the code you need to init footable passing in the data returned.

[steveush]

@puffster Brad's comment above is exactly what I was going to reply except I would add that the fajax.js file can be looked at to see how it works but it should not be included in your own site. It was created to basically help me create the demo pages, picture it as a dummy server, the operations it is performing should be done server side. I also updated the RequestData and ResponseData objects for V3 to be flat objects (no nested properties) to make them easier to handle on the server for ajax operations and added a short tutorial on what is required to get ajax working with FooTable. If you look in the docs folder the tutorial-setup-ajax.html now contains a few steps to help guide you. I'll get around to fleshing out the docs shortly, I've just had a very busy last few weeks finishing off renovating my house and now moving into it in the next few days.

[puffster]

Again, I can't thank you all enough for taking the time to help. To be honest, I've been out of the development game for a few years now and am trying to feel my way back into it -- I've been stuck doing sql and sharepoint/ssrs development and my web skills are quite rusty.

I'm loading the columns and rows the way I am because it is significantly faster than any other way I experimented with. I'm using asp.net for my "foundation", and when I tried to do a load into a gridview (again using my "worst case scenario" of around 29K rows, it was taking about 27 seconds. Doing it this way, it comes back in about 12 seconds. I wouldn't be surprised if I'm doing something incorrectly, but for the most part it seems to be working.

I read both of your comments and have been mostly successful in getting everything working now. The only thing I'm still having issues with is the filtering...but I suspect it is because I'm still using your request method for doing the filtering/sorting/paging (although it sorts and pages like a champ!). I apologize for being so dense -- I understand that you are saying it's only mimicking what a server should be doing, so does that mean I need to rewrite these methods in my server side code, send it the recordset (or request data), let the server manipulate it, and then have it send back the reworked dataset? If that's the case, then I'm not understanding what the javascript filtering/sorting/etc files are for?

Again, if you all are too busy to help, I completely understand -- I'll just keep muddling through it and I'm confident I'll eventually have that "a-ha" moment :). By the way - @steveush -- congrats on the house renovation -- there's nothing as satisfying as doing some house upgrades!!

[steveush]

@puffster You are understanding correctly, the code should be moved to the server side. The files for sorting, paging and filtering are mainly used if you use the inline or option methods to populate your table and when using the ajax method they mainly construct the request data object but also have client side functions. When using a large dataset like yours for best results I would have my server do the filtering, paging and sorting within the database query. So I would write a stored procedure that can handle the properties of the request data (page number, page size, sort column, sort direction and search filter) and filter the rows using the WHERE clause. The result returned from the stored procedure would be just the number of rows specified using the page size option and a count of the total rows available (Check the ResponseData object). This way you are only serving a small subset of rows to the client at a time speeding up performance.

[armsandlegsltd]

Hi,
I have read the last 12 months comments from where it started talking about large datasets.

I am in the unfortunate position of having a project land in my lap where the developer used footables.
All his code is hardly altered example code (so not named in any useful way for the app) with only minimised JS includes. He doesn't seem to have set any defaults (that i have found yet).... and .... this is the killer.....

with ONLY 1500 records (of maybe 10 fields max), this script is un-usable - pagination that can't cope with 100 pages?

I have spent the last 2 days just trying to find out where in the code the pagination is happening, but like so many JS frameworks, there is a million included files, most of which are minimised.

So, can you restore my faith in humanity and tell me about how to dis-entangle this mess so we can just use plain old PHP pagination with footable. I don't want to have to re-write all his work.

Is there a V3 or the update that has been talked about for quite a long time now?

Thanks,
Jeremy

[steveush]

Hi Jeremy,

If you actually look at this repo itself the readme specifies that it is now on V3. I must say though that even the old V2 should have handled 1500 rows. Either way please take a look at the V3 docs found here. One thing to note is that since the above comments were made the way FooTable V3 handles paging, sorting, etc. was changed away from the server side model it had back to an all client side solution as having to write server side code to handle the requests was generally out of the ability/care of users wanting to use FooTable. If you want to handle 20000+ rows then I suggest looking at a plugin like DataTables.

Also the script is provided in a variety of ways (minified, combined, separate individual components). How the previous developer choose to implement it was completely up to him, the minified script was used as it was probably the production code you have inherited and scripts should be minified in production code. The combined (un-minified) code can be used to debug issues but serving minified code drastically reduces file size and hence bandwidth and page load speed.

Unfortunately there is no upgrade path from V2 to V3 as it is drastically different code but that said implementing V3 is a lot easier and the plugin itself can handle far more rows now than V2 could. In the docs linked above there are multiple examples of how to create tables using FooTable.

Thanks