All geek questions in one place

Optimization of the query model and / or data for the calendar application

Our calendar application represents the destination domain as:

Appointment

  • ID (PK)
  • StartDateTime
  • Enddatetime
  • ...

AppointmentRole

  • AppointmentID (FK)
  • PersonOrGroupID (FK) / * joins a person / group, outside the scope of this question * /
  • Role
  • ...

An assignment has 1 to many relationships with AppointmentRoles. Each AppointmentRole represents a person or group in a specific role (e.g. dropdown, pickup, visit, ...).

Relations serve two purposes:

  • it defines an access control list — an authenticated director can only view a meeting if their access control list matches a related person or group.
  • These are documents that attend the meeting and in what roles.

/, . 1 :

AppointmentNote

  • AppointmentID (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 , .

/ , , , .

  ...

  // 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 .

, GetAppointments , SO.

T-SQL, . , ? , T-SQL Linq-to-SQL - . . MS-SqlServer 2008 .NET 4.0.

+3
3

, :

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

1M. ( 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 date). , ?

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

@acl 77 ( ).

, , , , , . , , , . table value , LINQ .

+3

, Appointment Roles Notes. ( ), Roles Notes Appointment. (select) q, Appointment , LINQ to SQL . :

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 , .

, . , LoadWith Roles. ( DataContext) LoadWith Notes).

.

+2
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 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();

" " . . , apptIds POGID ~ 2100 ints . ...

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
+1

Source: https://habr.com/ru/post/1757575/

More articles:


All Articles