LINQ は、強力な新しいデータ操作言語として .NET に組み込まれました。 LINQ to SQL の一部として、Entity Framework などを使用して DBMS と非常に簡単に通信できるようになります。 ただし、これを頻繁に使用すると、開発者はクエリ可能なプロバイダー (この場合は Entity Framework) がどのような種類の SQL クエリを生成するかを確認することを忘れてしまいます。
例を使用して XNUMX つの主要なポイントを見てみましょう。
これを行うには、SQL Server にテスト データベースを作成し、次のクエリを使用してその中に XNUMX つのテーブルを作成します。
テーブルの作成
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Ref](
[ID] [int] NOT NULL,
[ID2] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
CONSTRAINT [PK_Ref] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Ref] ADD CONSTRAINT [DF_Ref_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
USE [TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[ID] [int] NOT NULL,
[Name] [nvarchar](255) NOT NULL,
[Ref_ID] [int] NOT NULL,
[InsertUTCDate] [datetime] NOT NULL,
[Ref_ID2] [int] NOT NULL,
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_Ref_ID] DEFAULT ((0)) FOR [Ref_ID]
GO
ALTER TABLE [dbo].[Customer] ADD CONSTRAINT [DF_Customer_InsertUTCDate] DEFAULT (getutcdate()) FOR [InsertUTCDate]
GO
次に、次のスクリプトを実行して、Ref テーブルにデータを入力しましょう。
Refテーブルを埋める
USE [TEST]
GO
DECLARE @ind INT=1;
WHILE(@ind<1200000)
BEGIN
INSERT INTO [dbo].[Ref]
([ID]
,[ID2]
,[Name])
SELECT
@ind
,@ind
,CAST(@ind AS NVARCHAR(255));
SET @ind=@ind+1;
END
GO
次のスクリプトを使用して、同様に Customer テーブルに値を入力してみましょう。
Customer テーブルへのデータの入力
USE [TEST]
GO
DECLARE @ind INT=1;
DECLARE @ind_ref INT=1;
WHILE(@ind<=12000000)
BEGIN
IF(@ind%3=0) SET @ind_ref=1;
ELSE IF (@ind%5=0) SET @ind_ref=2;
ELSE IF (@ind%7=0) SET @ind_ref=3;
ELSE IF (@ind%11=0) SET @ind_ref=4;
ELSE IF (@ind%13=0) SET @ind_ref=5;
ELSE IF (@ind%17=0) SET @ind_ref=6;
ELSE IF (@ind%19=0) SET @ind_ref=7;
ELSE IF (@ind%23=0) SET @ind_ref=8;
ELSE IF (@ind%29=0) SET @ind_ref=9;
ELSE IF (@ind%31=0) SET @ind_ref=10;
ELSE IF (@ind%37=0) SET @ind_ref=11;
ELSE SET @ind_ref=@ind%1190000;
INSERT INTO [dbo].[Customer]
([ID]
,[Name]
,[Ref_ID]
,[Ref_ID2])
SELECT
@ind,
CAST(@ind AS NVARCHAR(255)),
@ind_ref,
@ind_ref;
SET @ind=@ind+1;
END
GO
したがって、1 つのテーブルを受け取りました。10 つは XNUMX 万行を超えるデータを持ち、もう XNUMX つは XNUMX 万行を超えるデータを持ちます。
次に、Visual Studio で、テスト用の Visual C# コンソール アプリ (.NET Framework) プロジェクトを作成する必要があります。
次に、Entity Framework がデータベースと対話するためのライブラリを追加する必要があります。
追加するには、プロジェクトを右クリックし、コンテキスト メニューから [NuGet パッケージの管理] を選択します。
次に、表示される NuGet パッケージ管理ウィンドウの検索ウィンドウに「Entity Framework」という単語を入力し、Entity Framework パッケージを選択してインストールします。
次に、App.config ファイルで、configSections 要素を閉じた後、次のブロックを追加する必要があります。
<connectionStrings>
<add name="DBConnection" connectionString="data source=ИМЯ_ЭКЗЕМПЛЯРА_MSSQL;Initial Catalog=TEST;Integrated Security=True;" providerName="System.Data.SqlClient" />
</connectionStrings>
connectionString には、接続文字列を入力する必要があります。
次に、3 つのインターフェイスを別のファイルに作成しましょう。
- IBaseEntityID インターフェイスの実装
namespace TestLINQ { public interface IBaseEntityID { int ID { get; set; } } } - IBaseEntityName インターフェイスの実装
namespace TestLINQ { public interface IBaseEntityName { string Name { get; set; } } } - IBaseNameInsertUTCDate インターフェイスの実装
namespace TestLINQ { public interface IBaseNameInsertUTCDate { DateTime InsertUTCDate { get; set; } } }
そして、別のファイルで、XNUMX つのエンティティの基本クラス BaseEntity を作成します。これには共通のフィールドが含まれます。
基本クラスBaseEntityの実装
namespace TestLINQ
{
public class BaseEntity : IBaseEntityID, IBaseEntityName, IBaseNameInsertUTCDate
{
public int ID { get; set; }
public string Name { get; set; }
public DateTime InsertUTCDate { get; set; }
}
}
次に、XNUMX つのエンティティを別のファイルに作成します。
- Refクラスの実装
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Ref")] public class Ref : BaseEntity { public int ID2 { get; set; } } } - Customer クラスの実装
using System.ComponentModel.DataAnnotations.Schema; namespace TestLINQ { [Table("Customer")] public class Customer: BaseEntity { public int Ref_ID { get; set; } public int Ref_ID2 { get; set; } } }
次に、別のファイルに UserContext コンテキストを作成しましょう。
UserContex クラスの実装
using System.Data.Entity;
namespace TestLINQ
{
public class UserContext : DbContext
{
public UserContext()
: base("DbConnection")
{
Database.SetInitializer<UserContext>(null);
}
public DbSet<Customer> Customer { get; set; }
public DbSet<Ref> Ref { get; set; }
}
}
MS SQL Server の EF 経由で LINQ to SQL による最適化テストを実行するための既製のソリューションを受け取りました。
次に、次のコードを Program.cs ファイルに入力します。
Program.cs ファイル
using System;
using System.Collections.Generic;
using System.Linq;
namespace TestLINQ
{
class Program
{
static void Main(string[] args)
{
using (UserContext db = new UserContext())
{
var dblog = new List<string>();
db.Database.Log = dblog.Add;
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
var result = query.Take(1000).ToList();
Console.WriteLine(dblog[1]);
Console.ReadKey();
}
}
}
}
次に、プロジェクトを起動しましょう。
作業が終了すると、コンソールに以下が表示されます。
生成されたSQLクエリ
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON ([Extent1].[Ref_ID] = [Extent2].[ID]) AND ([Extent1].[Ref_ID2] = [Extent2].[ID2])
つまり、一般に、LINQ クエリは MS SQL Server DBMS への SQL クエリを非常に適切に生成しました。
次に、LINQ クエリの AND 条件を OR に変更しましょう。
LINQクエリ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
|| (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
そして、アプリケーションを再度起動してみましょう。
コマンドの実行時間が 30 秒を超えるため、実行はエラーでクラッシュします。
LINQ によって生成されたクエリを見ると、次のようになります。
を選択すると、XNUMX つのセット (テーブル) のデカルト積を通じて選択が行われることを確認できます。
生成されたSQLクエリ
SELECT TOP (1000)
[Extent1].[Ref_ID] AS [Ref_ID],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
CROSS JOIN [dbo].[Ref] AS [Extent2]
WHERE [Extent1].[Ref_ID] = [Extent2].[ID] OR [Extent1].[Ref_ID2] = [Extent2].[ID2]
LINQ クエリを次のように書き換えてみましょう。
最適化されたLINQクエリ
var query = (from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID equals e2.ID
select new { Data1 = e1.Name, Data2 = e2.Name }).Union(
from e1 in db.Customer
join e2 in db.Ref
on e1.Ref_ID2 equals e2.ID2
select new { Data1 = e1.Name, Data2 = e2.Name });
次に、次の SQL クエリを取得します。
SQLクエリ
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3]
FROM ( SELECT DISTINCT TOP (1000)
[UnionAll1].[C1] AS [C1],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[Name1] AS [C3]
FROM (SELECT
1 AS [C1],
[Extent1].[Name] AS [Name],
[Extent2].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent1]
INNER JOIN [dbo].[Ref] AS [Extent2] ON [Extent1].[Ref_ID] = [Extent2].[ID]
UNION ALL
SELECT
1 AS [C1],
[Extent3].[Name] AS [Name],
[Extent4].[Name] AS [Name1]
FROM [dbo].[Customer] AS [Extent3]
INNER JOIN [dbo].[Ref] AS [Extent4] ON [Extent3].[Ref_ID2] = [Extent4].[ID2]) AS [UnionAll1]
) AS [Limit1]
悲しいことに、LINQ クエリでは結合条件は XNUMX つしかないため、条件ごとに XNUMX つのクエリを使用して同等のクエリを作成し、それらを Union で結合して行間の重複を削除することができます。
はい、完全に重複した行が返される可能性があることを考慮すると、クエリは通常は等価ではありません。 しかし、現実の生活では、完全な重複行は必要ないため、人々はそれらを削除しようとします。
次に、これら XNUMX つのクエリの実行プランを比較してみましょう。
- CROSS JOIN の場合、平均実行時間は 195 秒です。
- INNER JOIN-UNION の場合、平均実行時間は 24 秒未満です。
結果からわかるように、数百万のレコードを持つ XNUMX つのテーブルの場合、最適化された LINQ クエリは、最適化されていないクエリよりも何倍も高速です。
条件に AND を含むオプションの場合、次の形式の LINQ クエリです。
LINQクエリ
var query = from e1 in db.Customer
from e2 in db.Ref
where (e1.Ref_ID == e2.ID)
&& (e1.Ref_ID2 == e2.ID2)
select new { Data1 = e1.Name, Data2 = e2.Name };
ほとんどの場合、正しい SQL クエリが生成され、平均して約 1 秒で実行されます。
次のようなクエリの代わりに LINQ to Objects 操作の場合も同様です。
LINQ クエリ (最初のオプション)
var query = from e1 in seq1
from e2 in seq2
where (e1.Key1==e2.Key1)
&& (e1.Key2==e2.Key2)
select new { Data1 = e1.Data, Data2 = e2.Data };
次のようなクエリを使用できます。
LINQ クエリ (最初のオプション)
var query = from e1 in seq1
join e2 in seq2
on new { e1.Key1, e1.Key2 } equals new { e2.Key1, e2.Key2 }
select new { Data1 = e1.Data, Data2 = e2.Data };
ここで：
XNUMX つの配列の定義
Para[] seq1 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 4, Data = "999" } };
Para[] seq2 = new[] { new Para { Key1 = 1, Key2 = 2, Data = "777" }, new Para { Key1 = 2, Key2 = 3, Data = "888" }, new Para { Key1 = 3, Key2 = 5, Data = "999" } };
、Para 型は次のように定義されます。
パラ型の定義
class Para
{
public int Key1, Key2;
public string Data;
}
そこで、MS SQL Server への LINQ クエリを最適化する際のいくつかの側面を検討しました。
残念なことに、経験豊富で一流の .NET 開発者でさえ、使用する命令が舞台裏で何をしているのかを理解する必要があることを忘れています。 そうしないと、彼らは構成者となり、将来、ソフトウェア ソリューションを拡張するときと外部環境条件のわずかな変化の両方で時限爆弾を仕掛ける可能性があります。
簡単なレビューも行われました .
テストのソース - プロジェクト自体、TEST データベース内のテーブルの作成、およびこれらのテーブルにデータを入力するソースが配置されています。 .
また、このリポジトリの Plans フォルダーには、OR 条件を使用してクエリを実行するためのプランがあります。
出所： habr.com