c# - 优化日历应用程序的查询和/或数据模型

Question

我们的日历应用程序将约会域表示为:

约会

• 身份证(PK)
• 开始日期时间
• 结束时间
• ...

约会角色

• 约会 ID (FK)
• PersonOrGroupID (FK)/* 加入一个人/组，在这个问题的范围之外 */
• 角色
• ...

Appointment 与 AppointmentRoles 是一对多的关系。每个 AppointmentRole 代表担任特定角色(例如，送机、接机、出席……)的个人或组。

这种关系有两个目的:

1. 它定义了一个访问控制列表——经过身份验证的委托(delegate)人只有在其访问控制列表与关联的个人或组匹配时才能查看约会
2. 它记录了参加约会的人员以及担任的角色。

还有第三个表来跟踪与约会相关的注释/评论。它位于与约会的一对多关系的多边:

约会笔记

• 约会 ID (FK)
• ...

为了显示约会日历，我们目前使用类似...

List<IAppointment> GetAppointments(IAccess acl, DateTime start, DateTime end, ...
{
  // Retrieve distinct appointments that are visible to the acl

  var visible = (from appt in dc.Appointments
                 where !(appt.StartDateTime >= end || appt.EndDateTime <= start)
                 join role in
                   (from r in dc.Roles
                    where acl.ToIds().Contains(r.PersonOrGroupID)
                    select new { r.AppointmentID })
                 on appt.ID equals role.AppointmentID
                 select new
                 {
                   ...
                 }).Distinct();

  ...

visible Linq 表达式选择给定访问控制列表可见的不同约会。

下面，我们通过visible和join/intoroles和notes来挑选参与约会和约会笔记。

  ...

  // Join/into to get all appointment roles and notes

  var q = from appt in visible
          orderby appt.StartDateTime, ...
          join r in dc.Roles
          on appt.ID equals r.AppointmentID
          into roles
          join note in dc.AppointmentNotes
          on appt.ID equals note.AppointmentID
          into notes
          select new { Appointment = appt, Roles = roles, Notes = notes };

最后，我们枚举查询，希望 Linq-To-Sql 能生成一个非常优化的查询(没有后面讨论的运气)...

  // Marshal the anonymous type into an IAppointment
  // IAppointment has a Roles and Notes collection

  var result = new List<IAppointment>();
  foreach (var record in q)
  {
    IAppointment a = new Appointment();
    a.StartDateTime = record.StartDateTime;
    ...
    a.Roles = Marshal(record.Roles);
    a.Notes = Marshal(record.Notes);

    result.Add(a);
  }

Linq-to-Sql 生成的查询非常繁琐。它生成单个查询以确定可见约会。但随后它会在每次迭代中生成三个查询:一个用于获取约会字段，第二个用于获取角色，第三个用于获取注释。 where 子句始终是可见的约会 ID。

因此，我们正在重构 GetAppointments，并认为我们可以从 SO 社区的专业知识中获益。

我们希望将所有内容移动到 T-SQL 存储过程中，以便我们拥有更多控制权。你能分享一下你将如何解决这个问题的想法吗？数据模型的更改、T-SQL 和 Linq-to-SQL 修改都是公平的游戏。我们还需要有关索引的建议。我们使用的是 MS-SqlServer 2008 和 .NET 4.0。

Answer 1

我想说万恶之源从这里开始:

where acl.ToIds().Contains(r.PersonOrGroupID) 

acl.ToIds().Contains(...) 是无法在服务器端解析的表达式，因此必须解析visible 查询(非常低效)在客户端，更糟糕的是，结果必须保留在客户端，然后，当它被迭代时，必须为每个可见约会(约会字段、角色和注释)向服务器发送不同的查询).如果我按照自己的方式行事，我会创建一个存储过程，它接受 ACL 列表作为 Table Valued Parameter。并在服务器端进行所有加入/过滤。

我将从这个模式开始:

create table Appointments (
    AppointmentID int not null identity(1,1),
    Start DateTime not null,
    [End] DateTime not null,
    Location varchar(100),
    constraint PKAppointments
        primary key nonclustered (AppointmentID));

create table AppointmentRoles (
    AppointmentID int not null,
    PersonOrGroupID int not null,
    Role int not null,
    constraint PKAppointmentRoles
        primary key (PersonOrGroupID, AppointmentID), 
    constraint FKAppointmentRolesAppointmentID
        foreign key (AppointmentID)
        references Appointments(AppointmentID));

create table AppointmentNotes (
    AppointmentID int not null,
    NoteId int not null,
    Note varchar(max),

    constraint PKAppointmentNotes
        primary key (AppointmentID, NoteId),
    constraint FKAppointmentNotesAppointmentID
        foreign key (AppointmentID)
        references Appointments(AppointmentID));
go

create clustered index cdxAppointmentStart on Appointments (Start, [End]);
go

然后像这样检索任意 ACL 的约会:

create type AccessControlList as table 
    (PersonOrGroupID int not null);
go

create procedure usp_getAppointmentsForACL
 @acl AccessControlList readonly,
 @start datetime,
 @end datetime
as
begin
    set nocount on;
    select a.AppointmentID
        , a.Location
        , r.Role
        , n.NoteID
        , n.Note
    from @acl l 
    join AppointmentRoles r on l.PersonOrGroupID = r.PersonOrGroupID
    join Appointments a on r.AppointmentID = a.AppointmentID
    join AppointmentNotes n on n.AppointmentID = a.AppointMentID
    where a.Start >= @start
    and a.[End] <= @end;    
end
go

让我们在 100 万个约会上试试这个。首先，填充表格(大约需要 4-5 分钟):

set nocount on;
declare @i int = 0;
begin transaction;
while @i < 1000000
begin
    declare @start datetime, @end datetime;
    set @start = dateadd(hour, rand()*10000-5000, getdate());
    set @end = dateadd(hour, rand()*100, @start)
    insert into Appointments (Start, [End], Location)
    values (@start, @end, replicate('X', rand()*100));

    declare @appointmentID int = scope_identity();
    declare @atendees int = rand() * 10.00 + 1.00;
    while @atendees > 0
    begin
        insert into AppointmentRoles (AppointmentID, PersonOrGroupID, Role)
        values (@appointmentID, @atendees*100 + rand()*100, rand()*10);
        set @atendees -= 1;
    end

    declare @notes int = rand()*3.00;
    while @notes > 0
    begin
        insert into AppointmentNotes (AppointmentID, NoteID, Note)
        values (@appointmentID, @notes, replicate ('Y', rand()*1000));
        set @notes -= 1;
    end

    set @i += 1;
    if @i % 10000 = 0
    begin
        commit;
        raiserror (N'Added %i appointments...', 0, 1, @i);
        begin transaction;
    end
end
commit;
go

那么让我们看看今天几个人的约会:

set statistics time on;
set statistics io on;

declare @acl AccessControlList;
insert into @acl (PersonOrGroupID) values (102),(111),(131);
exec usp_getAppointmentsForACL @acl, '20100730', '20100731';

Table 'AppointmentNotes'. Scan count 8, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Appointments'. Scan count 1, logical reads 9829, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AppointmentRoles'. Scan count 3, logical reads 96, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#25869641'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 1294 ms.

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 1294 ms.

1.2 秒(在冷缓存上，在热缓存上达到 224 毫秒)。嗯，这不是很好。问题是在 Appointment 表中命中了 9829 页。为了改进这一点，我们希望同时拥有两个过滤条件(acl 和 日期)。也许是索引 View ？

create view vwAppointmentAndRoles 
with schemabinding
as
select r.PersonOrGroupID, a.AppointmentID, a.Start, a.[End]
from dbo.AppointmentRoles r
join dbo.Appointments a on r.AppointmentID = a.AppointmentID;
go

create unique clustered index cdxVwAppointmentAndRoles on vwAppointmentAndRoles (PersonOrGroupID, Start, [End]);
go

alter procedure usp_getAppointmentsForACL
 @acl AccessControlList readonly,
 @start datetime,
 @end datetime
as
begin
    set nocount on;
    select ar.AppointmentID
        , a.Location
        , r.Role
        , n.NoteID
        , n.Note
    from @acl l 
    join vwAppointmentAndRoles ar with (noexpand) on l.PersonOrGroupID = ar.PersonOrGroupID
    join AppointmentNotes n on n.AppointmentID = ar.AppointMentID
    join Appointments a on ar.AppointmentID = a.AppointmentID
    join AppointmentRoles r 
        on ar.AppointmentID = r.AppointmentID
        and ar.PersonOrGroupID = r.PersonOrGroupID
    where ar.Start >= @start
     and ar.Start <= @end
    and ar.[End] <= @end;   
end
go

我们还可以将 Appointments 的聚簇索引更改为可能更有用的 AppointmentID:

drop index cdxAppointmentStart on Appointments;
create clustered index cdxAppointmentAppointmentID on Appointments (AppointmentID);
go

这会在 77 毫秒(在热缓存中)中返回同一 @acl 列表中相同日期范围内的约会。

现在，当然，您应该使用的实际架构取决于更多未考虑的因素。但我希望这能让您对现在采取适当行动以获得良好性能有所了解。将表值参数添加到客户端执行上下文并将其传递给过程，以及 LINQ 集成，作为练习留给读者。