Millions of Data loading problem
Millions of Data loading problem
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
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
This discussion has been closed.
Replies
There is some discussion of this in the documentation here: http://datatables.net/usage/#data_sources
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
Allan
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...!
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
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?
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.
> 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
[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
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 ]
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?
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.
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!
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.
[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?