JavaScript data grid for millions of rows

Answer 1

I used jQuery Grid Plugin, it was nice.

Demos

Answer 2

best approach i could think of is by loading the chunk of data in json format for every scroll or some limit before the scrolling ends. json can be easily converted to objects and hence table rows can be constructed easily unobtrusively

Answer 3

I recommend the Ext JS Grid with the Buffered View feature.

http://www.extjs.com/deploy/dev/examples/grid/buffer.html

Answer 4

http://wiki.github.com/mleibman/SlickGrid/

"SlickGrid utilizes virtual rendering to enable you to easily work with hundreds of thousands of items without any drop in performance. In fact, there is no difference in performance between working with a grid with 10 rows versus a 100’000 rows."

Some highlights:

• Adaptive virtual scrolling (handle hundreds of thousands of rows)
• Extremely fast rendering speed
• Background post-rendering for richer cells
• Configurable & customizable
• Full keyboard navigation
• Column resize/reorder/show/hide
• Column autosizing & force-fit
• Pluggable cell formatters & editors
• Support for editing and creating new rows." by mleibman

It's free (MIT license). It uses jQuery.

Answer 5

Here are a couple of optimizations you can apply you speed up things. Just thinking out loud.

Since the number of rows can be in the millions, you will want a caching system just for the JSON data from the server. I can't imagine anybody wanting to download all X million items, but if they did, it would be a problem. This little test on Chrome for an array on 20M+ integers crashes on my machine constantly.

var data = [];
for(var i = 0; i < 20000000; i++) {
    data.push(i);
}
console.log(data.length);​

You could use LRU or some other caching algorithm and have an upper bound on how much data you're willing to cache.

For the table cells themselves, I think constructing/destroying DOM nodes can be expensive. Instead, you could just pre-define X number of cells, and whenever the user scrolls to a new position, inject the JSON data into these cells. The scrollbar would virtually have no direct relationship to how much space (height) is required to represent the entire dataset. You could arbitrarily set the table container's height, say 5000px, and map that to the total number of rows. For example, if the containers height is 5000px and there are a total of 10M rows, then the starting row ≈ (scroll.top/5000) * 10M where scroll.top represents the scroll distance from the top of the container. Small demo here.

To detect when to request more data, ideally an object should act as a mediator that listens to scroll events. This object keeps track of how fast the user is scrolling, and when it looks like the user is slowing down or has completely stopped, makes a data request for the corresponding rows. Retrieving data in this fashion means your data is going to be fragmented, so the cache should be designed with that in mind.

Also the browser limits on maximum outgoing connections can play an important part. A user may scroll to a certain position which will fire an AJAX request, but before that finishes the user can scroll to some other portion. If the server is not responsive enough the requests would get queued up and the application will look unresponsive. You could use a request manager through which all requests are routed, and it can cancel pending requests to make space.

Answer 6

Hi,

Disclaimer: i heavily use YUI DataTable without no headache for a long time. It is powerful and stable. For your needs, you can use a ScrollingDataTable wich suports

• x-scrolling
• y-scrolling
• xy-scrolling
• A powerful Event mechanism

For what you need, i think you want is a tableScrollEvent. Its API says

Fired when a fixed scrolling DataTable has a scroll.

As each DataTable uses a DataSource, you can monitoring its data through tableScrollEvent along with render loop size in order to populate your ScrollingDataTable according to your needs.

Render loop size says

In cases where your DataTable needs to display the entirety of a very large set of data, the renderLoopSize config can help manage browser DOM rendering so that the UI thread does not get locked up on very large tables. Any value greater than 0 will cause the DOM rendering to be executed in setTimeout() chains that render the specified number of rows in each loop. The ideal value should be determined per implementation since there are no hard and fast rules, only general guidelines:

• By default renderLoopSize is 0, so all rows are rendered in a single loop. A renderLoopSize > 0 adds overhead so use thoughtfully.
• If your set of data is large enough (number of rows X number of Columns X formatting complexity) that users experience latency in the visual rendering and/or it causes the script to hang, consider setting a renderLoopSize.
• A renderLoopSize under 50 probably isn't worth it. A renderLoopSize > 100 is probably better.
• A data set is probably not considered large enough unless it has hundreds and hundreds of rows.
• Having a renderLoopSize > 0 and < total rows does cause the table to be rendered in one loop (same as renderLoopSize = 0) but it also triggers functionality such as post-render row striping to be handled from a separate setTimeout thread.

For instance

// Render 100 rows per loop
 var dt = new YAHOO.widget.DataTable(<WHICH_DIV_WILL_STORE_YOUR_DATATABLE>, <HOW YOUR_TABLE_IS STRUCTURED>, <WHERE_DOES_THE_DATA_COME_FROM>, {
     renderLoopSize:100
 });

<WHERE_DOES_THE_DATA_COME_FROM> is just a single DataSource. It can be a JSON, JSFunction, XML and even a single HTML element

Here you can see a Simple tutorial, provided by me. Be aware no other DATA_TABLE pluglin supports single and dual click at the same time. YUI DataTable allows you. And more, you can use it even with JQuery without no headache

Some examples, you can see

• List item

Feel free to question about anything else you want about YUI DataTable.

regards,

Answer 7

dojox.grid.DataGrid offers a JS abstraction for data so you can hook it up to various backends with provided dojo.data stores or write your own. You'll obviously need one that supports random access for this many records. DataGrid also provides full accessibility.

Edit so here's a link to Matthew Russell's article that should provide the example you need, viewing millions of records with dojox.grid. Note that it uses the old version of the grid, but the concepts are the same, there were just some incompatible API improvements.

Oh, and it's totally free open source.

Answer 8

I suggest you read this

http://www.sitepen.com/blog/2008/11/21/effective-use-of-jsonreststore-referencing-lazy-loading-and-more/

Answer 9

Forget javascript libraries. Wrote your own. I suggest something that functions like twitter: click more button, and it appends more results to the end.

Using javascript, make Ajax call to the server, fetch the data, and append it to HTML page. After that - all html is there - it's up to your browser to do the scrolling.

Answer 10

I kind of fail to see the point, for jqGrid you can use the virtual scrolling functionality:

http://www.trirand.net/aspnetmvc/grid/performancevirtualscrolling

but then again, millions of rows with filtering can be done:

http://www.trirand.net/aspnetmvc/grid/performancelinq

I really fail to see the point of "as if there are no pages" though, I mean... there is no way to display 1,000,000 rows at once in the browser - this is 10MB of HTML raw, I kind of fail to see why users would not want to see the pages.

Anyway...

Answer 11

(Disclaimer: I am the author of SlickGrid)

Please see http://github.com/mleibman/SlickGrid/issues#issue/22 for an ongoing discussion on making SlickGrid work with larger numbers of rows.

The problem is that SlickGrid does not virtualize the scrollbar itself - the scrollable area's height is set to the total height of all the rows. The rows are still being added and removed as the user is scrolling, but the scrolling itself is done by the browser. That allows it to be very fast yet smooth (onscroll events are notoriously slow). The caveat is that there are bugs/limits in the browsers' CSS engines that limit the potential height of an element. For IE, that happens to be 0x123456 or 1193046 pixels. For other browsers it is higher.

There is an experimental workaround in the "largenum-fix" branch that raises that limit significantly by populating the scrollable area with "pages" set to 1M pixels height and then using relative positioning within those pages. Since the height limit in the CSS engine seems to be different and significantly lower than in the actual layout engine, this gives us a much higher upper limit.

I am still looking for a way to get to unlimited number of rows without giving up the performance edge that SlickGrid currently holds over other implementations.

Rudiger, can you elaborate on how you solved this?

UPDATE

This has now been implemented in SlickGrid. See http://github.com/mleibman/SlickGrid/tree/unlimited-rows.
Once this gets tested thoroughly it will be merged into the main branch.

Answer 12

I suggest sigma grid, sigma grid has embed paging features which could support millions of rows. And also, you may need a remote paging to do it. see the demo http://www.sigmawidgets.com/products/sigma_grid2/demos/example_master_details.html

Answer 13

I can say with pretty good certainty that you seriously do not need to show millions of rows of data to the user.

There is no user in the world that will be able to comprehend or manage that data set so even if you technically manage to pull it off, you won't solve any known problem for that user.

Instead I would focus on why the user wants to see the data. The user does not want to see the data just to see the data, there is usually a question being asked. If you focus on answering those questions instead, then you would be much closer to something that solves an actual problem.

Answer 14

Hi all,

I run into this problem myself and I was wondering if anyone has a workaround for this for jqgrid. Jqgrid is a much more complete plugin as a whole, with much better documentation and many more features. It has easy sorting, filtering resizing etc functionality and it seems to me a drag to switch to slickgrid and loose all this features. (I m not saying that you can't do all this in slickgrid, I m just saying it is does not seem so intuitive and it lacks of documentation).

What was the solution in slick grid that can not be implemented in jqgrid?

Than yo in advance.

Alex