"414 Request URI too long" error when using large numbers of columns and performance issue(s)

AdamR

First off, really love the tools you have here and I'd really like to make them work to solve a business use for us.

Having said that, I have a question that may not be entirely in the norm of how DataTables is expected to work but I'd love to know if there is a solution that I am just not seeing.

We provide a large editable dataset (in terms of both X and Y axes) to users. Both axes are dynamic and can go as (vertically) deep as say 65 rows and as wide as 200 columns (the cells themselves can contain anywhere from a Yes/No to a 1k block). We are determined to break from the controls we have used in the past and go w/ a jQuery/Dojo solution and I like DataTables and the plugins available a lot from my preliminary testing.

In trying to use the sAjaxSource with our larger (WIDTH) dataset, I run into 414 errors (the Request URI is too long) where the base request is over 19,000 bytes to the Ajax Handler (Controller in our case). I am using the smallest id I can and if I just look at the size of the mDataProp elements portion of the query string alone, it's over 4200 bytes.

e.g.
[code]
$(document).ready(function () {
var oTable = $('#@gridId').dataTable({
"bServerSide": true,
"sAjaxSource": window.location.pathname+"_Data",
"bProcessing": true,
"sScrollY": "400px",
"sScrollX": "100%",
"bDeferRender": true,
"aoColumns": [
{ "mDataProp": "Benefit", "sWidth": "150px", "sHeight": "112px" },
@{
var i = 0;
string continuation = ",\n";
foreach (var plan in headersCollection) {
if (++i == headersCollection.Count) {
continuation = "\n";
}
{ "mDataProp": "p@plan", "sWidth": "180px", "sHeight": "112px" }@continuation
}
}
]
});
});
[/code]
* I'd post the query string but it's over 19k.


What I tried to do to address this was to use POST to get around the issue. This works however it doesn't seem to be deferring rendering. The total load time for the grid is something like 5-7 seconds before I start to see anything (other than some very badly formed table headers). Is this expected behavior or am I doing something incorrectly? I would have thought w/ the bDeferRender that only what is in the portal (+/- rows for buffer) will be loaded but due to the time to paint, I am thinking that isn't the case. Also, can the deferred rendering take into account Columns outside of the viewable area?

e.g.
[code]
$(document).ready(function () {
var oTable = $('#@gridId').dataTable({
"bServerSide": true,
"sAjaxSource": window.location.pathname+"_Data",
"fnServerData": function ( sSource, aoData, fnCallback ) {
$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
});
},
"bProcessing": true,
"sScrollY": "400px",
"sScrollX": "100%",
"bDeferRender": true,
"aoColumns": [
{ "mDataProp": "Benefit", "sWidth": "150px", "sHeight": "112px" },
@{
var i = 0;
string continuation = ",\n";
foreach (var plan in headersCollection) {
if (++i == headersCollection.Count) {
continuation = "\n";
}
{ "mDataProp": "p@plan", "sWidth": "180px", "sHeight": "112px" }@continuation
}
}
]
});
});
[/code]

Thanks in Advance.

allan

The first thing to do would be to use POST rather than GET - use the sServerMethod option to specify that.

Beyond that, you would need to use fnServerParams to remove the options that you don't need from being posted to the server, since DataTables doesn't provide an option to remove them any other way (this might be provided in future though!).

Allan

AdamR

Thank you for the quick response Allan, I wasn't aware of the sServerMethod option. Excellent.

On the time to paint and the initially squeezed looking table headers (until it fully paints), am I missing something that it is taking 5-9 seconds to start to paint the grid using what I have (removed the fnServerData, added the sServerMethod=POST). Looking at the call for the data itself, it appears to be in the .200 ms range. It "appears" that rendering it however is the big time block (unless I am missing something obvious).

Thanks

allan

I think this would probably need to be profiled to see where the time is being taken (the Chrome dev tools I find are best for that). I wonder if the large number of columns is hitting something sub-optimal in DataTables. Can you post a link to the page?

Allan

AdamR

Allan, unfortunately in this case, the code is just in dev on a local VM. I am new to using Chrome's debugging tools so I may be reading this (http://ipsolve.com/images/chromedebug-datatables.png) wrong. The actual time to hit the DataTables_Data directly is ~200 ms, so not sure how to read the durations here on either the Request or the XhttpRequest Ready State Change. On the former, I assume (might be way wrong) it's waiting on the latter. Any way to figure out which readyState makes up how much of that XHR ReadyStateChange timeline?

allan

You probably want to use the profiling tools of Chrome rather than just the net view. This video has an introduction to the profiling tools: http://www.youtube.com/watch?v=OxW1dCjOstE . How long does it take to render in Chrome though? It might be that you need to look at a profiling tool for IE if its only IE that is showing the problem.

Allan

AdamR

Allan, sorry for the very belated reply. I have pretty much thrown everything I can at trying to resolve the timing issue w/ our solution to no avail. The issues are with Chrome (and Firefox, IE was somewhat worse but at this point, I do not recall the gap). Where there appears to be some latency in the initial load, I am also burning a lot of cycles in post rendering processing; for clarification, as I said prior, this is dynamic in columns and rows and there are only two columns I know about at render time. We are using both row grouping and a frozen left hand column. At say 50 cols by 70 rows, this all works rather reasonably (maybe 1.5-3 seconds render time). When I bump it up to over 100 cols, the speed really starts to degrade and at the full set for my example (227 cols), it's about 30-33 seconds.

This brings me to a question for you: I have read on a couple of posts that the concept of dynamic column addition could be a conceivable plug-in in the future. If that is the case, would it be possible to have those columns added post-render in some sort of process fork where the grid is initialized by what columns are in the viewport and the rest are loaded dynamically in the background? Conceptually, is this something that is possible?

allan

Conceptually yes that would be a possibility the is opened up by allowing columns to be dynamically added.

I'm surprised that the performance is as poor as you note. Did you have a bash with the Chrome developer tools - does it show what is taking up the time?

Allan

AdamR

Ok, good to know.

As for the duration; I think it's what I am trying to do here (or am missing some key piece to my puzzle). I am creating an "fnDrawCallback" function inside a new FixedColumns call (per a workaround I found searching to address the Row Grouping in conjunction w/ Frozen Columns) in an "fnInitComplete" function call. This takes me from ~9 seconds processing the 67 (row) x 227 (col) to about ~31 secs.

ergo, this looks like:
[code]
"aoColumns": [
{ "mDataProp": "b", "sClass": "b" },
....
}
],
"fnInitComplete": function() {
new FixedColumns(oTable, {
"iLeftWidth": "225",
"sHeightMatch": "none",
"fnDrawCallback": function (left, right) {
.... creates a group row and a frozen column (fairly sure I got the initial code from here - http://www.datatables.net/release-datatables/examples/advanced_init/row_grouping.html)
}
}
}
[/code]

If I remove the fnInitComplete processing (so this is the ~9 second render), and I look at the CPU Profile on the page load, it shows ~67% (program) and the next big hit is a 27% jQuery.ajaxTransport.send.callback. This looking at the Tree (Top Down) view.

This is probably acceptable time-wise for us but unfortunately, what makes DataTables so attractive to us is that it can manage both a frozen column and row grouping at the same time with the inclusion of the fnDrawBack addition. Haven't found any other jQuery grids that do that. I just don't know if I can sell the 30 sec load time thus my interest in dynamically loading the columns not in the viewport on page render.

Thanks as always for your product and attention Allan,
Adam

allan

> FixedColumns

Due to the way FixedColumns works it is an absolute killer on performance. You've done the right thing in using sHeightMatch none, but all the same it needs to clone the nodes and that's some heavy DOM manipulation which goes on, and basically its quite expensive (as you are seeing). In fairness, 9s still sounds like a horribly long time - particularly in Chrome, so I think there might still be something gone on that we can work on.

It is possible for me to see the table in action so I can profile it myself and see where / if any improvements can be made?

Allan

AdamR

Sure, let me try get a generic variant of this out somewhere you can access it. It will probably be Monday morning before I can get to it.

Thanks,
Adam

AdamR

Allan, here's a genericized example - http://ipsolve.com/DataTablesSample/Review/DataTables/250/60/on

It seems to me that w/ the fnComplete on (the new FixedColumns call) and with the column count at the 250 (this closely approximates our uses at the extreme), it's significantly slower that you'd expect seeing it at the 100 column count. If there's some other way that I can address this w/out the new FixedColumn call to address the rows & columns, then our need to lazy load columns in the background becomes unnecessary. Hope this helps draw a picture of what I am seeing Allan.

Thanks as always,
Adam

allan

Hi Adam,

Thanks for the link - a profile in Chrome is showing me that about 60% of the time taken is spent calculating height and/or width's (it looks like widths in the scroll callback function). Since the table has to be redrawn for FixedColumns, this problem is exacerbated by the fact that it will occur on each draw. Possibly DataTables needs the equivalent of the FixedColumns sHeightMatch:none option and to set the columns to be a fixed width, which would dramatically improve the performance here I think.

I've marked this on my to-do list to investigate further and see about providing just such an option (there are downsides such as cells could possibly overflow, not its not a trivial change, but certainly one worth looking into with considerable thought.

Regards,
Allan

AdamR

Thank you for that insight Allan. I would be interested in such a chance should implement it. I am also up for helping if I can as well.

Adam