CodeZine(コードジン)

特集ページ一覧

N+1問題を回避せよ! LINQから出力されるSQLを見てみよう&遅延ローディングの光と闇

Visual StudioでDB連携も簡単プログラミング ~知っておきたいLINQメソッド式&ラムダ式 第3回

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2015/02/03 14:00
目次

関連するテーブルの情報も取得する

 さて、データベースから取得する情報を増やします。前回定義した、関連テーブルの情報を含めてダンプするDumpProductsメソッドを呼んでみます(リスト4)。

リスト4 関連するテーブルの情報も出力する
using (var context = new CodeZineSampleContext())
{
  //実際に発行されるSQLをコンソールに出力する
  context.Database.Log = Console.WriteLine;
  //Priceが300を超えるものだけ出力する
  IQueryable<Product> products = context.Products.Where(x => x.Price > 300);
  //関連テーブルも含めてダンプする
  DumpProducts(products);
}

private static void DumpProducts(IQueryable<Product> products)
{
    foreach (var product in products) //①1件ずつ取り出す
    {
        //②商品(Products), 出荷元(Makers), 担当者(Employees), 部署(Departments)の情報をダンプ
        Console.WriteLine("Product Name: {0}, Price : {1}\n  Maker Name: {2}\n  Employee Name: {3}, Department: {4}",
            product.Name, product.Price, product.Maker.Name, product.Employee.Name, product.Employee.Department.Name);
    }
}

 今度は大量のSQLが発行されたことに驚いたかもしれません。慌てず騒がず、ステップ実行しながら追って見ましょう。まず①で発行されるのはProductsテーブルの全件を出力するSQLです(リスト5)。これ自体は前のサンプルと変わりません。

リスト5 ProductsテーブルへのSQL
2014/10/26 0:59:26 +09:00
 で接続を開きました
SELECT
    [Extent1].[Id] AS [Id],
    [Extent1].[Name] AS [Name],
    [Extent1].[Price] AS [Price],
    [Extent1].[Employee_Id] AS [Employee_Id],
    [Extent1].[Maker_Id] AS [Maker_Id]
    FROM [dbo].[Products] AS [Extent1]
    WHERE [Extent1].[Price] > 300
-- 2014/10/26 0:59:26 +09:00
 で実行しています
-- 19 ミリ秒で完了しました。結果: SqlDataReader

 しかし、②を実行すると一気に3つのSQLが発行されます(リスト6)。それぞれ、Makers, Employees, DepartmentsテーブルへのSQLが発行されています。「-- EntityKeyValue1: '1' (Type = Int32, IsNullable = false)」という行は、SQLに対して渡されるパラメータを指しています。たとえば最初のSQLであれば、「@EntityKeyValue1」に「1」という値(Productsレコードの最初のIdフィールドの値)が渡されています。

リスト6 Makers, Employees, DepartmentsテーブルへのSQL
SELECT
    [Extent2].[Id] AS [Id],
    [Extent2].[Name] AS [Name]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Makers] AS [Extent2] ON [Extent1].[Maker_Id] = [Extent2].[
Id]
    WHERE ([Extent1].[Maker_Id] IS NOT NULL) AND ([Extent1].[Id] = @EntityKeyVal
ue1)
-- EntityKeyValue1: '1' (Type = Int32, IsNullable = false)
-- 2014/10/26 1:00:44 +09:00
 で実行しています
-- 4 ミリ秒で完了しました。結果: SqlDataReader

SELECT
    [Extent2].[Id] AS [Id],
    [Extent2].[Name] AS [Name],
    [Extent2].[Birthday] AS [Birthday],
    [Extent2].[Department_Id] AS [Department_Id]
    FROM  [dbo].[Products] AS [Extent1]
    INNER JOIN [dbo].[Employees] AS [Extent2] ON [Extent1].[Employee_Id] = [Exte
nt2].[Id]
    WHERE ([Extent1].[Employee_Id] IS NOT NULL) AND ([Extent1].[Id] = @EntityKey
Value1)
-- EntityKeyValue1: '1' (Type = Int32, IsNullable = false)
-- 2014/10/26 1:00:44 +09:00
 で実行しています
-- 7 ミリ秒で完了しました。結果: SqlDataReader

SELECT
    [Extent2].[Id] AS [Id],
    [Extent2].[Name] AS [Name]
    FROM  [dbo].[Employees] AS [Extent1]
    INNER JOIN [dbo].[Departments] AS [Extent2] ON [Extent1].[Department_Id] = [
Extent2].[Id]
    WHERE ([Extent1].[Department_Id] IS NOT NULL) AND ([Extent1].[Id] = @EntityK
eyValue1)
-- EntityKeyValue1: '1' (Type = Int32, IsNullable = false)
-- 2014/10/26 1:00:44 +09:00
 で実行しています
-- 2 ミリ秒で完了しました。結果: SqlDataReader

 さらにステップ実行を続けると、製品担当者(Employee)、出荷元(Maker)、部署(Department)が変化するたびに、上記のSQLが適宜呼び出されている様子が分かるでしょう。それぞれのSQLはシンプルで実行時間も短いとはいえ、元のProductsのレコードを1件取り出すたびに、SQLが何度も発行されるというのは効率的な処理とは言えません。どうしてこんなSQLが発行されるのでしょうか。

遅延ローディングとN+1問題

 Entity Frameworkが実際にどのような処理を行っているのか、リスト4のコードから順を追って考えていきましょう。実際の処理の流れは以下のようになります。

 ①「foearch文でproductsの各要素を使うことになったから、データを取得しよう。productsの情報元はProdcutsテーブルに相当するcontext.Productsプロパティだから、ここでSQLを発行しよう。Whereメソッド等も付いてないから、純粋にProductsテーブルからSELECTするだけでOKだね。全件取り出せたから1行ずつproduct変数に値をセットしてforeachの中に入ろう」:SQLを1回実行

 ②「まずはproduct.Nameとproduct.Priceを出力するんだね。これは①のSQLからデータが取れているからOK。お次はproduct.Maker.Name……Makerプロパティは①のSQLだと取れていないデータだな。仕方ない、Makersテーブルから1行取るSQLを発行しよう。SELECT文へのパラメータはproductのIdプロパティを渡せばOKだな……次はEmployeeだから同じようにEmployeesから1行取るSQLを発行して……」:SQLを合計3回実行

 ②では、明示的に取得するよう指定していないテーブルについても、自動的にSQLを発行してデータを取得しています。これを遅延ローディングといいます。あらかじめ取得するテーブルを指定する必要が無いため、レコード数が少ない時は便利な機能です。

 しかし、Productsテーブルに大量のデータが入っている場合、①の全件取得は1回のSQL発行なのに対し、②では最大3回×レコード数のSQLが発行されることになってしまい、非効率な処理となってしまいます。

 これはしばしば「N+1問題」と呼ばれ、遅延ローディングを行うフレームワークでよく起きるパフォーマンス上の問題です(図1)。

図1 遅延ローディングによるN+1問題の発生
図1 遅延ローディングによるN+1問題の発生

 まずクエリ対象のテーブルをN件取得し(SQL実行は1回)、N回ループを回すあいだに関連テーブルを1件ずつ取得(SQL実行はN回)することになり、結果的にSQLを「N+1」回実行することになります。

 N+1問題で悩ましいのは、この問題が発生してもデータ自体は正しく取得できており、データの件数が少なければそれほど問題にならないことです。たとえば、アプリケーションの実装時点では、テストデータが少ないために気付かず、大量のデータを投入して本格的なテストを開始する段階で問題になるなど、プログラマにとっての頭痛の種です。


  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:Visual StudioでDB連携も簡単プログラミング ~知っておきたいLINQメソッド式&ラムダ式

著者プロフィール

  • WINGSプロジェクト 土井 毅(ドイ ツヨシ)

    <WINGSプロジェクトについて> 有限会社 WINGSプロジェクトが運営する、テクニカル執筆コミュニティ(代表 山田祥寛)。主にWeb開発分野の書籍/記事執筆、翻訳、講演等を幅広く手がける。2018年11月時点での登録メンバは55名で、現在も執筆メンバを募集中。興味のある方は、どしどし応募頂...

  • 山田 祥寛(ヤマダ ヨシヒロ)

    静岡県榛原町生まれ。一橋大学経済学部卒業後、NECにてシステム企画業務に携わるが、2003年4月に念願かなってフリーライターに転身。Microsoft MVP for ASP/ASP.NET。執筆コミュニティ「WINGSプロジェクト」代表。 主な著書に「入門シリーズ(サーバサイドAjax/XM...

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5