Kendo MVC ToDataSourceResult extremly slow with large IQueryable

Question

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?

Answer 1

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.