Server-side processing doesn't seem to work

Peter.prc48

Hi,
I'm using Datatables.net (for about 2.5 years now - great product) but I'm having an issue with server-side processing.
I need to enable server-side processing as a page on my client's site is taking 3.5 minutes to 4 minutes to load! It's not a particularly large table (6572 rows, each with several drop-down lists).
It's already getting json data via an ajax call and if I add the ServerSide: "true" parameter to the datatable in the javascript file, when I check Chrome developer tools, the ajax request is made on the first call and all the data is loaded. Clicking on the table pagination buttons does not make a subsequent ajax call, which I would have expected. Instead, no call is made and the table is still functioning completely client-side.
I've trawled through the server-side documentation on your site and I expected also to see some of the sent parameters e.g. draw, start, length etc but none were sent in the initial call.

Ultimately, I'm trying to make the ajax call for only the 10 records viewable in the current pagination.

My questions are
1. Is the ServerSide: "true" and ajax: properties the only ones required to enable server-side processing?
2. Why does my page still behave as client-side processing?
3. With server-side processing enabled, will each click of a pagination control send the appropriate parameters to the api?

If I can't get this to work, my options are to send the parameters myself and implement pagination without a full data-load, or use Cache-Cow for client and server-side caching.

The site is behind a login but if you need to access it to see what's happening better, I can arrange a test account for you.

Thanks,
Regards,
John

kthorngren

Did you modify your server script to return the data as described in the Server Side documentation:
https://datatables.net/manual/server-side#Returned-data

Are you using the provided Datatables server scripts?

If your returned data doesn't contain the additional SSP parameters then I suspect all the rows will be displayed and behave like client side.

This SSP Example shows the basic JS config needed. Anytime the table needs redrawn (paging, searching, sorting) Datatables will send a request to the server. The server side code is then responsible for querying the data using the paging, sorting and searching parameters provided and responding with just those rows.

Kevin

kthorngren

Have you verified the problem is with the length of time it takes to get the data and the delay is not with the rendering?

If its a rendering problem you may want to look at '-option deferRender`.

Kevin

Peter.prc48

Hi Kevin,
Thanks for your prompt reply but it doesn't really answer my questions.
To answer the questions you posed:
1. Did you modify your server script to return the data as described in the Server Side documentation? - this starts by saying 'Once DataTables has made a request for data, with the above parameters sent to the server...' There are no parameters sent by datatables e.g. the start and length parameters which is probably why it returns the whole data set.
2. Are you using the provided Datatables server scripts? - I'm using C#, not PHP.
3. The SSP Example shows the basic JS config needed. - my JS contains the Processing, serverSide and ajax parameters. Also, the SSP Example server-side script does not appear to make any use of limiting factors such as start and length - it looks like its also returning the entire data set.

So going back to my original question: If I set serverSide:true, processing: true, ajax: <url>, why does my page behave like client-side? (I know its downloaded all the records and is probably best to then operate client-side but shouldn't it make a second request when page 2 is clicked? Surely, serverside:true is telling it to go back to the server?)
To quote from your Server-side processing page:
'When making a request to the server using server-side processing, DataTables will send the following data in order to let the server know what data is required:...draw, start, length, etc'
How can I make it call for page 1, 10 records only and then when the pagination 2 button is clicked it calls for page 2, the next 10 records.
I can probably manually code this and ensure the parameters are sent but I thought datatables would do this out-of-the-box with the appropriate server-side parameters set.

I've just checked out the deferRender option and its made little difference - because its still downloading the complete data set.

An analysis of the ajax request showed it was downloading 15.5 Mb with a Waiting (TTFB) of 8.43 seconds and content download of 2.6 minutes.
The latency of 8.43 seconds is consistent when running the database query alone using SQL Management Studio.

Regards,
John

kthorngren

Lets take a more detailed look at the example:
https://datatables.net/examples/server_side/simple.html

processing tells Datatables to show a "processing" indication when its fetching data. By default Datatables displays 10 rows. Change to a new page and you will see "Processing" displayed because its fetching the next page from the server.

I use Chrome so my steps will be specific to Chrome. I suspect other browsers will have similar features. On the example page open the developer tools and select the network tab. Reload the page. Find "server_processing....", forth from the bottom and click on it. This example uses HTTP Get so the parameters are in the URL. You can use POST instead.

If you are using Chrome you can scroll to the bottom of the headers tab. You will see a section called "Query String Parameters". In this section you will see all of the column search and sort parameters and the start of 0 and length of 10.

If in Chrome click the preview tab and you will see the response containing 10 rows of data and the total and filtered record count.

Go to the next page and in the headers tab you will see a start of 10 and a length of 10. In the preview tab you will see the 10 rows for the next page.

The "start" parameter is associated with the page the user is on. The length parameter is based on the page length selected in the "Show [10] entries" option. The search and sorting parameters sent to the server will be based on what the user is doing with these options.

At a minimum, if you are not concerned with searching and sorting, your C# script will need to parse the parameters sent and send a query to the database requesting the appropriate data. In some SQL type variations LIMIT and OFFSET (length and start respectively) can be used to narrow SQL request to the, in this case, 10 rows starting at page 0.

HTH,

Kevin

rf1234

"This example uses HTTP Get so the parameters are in the URL. You can use POST instead."
In my experience server side didn't work without POST. @allan commented that here: https://datatables.net/forums/discussion/comment/115951/#Comment_115951
Good luck! Roland

allan

It all depends on what the server-side is looking for. If it expects POST parameters, then yes, it needs to be POSTed. If it expects GET, then send GET.

Allan

Peter.prc48

Thank you all for commenting.
I can see using Chrome developer tools that the example page does use a GET and all the query parameters are evident in the url. However, my code also makes a GET request to my API controller and there is only one query pair added to the url which is:
?_=1502742757795 (the number changes with each new request (I think to make sure it doesn't get cached data)).
If I change it to a POST, the variables still aren't sent. As Allan says, it is expecting a GET and I use POST when editing or creating data.

allan

I think to make sure it doesn't get cached data

Correct. That's the jQuery anti-cache parameter.

Can you link to a page showing the issue, and also show your PHP script please?

Allan

Peter.prc48

Hi Allan,
I'm using C# and my api controller code is shown below. The site is password protected - if you want I could email you test account credentials.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net.Http;
using System.Web;
using System.Web.Http;
using DataTables;
using NLog;
using ProjectTracker.ActionFilters;
using ProjectTracker.Extensions;
using ProjectTracker.Models.Projects;
using ProjectTracker.Models.User;
using ProjectTracker.Properties;
using ProjectTracker.Utils;
using TGE.BusinessLayer.Caching;
using TGE.Security.Interfaces;

namespace ProjectTracker.Controllers
{
    [Authorize]
    public class ProjectsMasterController : ApiController
    {
        private static readonly Logger Log = LogManager.GetLogger("ProjectsMasterController");
        private const string Table = "Project";
        private const string SoftDeleteProjectSql = "UPDATE Project SET Deleted = 1 WHERE Id = {0}";

        private const string ClientSql = "SELECT Id AS value, Name AS label FROM Client WHERE CompanyId = {0} AND Deleted = 0 AND Active = 1 ORDER BY Name";
        private const string ProjectStatusSql = "SELECT Id AS value, Name AS label FROM ProjectStatus WHERE CompanyId = {0} AND Active = 1 ORDER BY Name";
        private const string ServiceTypesSql = "SELECT Id AS value, Name AS label FROM ServiceType WHERE CompanyId = {0} AND Active = 1 ORDER BY Name";
        private const string SalesStaffSql = "SELECT Id AS value, CONCAT(Firstname, ' ', Surname) AS label FROM SalesStaff WHERE CompanyId = {0} AND Active = 1 ORDER BY label";
        private const string UpdateUserProjectFilterSql = @"UPDATE UserProjectFilter SET ProjectStatusId = {0},
                ClientId = {1}, DivisionId = {2}, DateUpdated = GETDATE() ";

        private readonly Func<List<Dictionary<string, object>>> _ynDdl = DdlCache.GetYnDropDown;
        private readonly Func<List<Dictionary<string, object>>> _divisionsDdl = DdlCache.GetDivisionsDdl;


        public IHttpActionResult Get()
        {
            var data = Request.GetQueryNameValuePairs();
            return Process(data);
        }


        public IHttpActionResult Post()
        {
            var data = HttpContext.Current.Request.GetFormCollection();
            var user = User as IUser;
            if (user == null)
                return null;

            int companyId = user.CompanyId;

            if (data[0].Value == "create")
                data.Add(new KeyValuePair<string, string>("data[0][Project][CompanyId]", companyId.ToString()));
            return Process(data);
        }


        public IHttpActionResult Put()
        {
            var data = Request.GetQueryNameValuePairs();
            return Process(data);
        }


        [AccessLevel(AccessLevel.SubAdmin)]
        public IHttpActionResult Delete(string id)
        {
            var results = DatatablesHelper.GetNewResult();

            try
            {
                int projectId;
                if (int.TryParse(id.Substring(4), out projectId))
                {
                    var settings = Settings.Default;

                    string softDeleteProjectSql = string.Format(SoftDeleteProjectSql, projectId);
                    using (var db = new Database(settings.DbType, settings.DbConnection))
                    {
                        var response = db.Sql(softDeleteProjectSql);
                        results["data"] = response;
                    }
                }
            }
            catch (Exception ex)
            {
                string error = $"Error deleting Project with ID {id}";
                Log.Error(ex, error);
                results.AddErrorToResult(error);
            }

            return Json(results);
        }


        private IHttpActionResult Process(IEnumerable<KeyValuePair<string, string>> data)
        {
            var dataList = data.ToList();
            var settings = Settings.Default;

            var user = User as IUser;
            if (user == null)
                return null;

            UpdateUserProjectFilter(dataList, user.Id);

            int companyId = user.CompanyId;

            string clientsSql = string.Format(ClientSql, companyId);
            string statusSql = string.Format(ProjectStatusSql, companyId);
            string serviceTypesSql = string.Format(ServiceTypesSql, companyId);
            string salesStaffSql = string.Format(SalesStaffSql, companyId);

            int clientId = Convert.ToInt32(dataList.FirstOrDefault(x => x.Key == "ClientId").Value);
            DtResponse response;
            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var editor = new Editor(db, Table, "Id")
                    .Model<ProjectJoinModel>()
                    .Field(new Field("Project.ClientId")
                        .Options(() => db
                        .Sql(clientsSql)
                        .FetchAll()))
                    .Field(new Field("Project.ServiceTypeId")
                        .Options(() => db
                        .Sql(serviceTypesSql)
                        .FetchAll()))
                    .Field(new Field("Project.DivisionId")
                        .Options(_divisionsDdl))
                    .Field(new Field("Project.StatusId")
                        .Options(() => db
                        .Sql(statusSql)
                        .FetchAll()))
                    .Field(new Field("Project.SalesStaffId").SetFormatter(Format.NullEmpty())
                        .Options(() => db
                        .Sql(salesStaffSql)
                        .FetchAll()))
                    .Field(new Field("Project.Billable")
                        .Options(_ynDdl))
                    .Field(new Field("Project.OpenDate").GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy")))
                    .Field(new Field("Project.Revenue").SetFormatter(Format.NullEmpty()))
                    .Field(new Field("Project.CommissionPercent").SetFormatter(Format.NullEmpty()))
                    .MJoin(new MJoin("Task")
                        .Link("Project.Id", "Task.ProjectId")
                        .Model<ProjectTaskModel>()
                        .Field(new Field("EmployeeId"))
                        .Field(new Field("TimeIn"))
                        .Field(new Field("TimeOut"))
                        .Field(new Field("LunchTime"))
                        .Field(new Field("EmployeeHourlyRate"))
                        .Field(new Field("EmployeeOvertimeRate"))
                        .Field(new Field("DivisionOverheadRate"))
                        .Field(new Field("Deleted"))
                        .Where("Task.Deleted", 0))
                    .MJoin(new MJoin("Material")
                        .Link("Project.Id", "Material.ProjectId")
                        .Model<ProjectMaterialModel>()
                        .Field(new Field("Cost")))
                    .LeftJoin("ProjectStatus", "ProjectStatus.Id", "=", "Project.StatusId")
                    .LeftJoin("Client", "Client.Id", "=", "Project.ClientId")
                    .LeftJoin("ServiceType", "ServiceType.Id", "=", "Project.ServiceTypeId")
                    .LeftJoin("Division", "Division.Id", "=", "Project.DivisionId")
                    .LeftJoin("SalesStaff", "SalesStaff.Id", "=", "Project.SalesStaffId")
                    .Where("Project.Deleted", 0);

                response = clientId == 0 ? editor.Process(dataList).Data() : editor.Where("ClientId", clientId).Process(dataList).Data();

            }
            return Json(response);
        }


        private void UpdateUserProjectFilter(List<KeyValuePair<string, string>> dataList, int userId)
        {
            int projectStatusId = Convert.ToInt32(dataList.FirstOrDefault(x => x.Key == "ProjectStatusId").Value);
            int clientId = Convert.ToInt32(dataList.FirstOrDefault(x => x.Key == "ClientId").Value);
            int divisionId = Convert.ToInt32(dataList.FirstOrDefault(x => x.Key == "DivisionId").Value);

            string updateUserProjectFilterSql = string.Format(UpdateUserProjectFilterSql, projectStatusId, clientId, divisionId);

            string fromDate = dataList.FirstOrDefault(x => x.Key == "FromDate").Value;
            if (!string.IsNullOrEmpty(fromDate))
            {
                updateUserProjectFilterSql = updateUserProjectFilterSql + ", FromDate = '" + fromDate + "' ";
            }

            string toDate = dataList.FirstOrDefault(x => x.Key == "ToDate").Value;
            if (!string.IsNullOrEmpty(toDate))
            {
                updateUserProjectFilterSql = updateUserProjectFilterSql + ", ToDate = '" + toDate + "' ";
            }

            updateUserProjectFilterSql = updateUserProjectFilterSql + " WHERE UserId = " + userId + " ";

            var settings = Settings.Default;
            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                db.Sql(updateUserProjectFilterSql);
            }
        }
    }
}

allan

Sorry - C#, not PHP! Yes, if it would be able to send me login details, that would be great. You can PM me by clicking my forum user name and then the Send message button.

Thanks,
Allan

Peter.prc48

Hi Allan,

Thanks for the steer in the right direction. i knew javascript was case-sensitive but what I didn't realise was that the wrong syntax would mask the error. Focussing on the correct syntax for the server side variable i.e. serverSide: true highlighted the problem I was actually having was that the datatables query string exceeded the default maximum for IIS.
This was fixed by adding a section in my system.webServer node as follows:
<security> <requestFiltering> <requestLimits maxQueryString="6000"/> </requestFiltering> </security>

And I also had to add to my existing httpRuntime node the following parameter:
maxQueryStringLength="6000"

I chose this value because the query string was 3992 characters in length.

So now I can get my pages of 10 records.
Thank you so much.

Regards,
John

allan

Hi John,

Awesome - great to hear you have it working now.

Another possibility would have been to use post rather than get. Server's will typically accept much longer post body's than the query string by default.

Allan

filippoaceto

i write in this post.
I have a datatable server-side paginating but when i click next button or a page number, the page remail in processing... mode.
In the backend i see new data but not appear in the new datatable page.

allan

Sounds like you might have a Javascript error occurring on the page. We'd need a link to the page to be sure though.

Allan