jQuery DataTable and MVC server side processing

Question

So I have implemented Server-Side processing in MVC for jQuery Datatables, I am Showing 1 to 10 of 8,037 entries (with pagination). However when I navigate to the page with the table the first load takes far to long (5 to 10 seconds). In my controller I set it to only take 10 records (iDisplayLength):

        var displayedProviders = filteredProviders
            .Skip(param.iDisplayStart)
            .Take(param.iDisplayLength);

So when I first navigate to the page with the table or I use pagination it should only load 10 records at a time.

Can anyone guide me to why the first load is taking such a long time? My fear is that its loading the entire table into the Json Request which is slowing it down at first. This wasnt what I had in mind when implementing server side processing. Is there a way around this? If I implement:

    var model = _db.CareProviders
.OrderBy(row => row.ProviderId).Skip((pageNumber - 1) * pageResults)
    .Take(pageResults).ToList();

This will only show 10 results and no more?

Model:

    public class jQueryDataTableParamModel
    {
        /// <summary>
        /// Request sequence number sent by DataTable,
        /// same value must be returned in response
        /// </summary>       
        public string sEcho { get; set; }

        /// <summary>
        /// Text used for filtering
        /// </summary>
        public string sSearch { get; set; }

        /// <summary>
        /// Number of records that should be shown in table
        /// </summary>
        public int iDisplayLength { get; set; }

        /// <summary>
        /// First record that should be shown(used for paging)
        /// </summary>
        public int iDisplayStart { get; set; }

        /// <summary>
        /// Number of columns in table
        /// </summary>
        public int iColumns { get; set; }

        /// <summary>
        /// Number of columns that are used in sorting
        /// </summary>
        public int iSortingCols { get; set; }

        /// <summary>
        /// Comma separated list of column names
        /// </summary>
        public string sColumns { get; set; }
    }
}

Controller:

    public ActionResult AjaxHandler(jQueryDataTableParamModel param)
    {
        var allProviders = _db.CareProviders.ToList();
        IEnumerable<CareProvider> filteredProviders;

        if (!string.IsNullOrEmpty(param.sSearch))
        {
            filteredProviders = _db.CareProviders.ToList()
                     .Where(c => c.ProviderId.Contains(param.sSearch)
                                 ||
                      c.CareServiceType.Contains(param.sSearch)
                                 ||
                                 c.CareServiceName.Contains(param.sSearch));
        }
        else
        {
            filteredProviders = allProviders;
        }
        var displayedProviders = filteredProviders
            .Skip(param.iDisplayStart)
            .Take(param.iDisplayLength);

        var result = from c in displayedProviders
                     select new[] { Convert.ToString(c.ProviderId), c.CareServiceType, c.CareServiceName, c.Email };
        return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = allProviders.Count(),
            iTotalDisplayRecords = filteredProviders.Count(),
            aaData = result
        },
                         JsonRequestBehavior.AllowGet);

    }

Jquery DataTable Script:

<script type="text/javascript">
    $(function () {
        // Initialize Example 2
        // $('#example2').dataTable();
        $('#example2').dataTable({
            "bServerSide": true,
            "sAjaxSource": "AdminPanel/AjaxHandler",
            "bProcessing": true,
            "aoColumns": [
                            { "sName": "ProviderId" },
                            { "sName": "CareServiceType" },
                            { "sName": "CareServiceName" },
                            { "sName": "Email" }
            ]
        });
    });
</script>

Dimitri · Accepted Answer · 2015-09-12 15:02:01Z

2

I don't believe this is a datatables problem. In your code, the first line var allProviders = _db.CareProviders.ToList(); is immediately hitting your database and loading all of your providers into memory. Only after loading into memory are you then filtering with skip and take, so you are hitting the initial query execution overhead. To make things a little worse, if you have a filtering condition, you run filteredProviders = _db.CareProviders.ToList() which hits your database and does the whole thing over again.

Remember while linq employs deferred execution, calling ToList() tells it "OK, I want this now." It stops building your expression trees, hits your database, and dumps the result into your list, and any further filtering is done on the list itself.

Your initial premise is correct. running var model = _db.CareProviders .OrderBy(row => row.ProviderId).Skip((pageNumber - 1) * pageResults) .Take(pageResults).ToList(); will indeed only return the rows you want, as long as you are using a provider that understands skip and take (EF does).

Consolidate your allProviders and filteredProviders variables, and call ToList() only after you've applied all possible filters and your skip and take, and see if that improves your speed.

edited Sep 12, 2015 at 15:02

answered Sep 12, 2015 at 14:53

Dimitri

1,2086 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

G Gr Over a year ago

If my database table is indexed with the provider id, is there no way I can just retrieve 10 records from the db at a time? Then use the index for pagination purposes, so when I click on page 2 it will load the next 10 records from the db into my datatable?

Dimitri Over a year ago

Hi Garrith, you've probably tested this by now, but that's exactly how it works, assuming that datatables is configured to make a trip to the server when you click page 2 (and that you are passing what you need to the server to get the right records). If you want to experiment with Linq against your own database without having to modify your code, Linqpad is an excellent tool.

G Gr Over a year ago

Yeah I now realise its a server side speed issue. I initially thought the server side was pushing the full 8000 records to the client and that was the cause of the lag. I am just using the free version of azure to host my site at the moment and realise its a combination of bad server side coding and the processing/memory issues of the free azure site. Well thats my current theory.

Matyas · Accepted Answer · 2015-09-12 15:00:30Z

0

In the first line of the AjaxHandler method, there is var allProviders = _db.CareProviders.ToList(); which will load everything from the table. Is it the large one? ... If so, that is your problem.

Also the line filteredProviders = _db.CareProviders.ToList() will load everything from the table and materialize that. The Where will be executed on the materialized data and also the Take method later in the code will take the small portion from already materialized data (the same reason).

It is the selection from the DB and the materialization what is taking a long time.

edited Sep 12, 2015 at 15:00

answered Sep 12, 2015 at 14:52

Matyas

1,1825 gold badges23 silver badges29 bronze badges

2 Comments

G Gr Over a year ago

Any suggestions on where I could look to optimise this?

Matyas Over a year ago

You have here two helpful answers. What is it you don't understand? I'll quote Dmitri's answer here: "Consolidate your allProviders and filteredProviders variables, and call ToList() only after you've applied all possible filters and your skip and take, and see if that improves your speed." That is exactly what you need to do. I'll be happy to explain more, but I don't understand what it is you don't understand.

Collectives™ on Stack Overflow

jQuery DataTable and MVC server side processing

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related