Millions of Data loading problem

Edge

Hi,

I'm facing huge problem in loading and showing the server-side data. I have millions of data coming right from mysql database. I'm using symfony2 as a php framework. I would really appreciate your help in this regard.

Here is the sample code:

[code]

var oTable = $('#example').dataTable( {

//"bPaginate": true,
//"sPaginationType": "full_numbers",
//"bSort": false,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "localhost/myproject/module/data.php",

//"bDeferRender": true
} );

[/code]

Thanks & Kind Regards,
Eli

allan

You are using server-side processing, but with a txt file? Is the txt file being processed on the server-side, or is it just static with millions of rows? If its static, then bDeferRender will help a lot, but probably not enough, as you are asking the Javascript engine to do a lot of work with that many rows. Server-side processing tends to use an SQL database, which is tuned for exactly this kind of thing, to do the filtering / sorting etc and will cope fine with many millions of rows.

There is some discussion of this in the documentation here: http://datatables.net/usage/#data_sources

Allan

Edge

Hi Allan,

Thanks for your reply!

the sAjaxSource as a text file is just for sample code here, but I'm definitely using mySql database to grab those 4 million rows and that is why I am also using Server-Side Processing to populate those results.

I just tested that I can populate 50,000 rows in a good way with Client-Side processing, but when I do it with server-side, the results are being shown on the whole page without any sorting, filtering, paging etc... and even it goes time-out if the results goes near 100,000.

any suggestion and good example to handle the code with Server-Side processing along with mySQL database on the backend???



Regards,
Eli

Edge

just updated the sample code above!

allan

There is an example server-side processing script available here: http://datatables.net/development/server-side/php_mysql . Are you using a server-side processing script such as that? That should be able to populate a table from a database with millions of records with no problem. There is an example of it in action here (albeit with only 57 records in the database): http://datatables.net/release-datatables/examples/server_side/server_side.html

Allan

Edge

yes I'm using the same as given at : http://datatables.net/development/server-side/php_mysql

I'm using Symfony2 as a PHP Framework and MYSQL as database but I have to make a custom JSON format (for the DataTables to use it) under the templates of symfony2. and then this module response path have been set as :

[code]
"sAjaxSource" : localhost/Symfony/web/app_dev.php/project_bundle/getall"
[/code]

getall.html.php is the template have the JSON records upto 4 millions, such as below:

[code]
{

"sEcho": 1,

"iTotalRecords":20,

"iTotalDisplayRecords":10,

"aaData":[["2","AKAMAI TECHNOLOGIES","SE","Same","0","511",0,"ISP"],
["2","AKAMAI TECHNOLOGIES","SE","Same","0","511",0,"ISP"],
["2","AKAMAI TECHNOLOGIES","SE","Same","0","511",0,"ISP"],
....
}

[/code]

and the problem is DataTables page gets crashed after 100,000 records, and it even shows everything on the same page without sorting, filtering or paging...!

allan

> and the problem is DataTables page gets crashed after 100,000 records, and it even shows everything on the same page without sorting, filtering or paging...!

I don't quite understand I'm afraid - to DataTables it doesn't matter i you have 20 rows or 20 million since all the sorting, filtering and paging is done on the server-side. You should never have aaData being longer than what can be seen on a single page (which is 10 records by default).

How many records are you attempting to load into DataTables on each draw?

Allan

Edge

I mean the page on which the DataTables is added, that page keeps itself in loading status and crashed due to time limit of 30 seconds.

And all my data of 4 million records, is being shown in aaData. May be that is the reason. But I'm using "iTotalRecords":4,000,000 and "iTotalDisplayRecords":10.

Any clue how to resolve it?

fbas

sounds like your LIMIT clause is not working. Are you handling LIMIT in your server side script?

You should be catching iDisplayLength and iDisplayStart and forming a LIMIT clause based on that (i.e. pagination). [ lines 45-53 on http://datatables.net/development/server-side/php_mysql ]

Check the debugger or make some php output to verify that you are sending and receiving those params properly.

allan

> iTotalRecords
> iTotalDisplayRecords

Unless you have a filter applied, these should be exactly the same value (i.e. the total number of records in the data set).

What would concern me however is:

> And all my data of 4 million records, is being shown in aaData

That's not good! :-). If you paging length is 10, then aaData should only contain 10 records! As fbas says the LIMIT sounds a bit dodgy. I would suggest echoing out $sQuery just before it is executed and paste it into the forum so we can take a look, if it isn't obvious what is going wrong.

Allan

Edge

I'm using the symfony2 query:

[code]
$maxResults = "10";
$query = $em->createQuery("SELECT d FROM EDBBundle:Domains d ");
$query->setMaxResults($maxResults);
$entities = $query->execute();

[/code]

And yes I've also applied custom filter!

please also let me know:

1. if I put the LIMIT to the records, then "sPaginationType": "full_numbers" gives only one page whereas "iTotalRecords":4000000.

2. how the pagination will work and recieve the next 10 results?


regards,
Eli

fbas

I'm not familiar enough with symfony2's "query" object in order to tell you how to set up your limit query, but most databases let you specify a beginning point and a length. This is how the pagination of data will work.

from this link (http://groups.google.com/group/symfony2/browse_thread/thread/b6efd24dd618a602?pli=1) it appears you can specify:
$query->setMaxResults($maxResults) as well as
$query->setFirstResult($iDisplayStart)

[note: $iDisplayStart is sent to your server script as a parameter from DataTables.
if you're not already, you should be basing $maxResults on $iDisplayLength
see http://www.datatables.net/usage/server-side ]

Edge

You are saying absolutely right, this was the main problem in producing the right results... Wonderfull, Thanks for that!
The other thing is generation of pagination. How can send the iDisplayStart from DataTables to sever-side scripting from withing the page numbers or something like that?

fbas

DataTables already calculates the iDisplayStart offset for you based on the number of rows you show per page (iDisplayLength) and which page number you are on.

it sends iDisplayStart and iDisplayLength as parameters to the server side script.

see http://www.datatables.net/usage/server-side . the table on the left side describes the out-going parameters DataTables sends.

Edge

Now I'm able to fetch the rows and can see the generated pagination. Also have edited code according to iDisplayStart and iDisplayLength and even can see it in the Firebug under Firefox as an JSON Response, but the Grid doesn't load the new results onClick next page event, any idea what to do from here on?

I can use Teamviewer screensharing tool, to show you how it looks like in my machine,
my skype id is: ilyasiqbal

looking forward to hear from you!

fbas

When changing pages in DataTables, a new fnServerData function should be kicked off, making an ajax call to your server side script.

In the browser debugger, do you see a new ajax call going to the server? what params are being sent?

Upon receiving the response from the server, fnServerData calls the fnCallback passed into it (by datatables). Possible failures might indicate malformed JSON in the server response. Look at the debugger to verify the JSON - you can check if the JSON is valid by pasting it into jsonlint.com.

Edge

Below is the Response back from server within the debugger:

[code]
localhost/Symfony/web/app_dev.php/domains/getall?_=1317649554367&sEcho=2&iColumns=13&
sColumns=&iDisplayStart=40&iDisplayLength=10&mDataProp_0=0&mDataProp_1=1&
mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&mDataProp_5=5&mDataProp_6=6&
mDataProp_7=7&mDataProp_8=8&mDataProp_9=9&mDataProp_10=10&mDataProp_11=11&
mDataProp_12=12&sSearch=&bRegex=false&sSearch_0=&bRegex_0=false&bSearchable_0=true&
sSearch_1=&bRegex_1=false&bSearchable_1=true&sSearch_2=&bRegex_2=false&
bSearchable_2=true&sSearch_3=&bRegex_3=false&bSearchable_3=true&sSearch_4=&
bRegex_4=false&bSearchable_4=true&sSearch_5=&bRegex_5=false&bSearchable_5=true&
sSearch_6=&bRegex_6=false&bSearchable_6=true&sSearch_7=&bRegex_7=false&
bSearchable_7=true&sSearch_8=&bRegex_8=false&bSearchable_8=true&sSearch_9=&
bRegex_9=false&bSearchable_9=true&sSearch_10=&bRegex_10=false&bSearchable_10=true&
sSearch_11=&bRegex_11=false&bSearchable_11=true&sSearch_12=&bRegex_12=false&
bSearchable_12=true&iSortingCols=1&iSortCol_0=0&sSortDir_0=asc&bSortable_0=true&
bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true&
bSortable_6=true&bSortable_7=true&bSortable_8=true&bSortable_9=true&bSortable_10=true&
bSortable_11=true&bSortable_12=true
[/code]

I've also checked the JSON result at jsonlint.com, and it also returns the response a Valid result!

would it be possible for you to see the working app through teamviewer?