🥇MS SQL Server용 C#.NET에서 LINQ 쿼리 최적화의 몇 가지 측면

LINQ는 강력한 새 데이터 조작 언어로 .NET을 도입했습니다. LINQ to SQL을 사용하면 Entity Framework 등을 사용하여 DBMS와 매우 편리하게 통신할 수 있습니다. 그러나 이를 자주 사용하면 개발자는 쿼리 가능한 공급자(예: Entity Framework)가 어떤 종류의 SQL 쿼리를 생성하는지 확인하는 것을 잊어버립니다.

예제를 사용하여 두 가지 주요 사항을 살펴보겠습니다.
이렇게 하려면 SQL Server에서 테스트 데이터베이스를 만들고 다음 쿼리를 사용하여 그 안에 두 개의 테이블을 만듭니다.

테이블 생성

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 테이블을 채워 보겠습니다.

고객 테이블 채우기

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천만 개가 넘는 데이터 행이 있습니다.

이제 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; }
    }
}

그리고 별도의 파일에서 공통 필드를 포함하는 두 엔터티에 대한 기본 클래스 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; }
    }
}

다음으로 별도의 파일에 두 엔터티를 만듭니다.

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에서 생성된 쿼리를 보면 다음과 같습니다.

, 그러면 두 세트(테이블)의 데카르트 곱을 통해 선택이 발생하는지 확인할 수 있습니다.

생성된 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 쿼리에는 하나의 조인 조건만 있을 수 있으므로 여기에서는 각 조건에 대해 두 개의 쿼리를 사용하여 동등한 쿼리를 만든 다음 Union을 통해 결합하여 행 간의 중복을 제거하는 것이 가능합니다.
예, 완전한 중복 행이 반환될 수 있다는 점을 고려하면 쿼리는 일반적으로 동일하지 않습니다. 그러나 실제 생활에서는 완전한 중복 라인이 필요하지 않으며 사람들은 이를 제거하려고 합니다.

이제 이 두 쿼리의 실행 계획을 비교해 보겠습니다.

CROSS JOIN의 경우 평균 실행 시간은 195초입니다.
INNER JOIN-UNION의 경우 평균 실행 시간은 24초 미만입니다.

결과에서 볼 수 있듯이 수백만 개의 레코드가 포함된 두 테이블의 경우 최적화된 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 };

지역 :

두 개의 배열 정의

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

MS SQL Server용 C#.NET에서 LINQ 쿼리를 최적화하는 몇 가지 측면

코멘트를 추가 답장을 취소