Delay in loading of datatables for 1,216 records using in Spring MVC
Delay in loading of datatables for 1,216 records using in Spring MVC
Asmita_h
Posts: 2Questions: 1Answers: 0
User has to wait for a minute till the data is loaded in the data table. While waiting user is unable to access any tabs
========================Here is my code for the jsp page============================================
var oTable=$("#lBookingsDatatable").dataTable({
"responsive": true,
"bLengthChange": false,
"bScrollCollapse": false,
scrollY: "300px",
scrollX: "100%",
scrollCollapse: true,
"bPaginate":false,
"sAjaxSource":"",
"bServerSide":true,
"bFilter":false,
"bInfo":false,
"bProcessing":true,
"bAutoWidth":false,
"bDeferRender": true,
"aaSorting": [[ 3, "desc" ]],
"aoColumns":[
{"sTitle" : "Transaction ID","mData":"transaction_id","bSortable":true,"bVisible":true,"sWidth":"5px" },
{"sTitle" : "First Name","mData":"fname","bSortable":true,"sWidth":"30px"},
{"sTitle" : "Last Name","mData":"lname","bSortable":true,"sWidth":"30px"},
{"sTitle" : "Title","mData":"title_name","bSortable":true,"sWidth":"30px"},
{"sTitle" : "Event Name","mData":"event_name","bSortable":true,"sWidth":"50px"},
{"sTitle" : "Status","mData":"booking_statusname","bSortable":false,"sWidth":"30px"},
{"sTitle" : "Email","mData":"reg_user_email","bSortable":true,"bVisible":true,"sWidth":"120px"},
{"sTitle" : "Mobile","mData":"mobile","bSortable":true,"sWidth":"30px"},
{"sTitle" : "City","mData":"city","bSortable":true,"sWidth":"30px" },
{"sTitle" : "State","mData":"state_name","bSortable":true,"sWidth":"30px" },
{"sTitle" : "Program","mData":"program_name","bSortable":true,"sWidth":"30px" },
],
"dom": 'Bfrtip',
"buttons": [
{
"extend":'excel',
"text":"Export To Excel",
"title":"Booking Details",
"className":'btn btn-default btn-xs',
exportOptions:{
columns: [ 0, 1, 2, 3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30]
}
}
],
"fnServerData":function(sSource,aoData,fnCallback)
{
var event_id=$("#event_id").val();
if(event_id !='')
{
aoData.push({"name": "event_name", "value": event_id} );
}
else
{
aoData.push({"name":"event_name","value":0});
}
var location_id=$("#location_id").val();
if(location_id !='')
{
aoData.push({"name": "location_name", "value": location_id} );
}
else
{
aoData.push({"name":"location_name","value":0});
}
var email_id=$("#email_id").val();
if(email_id !='')
{
aoData.push({"name": "email", "value": email_id} );
}
else
{
aoData.push({"name":"email","value":0});
}
$.ajax({
"dataType":"json","type":"GET","url":sSource,"cache":false,"data":aoData,"success":fnCallback,error: function(xhr, status, error) {
}
})
},
"fnFooterCallback": function ( nRow, aaData, iStart, iEnd, aiDisplay ) {
}
});
=========================SAJax Call home controller==========================================
List<Reg_user_booking_details> bookings = null;
Datatable datatable=new Datatable();
Dashboard_check lDashboard_check = new Dashboard_check();
int start=0;
int col=0;
int amount;
String dir = "asc";
String sStart=request.getParameter("iDisplayStart");
String sAmount=request.getParameter("iDisplayLength");
String sCol=request.getParameter("iSortCol_0");
String sdir=request.getParameter("sSortDir_0");
if (sStart != null) {
start = Integer.parseInt(sStart);
if (start < 0) {
start = 0;
}
}
if (sAmount != null) {
amount = Integer.parseInt(sAmount);
if (amount < 10 || amount > 50) {
amount = 10;
}
}
if (sCol != null) {
col = Integer.parseInt(sCol);
if (col < 0 || col > 6)
col = 0;
}
if (sdir != null) {
if (!sdir.equals("asc"))
dir = "desc";
}
boolean bSortable=Boolean.valueOf(request.getParameter("bSortable_"+col));
bookings=mDatatabledao.displayBookings( aColumns[col],
dir,
location_id,
event_id,
email_id,
status,
year,
from_date,
to_date,fname,
lname,mobile);
datatable.setAaData(bookings);
// datatable.setiTotalRecords(bookings.size());
Gson gson=new GsonBuilder().setPrettyPrinting().create();
String json=gson.toJson(lDashboard_check);
===============================================SQL Query and fetching list===================
" SELECT "
+ " COLUMN,COLUMN"
+ " ,COLUMN,COLUMN,"
+ " ,COLUMN,COLUMN"
+ " ,COLUMN,COLUMN,COLUMN"
+ " ,COLUMN,COLUMN"
+ " ,COLUMN,COLUMN"
+ " ,COLUMN,COLUMN,"
+ " FROM "
+ " TABLE "
+ " JOIN "
+ " TABLE "
+ " ON COLUMN=COLUMN "
+ " JOIN "
+ " TABLE "
+ " ON "
+ " COLUMN=COLUMN "
+ " JOIN "
+ " TABLE "
+ " ON "
+ " COLUMN=COLUMN "
+ " JOIN "
+ " TABLE "
+ " ON "
+ " COLUMN=COLUMN ORDER BY "+" "+col_name+" "+dir;
=================================List to fetch the data==========================================
List<Reg_user_booking_details> listContact = mJdbcTemplate.query(sql, new RowMapper<Reg_user_booking_details>() {
@Override
public Reg_user_booking_details mapRow(ResultSet rs, int rowNum) throws SQLException {
Reg_user_booking_details bookings=new Reg_user_booking_details();
Address_DAOimpl addressdao=new Address_DAOimpl(mDataSource);
List<Address> list=addressdao.get_address(rs.getString("COLUMN"));
if(!list.isEmpty())
{
bookings.setCOLUMN(list.get(0).getAddress_text().replace(",", ""));
bookings.setCOLUMN(list.get(0).getCity_name());
bookings.setCOLUMN(list.get(0).getState_name());
bookings.setCOLUMNe(list.get(0).getPincode());
}
bookings.setCOLUMN(rs.getInt("COLUMN"));
bookings.setCOLUMN(rs.getString("status"));
bookings.setCOLUMN(rs.getDouble("COLUMN"));
bookings.setCOLUMN(rs.getString("LOCATION_NAME").replace(",", ""));
bookings.setCOLUMN(rs.getString("EVENT_NAME").replace(",", ""));
bookings.setCOLUMN(rs.getString("COLUMN"));
bookings.setCOLUMN(rs.getInt("COLUMN"));
return bookings;
}
});
Please i need help as to why the datatables is taking a minute to load s@override
Any help would be appreciated
This discussion has been closed.
Answers
Happy to take a look at a page showing the issue so we can profile it and suggest where the slow down might be happening.
Allan
Thank you for replying the problem is in jsp page when a user clicks on the page its doesn't not allow them to click on tab till the data is loaded .I tried pagination and scroller but unfortunately it is not working it loads the entire data.
I'm afraid I would need a link to the page showing the issue to understand where the issue is. It might be in the amount of time that it takes the server to respond with the data, server bandwidth, or you might have hit a performance issue in DataTables. Without being able to see the page, I can't say.
Allan