c#根据一组日期查找开始日期和结束日期?
c#根据一组日期查找开始日期和结束日期?
我有一个包含超过200K+记录和一个包含日期的列的数据库表(NOT NULL
)。由于数据库很庞大,执行GroupBy
日期的查询需要很长时间(大约1分钟)。
我的理论:
- 获取该表的所有记录列表
- 从该列表中找到结束日期和开始日期(即最旧的日期和最新的日期)
- 然后选择大约20个日期进行
GroupBy
,以便在较少的记录集上完成查询
这是我用来获取列表的模型:
registration.Select(c => new RegistrationViewModel() { DateReference = c.DateReference, MinuteWorked = c.MinuteWorked, });
DateReference
是我必须使用的数据库列。
我不确定如何在不耗费太长时间的情况下循环遍历列表获取日期的开始和结束。有什么想法吗?
编辑:
var registrationList = await context.Registration .Where(c => c.Status == StatusRegistration.Active) // 获取所有活动的注册 .ToRegistrationViewModel() // 这只是一个选择方法 .OrderBy(d => d.DateReference.Date) // 这需要很长时间 .ToListAsync();
GroupBy:
var grpList = registrationList.GroupBy(x => x.DateReference.Date).ToList(); var tempList = new List>(); foreach (var item in grpList) { var selList = item.Select(c => new RegistrationViewModel() { RegistrationId = c.RegistrationId, DateReference = c.DateReference, MinuteWorked = c.MinuteWorked, }).ToList(); tempList.Add(selList); }
这是我的SQL表:
这是ToRegistrationViewModel()
函数:
return registration.Select(c => new RegistrationViewModel() { RegistrationId = c.RegistrationId, PeopleId = c.PeopleId, DateReference = c.DateReference, DateChange = c.DateChange, UserRef = c.UserRef, CommissionId = c.CommissionId, ActivityId = c.ActivityId, MinuteWorked = c.MinuteWorked, Activity = new ActivityViewModel() { Code = c.Activity.Code, Description = c.Activity.Description, }, Commission = new CommissionViewModel() { Code = c.Commission.Code, Description = c.Commission.Description }, People = new PeopleViewModel() { UserId = c.People.UserId, Code = c.People.Code, Name = c.People.Name, Surname = c.People.Surname, Active = c.People.Active } });
问题的原因:
1. 缺乏索引:查询中使用的Status和DateReference列都没有索引。如果只有少量活动状态,则对该列创建一个索引可能足够,否则需要对日期列创建索引以加快排序速度。还可以考虑创建包含这两个列的复合索引。适当的索引应该可以解决排序问题。
2. 查询结果的实例化:ToListAsync将触发SQL查询的执行,使得之后的每个操作都在客户端上运行。对于ToRegistrationViewModel,我非常怀疑它,我建议尝试将其更改为匿名类型,并在查询被实例化之后才转换为实际类型。通常认为在客户端上运行排序和分组等操作是不好的,但您需要考虑实际的瓶颈在哪里,如果数据传输占用了大部分时间,优化分组也无济于事。
3. 数据传输:无论如何,获取大量行的速度都会很慢。通常的目标是尽可能在数据库中进行过滤,以便不需要获取那么多的行。如果必须获取大量记录,可以使用分页,即将OrderBy与Skip和Take结合起来,以获取较小的数据块。这样做并不能节省整体时间,但可以实现进度显示和持续显示数据等功能。
解决方法:
1. 创建适当的索引来加快排序速度,可以考虑创建一个包含Status和DateReference列的复合索引。
2. 将查询结果先转换为匿名类型,待查询被实例化后再转换为实际类型,避免在客户端上运行排序和分组等操作。
3. 尽可能在数据库中进行过滤,以减少需要获取的记录数量。可以使用分页技术,将OrderBy与Skip和Take结合起来,获取较小的数据块。这样可以提供更好的用户体验。
注意事项:
1. 如果数据记录已经按日期排序,可以通过比较记录的日期与前一个日期来判断是否需要输出一个分组。
2. 分页技术不能使整体速度更快,它只能改善用户体验。