Using serverside Processing for more than 1 lac records is still failing.How do i resolve the issue?
Using serverside Processing for more than 1 lac records is still failing.How do i resolve the issue?
chaturvedi_anshumaan
Posts: 25Questions: 5Answers: 0
First i would like to put the dataTable initialisation code. and I will every possible details. It is like literally now a cry for help.
Below is the Datatable initialisation which gets executed when the control goes into success property of another ajax call. That Ajax call, in particular , is just fro submitting data to Stored Procedure. (I will put the code behind also). pLEASE READ TILL END. I have removed all unnecessary code.
function initializeDataTable(data) {
$('#tblDiscount').DataTable().clear();
$('#tblDiscount').DataTable().destroy();
var table= $('#tblDiscount').DataTable({
serverSide: true, // true
processing: true,
responsive:true,
ordering: false, // false
searching: false, // false
lengthChange: false, // pagesize
paging: true,
deferRender:true,
footer: true,
columnDefs: [{ targets: [14, 16, 17], className: 'dt-body-right' },
{ targets: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15], classname: 'dt-body-left' }
],
//searching: true,
dom: 'frBtip',
async: false,
ajax: function (data, callback, settings) {
var skip = 0;
if (data.start == 0)
skip = 0;
else
skip = data.start + 50; //data.length;
$.ajax({
url: '../Reports/_GetOnScrollData',
data: {
skipRows: skip,
limit: 50 //data.length
},
datatype: "Json",
async: false,
success: function (dataset) {
for (var i = 0; i < dataset.res.length; i++) {
out.push([dataset.res[i].state, dataset.res[i].zone, dataset.res[i].amro,
dataset.res[i].StoreCode, dataset.res[i].OfferType, dataset.res[i].DiscountType,
dataset.res[i].OfferNo, dataset.res[i].Description, dataset.res[i].ReceiptNo, dataset.res[i].TransDate,
dataset.res[i].Category, dataset.res[i].ItemCode, dataset.res[i].LotNo, dataset.res[i].ExpirationDate,
dataset.res[i].Quantity, dataset.res[i].FreeItem, dataset.res[i].NetValue, dataset.res[i].Discount]);
}
},
error: function (jqXHR, textStatus, errorThrown) {
if (jqXHR.status == 500) {
alert('Internal error: ' + jqXHR.responseText);
} else {
alert('Please revise your FILTER selection as the \n' +
'information You are fetching has more than 1 lac Records');
location.reload(true);
}
} //error closed
}).done(function (response) {
callback(
{
draw: data.draw,
data: out,
recordsTotal: parseInt(countRecords),
recordsFiltered: parseInt(countRecords)
});
}).fail(function (err) {
console.error('error...', err)
})
}, //end of ajax call for controller
scrollY: 500, // div height
scrollX: true,
scroller: {
loadingIndicator: true
},
footerCallback: function (row, data, start, end, display) {
var api = this.api()
var json = api.ajax.json();
$(api.column(17).footer()).html(totalOfDiscount);
$(api.column(16).footer()).html('Total:' + totalOfNetValue);
$(api.column(14).footer()).html('Total:' + totalOfQuantity);
}
}); //end of ajax call for scroller
}
Now as for the Controller action method :
public ActionResult _GetOnScrollData(int skipRows, int limit)
{
List<DiscountGrid> res = new List<DiscountGrid>();
try
{
if (skipRows == 0)
{
Session["TotalList"] as List<DiscountGrid>=res
res = res.Skip(skipRows).Take(limit).ToList();
}
else if(skipRows!=0)
{
res = Session["TotalList"] as List<DiscountGrid>;
res = res.Skip(skipRows).Take(limit).ToList();
}
}
catch (SqlException ex)
{
throw ex;
}
return Json(res,JsonRequestBehavior.AllowGet);
}
So as far as records of 1k or 10k is fine. It is showing up in Datatable grid. But as soon as the records count gets beyond 50K the control flow of execution instead of going to success property , it gets hit in error property of the ajax call.(The call which goes to /controller=Reports/Action=_GetOnScrollData. I have no idea what is causing this issue. I have tried many options and premutation. I even reduced the ajax call in the above function to one(removed the following)
$.ajax({
url: '../Reports/_GetOnScrollData',
data: {
skipRows: skip,
limit: 50 //data.length
},
For reference i used the example used in ServerSide Processing for 5,000,000 records given by @allan . i HOPE SOMEONE JUST RESPONDS.
Answers
You've got nested Ajax calls there, which is pretty odd. If you've got
serverSide, then generally all you would do is expect to get the records to be displayed on that page.What's purpose of the Ajax call within the ajax method? Can the server just return the expected data as in this example here?
Colin
Actually if you remember Colin, i asked a question what is this data.draw and callback do.. If you look into Allan's example of Server side processing of 5,000,000 records i saw this line,
ajax: function (data, callback, settings) (LINE number 23 here. in the first code snippet). It had some different meaning . So i thought i would have to nest ajax to make sure a call goes to controller. I just referenced that example and frankly did so, without understanding,What am i doing. So I had nested ajax call.
The thing is i just want the data to be loaded OnScroll,whether it the number of records is 50K or 5,000,000. The JSON data i am getting from controller no problems there. (M good with MVC). So what do i do? Shud i remove the nested ajax.?
If so, how will my code be to display data along with footer (Footer i need to dsiplay total of integer based columns), ?
Trust me if you help me understand thsi much then i can contribute a valuable post on this forum.
Have you looked at Scroller - there's an example there of that.
Colin
Yes i have Colin. Okay here is my specific question .. In this line,
ajax: '/api/data',
what excatly do i put. My data is coming in JSON format. It is coming from an action method from Controller.
Ideally you would just put the address of your controller. The controller then sends the JSON formatted data in expected format. The server should be sending the expected data, then it wouldn't need any manipulation.
Colin
You mean that stored Procedure from where my data is coming should send chunks of 100 records. ?? Is it something like this,that for every call 100 rows should come by executing stored procedure?
@colin : Do you think the store procedure should include paeIndex adn parameters like that so that only ertain number of records are shown?
For server side processing, data is always sent in chunks, the chunks being the page size. I'd suggest reading up on it, it seems like you need to go back to basic to get that level of understanding. The protocol is discussed here. Also see examples here.
Cheers,
Colin