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

我想说,万恶之源始于此:

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

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毫秒)。嗯,那不太好。问题是预约表中的9829页。为了改进这一点,我们希望有两个过滤条件(acl 日期)立刻。也许是索引视图?

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

我们还可以将约会的聚集索引更改为可能更有用的AppointmentID:

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

当然,现在您应该使用的实际模式取决于许多没有考虑到的因素。但我希望这能给你一些想法,让你现在采取适当的行动,以获得体面的表现。将表值参数添加到客户机执行上下文并将其传递给过程,以及LINQ集成,留给读者作为练习。

如果我理解正确 Appointment 有一个 Roles Notes . 如果是这样的话(并且您在设计器中正确地对其进行了建模),那么您就有了这些 角色 预约 班级。当你改变投影时 select q 查询选择 预约 就其本身而言,您可以帮助linqtosql为您获取以下集合。在这种情况下,您应该按照以下方式编写查询:

var q =
    from appt in visible
    ...
    select appt;

在此之后,您可以使用 LoadOptions 财产 DataContext 按以下步骤预取子集合:

using (var db = new AppointmentContext())
{
    db.LoadOptions.LoadWith<Appointment>(a => a.Roles);

    // Do the rest here
}

但有一个问题是我认为 LoadWith

您可以通过在两个查询中写出它来解决这个问题。第一个查询是获取约会并使用 加载 全部取回 角色 数据上下文 )使用 加载 笔记 ).

where !(appt.StartDateTime >= end || appt.EndDateTime <= start)

这可能是一个非常好的标准。

where appt.StartDateTime < end && start < appt.EndDateTime

acl.ToIds().

把它从查询中拉出来,要求数据库执行操作毫无意义。

List<int> POGIDs = acl.ToIds();

join role in

您想将角色用作筛选器。如果你在哪里,而不是加入,你不必区分以后。

试试这个,有DataLoadOptions和没有DataLoadOptions。如果查询在没有DataLoadOptions的情况下是好的,那么还有另一种(更手动的)方法来加载相关行。

DataLoadOptions myOptions = new DataLoadOptions();
myOptions.LoadWith<Appointment>(appt => appt.Roles);
myOptions.LoadWith<Appointment>(appt => appt.Notes);
dc.LoadOptions = myOptions;


List<int> POGIDs = acl.ToIds();

IQueryable<Roles> roleQuery = dc.Roles
  .Where(r => POGIDs.Contains(r.PersonOrGroupId));

IQueryable<Appointment> visible =
  dc.Appointments
    .Where(appt => appt.StartDateTime < end && start < appt.EndDateTime)
    .Where(appt => appt.Roles.Any(r => roleQuery.Contains(r));

IQueryable<Appointment> q =
  visible.OrderBy(appt => appt.StartDateTime);

List<Appointment> rows = q.ToList();

以下是获取相关数据的“更手动”方法。注意:当apptid或pogid中的int数超过~2100时,此技术会中断。也有办法解决这个问题。。。

List<int> POGIDs = acl.ToIds();

List<Role> visibleRoles = dc.Roles
  .Where(r => POGIDs.Contains(r.PersonOrGroupId)
  .ToList()

List<int> apptIds = visibleRoles.Select(r => r.AppointmentId).ToList();

List<Appointment> appointments = dc.Appointments
  .Where(appt => appt.StartDateTime < end && start < appt.EndDate)
  .Where(appt => apptIds.Contains(appt.Id))
  .OrderBy(appt => appt.StartDateTime)
  .ToList();

ILookup<int, Roles> appointmentRoles = dc.Roles
  .Where(r => apptIds.Contains(r.AppointmentId))
  .ToLookup(r => r.AppointmentId);

ILookup<int, Notes> appointmentNotes = dc.AppointmentNotes
  .Where(n => apptIds.Contains(n.AppointmentId));
  .ToLookup(n => n.AppointmentId);

foreach(Appointment record in appointments)
{
  int key = record.AppointmentId;
  List<Roles> theRoles = appointmentRoles[key].ToList();
  List<Notes> theNotes = appointmentNotes[key].ToList();
}

此样式突出显示需要索引的位置:

Roles.PersonOrGroupId
Appointments.AppointmentId (should be PK already)
Roles.AppointmentId
Notes.AppointmentId