[SQL] 인라인 테이블 값 함수 대 다중 문 테이블 반환 함수

인라인 테이블 값 함수 대 다중 문 테이블 반환 함수

몇 가지 예는 넣다, 표시합니다 :

인라인 표는 소중한

CREATE FUNCTION MyNS.GetUnshippedOrders()
RETURNS TABLE
AS 
RETURN SELECT a.SaleId, a.CustomerID, b.Qty
    FROM Sales.Sales a INNER JOIN Sales.SaleDetail b
        ON a.SaleId = b.SaleId
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.ShipDate IS NULL
GO

다중 문 테이블은 소중한

CREATE FUNCTION MyNS.GetLastShipped(@CustomerID INT)
RETURNS @CustomerOrder TABLE
(SaleOrderID    INT         NOT NULL,
CustomerID      INT         NOT NULL,
OrderDate       DATETIME    NOT NULL,
OrderQty        INT         NOT NULL)
AS
BEGIN
    DECLARE @MaxDate DATETIME

    SELECT @MaxDate = MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = @CustomerID

    INSERT @CustomerOrder
    SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
    FROM Sales.SalesOrderHeader a INNER JOIN Sales.SalesOrderHeader b
        ON a.SalesOrderID = b.SalesOrderID
        INNER JOIN Production.Product c ON b.ProductID = c.ProductID
    WHERE a.OrderDate = @MaxDate
        AND a.CustomerID = @CustomerID
    RETURN
END
GO

다른 이상 한 가지 유형 (인라인 또는 다중 문)를 사용하는 이점이 있습니까? 하나가 다른 것보다 낫다거나 차이가 순수하게 구문 때이 특정 시나리오 있습니까? 나는 두 개의 예제 쿼리는 다른 일을하고 있지만 그 방법을 쓸 것 이유가 실현?

그들과 장점에 대해 읽기 / 차이는 정말 설명되지 않았다.

해결법

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

   1.매트의 의견을 연구, 나는 내 원래의 문을 수정했다. 둘 다 단순히 SELECT 문을 실행할 경우에도 그는이 올바른지, 인라인 테이블 값 함수 (ITVF)와 다중 문 테이블 값 함수 (MSTVF) 사이의 성능 차이가있을 것입니다. SQL 서버는 어느 정도는 문제의 테이블에 대한 최신 통계를 사용하여 실행 계획을 계산하는 것을에서 VIEW 같은 ITVF 처리합니다. MSTVF는에 가입 한 후 테이블 변수로 SELECT 문의 전체 내용을 채우고 동일합니다. 따라서, 컴파일러는 MSTVF의 테이블에 모든 테이블 통계를 사용할 수 없습니다. 그래서, 모든 것을는 ITVF는 MSTVF보다 더 잘 수행한다 (그들은 거의 없다 함), 동일하고. 내 테스트에서 완료 시간의 성능 차이는 두드러졌다, 통계의 관점에서 그러나 무시할 수 있었다.

   매트의 의견을 연구, 나는 내 원래의 문을 수정했다. 둘 다 단순히 SELECT 문을 실행할 경우에도 그는이 올바른지, 인라인 테이블 값 함수 (ITVF)와 다중 문 테이블 값 함수 (MSTVF) 사이의 성능 차이가있을 것입니다. SQL 서버는 어느 정도는 문제의 테이블에 대한 최신 통계를 사용하여 실행 계획을 계산하는 것을에서 VIEW 같은 ITVF 처리합니다. MSTVF는에 가입 한 후 테이블 변수로 SELECT 문의 전체 내용을 채우고 동일합니다. 따라서, 컴파일러는 MSTVF의 테이블에 모든 테이블 통계를 사용할 수 없습니다. 그래서, 모든 것을는 ITVF는 MSTVF보다 더 잘 수행한다 (그들은 거의 없다 함), 동일하고. 내 테스트에서 완료 시간의 성능 차이는 두드러졌다, 통계의 관점에서 그러나 무시할 수 있었다.

   귀하의 경우, 두 기능은 기능적으로 동일하지 않습니다. MSTV 기능은 고객 ID에 가장 중요한 필터를 추가 쿼리를가 호출 될 때마다 수행합니다. 그것은 각각의 고객 ID를 통과하기위한 함수를 호출해야합니다 같은 큰 쿼리에서 옵티마이 저는 조인의 다른 유형을 활용 할 수 없습니다. 당신이 경우, 다음처럼 MSTV 기능을 다시 썼다 :

   CREATE FUNCTION MyNS.GetLastShipped()
   RETURNS @CustomerOrder TABLE
       (
       SaleOrderID    INT         NOT NULL,
       CustomerID      INT         NOT NULL,
       OrderDate       DATETIME    NOT NULL,
       OrderQty        INT         NOT NULL
       )
   AS
   BEGIN
       INSERT @CustomerOrder
       SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
       FROM Sales.SalesOrderHeader a 
           INNER JOIN Sales.SalesOrderHeader b
               ON a.SalesOrderID = b.SalesOrderID
           INNER JOIN Production.Product c 
               ON b.ProductID = c.ProductID
       WHERE a.OrderDate = (
                           Select Max(SH1.OrderDate)
                           FROM Sales.SalesOrderHeader As SH1
                           WHERE SH1.CustomerID = A.CustomerId
                           )
       RETURN
   END
   GO
   

   쿼리에 최적화 한 번 그 함수를 호출하고 더 나은 실행 계획을 구축 할 수있을 것입니다하지만 여전히 동등한, 비 파라미터 ITVS 또는 VIEW보다 더하지 않을 것입니다.

   테이블의 컬럼에서 데이터 유형, Null 허용 여부 및 데이터 정렬이 중요한 것은, 당신은 ITVF에서 더 나은 실행 계획을 얻을 것이다, 다중 문 테이블 값 함수에서 이러한 속성을 선언하고 반면 때문에 가능하면 ITVFs는 MSTVFs를보다 선호한다. 내 경험에 의하면, 나는 ITVF가보기보다 더 나은 옵션했지만 마일리지는 다를 수 있습니다 많은 상황을 발견하지 않았습니다.

   매트에 감사합니다.

   부가

   나는이 최근에 와서보고 있기 때문에, 여기에 인라인 테이블 반환 함수와 다중 문 기능 사이의 성능 차이를 비교 웨인 셰필드에 의해 수행 탁월한 분석이다.

   그의 원래 블로그 게시물.

   SQL Server 중앙에 복사

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

   2.내부적으로, SQL Server가 많이처럼 인라인 테이블 값 함수를 취급 것이라고 전망 및 취급 방법은 것 저장 프로 시저와 유사한 다중 문 테이블 반환 함수.

   내부적으로, SQL Server가 많이처럼 인라인 테이블 값 함수를 취급 것이라고 전망 및 취급 방법은 것 저장 프로 시저와 유사한 다중 문 테이블 반환 함수.

   인라인 테이블 값 함수가 외부 쿼리의 일부로서 사용되는 경우, 쿼리 프로세서는 UDF 정의를 확장하고 이러한 객체의 인덱스를 사용하여, 기본 오브젝트에 액세스하는 실행 계획을 생성한다.

   다중 문 테이블 값 함수를 들면, 실행 계획 함수 자체 생성되고 (기능이 처음으로 실행 된 후에) 실행 계획 캐시에 저장된다. 다중 문 테이블 반환 함수가 큰 쿼리의 일부로 사용하는 경우 옵티마이는 어떤 함수가 반환을 모르는, 그래서 어떤 표준 가정한다 -이 기능은 하나의 행을 리턴한다고 가정 효과를, 그리고 그 수익 함수가 일렬로 테이블에 대해 테이블 ​​스캔을 사용하여 액세스된다.

   그들은 많은 수의 행을 반환하고 외부 쿼리에 가입 할 때 어디에서 다중 문 테이블 반환 함수가 제대로 수행 할 수있는 것은. 성능 문제는 주로 아래 옵티마이 반드시 가장 적절한 계획되지 않습니다 하나의 행이 반환되는 것을 가정 계획을 생성 할 것이라는 사실이다.

   엄지 손가락의 일반적으로 우리가 발견 한 그합니다 (UDF가 외부 쿼리의 일부로서 사용되는 경우) 가능한 인라인 테이블 반환 함수 인해 이러한 잠재적 성능 문제에 다중 문 것들에 우선하여 사용되어야한다.

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

   3.또 다른 차이가 있습니다. 인라인 테이블 반환 함수는, 삽입, 업데이트 및 삭제할 수 있습니다 - 다만보기있다. 비슷한 제한 사항이 적용됩니다 - 골재를 사용하여 기능을 업데이트 할 수 없습니다 계산 된 열을 업데이트 할 수 없습니다, 등등.

   또 다른 차이가 있습니다. 인라인 테이블 반환 함수는, 삽입, 업데이트 및 삭제할 수 있습니다 - 다만보기있다. 비슷한 제한 사항이 적용됩니다 - 골재를 사용하여 기능을 업데이트 할 수 없습니다 계산 된 열을 업데이트 할 수 없습니다, 등등.

4. ==============================

   4.귀하의 예를 들면, 나는 생각 아주 잘 질문에 대답. 첫 번째 함수는 하나의 선택으로 수행하고, 인라인 스타일을 사용하는 것이 이유가 될 수있다. 두 번째는 아마도 (최대 날짜를 얻을 수있는 하위 쿼리를 사용하여) 하나의 문으로 수행 될 수 있지만, 일부 코더는 읽기 쉽게 찾거나 할 수 있습니다 당신이했던 것처럼 여러 문에서 그것을 할 자연. 일부 기능은 보통 하나 개의 문장에 끝낼 수 없다, 그래서 다중 문 버전이 필요합니다.

   귀하의 예를 들면, 나는 생각 아주 잘 질문에 대답. 첫 번째 함수는 하나의 선택으로 수행하고, 인라인 스타일을 사용하는 것이 이유가 될 수있다. 두 번째는 아마도 (최대 날짜를 얻을 수있는 하위 쿼리를 사용하여) 하나의 문으로 수행 될 수 있지만, 일부 코더는 읽기 쉽게 찾거나 할 수 있습니다 당신이했던 것처럼 여러 문에서 그것을 할 자연. 일부 기능은 보통 하나 개의 문장에 끝낼 수 없다, 그래서 다중 문 버전이 필요합니다.

   나는 간단한 (인라인)을 가능한 사용하고, 필요 (명백하게) 나 개인 취향에 / 가독성이 추가 입력을 워스 보낼 때 멀티 문을 사용하는 것이 좋습니다.

5. ==============================

   5.당신이 좋은 설명 및 성능 벤치 마크를 찾을 수 비교 인라인 및 다중 문 테이블 반환 함수를 보면

   당신이 좋은 설명 및 성능 벤치 마크를 찾을 수 비교 인라인 및 다중 문 테이블 반환 함수를 보면

6. ==============================

   6.나는이 테스트를하지 않은,하지만 다중 문 함수는 결과 집합을 캐시합니다. 너무 많이가는 최적화 함수를 인라인하기에이 경우가있을 수 있습니다. 예를 들어 당신이 "회사 번호"로 전달할 내용에 따라 서로 다른 데이터베이스에서 결과를 반환하는 함수가 있다고 가정합니다. 일반적으로, 당신은 회사 번호로 모든 후 조합과 뷰 필터를 만들 수 있지만 때로는 SQL 서버 당긴 전체 조합을 백업하고 하나가 선택 호출하는 스마트 충분하지 않습니다 발견했다. 테이블 함수는 소스를 선택하는 로직을 가질 수 있습니다.

   나는이 테스트를하지 않은,하지만 다중 문 함수는 결과 집합을 캐시합니다. 너무 많이가는 최적화 함수를 인라인하기에이 경우가있을 수 있습니다. 예를 들어 당신이 "회사 번호"로 전달할 내용에 따라 서로 다른 데이터베이스에서 결과를 반환하는 함수가 있다고 가정합니다. 일반적으로, 당신은 회사 번호로 모든 후 조합과 뷰 필터를 만들 수 있지만 때로는 SQL 서버 당긴 전체 조합을 백업하고 하나가 선택 호출하는 스마트 충분하지 않습니다 발견했다. 테이블 함수는 소스를 선택하는 로직을 가질 수 있습니다.

7. ==============================

   7.멀티 라인 기능을 사용하는 또 다른 경우는 where 절을 아래로 밀어에서 우회하기 SQL Server에 대한 것입니다.

   멀티 라인 기능을 사용하는 또 다른 경우는 where 절을 아래로 밀어에서 우회하기 SQL Server에 대한 것입니다.

   예를 들어, 나는 테이블 이름과 어떤 테이블 이름과 테이블이 C05_2019 및 C12_2018와 같은 형식되고 모든 테이블이 방법은 동일한 스키마를 가지고 형식이있다. 나는 모든 병합 싶었던 년 컬럼에 05 및 12 CompNo 열 및 2018,2019 중 1 개 테이블과 구문 분석에 데이터. 그러나, 나는 CompNo 및 CompYr을 추출 할 수 없습니다 내가 노력하면 변환 오류를 얻을 것 ACA_StupidTable 같은 다른 테이블이 있습니다. 그래서, 내 쿼리는 두 부분, 외부 쿼리는 하위 문자열 INT 변환을 한 후 'C_______'과 같은 형식의 테이블 만 반환 내부 쿼리에 있었다. CompNo로 (int로서 하위 문자열 (2, 2)) 즉, 캐스트. 그 SQL 서버를 제외한 모든 외모 좋은 내가 변환 오류 스크램블링 마음을 얻을 수 있도록 결과를 필터링되기 전에 내 캐스트 기능을 넣어하기로 결정했다. 그것은 기본적으로 "새로운"테이블이기 때문에 다중 문 테이블 함수가 일어나는 것을 방지 할 수있다.

8. ==============================

   8.어쩌면 매우 응축 방법이다. ITVF (인라인 TVF) : u는 DB 사람이 있다면 더 걸릴 종류의 매개 변수보기의 단일 SELECT 일

   어쩌면 매우 응축 방법이다. ITVF (인라인 TVF) : u는 DB 사람이 있다면 더 걸릴 종류의 매개 변수보기의 단일 SELECT 일

   MTVF (다중 문 TVF) : 개발자가 만들고 테이블 변수를로드합니다.

9. ==============================

   9.당신은 당신처럼 인라인 테이블 반환 함수에 참여 할 수있는 쿼리를 수행하려는 경우 :

   당신은 당신처럼 인라인 테이블 반환 함수에 참여 할 수있는 쿼리를 수행하려는 경우 :

   SELECT
       a.*,b.*
       FROM AAAA a
           INNER JOIN MyNS.GetUnshippedOrders() b ON a.z=b.z
   

   그것은 약간의 오버 헤드 및 실행 벌금을 부과됩니다.

   당신이 유사한 쿼리에서 소중한 당신의 다중 문 테이블을 사용하려고하면 성능 문제가있을 것이다 :

   SELECT
       x.a,x.b,x.c,(SELECT OrderQty FROM MyNS.GetLastShipped(x.CustomerID)) AS Qty
       FROM xxxx   x
   

   당신이 반환 된 각 행에 대해 기능 1 시간을 실행하기 때문에 결과 집합이 큰수록, 그것은 느린 속도가 느린 실행됩니다.

from https://stackoverflow.com/questions/2554333/multi-statement-table-valued-function-vs-inline-table-valued-function by cc-by-sa and MIT license