Розширене використання індексованих уявлень MSSQL в EF Core

У світі Microsoft SQL Server уявлення (views) є потужним інструментом для спрощення складних запитів до даних. Прості уявлення не дають значного покращення продуктивності, але часто використовуються для реалізації простих запитів до бази даних (я все ж віддаю перевагу реалізаціям на рівні додатків, а не бази даних для простих уявлень). Однак індексовані уявлення надають реальний приріст продуктивності.

Індексовані уявлення, також відомі як матеріалізовані уявлення, є спеціальними об'єктами бази даних, які зберігають результати уявлення постійно в базі даних. Це зберігання оновлюється, як тільки відбувається запис, який впливає на записи даних, що містяться в уявленні. Цей механізм дозволяє зберігати результати складного запиту і отримувати їх за необхідності без повторного виконання запиту. Це реальний приріст продуктивності, особливо для додатків з дуже високим рівнем читання.

Просте уявлення в EF Core

На жаль, EF Core надає дуже обмежену підтримку уявлень у MSSQL; фактично лише базові функції.

Для цього створюється так званий безключовий тип сутності — тут як приклад користувач і кількість його публікацій на форумі — які потім відображаються на уявлення в конфігурації.

public class UserForumPostCount  
{  
 public int UserId { get; set; }  
 public int TotalPostCount { get; set; }  
}  

public class UserForumPostCountConfig : IEntityTypeConfiguration  
{  
 public void Configure(EntityTypeBuilder builder)  
 {  
 builder.HasNoKey();  

 builder.ToView("UserForumPostCountView");  
 }  
}

Уявлення — це не матеріалізоване уявлення — створюється в міграції за допомогою SQL-команди.

CREATE VIEW UserForumPostCountView  
AS  
 SELECT  
 UserId,  
 COUNT(*) AS TotalPostCount  
 FROM  
 ForumPosts  
 GROUP BY  
 UserId;

Наприкінці лише уявлення потрібно зареєструвати в DbContext.

public class MyDbContext : DbContext  
{  
 // ....  

 public DbSet UserForumPostCountViews  
 => Set();  

 protected override void OnModelCreating(ModelBuilder modelBuilder)  
 {  
 // ...  
 modelBuilder.ApplyConfiguration(new UserForumPostCountConfig());  
 }

Недоліки

Цей спосіб декларації має деякі недоліки, два найбільших з яких:

• Уявлення в EF Core не підтримують властивості навігації; інші сутності не можуть бути доступні — хоча це підтримується в усіх звичних реляційних базах даних. EF Core відмовляється це робити з відповідними повідомленнями про помилки, такими як The navigation cannot be added because it targets the keyless entity type 'Document'. Navigations can only target entity types with keys. (див. Проблема з навігацією без ключа в Entity Framework Core).
• Залежно від системи бази даних існують додаткові функціональності, які не можуть бути використані лише за допомогою оголошення уявлення в EF Core (ToView). Для індексованих уявлень є так званий NOEXPAND хінт, який забезпечує, що SQL сервер дійсно читає з матеріалізованого уявлення і не виконує його знову. Цей хінт не можна встановити через EF Core ToView.

Розширене використання — Індексовані уявлення

Індексовані уявлення настільки потужні, що будь-який засіб є правильним для їх використання.
Одним з варіантів є оголосити уявлення як звичайну сутність і використати функціональність EF Core — на власний ризик, звичайно.

По-перше, уявлення створюється за допомогою міграції.

CREATE VIEW UserForumPostCountView WITH SCHEMABINDING AS  
SELECT  
 UserId,  
 COUNT_BIG (*) AS TotalPostCount  
FROM  
 dbo.ForumPosts  
GROUP BY  
 UserId;  

CREATE UNIQUE CLUSTERED INDEX IX_UserForumPostCountView  
 ON UserForumPostCountView (UserId);

Особливі моменти тут такі:

• Деякі SQL функції не підтримуються в індексованих уявленнях.
• Потрібно використовувати COUNT_BIG, що означає, що ми більше не використовуємо int в сутності, а повинні використовувати long.
• Все це здійснюється за допомогою прив'язки до схеми.
• Створюється кластеризований індекс.

Тепер у конфігурації сутності ми можемо не використовувати ToView, а використовувати нашу власну SQL команду.

public class UserForumPostCount  
{  
 public int UserId { get; set; }  
 public long TotalPostCount { get; set; }  
}  

public class UserForumPostCountConfig : IEntityTypeConfiguration  
{  
 public void Configure(EntityTypeBuilder builder)  
 {  
 builder.HasKey(x => x.UserId);  

 builder.ToSqlQuery(  
 /* language = sql*  
 @"""  
 SELECT  
 UserId,  
 TotalPostCount  
 FROM  
 UserForumPostCountView WITH (NOEXPAND);  
 """;  
 }  
}

Це абсолютно необхідно, щоб мати можливість використовувати SQL хінт NoExpand, який гарантує, що запит ніколи не виконується динамічно, а завжди використовується вже збережений результат — і тим самим дає приріст продуктивності.

Побічні ефекти

Приємний побічний ефект — такий спосіб декларації уявлення тепер також дозволяє використовувати властивості навігації в нашій сутності уявлення:

public class UserForumPostCount  
{  
 public int UserId { get; set; }  
 public virtual UserEntity User { get; set; } = null!;  

 public long TotalPostCount { get; set; }  
}  

public class UserForumPostCountConfig : IEntityTypeConfiguration  
{  
 public void Configure(EntityTypeBuilder builder)  
 {  
 builder.HasKey(x => x.UserId);  

 builder.ToSqlQuery(  
 /* language = sql*  
 @"""  
 SELECT  
 UserId,  
 TotalPostCount  
 FROM  
 UserForumPostCountView WITH (NOEXPAND);  
 """;  

 builder.HasOne(x => x.User)  
 .WithOne()  
 .HasForeignKey(x => x.UserId);  
 }  
 }

Вплив на міграцію

Через обхідне рішення для декларації уявлення EF Core не знає, що це уявлення. Тепер EF Core вважає це окремою сутністю і генерує відповідні команди CREATE в міграції.

Ці команди потрібно адаптувати вручну:

• Ручне створення уявлення.
• Ручне створення індексу.
• Адаптувати/видалити команди міграції EF Core.

Висновок

EF Core чудово працює з індексованими уявленнями в MSSQL; навіть властивості навігації працюють без проблем, що я успішно використовую вже багато років навіть у великих додатках. Однак все робиться на власний ризик з розумінням, що це дійсно обхідне рішення — для якого ми, можливо, не отримаємо офійну підтримку навіть через 200 років.

Це потрібно використовувати з обережністю; потрібно мати досвід, знати, що ви робите — але ви отримаєте величезний приріст продуктивності.

Автор

БЕН АБТ

Бен — захоплений розробник і програмний архітектор, зокрема сфокусований на .NET, хмарних технологіях та IoT. У своїй професійній діяльності він працює над високошвидкісними платформами для IoT та Індустрії 4.0, зосередженими на наступному поколінні з'єднаних промислових технологій на базі Azure і .NET. Він веде найбільший німецькомовний форум C# myCSharp.de, є засновником Azure UserGroup Stuttgart, співорганізатором AzureSaturday, веде свій блог, бере участь в проектах з відкритим кодом, виступає на конференціях і в користувацьких групах, а також має трохи вільного часу. Він є Microsoft MVP з 2015 року за .NET та Azure.

Перекладено з: Advanced Usage of MSSQL Indexed Views in EF Core