ServerSide Processing not working

Xursian

Hello, I have successfully created a data table that works up to 10,000 rows quickly. However My database has over 200,000 rows that I need to display. The problem is for the life of me, I can not get this working. Could you please assist?
My table and my query are are in the same file (So i am not sure if this poses the issue).
Here is the current working code : (that we can't use due to the inability to display large amounts of records)
[code]
$('#example').dataTable( {
"bProcessing": true,
"bPaginate": true,
"bsortClasses": false,
"sPaginationType": 'full_numbers',
"aaData": tableData,
"aoColumns": j_cols
} );
[/code]
The j_cols and tableData are populated from the sql ran and are formatted as correct json.

Please help me to get the serverside processing working. Thank you
[code]
"bServerSide": true,
"sAjaxSource": "table.php",
[/code]

allan

Link to a test case, as noted int he forum rules please.

Allan

Xursian

Hello Allan,

Here is a test case to show you an example of my situation.
http://bitproject05.academic.rrc.ca/volunteer/tableTestCase.php?pageNum=0

This table starts with a limit of around 1000 and works okay up to around 50,000 records.

However, if you click "SORT" it will re-query the data table with 100,000 records and BREAKS.

I have no idea how to get this working on server side and have tried many things, I'm awaiting your guidance thank you.

allan

I don't understand what you mean by it re-querying the whole table - have you implemented server-side processing as described here: http://datatables.net/usage/server-side ? Are you using an SQL server to do the sorting?

Allan

Xursian

Yes we are sorting on the server with the SQL query, and we are using MS SQLSRV, Re-Quering the table was to show you that it is unable to process large amounts of records.

We have tried to implement server-side processing from that very page you linked, however with failure. We get nothing showing up, or a constant processing image. even if we set the limit to about 100 results it still hangs and doesn't populate.

I am looking for guidance on how to use it, i know you have a well documented page but I must be over looking or over complicating things. There is me and one other person tasked with this issue, and we have both yet to get it working.

Inside out PHP script however we have the html markup which hosts the table aswell, and the ajax source would link it it's own page that its called from, Could this be the issue?

allan

> SQLSRV

How are you doing the limit on the table? A key thing about server-side processing is that it operates on a "window" of the data - it should never be necessary for the PHP to see more rows than the display requires - all the others can stay in the database. Is that the case in your script? SQL Server is tricky since it doesn't support LIMIT and OFFSET, although there are sub-query workarounds or the new features of SQL Server 2013.

Allan

Xursian

I think the issue we're having is with the sEcho, what exactly should this contain. I see that our code has it holding a casted int offset value. we then are trying to pass in the "numberPerPage" as the limit value, so that we get specific results (one page only). the number of unfiltered results we are using also so that we can get the pagination working.

Here's an example of the subquery that allows us to query with offset and limits.
[code]
With DataTable AS (
SELECT LastName,FirstName,
ROW_NUMBER() OVER (ORDER BY LastName, FirstName) AS RowNum
From TableNameHere
)
SELECT *
FROM DataTable
WHERE RowNum >= @offset
AND RowNum < @offset + @limit;
[/code]

still, we are unable to display any rows (even a single row) when the "bServerSide": true.

I will be attempting another work around but I would really enjoy getting this feature working. I'll keep you posted if I find the answer, with the conclusion. If you have any other suggestions feel free to post them. Thank you for your efforts.

allan

> I think the issue we're having is with the sEcho, what exactly should this contain.

It should be what is sent to the server in the HTTP sEcho parameter. It's just a draw counter that DataTables uses, since Ajax responses can return out of sequence. The counter is used to ensure that only the page that has been requested latest is drawn - otherwise an old page of data might be drawn!

You should cast sEcho as an integer value, since it can only be an integer. This is for security - simply echoing back what the user has sent to the server is a security hole...

What does the JSON that your server-side is returning look like other than that?

Regards,
Allan

Xursian

The JSON returned is valid, but the sEcho is a ZERO.

[code]
{"sEcho":0,"iTotalRecords":38,"iTotalDisplayRecords":10,"aaData":[["Anderson","A..due",null,47,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Anderson","Sigfus",null,14,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bergson","John",null,43,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bergson","Margriet",null,24,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bergson","G","Gudberg",0,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bergson","H","Stefaan",10,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bergson","S","Oddny",7,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bolton","George",null,29,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bolton","Annie",null,25,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"],["Bolton","George",null,7,"Winnipeg","A-1","Ward 1","T-6435",1,"c ","CURRENT"]]}
[/code]

I should also mention that in firebug -> Net -> XHR -> Params -> the echo value is "sEcho 1"

the table loads if i take off server side processing and add that aaData to the param list, so i know the json website validated it correctly. The ajaxsource is purely based off (in fact is almost identical to your example) .

The problem still is the same thing, The serverside returns valid json, it queries and works on the server how it should, but when it comes to populating the datatable with the jsonabove, nothing shows up just the "processing..." which hangs.