Datatable taking long time for loading

anubhav1985

Hi Allan,

First of all, i would like to thank you for this wonderful tool of yours. It really made my life a hell lot easier.
I have a website which is using the Datatable component. I am very new to this entire framework and hence i might be facing a very trivial issue here but just wanted to ask everyone's opinion on it.

I am a ASP.Net developer and i am using datatables in .net.
Okay, so here is the problem. I have a very simple page with nothing on it except for a GridView which is being used by a Datatable to display data. This data happens to be coming from a SQL Server database which is hosted on one of the remote machines. The thing is that the data comes from the database pretty quickly however, the page takes a lot of time to render up. I should add that i am using one of the UI Themes called UI Darkness.

Now, my question is, that does it take this long to load some 9999 records on a page or am i missing something here? I do not want to go for Server Side as the maximum number of rows that can be on the page can be only 9999. I think Datatable is good enough to handle this much size atleast.
When i remove the datatable part of the code and let the gridview be loaded by itself, it hardly takes 10 seconds to display the gridview with the pagination and sorting in place. However, with Datatable, it is taking more than 5 minutes.

Can someone please let me know how i can speed up the rendering of the web page.

Regards,
Anubhav

allan

5 minutes! Wow! What web-browser are you using to test this (I'm a bit surprised that any browser would actually allow processing to go on that long without breaking out)? There is certainly no way that it should be taking that long to display 9999 rows. There are things which can take a while in DataTables (bSortClasses for example) and various other things, but I've had considerably more rows than that displaying in a much shorter time, even in IE.

So a couple of questions:
- Can you post a link to show this happening
- It would be worth 'profiling' in Firebug to see what is running show
- What is your initialisation code.

Regards,
Allan

anubhav1985

Hi Allan,

Thanks a lot for the quick reply!
I am using IE 6.0. I cannot incidentally host it up anywhere as i don't have any place to do that.
IE doesn't break up, it just hangs up for some time and after that it displays the results.
I would try 'profiling' the code on Firebug. Thanks a lot for that suggestion. However, as i do not really have any hands on experience with Firefox/Firebug it would be nice if you can tell me what exactly i should look out for.

I would post the initialisation code as soon as I go to my work place today.

Regards,
Anubhav

allan

There are a few posts around the web which consider profiling with Firebug for example: http://www.stoimen.com/blog/2010/02/02/profiling-javascript-with-firebug-console-profile-console-time/ . It is very much worth getting to grips with Firebug if you are doing some serious web-dev.

Having said that, I suspect the issue here is not going to be found with Firebug (although it might help with finding a few bits of code which can be optimised). If you use a browser such as Chrome or Firefox, how long does your page take to load? I'm going to guess a whole lot less than 5 minutes! I'd say the issue is actually the speed of IE6 and what it is being asked to do. Modern browsers should fly thought that, but there is a lot of DOM processing, and sorting (a real killer in IE6) which it will be very slow at dealing with.

If IE6 is a target browser for you, I'd very much suggest going with server-side processing. The little bit of effort needed to make it work will be worth it for the performance gains!

Regards,
Allan

anubhav1985

Hi Allan,

I will have a thorough look at the link suggested by you.

I am afraid to say that the target browser remains to be IE6. I am developing this as a proof of concept to be made to work on IE6 and hence cannot derail it from there.

I would have loved to go for Server-Side processing but there is one little catch with it. First off, my development environment is .Net 2.0 and i am hardly able to find any material for use in .Net 2.0 for Server-Side.
All the examples that i browsed through were using some or the other indispensable feature of the .Net 3.5 framework which cannot be replicated here in .Net 2.0.
Another problem is, that the application needs to have sorting (which will be limited to only a few columns) on the client side as it is today. We have the application up and running using a GridView in .Net 2.0 with appropriate styling used. Hence, the users are already too comfortable using the sorting on the client side. With Server-Side and even with Server-Side-pipelining method this becomes a bit painfully slow.

The only thing that i am unable to understand is that why the data is getting loaded in the GridView alone so quickly and why it takes a hell of a long time for IE6 to process the DataTable part.

I know this all would only make sense once i post the initialization code. I am going to go to the office and post the sample code as soon as possible for your and every body else's reference.

I just want to this to work. I know i can do it. It's just that i would be requiring some help from the experts like you and other forumers.

Regards,
Anubhav

anubhav1985

Hi Allan,

As promised, please find the code below. In the meantime, I would have a look at the Firebug and let you know in case i notice something there.

[code] $('#GridView1').dataTable({"iDisplayLength": 10,
"bJQueryUI": true,
"bAutoWidth": false,
"oLanguage": {"sSearch": "Filter records based on Column5 value:",
"sZeroRecords": "No records to display",
"sInfoFiltered": " - filtering from _MAX_ records",
"sLengthMenu": 'Display '+ '11'+ '21'+ '31'+ '41'+ '51'+ 'All'+ ' records'},
"aoColumns": [ { "bSearchable": false },{ "bSearchable": false },{ "bSearchable": false },{ "bSearchable": false },{ "bSearchable": true }],
"sPaginationType": "full_numbers"});
});
[/code]

Do let me know if you find something wrong with this code.

Regards,
Anubhav

anubhav1985

I opened the same page in Firefox and boom i got the pain point.
This is what Firefox told me after some 30 seconds of processing:-

A script on this page may be busy, or it may have stopped responding. You can stop the script now, or you can continue to see if the script will complete.

Script: http://localhost:3026/DataTablesJQuery/DataTables/js/jquery.dataTables.js:2604

The page gets loaded once I click on Stop Script. It seems that it breaks somewhere in the code because the page got loaded with everything else except for pagination and alternate coloring for each row. So it might be any one of these two getting hung in between.

Regards,
Anubhav

allan

There is nothing wrong with your initialisation code - nothing there that would cause this to take such a long time.

As I mentioned, I was surprised that IE didn't give a similar error as Firefox, I guess your IE install must have that option disabled (not unreasonable if you are using IE6 ;-) ). I am surprised that it is taking Firefox 30 seconds to do this though - having said that... I've just tried 10'000 rows on my computer and it takes Firefox 17 seconds, Safari 3 seconds. So yes, I guess IE would be suffering bad under that.

I'm afraid I really do think that this is a case for server-side processing. Even the 3 seconds in Safari I would consider to be unacceptably slow for an end user. If anyone has any suggestions for optimisation, then they are very welcome! I think it's just down to needing to process as much data as it is.

I know next to nothing about .NET, so I won't be able to help much, but I can certainly comment on what the expected interaction is if you decide to go that route.

Allan

allan

Looking at my own profiling in Firefox when I had my 10'000 rows, I wondered if I could improve the rather poor performance, and I think I rather have - but probably still not to the point where it will be acceptable in IE6. Actually I've not tested it in IE at all (no access atm) but if you are willing, it would be very interesting to know the results.

The following code change made Firefox go from 17 seconds to 5 on my computer, with most of the time now spent gathering data and adding the HTML for the options (which is slightly surprising) rather than 75% of the time in _fnDraw...

1.6.2 code
[code]
var nBody = oSettings.nTable.getElementsByTagName('tbody');
if ( nBody[0] )
{
var nTrs = nBody[0].childNodes;
for ( i=nTrs.length-1 ; i>=0 ; i-- )
{
nTrs[i].parentNode.removeChild( nTrs[i] );
}

/* Put the draw table into the dom */
for ( i=0, iLen=anRows.length ; i=0 ; i-- )
{
nTrs[i].parentNode.removeChild( nTrs[i] );
}

/* Put the draw table into the dom */
for ( i=0, iLen=anRows.length ; i

anubhav1985

Hi Allan,

Thanks a lot for your comments. Did you notice the error that Firefox showed to me. I guess it would not have been 30 seconds after all. It was my bad time keeping. It must have been around 10 seconds.

Do you have any special comments on that? It was on line number 2604 in the jquery.datatables.js file.

Is there a compatibility issue that i am facing between the jQuery setup and DataTables?
I have the latest version of DataTable, the latest version of jQuery with the latest version of Theme Roller installed.
Am i doing something wrong?

My data looks fine in the datagrid when it gets populated.

Regards,
Anubhav

anubhav1985

One more thing: Where is the code given by you supposed to be added/replaced?

Regards,
Anubhav

allan

Isn't the error in Firebug just the error that occurs where the script has caused Firefox to send up the 'slow script' warning? That's partly what made me look at the optimisation above. Line 2604 in 1.6.2 is a loop for removeChild() which was very slow in Firefox. Has that made any differences?

Allan

anubhav1985

Oh! I did not know that about Firebug. I thought that it was some general optimization code. I came back from work. I will look at it the first thing tomorrow and let you know if it has made any difference.

In the meantime, can you please answer my doubt about whether there is any problem with versions of the tools that i have, considering that i have the very latest versions from everywhere (Datatables, jQuery and Themeroller).

Regards,
Anubhav

allan

There is no known incompatibility between the latest version of DataTables, jQuery and ThemeRoller.

Allan

anubhav1985

Thanks a lot for that clarification Allan!

Regards,
Anubhav

anubhav1985

Hi Allan,

I made the change of code suggested by you but it did not work. Instead, now my web site has started giving me errors when i turn on the debugging option in IE. The error that it gives me is,
"Microsoft JScript runtime error: 'nTBody.parentNode' is null or not an object". This error came on the following line of code, [code] nBodyPar = oSettings.nTBody.parentNode, [/code]
Can you please let me know why this is happening?

Also, i tried removing the CSS of Theme Roller hoping that there might be some CSS issue but it did nothing. I am still facing the same slow loading speed.

P.S. --> Is there anyone out there who has working experience with Datatables and .Net? If yes, i would request some help!

Regards,
Anubhav

allan

Oops sorry - slight error in my back porting:

[code]
var
nAddFrag = document.createDocumentFragment(),
nRemoveFrag = document.createDocumentFragment(),
nBody = oSettings.nTable.getElementsByTagName('tbody'),
nBodyPar;

if ( nBody[0] )
{
nBody = nBody[0];
nBodyPar = nBody[0].parentNode;
nRemoveFrag.appendChild( nBody );
var nTrs = nBody.childNodes;
for ( i=nTrs.length-1 ; i>=0 ; i-- )
{
nTrs[i].parentNode.removeChild( nTrs[i] );
}

/* Put the draw table into the dom */
for ( i=0, iLen=anRows.length ; i

anubhav1985

Thanks a lot for the updated code Allan!

I used it and got an error message here:-
[code]nBodyPar = nBody[0].parentNode; [/code]
It said that 0.parentNode was null.

I made this change to the code:-
[code]nBodyPar = nBody.parentNode; [/code]
Now it is working as expected. In mozilla it takes a little less than 6 seconds for me to load 9999 records.

However, IE 6.0 is still a pain. It is taking beyond 5 minutes to load this page. It just hangs there forever it seems.

P.S. --> Any opinions from other IE developers are welcome! Please help!

Regards,
Anubhav

anubhav1985

And yeah, the page does not even load after 5 minutes of execution on IE 6.0. It just tends to hang the entire system.

Regards,
Anubhav