[SQL] 쿼리 로컬 변수 도움말 최적화로 저장 프로 시저의 입력 매개 변수를 지정합니까?

쿼리 로컬 변수 도움말 최적화로 저장 프로 시저의 입력 매개 변수를 지정합니까?

나는 5 개 입력 매개 변수를 저장 프로 시저가 있습니다. 이 절차는 다소 복잡하고 실행하기 위해 약 2 분 걸린다. 나는 쿼리를 최적화하는 과정입니다.

그래서, 내 질문은, 항상 지역 변수에 할당 입력 매개 변수에 도움이 다음 절차에서 로컬 변수를 사용합니까?

그렇다면, 어떻게 도움이 되나요?

해결법

1. ==============================

   1.나는 시도하고 스니핑 매개 변수의 자세한 내용을 설명하지만, 짧은에, 아니 항상 도움이되지 않습니다 (그것은 방해 할 수)하지 않습니다.

   나는 시도하고 스니핑 매개 변수의 자세한 내용을 설명하지만, 짧은에, 아니 항상 도움이되지 않습니다 (그것은 방해 할 수)하지 않습니다.

   표에이 1000 개 행이있다, (400)은 동일한 가치를 가지고, 기본 키 및 인덱스 날짜 열 (A)와 테이블 (T)을 상상해, 나머지 600 행은 다음 600 일입니다 (현재 20,130,122 말을 할 수 있습니다) 날짜 별, 그래서 단 1 개 기록.

   이 쿼리 :

   SELECT *
   FROM T
   WHERE A = '20130122';
   

   다른 실행 계획을 얻을 것입니다 :

   SELECT *
   FROM T
   WHERE A = '20130123';
   

   북마크 룩업은 많은 수 있도록 통계는 제 1에만 열을 수득하는 반면 테이블 스캔 북마크 룩업보다 더 효율적일 것으로 인식해야 최적화를 반환한다 1000 행의 제 400 출력에 대한 것을 나타낼 것이므로 더 효율적입니다.

   지금, 당신의 질문에 다시는, 경우 우리는이 절차했다 :

   CREATE PROCEDURE dbo.GetFromT @Param DATE
   AS
       SELECT *
       FROM T
       WHERE A = @Param
   

   그런 다음 실행

   EXECUTE dbo.GetFromT '20130122'; --400 rows
   

   당신이 그것을 처음 실행할 때 당신이 그것을 북마크 조회 계획을 저장하는 매개 변수로 '20130123'을 사용하는 경우 테이블 스캔을 가진 쿼리 계획이 사용됩니다. 절차와 같은 시간이 다시 컴파일 될 때까지 계획은 동일하게 유지됩니다. 이 같은 일을 :

   CREATE PROCEDURE dbo.GetFromT @Param VARCHAR(5)
   AS
       DECLARE @Param2 VARCHAR(5) = @Param;
       SELECT *
       FROM T
       WHERE A = @Param2
   

   그런 다음이 실행됩니다 :

   EXECUTE dbo.GetFromT '20130122';
   

   프로 시저가 한 번에 컴파일하는 동안 처음 컴파일에서 생성 된 쿼리 계획은 아무 생각이 없다, 그래서 그것은과 Param2 @ 행 수에 대한 지식에로 (옵티마이 때문에, @param 같은 될 것이라고, 제대로 흐르지 않는다 ) 테이블을 간주합니다 같은보다 효율적으로 스캔 책갈피 조회와 300) 30 % (반환됩니다 가정합니다 기대합니다. 매개 변수로 '20130123'과 같은 절차를 실행 한 경우는 (관계없이 처음으로 호출 된 매개 변수 것의) 통계가 unkonwn 값을 사용할 수 없으므로 같은 계획을 얻을 것입니다. 그래서 더 효율적인 것 '20130122'에 대해이 절차를 실행하지만, 다른 모든 값 ( '20130122'첫 아무것도 호출 된 지역 매개 변수없이 절차를 가정하지만) 지역 매개 변수없이보다 효율적인 것

   당신은 자신에 대한 실행 계획을보실 수 있습니다 일부 쿼리는 설명합니다

   스키마 및 샘플 데이터를 생성

   CREATE TABLE T (ID INT IDENTITY(1, 1) PRIMARY KEY, A DATE NOT NULL, B INT,C INT, D INT, E INT);
   
   CREATE NONCLUSTERED INDEX IX_T ON T (A);
   
   INSERT T (A, B, C, D, E)
   SELECT  TOP 400 CAST('20130122' AS DATE), number, 2, 3, 4 
   FROM    Master..spt_values 
   WHERE   type = 'P'
   UNION ALL
   SELECT TOP 600 DATEADD(DAY, number, CAST('20130122' AS DATE)), number, 2, 3, 4 
   FROM    Master..spt_values 
   WHERE   Type = 'P';
   GO
   CREATE PROCEDURE dbo.GetFromT @Param DATE
   AS
       SELECT *
       FROM T
       WHERE A = @Param
   GO
   CREATE PROCEDURE dbo.GetFromT2 @Param DATE
   AS
       DECLARE @Param2 DATE = @Param;
       SELECT *
       FROM T
       WHERE A = @Param2
   GO
   

   실행 절차 (실제 실행 계획 표시) :

   EXECUTE GetFromT '20130122';
   EXECUTE GetFromT '20130123';
   EXECUTE GetFromT2 '20130122';
   EXECUTE GetFromT2 '20130123';
   GO
   EXECUTE SP_RECOMPILE GetFromT;
   EXECUTE SP_RECOMPILE GetFromT2;
   GO
   EXECUTE GetFromT '20130123';
   EXECUTE GetFromT '20130122';
   EXECUTE GetFromT2 '20130123';
   EXECUTE GetFromT2 '20130122';
   

   당신은 처음 GetFromT 그것은 테이블 스캔을 사용하여 컴파일 및 매개 변수 '20130122'이, GetFromT2는 테이블 스캔을 사용하고, '20130122'에 대한 계획을 유지하여 실행할 때이 유지되는 것을 볼 수 있습니다.

   절차는 재 컴파일 설정 및 (주 다른 순서로) 다시 실행 한 후, GetFromT는 북마크 loopup를 사용하고, 이전에 테이블 스캔이 더 approprate 계획이라고 간주에도 불구, '20130122'에 대한 계획을 유지합니다. GetFromT2는 순서에 의해 영향을받지이며, recompliateion 이전과 같은 계획을 가지고있다.

   그래서, 요약, 그것은 당신의 데이터와 인덱스, 재 컴파일하여 주파수 및 절차는 지역 변수를 사용하여 도움이됩니다 여부에 약간의 운의 분포에 따라 달라집니다. 그것은 확실히 항상 도움이되지 않습니다.

   다행스럽게도 필자는 지역 변수, 실행 계획 및 저장 프로 시저 complilation 사용의 효과에 도움이 되거있다. 나는 완전히 실패하거나 핵심을 놓친 경우 훨씬 더 깊이 설명에 여기에서 찾을 수 있습니다 :

   http://www.sommarskog.se/query-plan-mysteries.html

2. ==============================

   2.그것은 도움을 않습니다.

   그것은 도움을 않습니다.

   링크 파라미터 스니핑에 대한 자세한 내용을 포함 아래.

   http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx

   http://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options

   처음 매개 변수와 SP를 실행하면 쿼리 최적화 프로그램은 매개 변수의 값에 따라 쿼리 계획을 생성합니다. 쿼리 최적화 프로그램은 최적의 쿼리 계획을 결정하는 특정 값에 대한 통계 데이터를 사용합니다. 그러나 카디 문제는이 영향을 미칠 수 있습니다. 어떤 이전에 생성 된 쿼리 계획이 최적의 계획을하지 않을 수 서로 다른 매개 변수 값과 동일 SP를 실행하면 의미합니다.

   지역 변수에 매개 변수를 할당하여 우리는 쿼리 최적화 프로그램에서 매개 변수 값을 숨 깁니다. 그래서 일반적인 경우에 대한 쿼리 계획을 생성합니다.

   이것은 SP에 "UNKNOWN OPTIMIZE FOR"힌트를 사용하는 것과 동일하다.

3. ==============================

   3.난 그렇게 생각하지 않습니다. 현대 컴퓨터 아키텍처는 저장 프로 시저 값에 착용하는 프로세서 캐시 주변의 많음이있다. 기본적으로, 당신은 로컬 캐시 메모리에로드되는는 "스택"에있는 것으로이를 고려할 수 있습니다.

   난 그렇게 생각하지 않습니다. 현대 컴퓨터 아키텍처는 저장 프로 시저 값에 착용하는 프로세서 캐시 주변의 많음이있다. 기본적으로, 당신은 로컬 캐시 메모리에로드되는는 "스택"에있는 것으로이를 고려할 수 있습니다.

   당신이 출력 매개 변수를 가지고 있다면, 아마도 로컬 변수에 입력 값을 복사하는 간접적 인 단계를 제거하는 것입니다. 그러나 간접가 실행되는 것은 이번이 처음은, 대상 메모리는 로컬 캐시에 넣어되며, 아마이 유지됩니다.

   그래서, 아니, 나는 이것이 중요한 최적화 생각하지 않습니다.

   이 도움이 될 경우에, 당신은 항상 다른 저장 프로 시저 변종 시간을 볼 수 있습니다.

from https://stackoverflow.com/questions/14468603/does-assigning-stored-procedure-input-parameters-to-local-variables-help-optimiz by cc-by-sa and MIT license