6 파라미터 스니핑 IF

여기 넣어 너무 큰 동적 쿼리가 있습니다. 안전이 현재 형태의에서 검색 매개 변수의 수는 전달에 따라 조인 동적으로 빌드에 대한 CLR 절차를 이용하여 말을 그 결과를 받아 다시 가져 것은 최종 사용자에게 중요한 속성에 더 테이블을 설명하기 위해 섭니다. 나는 엔티티에 LINQ에 전체 쿼리를 변환 내가 발견 한 것은 생산하는 SQL이 일을 할 수있는 효율적인 충분한 그러나 EF 6, 쿼리 timesout를 통해 실행 있다는 것이다있다. SSMS에서 그것을 결과 SQL을 복용하고 실행하는 3 이하 초에서 실행됩니다. 난 단지 내 문제가 스니핑 매개 변수는 것을 상상할 수있다. 나는 데이터베이스의 모든 테이블에 업데이트 통계를 시도하고이 문제를 해결하지 않았습니다.

내 질문은 :

어떻게 든 EF를 통해 "옵션 RECOMPILE"와 같은 옵션을 포함 할 수 있습니까?

해결법

==============================

1.이 명령의 끝에 옵션 (재 컴파일)를 추가 예를 들어, DB에 그들을 실행하기 전에 내부 SQL 명령을 조작 할 수 EF6의 차단 기능을 사용하는 것이 가능하다 :

이 명령의 끝에 옵션 (재 컴파일)를 추가 예를 들어, DB에 그들을 실행하기 전에 내부 SQL 명령을 조작 할 수 EF6의 차단 기능을 사용하는 것이 가능하다 :

public class OptionRecompileHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        addQueryHint(command);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        addQueryHint(command);
    }

    private static void addQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

그것을 사용하려면 응용 프로그램의 시작 부분에 다음 줄을 추가합니다 :

DbInterception.Add(new OptionRecompileHintDbCommandInterceptor());

==============================

2.나는 VahidN의 솔루션처럼, 그에게 투표를 할 수 있지만, 나는 그것이 일어날 때 더 제어 할 수 있습니다. 그것은 DB 인터셉터는 매우 전역 밝혀, 나는 단지이 특정 시나리오에서 특정 컨텍스트에서 발생하고 싶었다.

나는 VahidN의 솔루션처럼, 그에게 투표를 할 수 있지만, 나는 그것이 일어날 때 더 제어 할 수 있습니다. 그것은 DB 인터셉터는 매우 전역 밝혀, 나는 단지이 특정 시나리오에서 특정 컨텍스트에서 발생하고 싶었다.

여기에서 우리는 또한에 원하는대로 해제 할 수있는 다른 쿼리 힌트를 추가 지원하기 위해 접지 작업을 설정하고 있습니다.

나는 종종 연결 문자열을 전달하는 방법을 노출하기 때문에, 나는 또한에 대한 지원이 포함되어 있습니다.

다음은 컨텍스트를 활성화 / 부분 클래스 EF 생성 확장하여, 프로그램 적 힌트를 해제하는 플래그를 줄 것이다. 우리는 또한 자신의 방법으로 인터셉터에서 재사용 코드의 작은 조각을 던졌다.

작은 인터페이스

public interface IQueryHintable
{
    bool HintWithRecompile { get; set; }
}

DB 명령 인터셉터

public class OptionHintDbCommandInterceptor : IDbCommandInterceptor
{
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<Int32> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
    }

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        AddHints(command, interceptionContext);
    }

    private static void AddHints<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext)
    {
        var context = interceptionContext.DbContexts.FirstOrDefault();
        if (context is IQueryHintable)
        {
            var hints = (IQueryHintable)context;

            if (hints.HintWithRecompile)
            {
                addRecompileQueryHint(command);
            }
        }
    }

    private static void addRecompileQueryHint(IDbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
            return;

        if (command.CommandText.StartsWith("select", StringComparison.OrdinalIgnoreCase) && !command.CommandText.Contains("option(recompile)"))
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }
    }
}

엔터티 컨텍스트를 확장하는 것은 IQueryHintable를 추가하는 방법

public partial class SomeEntities : DbContext, IQueryHintable
{
    public bool HintWithRecompile { get; set; }

    public SomeEntities (string connectionString, bool hintWithRecompile) : base(connectionString)
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (bool hintWithRecompile) : base()
    {
        HintWithRecompile = hintWithRecompile;
    }

    public SomeEntities (string connectionString) : base(connectionString)
    {
    }

}

등록 DB 명령 인터셉터 (Global.asax에)

    DbInterception.Add(new OptionHintDbCommandInterceptor());

다양한 컨텍스트를 사용

    using(var db = new SomeEntities(hintWithRecompile: true) )
    {
    }

켜기 또는 끄기

    db.HintWithRecompile = true;
    // Do Something
    db.HintWithRecompile = false;

당신은 또한 HintOptimizeForUnknown, 또는 다른 쿼리 힌트를 구현 할 수 있습니다 때문에,이 HintWithRecompile을했다.

==============================

3.내가 쿼리하기 위해 할 수있는 일시적인 추가 옵션 (재 컴파일)을 OptionRecompileScope 내에서 실행하는 것이이 작은 유틸리티 클래스를 썼다 그래서 나를 위해 동일은 @Greg에 관해서는, 폭이 시스템을 가능하게하는 것은 옵션이 아니었다.

내가 쿼리하기 위해 할 수있는 일시적인 추가 옵션 (재 컴파일)을 OptionRecompileScope 내에서 실행하는 것이이 작은 유틸리티 클래스를 썼다 그래서 나를 위해 동일은 @Greg에 관해서는, 폭이 시스템을 가능하게하는 것은 옵션이 아니었다.

사용 예제

using (new OptionRecompileScope(dbContext))
{
    return dbContext.YourEntities.Where(<YourExpression>).ToList();
}

이행

public class OptionRecompileScope : IDisposable
{
    private readonly OptionRecompileDbCommandInterceptor interceptor;

    public OptionRecompileScope(DbContext context)
    {
        interceptor = new OptionRecompileDbCommandInterceptor(context);
        DbInterception.Add(interceptor);
    }

    public void Dispose()
    {
        DbInterception.Remove(interceptor);
    }

    private class OptionRecompileDbCommandInterceptor : IDbCommandInterceptor
    {
        private readonly DbContext dbContext;

        internal OptionRecompileDbCommandInterceptor(DbContext dbContext)
        {
            this.dbContext = dbContext;
        }

        public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
        }

        public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
            if (ShouldIntercept(command, interceptionContext))
            {
                AddOptionRecompile(command);
            }
        }

        public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {
        }

        public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            if (ShouldIntercept(command, interceptionContext))
            {
                AddOptionRecompile(command);
            }
        }

        public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
        }

        private static void AddOptionRecompile(IDbCommand command)
        {
            command.CommandText = command.CommandText + " option(recompile)";
        }

        private bool ShouldIntercept(IDbCommand command, DbCommandInterceptionContext interceptionContext)
        {
            return 
                command.CommandType == CommandType.Text &&
                command is SqlCommand &&
                interceptionContext.DbContexts.Any(interceptionDbContext => ReferenceEquals(interceptionDbContext, dbContext));
        }
    }
}

==============================

4.나는 비슷한 문제가 있었다. 결국,이 명령을 사용하여 캐시 된 쿼리 계획을 제거 :

나는 비슷한 문제가 있었다. 결국,이 명령을 사용하여 캐시 된 쿼리 계획을 제거 :

dbcc freeproccache([your plan handle here])

계획 핸들을 얻기 위해서는 다음과 같은 쿼리를 사용할 수 있습니다 :

SELECT qs.plan_handle, a.attrlist, est.dbid, text
FROM   sys.dm_exec_query_stats qs
CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '
      FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa
      WHERE  epa.is_cache_key = 1
      ORDER  BY epa.attribute
      FOR    XML PATH('')) AS a(attrlist)
 WHERE  est.text LIKE '%standardHourRate%' and est.text like '%q__7%'and est.text like '%Unit Overhead%'
 AND  est.text NOT LIKE '%sys.dm_exec_plan_attributes%'

쿼리의 적절한 조각 조항 '등'의 내용을 교체.

당신은 내 모든 문제에서 볼 수있다 :

엔티티 프레임 워크가 느리게 실행하여 SQL 쿼리, 나쁜 쿼리 계획을 사용

==============================
5.EF 코어 2에서 유사한 경우가 있었다, 그러나 그것은 단지 인터셉터 구현에 차이가있다. 이 스레드 나에게 가장 도움이 때문에, 나는 영업 이익은 EF 6 요청하는 경우에도, 당신과 함께 내 구현을 공유하고자합니다. 내가 @Oskar 베르그와 @Greg 솔루션 조금 개선 Furthemore, 재 컴파일 옵션을 확장해야하는 쿼리를 골라합니다.

EF 코어 2에서 유사한 경우가 있었다, 그러나 그것은 단지 인터셉터 구현에 차이가있다. 이 스레드 나에게 가장 도움이 때문에, 나는 영업 이익은 EF 6 요청하는 경우에도, 당신과 함께 내 구현을 공유하고자합니다. 내가 @Oskar 베르그와 @Greg 솔루션 조금 개선 Furthemore, 재 컴파일 옵션을 확장해야하는 쿼리를 골라합니다.

EF 코어 2에서 인터셉터가 좀 까다 롭고 조금 다르다.

그것은 패키지 Microsoft.Extensions.DiagnosticAdapter 다음과 같은 코드를 통해 구현 될 수
```
var contextDblistener = this.contextDb.GetService<DiagnosticSource>();
(contextDblistener as DiagnosticListener).SubscribeWithAdapter(new SqlCommandListener());
```
인터셉터 자체는 해당 DiagnosticName 주석 표시의 방법이 필요하다.

내가 인터셉터에 준 팅겨는 원하는 옵션을 확장해야하는 쿼리 밖으로 하나의 명령 안에 특정 태그 (SQL 주석)를 찾는 것을이었다.

재 컴파일 옵션을 사용하는 쿼리를 표시하려면, 당신은 단순히 false로 다시 true로 부울 설정으로 주위를 성가 시게하고없이 쿼리에 .TagWith (Constants.SQL_TAG_QUERYHINT_RECOMPILE)를 추가해야합니다.

이 방법 당신은 또한 병렬 쿼리가 차단되는 모든 때문에 하나의 부울 HintWithRecompile의 재 컴파일 옵션으로 확대되고 문제가 없습니다.

상수 태그 문자열은 그들이 단지 내부의 SQL 주석이 아닌 쿼리 자체의 일부가 될 수 있도록 설계되었습니다. 전체 SQL 명령을 분석하고 쿼리의 일부 텍스트 안에 당신의 깃발을 일치하기 때문에 재 컴파일을 추가하지 않도록 나는 단지 태그 부분을 (EF의 구현 세부)을 분석 할 수있는 해결책을 찾기 could't.

은 "최적화 알 수없는 내용은"일부는 명령 매개 변수 속성을 사용하여 더욱 향상시킬 수 있습니다,하지만 난 당신에게 그를 떠날거야.
```
public class SqlCommandListener
{
    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuting")]
    public void OnCommandExecuting(DbCommand command, DbCommandMethod executeMethod, Guid commandId, Guid connectionId, bool async, DateTimeOffset startTime)
    {
        AddQueryHintsBasedOnTags(command);
    }

    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandExecuted")]
    public void OnCommandExecuted(object result, bool async)
    {
    }

    [DiagnosticName("Microsoft.EntityFrameworkCore.Database.Command.CommandError")]
    public void OnCommandError(Exception exception, bool async)
    {
    }

    private static void AddQueryHintsBasedOnTags(DbCommand command)
    {
        if (command.CommandType != CommandType.Text || !(command is SqlCommand))
        {
            return;
        }

        if (command.CommandText.Contains(Constants.SQL_TAG_QUERYHINT_RECOMPILE) && !command.CommandText.Contains("OPTION (RECOMPILE)", StringComparison.InvariantCultureIgnoreCase))
        {
            command.CommandText = command.CommandText + "\nOPTION (RECOMPILE)";
        }
        else if (command.CommandText.Contains(Constants.SQL_TAG_QUERYHINT_OPTIMIZE_UNKNOWN_USER) && !command.CommandText.Contains("OPTION (OPTIMIZE FOR (@__SomeUserParam_0 UNKNOWN))", StringComparison.InvariantCultureIgnoreCase))
        {
            command.CommandText = command.CommandText + "\nOPTION (OPTIMIZE FOR (@__SomeUserParam_0 UNKNOWN))";
        }
    }
}
```
편집 :이 컨텍스트 객체에 가입하지 않기 때문에 당신의 DiagnosticSource에 가입 할 경우주의하십시오. DiagnosticSource 다른 수명을 가지고 (많은 상황에 대한 원인이 될 수 있습니다.) 당신이 작성하는 모든 범위의 문맥에 가입한다면, 당신은 결국 더 많은 구독을 만들 것입니다. 해결책은 단 하나의 구독을 만들 여기 여기 내 대답을 참조하십시오.

from https://stackoverflow.com/questions/25145667/ef-6-parameter-sniffing by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] PostgreSQL를위한 시간에 타임 스탬프 차이 (0)	2020.06.07
[SQL] 생성 SQL 코드 프로그램 (0)	2020.06.07
[SQL] 어떤 변화에 오라클 ROWID를 일으킬 수 있습니다? (0)	2020.06.07
[SQL] 안드로이드 이클립스 디버그 모드에서 SQL 데이터베이스를 보는 방법 (0)	2020.06.07
[SQL] 내가 키 생성 BIGINT를 배출하면 어떻게됩니까? 어떻게 그것을 처리하기 위해? (0)	2020.06.07

복붙노트

[SQL] 6 파라미터 스니핑 IF

6 파라미터 스니핑 IF

해결법

1.이 명령의 끝에 옵션 (재 컴파일)를 추가 예를 들어, DB에 그들을 실행하기 전에 내부 SQL 명령을 조작 할 수 EF6의 차단 기능을 사용하는 것이 가능하다 :

2.나는 VahidN의 솔루션처럼, 그에게 투표를 할 수 있지만, 나는 그것이 일어날 때 더 제어 할 수 있습니다. 그것은 DB 인터셉터는 매우 전역 밝혀, 나는 단지이 특정 시나리오에서 특정 컨텍스트에서 발생하고 싶었다.

4.나는 비슷한 문제가 있었다. 결국,이 명령을 사용하여 캐시 된 쿼리 계획을 제거 :

'SQL' 카테고리의 다른 글

티스토리툴바