【Entity Framework Core】 N + 1 とかなんとか 1
はじめに
今日も Entity Framework Core と戯れているワタクシですが、いわゆる N + 1 問題が気になってしまったのであれこれ試してみたメモです。
準備
環境
- ASP.NET Core 2.2
- Entity Framework Core 2.2.2
- Npgsql.EntityFrameworkCore.PostgreSQL 2.2.0
前提条件など
画像のように、 Store が複数の Author を持ち、その Author が複数の Book を持つ、というテーブル間の関係性を持つ場合。
( Store に Author が紐づくのはおかしい、という話はあるかもしれませんが、ここでは見逃してください(..)_)
Class
各モデルクラスは下記とします。
Store.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Store")]
public class Store
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
}
}
Author.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Author")]
public class Author
{
[Key]
public int Id { get; set; }
[ForeignKey("Store")]
public int StoreId { get; set; }
public string Name { get; set; }
}
}
Book.cs
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace EfCoreNpgsqlSample.Models
{
[Table("Book")]
public class Book
{
[Key]
public int Id { get; set; }
[ForeignKey("Author")]
public int AuthorId { get; set; }
public string Name { get; set; }
public bool Available { get; set; }
}
}
SQL
SQL はこんな感じ。
Store
CREATE TABLE "Store" (
"Id" SERIAL PRIMARY KEY,
"Name" TEXT NOT NULL)
Author
CREATE TABLE "Author" (
"Id" SERIAL PRIMARY KEY,
"StoreId" BIGINT REFERENCES "Store"("Id"),
"Name" TEXT NOT NULL)
Book
CREATE TABLE "Book" (
"Id" SERIAL PRIMARY KEY,
"AuthorId" BIGINT REFERENCES "Author"("Id"),
"Name" TEXT NOT NULL,
"Available" BOOLEAN NOT NULL)
データは Store: 3 件、 Author: 6 件、 Book: 18 件程追加してみました。
取得したいデータ
で、下記の StoreItem のようなクラスのリストを取得したいとします。
StoreItem.cs
using System.Collections.Generic;
namespace EfCoreNpgsqlSample.Models
{
public class StoreItem
{
public Store Store { get; set; }
public List< AuthorItem> Authors { get; set; }
}
}
AuthorItem
using System.Collections.Generic;
namespace EfCoreNpgsqlSample.Models
{
public class AuthorItem
{
public Author Author { get; set; }
public List< Book> Books { get; set; }
}
}
N + 1 問題について
ようやく課題の話に入れます。
これを実現しようと、まずあまり何も考えずに、下記のように書いてみました。
using System.Collections.Generic; using System.Linq; using System.Threading.Tasks; using Microsoft.AspNetCore.Mvc; using Microsoft.EntityFrameworkCore; using EfCoreNpgsqlSample.Models; namespace EfCoreNpgsqlSample.Controllers { public class BooksController : Controller { private readonly EfCoreNpgsqlSampleContext _context; public BooksController(EfCoreNpgsqlSampleContext context) { _context = context; } [Route("/")] [Route("/Home")] [Produces("application/json")] public async Task< List< StoreItem>> Index() { List< StoreItem> results = await _context.Stores.Select(s => new StoreItem { Store = s, Authors = _context.Authors.Where(a => a.StoreId == s.Id) .Select(a => new AuthorItem { Author = a, Books = _context.Books.Where(b => b.AuthorId == a.Id).ToList(), }).ToList(), }) .ToListAsync(); return results; } } }
これで目的の結果自体は得ることができます。
で、何が問題なのかというと、 SQL が大量に発行されてしまい、結果の数が増えれば増えるほどパフォーマンスが低下してしまうことです。
今回はコンソールに出力されていた分で 10 回発行されているのが確認できました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT s."Id", s."Name" FROM "Store" AS s info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (19ms) [Parameters=[@_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT a."Id", a."Name", a."StoreId" FROM "Author" AS a WHERE a."StoreId" = @_outer_Id info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (311ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (6ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (4ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[@_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT a."Id", a."Name", a."StoreId" FROM "Author" AS a WHERE a."StoreId" = @_outer_Id info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (2ms) [Parameters=[@_outer_Id='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT a."Id", a."Name", a."StoreId" FROM "Author" AS a WHERE a."StoreId" = @_outer_Id info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1 info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (5ms) [Parameters=[@_outer_Id1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30'] SELECT b."Id", b."AuthorId", b."Available", b."Name" FROM "Book" AS b WHERE b."AuthorId" = @_outer_Id1
内訳は下記の通りです。
- Store への SELECT: 1 回
- Author への SELECT: 3 回
- Book への SELECT: 6 回
Store が 3 件あるのでそこに紐づく Author を 3 回検索し、 Author が 6 件あるので Book を 6 回検索している、ということのようです。
当然レコード数が増えればさらに増えていくことになります。
というのが N + 1 問題で、このような問題は言語を問わず、 Entity Framework Core のような ORM を使う場合に起こりがちであるようです。
(リンクは Ruby ばかりですが)
これを防ぐ方法として、上記サイトでは includes を使って先にテーブルの情報を読み込んでおき、発行される SQL の数を抑える、という方法をとっています。
また、発行される SQL の数を抑える、ということであれば JOIN を使って Author や Book がリストに入っていない状態(各レコードが別の行に分かれた状態)で一旦結果を取得し、 C# 側で加工する、という方法も良いかもしれません。
ということで試してみます。
Include
Ruby(ActiveRecord?) にはあるらしい includes 、 Entity Framework Core ではどうかしらと思ったらありました。
準備
Include を使うには、例えば Store と Author が、 Store:1 < - > Author:多 の関係にあることを教えておく必要があります。
Store.cs
～省略～
public class Store
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
[NotMapped]
public List< Author> Authors { get; set; }
}
}
- NotMapped としているのは DB のテーブルに該当カラムが無くエラーになるのを防ぐためです。
Author.cs
～省略～
public class Author
{
[Key]
public int Id { get; set; }
[ForeignKey("Store")]
public int StoreId { get; set; }
public string Name { get; set; }
[NotMapped]
[IgnoreDataMember]
public Store Store { get; set; }
[NotMapped]
public List< Book> Books { get; set; }
}
}
Book.cs
～省略～
public class Book
{
[Key]
public int Id { get; set; }
[ForeignKey("Author")]
public int AuthorId { get; set; }
public string Name { get; set; }
public bool Available { get; set; }
[NotMapped]
[IgnoreDataMember]
public Author Author { get; set; }
}
}
EfCoreNpgsqlSampleContext.cs
using Microsoft.EntityFrameworkCore; namespace EfCoreNpgsqlSample.Models { public class EfCoreNpgsqlSampleContext: DbContext { public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity< Store>().HasMany(store => store.Authors) .WithOne(author => author.Store); modelBuilder.Entity< Author>().HasMany(author => author.Books) .WithOne(book => book.Author); } public DbSet< Store> Stores { get; set; } public DbSet< Author> Authors { get; set; } public DbSet< Book> Books { get; set; } } }
- この指定を行わない場合、実行時に Authors が Store の navigation property と InvalidOperationException が発生します。
これらのデータを Include を使ってひとまとめにします。
HomeController.cs
～省略～
[Route("/")]
[Route("/Home")]
[Produces("application/json")]
public async Task< List< Store>> Index()
{
List< Store> results = await _context.Stores
.Include(store => store.Authors)
.ThenInclude(author => author.Books)
.ToListAsync();
return results;
}
}
}
- 「.Include(store => store.Authors)」 で Store の Authors に Store.Id で関連付けられた Author がセットされます。
- 「.ThenInclude(author => author.Books)」 で、Store.Authors にセットされた Author の Books に Author.Id で関連付けられた Book がセットされます。
出力された SQL は 3 つになりました。
info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (16ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT s."Id", s."Name" FROM "Store" AS s ORDER BY s."Id" info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT "s.Authors"."Id", "s.Authors"."Name", "s.Authors"."StoreId" FROM "Author" AS "s.Authors" INNER JOIN ( SELECT s0."Id" FROM "Store" AS s0 ) AS t ON "s.Authors"."StoreId" = t."Id" ORDER BY t."Id", "s.Authors"."Id" info: Microsoft.EntityFrameworkCore.Database.Command[20101] Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT "s.Authors.Books"."Id", "s.Authors.Books"."AuthorId", "s.Authors.Books"."Available", "s.Authors.Books"."Name" FROM "Book" AS "s.Authors.Books" INNER JOIN ( SELECT DISTINCT "s.Authors0"."Id", t0."Id" AS "Id0" FROM "Author" AS "s.Authors0" INNER JOIN ( SELECT s1."Id" FROM "Store" AS s1 ) AS t0 ON "s.Authors0"."StoreId" = t0."Id" ) AS t1 ON "s.Authors.Books"."AuthorId" = t1."Id" ORDER BY t1."Id0", t1."Id"
Include/ThenInclude を使うと INNER JOIN が使用されることがわかります。
注意
上記のように結果をそのまま JSON で出力しようとする場合、 1:多 の 多 のクラスで持つ 1 のインスタンス(例: Store < - > Author の場合の Author.Store )は IgnoreDataMember で JSON に出力されないようにする必要があります。
理由は、 Store > Author > Store ... と無限ループが発生するためですorz
Include を使った場合のフィルタリング
ここまでは全レコードを取得していました。
では、フィルタリングしたい場合はどうすれば良いでしょうか。
例えば Book.Available が true のレコードのみ取得したいとします。
こんなことをしようとすると。。。？
HomeController.cs
～省略～
public async Task< List< Store>> Index()
{
List< Store> results = await _context.Stores
.Include(store => store.Authors)
.ThenInclude(author => author.Books.Where(b => b.Available))
.ToListAsync();
return results;
}
}
}
当然のごとくエラーが発生します。
Include を使ったフィルタリングには対応していないようです。
となると。。。
HomeController.cs
～省略～
public async Task< List< Store>> Index()
{
List< Store> results = await _context.Stores
.Include(s => s.Authors)
.ThenInclude(a => a.Books)
.ToListAsync();
foreach (Store result in results)
{
foreach (Author author in result.Authors)
{
author.Books = author.Books.Where(b => b.Available).ToList();
}
}
return results;
}
}
}
この敗北感よ。。。
C# で JOIN した結果をまとめる
もう一つ、 INNER JOIN して取得した結果を、 C# でまとめる、というのも試したいと思います。
JOIN すると結果は画像のようにバラバラに出力されるので、リストにセットするようにします。
HomeController.cs
～省略～
public async Task< List< Store>> Index()
{
var results = await (from store in _context.Stores
join author in _context.Authors on store.Id equals author.StoreId
join book in _context.Books on author.Id equals book.AuthorId
where book.Available
select new
{
StoreId = store.Id,
StoreName = store.Name,
Author = author,
Book = book,
})
.ToListAsync();
List< Store> mergedResults = new List< Store>();
foreach (var result in results)
{
int index = mergedResults.FindIndex(m => m.Id == result.StoreId);
if(index < 0)
{
index = mergedResults.Count;
mergedResults.Add(new Store
{
Id = result.StoreId,
Name = result.StoreName,
Authors = new List()
});
};
int authorIndex = mergedResults[index].Authors.FindIndex(a => a.Id == result.Author.Id);
if (authorIndex < 0)
{
authorIndex = mergedResults[index].Authors.Count;
result.Author.Books = new List();
mergedResults[index].Authors.Add(result.Author);
}
mergedResults[index].Authors[authorIndex].Books.Add(result.Book);
}
}
}
}
長くなってきたのでいったん切ります。
