gpt4 book ai didi

c# - Kendo MVC ToDataSourceResult 非常慢,IQueryable 很大

转载 作者:太空狗 更新时间:2023-10-29 21:50:47 28 4
gpt4 key购买 nike

我广泛使用了 Kendo DataSourceResult ToDataSourceResult(this IQueryable enumerable, DataSourceRequest request); 扩展,直到现在查询包含 4000 万条记录的表时才注意到性能问题。我编写的 take 10 查询作为基准,因为它与传入的请求相同。

这是我的阅读 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);
}

调试的输出:

00:00:00.1316569 : Got Context
00:00:00.1332584 : Built Query
00:00:00.2407656 : Taken 10
00:00:21.5013946 : Datasource Result

虽然有时候查询会超时。使用 dbMonitor 我捕获了两个查询,首先是手动捕获 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

以及 ToDataSourceRequest 查询:

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"

这是传入的DataSourceRequest请求参数:

request.Aggregates Count = 0
request.Filters Count = 0
request.Groups Count = 0
request.Page 1
request.PageSize 10
request.Sorts Count = 1

这是 var result = events.ToDataSourceResult(request); 的结果

result.AggregateResults null
result.Data Count = 10
result.Errors null
result.Total 43642809

如何使用 DataSourceRequest 以更有效和更快的方式从事件 IQueryable 中获取 DataSourceResult

最佳答案

在实现带有大量调试输出时间戳的自定义绑定(bind)(由 Atanas Korchev 建议)后,很明显是什么导致了性能问题,即总计数。

看我抓到的SQL也支持这个,不知道为什么以前没看到。

快速获取总行数是另一个问题,但我会在此处发布我找到的任何答案。

关于c# - Kendo MVC ToDataSourceResult 非常慢,IQueryable 很大,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/15843703/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com