C#.NET で LINQ クエリを最適化する方法

導入

В この記事 いくつかの最適化方法が検討されました LINQクエリ.
ここでは、以下に関連するコード最適化へのいくつかのアプローチも紹介します。 LINQクエリ.

それが知られている LINQ(言語統合クエリ) は、データ ソースをクエリするためのシンプルで便利な言語です。

А LINQ to SQL DBMS 内のデータにアクセスするためのテクノロジです。これはデータを操作するための強力なツールであり、クエリは宣言型言語を通じて構築され、その後、次のように変換されます。 SQLクエリ プラットフォームに送信され、実行のためにデータベース サーバーに送信されます。この場合、DBMS とは MS SQL Server.

しかし、 LINQクエリ 最適に書かれたものに変換されない SQLクエリ、経験豊富な DBA であれば、最適化のあらゆるニュアンスを含めて書くことができます。 SQL クエリ:

1. 最適な接続 (登録) および結果のフィルタリング (WHERE)
2. 接続とグループ条件の使用における多くのニュアンス
3. 交換条件のバリエーションが豊富 IN на 存在するи ありませんで、<> 上 存在する
4. 一時テーブル、CTE、テーブル変数による結果の中間キャッシュ
5. 文の使用 (オプション) 説明と表のヒント付き WITH （...）
6. 選択中の冗長なデータ読み取りを取り除く手段の XNUMX つとしてインデックス付きビューを使用する

結果として生じる主なパフォーマンスのボトルネック SQL クエリ コンパイル時 LINQクエリ 次のとおりです。

1. データ選択メカニズム全体を XNUMX つのリクエストに統合
2. 同一のコード ブロックを複製し、最終的には複数の不要なデータの読み取りにつながる
3. 複数の要素からなる条件のグループ (論理的な「and」と「or」) - そして и ORを複雑な条件に組み合わせると、オプティマイザが必要なフィールドに適切な非クラスター化インデックスを持ち、最終的にクラスター化インデックスに対してスキャンを開始するという事実が生じます (インデックススキャン) 条件のグループごと
4. サブクエリのネストが深いため、解析が非常に問題になります SQL文 開発者側のクエリ プランの分析と DBA

最適化手法

それでは、最適化メソッドに直接移りましょう。

1) 追加のインデックス作成

多くの場合、クエリ全体が XNUMX つまたは XNUMX つのメイン テーブル (アプリケーション、ユーザー、操作) を中心に、標準の条件セット (IsClosed、Canceled、Enabled、Status) を使用して構築されるため、メインの選択テーブルでフィルターを検討することをお勧めします。特定されたサンプルに対して適切なインデックスを作成することが重要です。

これらのフィールドを選択すると、クエリに返されるセットが大幅に制限される場合、このソリューションは意味があります。

たとえば、500000 件のアプリケーションがあります。ただし、アクティブなアプリケーションは 2000 件しかありません。次に、正しく選択されたインデックスにより、次のような問題が発生します。 インデックススキャン 大きなテーブルの場合は、非クラスター化インデックスを使用してデータをすばやく選択できます。

また、インデックスの不足は、クエリ プランの解析またはシステム ビュー統計の収集を求めるプロンプトによって特定できます。 MS SQL Server:

1. sys.dm_db_missing_index_groups
2. sys.dm_db_missing_index_group_stats
3. sys.dm_db_missing_index_details

空間インデックスを除き、すべてのビュー データには、欠落しているインデックスに関する情報が含まれています。

ただし、インデックスとキャッシュは、多くの場合、不適切に記述された場合の影響に対処する方法です。 LINQクエリ и SQL クエリ.

厳しい生活習慣が示すように、多くの場合、企業にとって、特定の期限までにビジネス機能を実装することが重要です。したがって、重いリクエストはキャッシュを使用してバックグラウンドに転送されることがよくあります。

ユーザーは必ずしも最新のデータを必要とするわけではなく、ユーザー インターフェイスの応答性には許容可能なレベルがあるため、これは部分的には正当化されます。

このアプローチにより、ビジネス ニーズの解決は可能になりますが、問題の解決が遅れるだけで、最終的には情報システムのパフォーマンスが低下します。

追加する必要があるインデックスを検索する過程で、提案が行われることも覚えておく価値があります。 MS SQL 次のような条件下では、最適化が正しく行われない可能性があります。

1. 同様のフィールドのセットを持つインデックスがすでに存在する場合
2. インデックス作成の制限によりテーブル内のフィールドにインデックスを作成できない場合 (詳細は後述) ここで).

2) 属性を XNUMX つの新しい属性に結合する

場合によっては、条件グループの基礎として機能する XNUMX つのテーブルの一部のフィールドを、XNUMX つの新しいフィールドを導入することで置き換えることができます。

これは、ステータス フィールドに特に当てはまります。ステータス フィールドは通常、ビット型または整数型です。

例：

IsClosed = 0 かつキャンセル済み = 0 かつ有効 = 0 に置き換えられる ステータス = 1.

ここでは、これらのステータスがテーブルに確実に入力されるようにするために、整数の Status 属性が導入されています。次に、この新しい属性にインデックスが付けられます。

不必要な計算を行わずにデータにアクセスするため、これはパフォーマンスの問題に対する根本的な解決策です。

3) ビューの具体化

残念ながら、 LINQクエリ 一時テーブル、CTE、テーブル変数は直接使用できません。

ただし、この場合に最適化する別の方法、つまりインデックス付きビューがあります。

条件グループ (上記の例から) IsClosed = 0 かつキャンセル済み = 0 かつ有効 = 0 (または他の同様の条件のセット) は、インデックス付きビューでそれらを使用し、大規模なセットからデータの小さなスライスをキャッシュするための良いオプションになります。

ただし、ビューを実体化する場合には、いくつかの制限があります。

1. サブクエリ、句の使用 存在する を使用して置き換える必要があります 登録
2. 文章は使えない 連合, UNION ALL, 例外, 交差する
3. テーブルのヒントと句は使用できません オプション
4. サイクルを扱う可能性はありません
5. 異なるテーブルのデータを XNUMX つのビューに表示することはできません

インデックス付きビューを使用する本当のメリットは、実際にインデックスを作成することによってのみ得られることを覚えておくことが重要です。

ただし、ビューを呼び出す場合、これらのインデックスは使用できない場合があり、明示的に使用するには、次のように指定する必要があります。 あり(拡張なし).

以来 LINQクエリ テーブル ヒントを定義することは不可能なので、別の表現、つまり次の形式の「ラッパー」を作成する必要があります。

CREATE VIEW ИМЯ_представления AS SELECT * FROM MAT_VIEW WITH (NOEXPAND);

4) テーブル関数の使用

しばしば LINQクエリ サブクエリの大きなブロック、または複雑な構造のビューを使用するブロックは、非常に複雑で次善の実行構造を持つ最終的なクエリを形成します。

テーブル関数を使用する主な利点 LINQクエリ:

1. ビューの場合と同様に、オブジェクトとして使用および指定できる機能ですが、一連の入力パラメータを渡すこともできます。
   FROM FUNCTION(@param1, @param2 ...)
   これにより、柔軟なデータサンプリングが可能になります。
2. テーブル関数を使用する場合、上記のインデックス付きビューの場合のような強い制限はありません。
   1. テーブルのヒント:
      スルー LINQ クエリ時に使用するインデックスを指定したり、データ分離レベルを決定したりすることはできません。
      しかし、この関数にはこれらの機能があります。
      この関数を使用すると、インデックスとデータ分離レベルを操作するためのルールが定義されている、ほぼ一定の実行クエリ プランを実現できます。
   2. この関数を使用すると、インデックス付きビューと比較して、以下を取得できます。
      • 複雑なデータ サンプリング ロジック (ループを使用する場合も含む)
      • さまざまなテーブルからデータをフェッチする
      • の使用 連合 и 存在する

3. 提案 オプション 同時実行制御を提供する必要がある場合に非常に役立ちます オプション(MAXDOP N)、クエリ実行プランの順序。例えば：
   • クエリプランの強制再作成を指定できます。 オプション (再コンパイル)
   • クエリで指定された結合順序をクエリ プランで強制的に使用するかどうかを指定できます。 オプション（強制命令）

   詳細については、 オプション 説明された ここで.

4. 最も狭く、最も必要なデータ スライスを使用します。
   (インデックス付きビューの場合のように) 大きなデータ セットをキャッシュに保存する必要はなく、キャッシュからパラメータでデータをフィルタリングする必要があります。
   たとえば、フィルターが適用されるテーブルがあります。 WHERE XNUMXつのフィールドが使用されます (a、b、c).

   従来、すべてのリクエストには一定の条件がありました a = 0 および b = 0.

   ただし、フィールドへのリクエストは、 c より変化しやすい。

   条件を付けましょう a = 0 および b = 0 これは、必要な結果セットを数千のレコードに制限するのに非常に役立ちますが、 с 選択を XNUMX 件のレコードに絞り込みます。

   ここではテーブル関数の方が良いかもしれません。

   また、テーブル 関数は実行時間の予測可能性が高く、一貫性があります。

例

例として質問データベースを使用した実装例を見てみましょう。

リクエストがあります SELECT、複数のテーブルを結合し、XNUMX つのビュー (OperativeQuestions) を使用します。所属は電子メール (経由) でチェックされます。 存在する)「操作上の質問」:

要望No.1

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM [dbo].[Questions] AS [Extent1]
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id],
[Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId],
[Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]) AND ( EXISTS (SELECT
1 AS [C1]
FROM [dbo].[OperativeQuestions] AS [Extent5]
WHERE (([Extent5].[Email] = @p__linq__0) OR (([Extent5].[Email] IS NULL) 
AND (@p__linq__0 IS NULL))) AND ([Extent5].[Id] = [Extent1].[Id])
));

ビューはかなり複雑な構造をしています。サブクエリ結合があり、並べ替えが使用されます。 DISTINCTこれは一般に、かなりリソースを大量に消費する操作です。

OperativeQuestions のサンプルは約 XNUMX 万件のレコードです。

このクエリの主な問題は、外部クエリからのレコードに対して、内部サブクエリが [OperativeQuestions] ビューで実行されることです。これにより、[Email] = @p__linq__0 の場合、出力選択を制限できるようになります (経由) 存在する) 最大数百のレコード。

また、サブクエリでは [Email] = @p__linq__0 によってレコードを XNUMX 回計算し、その後、これらの数百のレコードを ID と質問によって接続する必要があり、クエリは高速になるように思われるかもしれません。

実際、すべてのテーブルには順次接続があり、ID 質問と OperativeQuestions からの ID の対応を確認し、電子メールでフィルタリングします。

実際、このリクエストは何万もの OperativeQuestions レコードすべてを処理しますが、電子メール経由で必要なのは関心のあるデータのみです。

OperativeQuestions ビューのテキスト:

要望No.2

 
CREATE VIEW [dbo].[OperativeQuestions]
AS
SELECT DISTINCT Q.Id, USR.email AS Email
FROM            [dbo].Questions AS Q INNER JOIN
                         [dbo].ProcessUserAccesses AS BPU ON BPU.ProcessId = CQ.Process_Id 
OUTER APPLY
                     (SELECT   1 AS HasNoObjects
                      WHERE   NOT EXISTS
                                    (SELECT   1
                                     FROM     [dbo].ObjectUserAccesses AS BOU
                                     WHERE   BOU.ProcessUserAccessId = BPU.[Id] AND BOU.[To] IS NULL)
) AS BO INNER JOIN
                         [dbo].Users AS USR ON USR.Id = BPU.UserId
WHERE        CQ.[Exp] = 0 AND CQ.AnswerId IS NULL AND BPU.[To] IS NULL 
AND (BO.HasNoObjects = 1 OR
              EXISTS (SELECT   1
                           FROM   [dbo].ObjectUserAccesses AS BOU INNER JOIN
                                      [dbo].ObjectQuestions AS QBO 
                                                  ON QBO.[Object_Id] =BOU.ObjectId
                               WHERE  BOU.ProcessUserAccessId = BPU.Id 
                               AND BOU.[To] IS NULL AND QBO.Question_Id = CQ.Id));

DbContext の初期ビュー マッピング (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}

初期の LINQ クエリ

var businessObjectsData = await context
    .OperativeQuestions
    .Where(x => x.Email == Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

この特定のケースでは、インフラストラクチャの変更を行わず、既製の結果 (「アクティブ クエリ」) を含む別のテーブルを導入せずに、この問題の解決策を検討しています。テーブルにデータを入力して最新の状態に保つためのメカニズムが必要になります。 。

これは良い解決策ですが、この問題を最適化する別のオプションがあります。

主な目的は、OperativeQuestions ビューから [Email] = @p__linq__0 によるエントリをキャッシュすることです。

テーブル関数 [dbo].[OperativeQuestionsUserMail] をデータベースに導入します。

電子メールを入力パラメーターとして送信すると、値のテーブルが返されます。

要望No.3

CREATE FUNCTION [dbo].[OperativeQuestionsUserMail]
(
    @Email  nvarchar(4000)
)
RETURNS
@tbl TABLE
(
    [Id]           uniqueidentifier,
    [Email]      nvarchar(4000)
)
AS
BEGIN
        INSERT INTO @tbl ([Id], [Email])
        SELECT Id, @Email
        FROM [OperativeQuestions]  AS [x] WHERE [x].[Email] = @Email;
     
    RETURN;
END

これにより、事前定義されたデータ構造を持つ値のテーブルが返されます。

OperativeQuestionsUserMail へのクエリを最適化し、最適なクエリ プランを作成するには、厳密な構造が必要です。 テーブルをリターンとして返す...

この場合、必要なクエリ 1 はクエリ 4 に変換されます。

要望No.4

(@p__linq__0 nvarchar(4000))SELECT
1 AS [C1],
[Extent1].[Id] AS [Id],
[Join2].[Object_Id] AS [Object_Id],
[Join2].[ObjectType_Id] AS [ObjectType_Id],
[Join2].[Name] AS [Name],
[Join2].[ExternalId] AS [ExternalId]
FROM (
    SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] (@p__linq__0)
) AS [Extent0]
INNER JOIN [dbo].[Questions] AS [Extent1] ON([Extent0].Id=[Extent1].Id)
INNER JOIN (SELECT [Extent2].[Object_Id] AS [Object_Id], [Extent2].[Question_Id] AS [Question_Id], [Extent3].[ExternalId] AS [ExternalId], [Extent3].[ObjectType_Id] AS [ObjectType_Id], [Extent4].[Name] AS [Name]
FROM [dbo].[ObjectQuestions] AS [Extent2]
INNER JOIN [dbo].[Objects] AS [Extent3] ON [Extent2].[Object_Id] = [Extent3].[Id]
LEFT OUTER JOIN [dbo].[ObjectTypes] AS [Extent4] 
ON [Extent3].[ObjectType_Id] = [Extent4].[Id] ) AS [Join2] 
ON [Extent1].[Id] = [Join2].[Question_Id]
WHERE ([Extent1].[AnswerId] IS NULL) AND (0 = [Extent1].[Exp]);

DbContext でのビューと関数のマッピング (EF Core 2)

public class QuestionsDbContext : DbContext
{
    //...
    public DbQuery<OperativeQuestion> OperativeQuestions { get; set; }
    //...
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<OperativeQuestion>().ToView("OperativeQuestions");
    }
}
 
public static class FromSqlQueries
{
    public static IQueryable<OperativeQuestion> GetByUserEmail(this DbQuery<OperativeQuestion> source, string Email)
        => source.FromSql($"SELECT Id, Email FROM [dbo].[OperativeQuestionsUserMail] ({Email})");
}

最終的な LINQ クエリ

var businessObjectsData = await context
    .OperativeQuestions
    .GetByUserEmail(Email)
    .Include(x => x.Question)
    .Select(x => x.Question)
    .SelectMany(x => x.ObjectQuestions,
                (x, bo) => new
                {
                    Id = x.Id,
                    ObjectId = bo.Object.Id,
                    ObjectTypeId = bo.Object.ObjectType.Id,
                    ObjectTypeName = bo.Object.ObjectType.Name,
                    ObjectExternalId = bo.Object.ExternalId
                })
    .ToListAsync();

実行時間は 200 ～ 800 ミリ秒から 2 ～ 20 ミリ秒などに低下し、数十倍速くなりました。

もっと平均的に考えると、350 ミリ秒ではなく 8 ミリ秒になります。

明らかな利点から、次のようなメリットも得られます。

1. 読み取り負荷の全体的な軽減、
2. ブロックされる可能性が大幅に減少します
3. 平均ブロッキング時間を許容値まで短縮する

出力

データベース呼び出しの最適化と微調整 MS SQL スルー LINQ 解決できる問題です。

この仕事では、注意力と一貫性が非常に重要です。

プロセスの開始時:

1. リクエストが機能するデータ (値、選択されたデータ型) を確認する必要があります。
2. このデータの適切なインデックス付けを実行する
3. テーブル間の結合条件の正確性をチェックする

次の最適化反復により、次のことが明らかになります。

1. リクエストの基礎を作成し、メインのリクエストフィルターを定義します
2. 同様のクエリブロックを繰り返し、条件の交差を分析する
3. SSMS または他の GUI で SQLサーバー それ自体を最適化します SQLクエリ (中間データ ストレージを割り当て、このストレージを使用して結果のクエリを構築します (複数ある場合があります))
4. 最終段階では、その結果を基礎として、 SQLクエリ、構造が再構築されています LINQクエリ

結果として LINQクエリ 特定された最適なものと構造が同一になるはずです SQLクエリ ポイント3から。

感謝

同僚に感謝します ジョブジェムズ и アレックス・オズル 会社から 硬音 この資料の作成にご協力ください。

出所： habr.com