Kendo MVC ToDataSourceResult extremly slow with large IQueryable
asp.net, asp.net-mvc-4, c#, datasource, kendo-ui
Solution
After implementing a custom binding (suggested by Atanas Korchev) with lots of debug output time stamps, it was obvious what was causing the performance issue, the total count.
Looking at the SQL I captured backs this up, don't know why I didn't see it before.
Getting the total row count quickly is another question but will post any answers I find here.
Problem
I have used the Kendo `DataSourceResult ToDataSourceResult(this IQueryable enumerable, DataSourceRequest request);` extension extensively and never noticed a performance issue until now when querying a table of 40 million records. The take 10 query I wrote as a benchmark as it is the same as the request passed in. This is my read action: ``` public ActionResult ReadAll([DataSourceRequest] DataSourceRequest { var startTimer = DateTime.Now; var context = Helpers.EFTools.GetCADataContext(); Debug.WriteLine(string.Format("{0} : Got Context", DateTime.Now - startTimer)); var events = from e in context.Events select new Models.Event() { Id = e.Id, DateTime = e.EventDateTime, HostId = e.Door.HostId, SiteId = e.Door.Host.SiteId, UserId = (int)e.UserId, UserName = e.User.FirstName + " " + e.User.Surname, DoorId = e.DoorId, Door = e.Door.Name, Description = e.Description, SubDescription = e.SubDescription }; Debug.WriteLine(string.Format("{0} : Built Query", DateTime.Now - startTimer)); var tenRecods = events.OrderByDescending(i => i.DateTime).Take(10).ToList(); Debug.WriteLine(string.Format("{0} : Taken 10", DateTime.Now - startTimer)); var result = events.ToDataSourceResult(request); Debug.WriteLine(string.Format("{0} : Datasource Result", DateTime.Now - startTimer)); return this.Json(result); } ``` The output from Debug: ``` 00:00:00.1316569 : Got Context 00:00:00.1332584 : Built Query 00:00:00.2407656 : Taken 10 00:00:21.5013946 : Datasource Result ``` Although sometimes the query times out. Using dbMonitor I captured both querys, first the manual take 10: ``` "Project1".id, "Project1"."C1", "Project1".hostid, "Project1".siteid, "Project1".userid, "Project1"."C2", "Project1".doorid, "Project1"."name", "Project1".description, "Project1".subdescription FROM ( SELECT "Extent1".id, "Extent1".userid, "Extent1".description, "Extent1".subdescription, "Extent1".doorid, "Extent2"."name", "Extent2".hostid, "Extent3".siteid, CAST("Extent1".eventdatetime AS timestamp) AS "C1", "Extent4".firstname || ' ' || "Extent4".surname AS "C2" FROM public.events AS "Extent1" INNER JOIN public.doors AS "Extent2" ON "Extent1".doorid = "Extent2".id INNER JOIN public.hosts AS "Extent3" ON "Extent2".hostid = "Extent3".id INNER JOIN public.users AS "Extent4" ON "Extent1".userid = "Extent4".id ) AS "Project1" ORDER BY "Project1"."C1" DESC LIMIT 10 ``` And the ToDataSourceRequest query: ``` SELECT "GroupBy1"."A1" AS "C1" FROM ( SELECT Count(1) AS "A1" FROM public.events AS "Extent1" INNER JOIN public.doors AS "Extent2" ON "Extent1".doorid = "Extent2".id ) AS "GroupBy1" ``` This is the `DataSourceRequest request` parameter passed in: ``` request.Aggregates Count = 0 request.Filters Count = 0 request.Groups Count = 0 request.Page 1 request.PageSize 10 request.Sorts Count = 1 ``` This is the result of `var result = events.ToDataSourceResult(request);` ``` result.AggregateResults null result.Data Count = 10 result.Errors null result.Total 43642809 ``` How can I get a `DataSourceResult` from the events IQueryable using the `DataSourceRequest` in a more efficient and faster way?