C#.NET에서 LINQ 쿼리를 최적화하는 방법

소개

В 이 기사 몇 가지 최적화 방법이 고려되었습니다. LINQ 쿼리.
여기서는 다음과 관련된 코드 최적화에 대한 몇 가지 추가 접근 방식을 제시합니다. LINQ 쿼리.

그거 알았어. 링크(Language-Integrated Query)는 데이터 소스를 쿼리하기 위한 간단하고 편리한 언어입니다.

А LINQ에서 SQL로 DBMS의 데이터에 접근하는 기술이다. 이는 선언적 언어를 통해 쿼리가 구성된 후 다음으로 변환되는 데이터 작업을 위한 강력한 도구입니다. SQL 쿼리 플랫폼으로 전송되어 실행을 위해 데이터베이스 서버로 전송됩니다. 우리의 경우 DBMS는 다음을 의미합니다. MS SQL 서버.

그러나, LINQ 쿼리 최적으로 작성된 것으로 변환되지 않습니다 SQL 쿼리, 숙련된 DBA가 최적화의 모든 뉘앙스를 담아 작성할 수 있습니다. SQL 쿼리:

1. 최적의 연결(JOIN) 및 결과 필터링(WHERE)
2. 연결 및 그룹 조건 사용 시 많은 차이
3. 교체 조건의 다양한 변형 IN 에 존재и 안, <> 켜짐 존재
4. 임시 테이블, CTE, 테이블 변수를 통한 결과의 중간 캐싱
5. 문장의 사용 (OPTION) 지침 및 표 힌트 포함 세이프가드가 (...)
6. 선택 중에 중복된 데이터 읽기를 제거하기 위한 수단 중 하나로 인덱싱된 뷰 사용

결과의 주요 성능 병목 현상 SQL 쿼리 컴파일할 때 LINQ 쿼리 위치 :

1. 하나의 요청으로 전체 데이터 선택 메커니즘 통합
2. 동일한 코드 블록을 복제하여 궁극적으로 불필요한 데이터 읽기가 여러 번 발생합니다.
3. 다중 구성 요소 조건 그룹(논리적 "and" 및 "or") - 및 и OR, 복잡한 조건으로 결합하면 필요한 필드에 적합한 비클러스터형 인덱스를 갖는 최적화 프로그램이 궁극적으로 클러스터형 인덱스에 대해 스캔하기 시작한다는 사실로 이어집니다(인덱스 스캔) 조건 그룹별
4. 하위 쿼리의 깊은 중첩으로 인해 구문 분석이 매우 문제가 됩니다. SQL 문 개발자 측의 쿼리 계획 분석 및 DBA

최적화 방법

이제 최적화 방법으로 직접 이동해 보겠습니다.

1) 추가 인덱싱

전체 쿼리가 하나 또는 두 개의 기본 테이블(응용 프로그램-사람-작업)을 중심으로 표준 조건 집합(IsClosed, Canceled, Enabled, Status)을 사용하여 구축되는 경우가 많기 때문에 기본 선택 테이블의 필터를 고려하는 것이 가장 좋습니다. 식별된 샘플에 대해 적절한 인덱스를 생성하는 것이 중요합니다.

이 솔루션은 이러한 필드를 선택하면 반환되는 쿼리 집합이 크게 제한되는 경우에 적합합니다.

예를 들어, 500000개의 애플리케이션이 있습니다. 그러나 활성 애플리케이션은 2000개뿐입니다. 그러면 올바르게 선택된 색인이 우리를 구해줄 것입니다. 인덱스 스캔 큰 테이블에서 비클러스터형 인덱스를 통해 데이터를 빠르게 선택할 수 있습니다.

또한 쿼리 계획을 구문 분석하거나 시스템 뷰 통계를 수집하라는 프롬프트를 통해 인덱스 부족을 식별할 수 있습니다. MS SQL 서버:

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) 속성을 하나의 새로운 속성으로 병합

때로는 조건 그룹의 기초로 사용되는 한 테이블의 일부 필드가 하나의 새 필드를 도입하여 대체될 수 있습니다.

이는 일반적으로 유형이 비트 또는 정수인 상태 필드의 경우 특히 그렇습니다.

예 :

IsClosed = 0 AND 취소됨 = 0 AND 활성화됨 = 0 로 대체됩니다 상태 = 1.

여기에 이러한 상태가 테이블에 채워지도록 정수 상태 속성이 도입됩니다. 다음으로 이 새 속성이 색인화됩니다.

불필요한 계산 없이 데이터에 접근하기 때문에 이는 성능 문제에 대한 근본적인 해결책입니다.

3) 뷰의 구체화

불행하게도, LINQ 쿼리 임시 테이블, CTE 및 테이블 변수는 직접 사용할 수 없습니다.

그러나 이 경우를 최적화하는 또 다른 방법, 즉 인덱싱된 뷰가 있습니다.

조건 그룹(위 예) IsClosed = 0 AND 취소됨 = 0 AND 활성화됨 = 0 (또는 기타 유사한 조건 집합)은 인덱싱된 뷰에서 사용하여 큰 집합에서 작은 데이터 조각을 캐싱하는 좋은 옵션이 됩니다.

그러나 뷰를 구체화할 때 다음과 같은 여러 가지 제한 사항이 있습니다.

1. 하위 쿼리, 절 사용 존재 를 사용하여 교체해야 합니다. JOIN
2. 너는 문장을 사용할 수 없다 UNION, UNION ALL, 예외, 교차
3. 테이블 힌트와 절은 사용할 수 없습니다. OPTION
4. 사이클 작업 가능성 없음
5. 다른 테이블의 하나의 뷰에 데이터를 표시하는 것은 불가능합니다.

인덱싱된 뷰 사용의 실제 이점은 실제로 인덱싱해야만 얻을 수 있다는 점을 기억하는 것이 중요합니다.

그러나 뷰를 호출할 때 이러한 인덱스는 사용할 수 없으며 명시적으로 사용하려면 다음을 지정해야 합니다. 포함(확장 없음).

부터 LINQ 쿼리 테이블 힌트를 정의하는 것은 불가능하므로 다음 형식의 "래퍼"라는 또 다른 표현을 만들어야 합니다.

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

4) 테이블 함수 사용하기

종종 LINQ 쿼리 복잡한 구조의 뷰를 사용하는 큰 하위 쿼리 블록 또는 블록은 매우 복잡하고 최적이 아닌 실행 구조를 가진 최종 쿼리를 형성합니다.

테이블 함수 사용의 주요 이점 LINQ 쿼리:

1. 뷰의 경우와 같이 객체로 사용 및 지정되는 기능이 있지만 입력 매개변수 세트를 전달할 수 있습니다.
   함수에서(@param1, @param2 ...)
   결과적으로 유연한 데이터 샘플링이 가능합니다.
2. 테이블 함수를 사용하는 경우 위에서 설명한 인덱싱된 뷰의 경우처럼 강력한 제한이 없습니다.
   1. 테이블 힌트:
      를 통해 링크 쿼리할 때 사용할 인덱스를 지정하고 데이터 격리 수준을 결정할 수 없습니다.
      하지만 함수에는 이러한 기능이 있습니다.
      이 기능을 사용하면 인덱스 및 데이터 격리 수준 작업에 대한 규칙이 정의되는 상당히 일정한 실행 쿼리 계획을 달성할 수 있습니다.
   2. 이 기능을 사용하면 인덱싱된 뷰와 비교하여 다음을 얻을 수 있습니다.
      • 복잡한 데이터 샘플링 논리(루프 사용 시에도)
      • 다양한 테이블에서 데이터 가져오기
      • 의 사용 UNION и 존재

3. 제안 OPTION 동시성 제어를 제공해야 할 때 매우 유용합니다. 옵션(MAXDOP N), 쿼리 실행 계획의 순서입니다. 예를 들어:
   • 쿼리 계획을 강제로 다시 생성하도록 지정할 수 있습니다. 옵션(재컴파일)
   • 쿼리 계획이 쿼리에 지정된 조인 순서를 사용하도록 강제할지 여부를 지정할 수 있습니다. 옵션(강제 명령)

   에 대한 자세한 내용 OPTION 설명 여기에.

4. 가장 좁고 가장 필요한 데이터 조각 사용:
   인덱싱된 뷰의 경우처럼 캐시에 대규모 데이터 세트를 저장할 필요가 없으며 캐시에서 여전히 매개 변수별로 데이터를 필터링해야 합니다.
   예를 들어 필터가 있는 테이블이 있습니다. WHERE 세 개의 필드가 사용됩니다 (a, b, c).

   일반적으로 모든 요청에는 일정한 조건이 있습니다. a = 0 및 b = 0.

   그러나 해당 분야에 대한 요청은 c 더 변수.

   조건을 놔두세요 a = 0 및 b = 0 필요한 결과 집합을 수천 개의 레코드로 제한하는 것은 정말 도움이 되지만 с 선택 범위를 100개의 레코드로 좁힙니다.

   여기서는 테이블 함수가 더 나은 옵션일 수 있습니다.

   또한 테이블 함수는 실행 시간에 있어 더 예측 가능하고 일관성이 있습니다.

예

질문 데이터베이스를 예로 들어 구현 예를 살펴보겠습니다.

요청이 있습니다 SELECT, 여러 테이블을 결합하고 하나의 뷰(OperativeQuestions)를 사용하며, 이메일을 통해 소속을 확인합니다. 존재) "운영 질문":

요청 번호 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의 샘플은 약 10,000개의 레코드입니다.

이 쿼리의 주요 문제점은 외부 쿼리의 레코드에 대해 내부 하위 쿼리가 [Email] = @p__linq__0에 대해 [OperativeQuestions] 보기에서 실행된다는 것입니다. 이를 통해 출력 선택을 제한할 수 있습니다. 존재) 최대 수백 개의 레코드.

그리고 서브쿼리는 [Email] = @p__linq__0으로 한번 레코드를 계산하고, 이 몇백개의 레코드를 질문으로 ID로 연결해야 쿼리가 빨라지는 것처럼 보일 수도 있습니다.

실제로 모든 테이블에는 순차적인 연결이 있습니다. 즉, OperativeQuestions의 Id와 Id 질문의 일치 여부를 확인하고 이메일로 필터링합니다.

실제로 요청은 수만 개의 OperativeQuestions 레코드와 모두 작동하지만 이메일을 통해 관심 있는 데이터만 필요합니다.

OperativeQuestes 보기 텍스트:

요청 번호 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]를 데이터베이스에 도입합니다.

이메일을 입력 매개변수로 보내면 값 테이블이 반환됩니다.

요청 번호 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로 변환됩니다.

요청 번호 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-800ms에서 2-20ms 등으로 감소했습니다. 즉, 수십 배 더 빨라졌습니다.

더 평균적으로 취하면 350ms 대신 8ms가 됩니다.

명백한 이점으로부터 우리는 또한 다음을 얻습니다:

1. 독서 부하의 전반적인 감소,
2. 차단 가능성이 크게 감소합니다.
3. 평균 차단 시간을 허용 가능한 값으로 줄입니다.

출력

데이터베이스 호출의 최적화 및 미세 조정 MS의 SQL 를 통해 링크 해결될 수 있는 문제다.

이 작업에서는 세심함과 일관성이 매우 중요합니다.

프로세스 시작 시:

1. 요청이 작동하는 데이터(값, 선택한 데이터 유형)를 확인해야 합니다.
2. 이 데이터의 적절한 인덱싱을 수행하십시오.
3. 테이블 간 조인 조건이 올바른지 확인

다음 최적화 반복에서는 다음이 드러납니다.

1. 요청을 기반으로 하고 기본 요청 필터를 정의합니다.
2. 유사한 쿼리 블록을 반복하고 조건의 교차점을 분석합니다.
3. SSMS 또는 기타 GUI에서 SQL 서버 자체적으로 최적화 SQL 쿼리 (중간 데이터 저장소 할당, 이 저장소를 사용하여 결과 쿼리 작성(여러 개가 있을 수 있음))
4. 마지막 단계에서 결과를 기초로 삼아 SQL 쿼리, 구조가 재건되고 있습니다 LINQ 쿼리

결과 LINQ 쿼리 확인된 최적의 구조와 구조가 동일해야 합니다. SQL 쿼리 포인트 3부터.

감사의 말

동료들에게 깊은 감사를 드립니다 직업 보석 и alex_ozr 회사에서 포르 티스 이 자료를 준비하는 데 도움을 요청했습니다.

출처 : habr.com