[SQL] 왜 UDF 너무 느린 서브 쿼리보다?

왜 UDF 너무 느린 서브 쿼리보다?

내가 같은 테이블에서 여러 값을 (조회)를 번역해야 경우가 있습니다. 첫 번째 방법은 내가 서브 쿼리를 사용했는데, 쓴 :

SELECT
    (SELECT id FROM user WHERE user_pk = created_by) AS creator,
    (SELECT id FROM user WHERE user_pk = updated_by) AS updater,
    (SELECT id FROM user WHERE user_pk = owned_by) AS owner,
    [name]
FROM asset

내가 (즉, 나는이 분야에 약 50 테이블이) 많은이 하위 쿼리를 사용하고, 나는 서브 쿼리에 좀 더 코드를 추가해야 할 수도 있습니다으로 (예를 들어, "AND 활성은 = 1") 나는 '나에게 생각 사용자 정의 기능이 UDF에 넣고 그 사용 거라고. 그러나 사용하여 성능이 UDF는 한심한했다.

CREATE FUNCTION dbo.get_user ( @user_pk INT )
RETURNS INT
AS BEGIN 
    RETURN ( SELECT id
             FROM   ice.dbo.[user]
             WHERE  user_pk = @user_pk )
END

SELECT dbo.get_user(created_by) as creator, [name]
FROM asset

# 1의 성능은 1 초 미만이다. # 2의 성능은 30 초에 관하여이다 ...

왜, 또는 더 중요한 것은, 내가 SQL Server 2008의 코딩 할 수있는 방법이 내가 너무 많은 하위 쿼리를 사용할 필요가 없습니다 그래서, 거기?

그냥 litte이 유용 할 때 더 설명. 이 간단한 쿼리 (즉, GET 사용자 ID) 나 사용자에 대한 텍스트를 할 때 언어가 '페치해야하는지 여부를 확인하기 위해 회사와 언어를 얻기 위해 프로필을 사용하여 가입 할 필요가 있기 때문에, 훨씬 더 복잡해 대신에 거기에서 에드 및 ​​변환 표에 번역 된 텍스트를 얻을 수 있습니다. 그리고 이러한 쿼리의 대부분 성능이 가독성과 유지 보수에 대한 보조 문제입니다.

해결법

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

   1.이 모든 행에 대해 실행 있어요 있도록 UDF는 쿼리 최적화에 블랙 박스입니다. 당신은 행 단위 커서를하고 있습니다. 자산의 각 행에 대해, 다른 테이블에 ID를 세 번을 찾아보십시오. 당신이 (인라인 UDF를가 외부 쿼리로 확장 매크로 단순히) 스칼라 또는 다중 문 UDF를 사용할 때 발생

   이 모든 행에 대해 실행 있어요 있도록 UDF는 쿼리 최적화에 블랙 박스입니다. 당신은 행 단위 커서를하고 있습니다. 자산의 각 행에 대해, 다른 테이블에 ID를 세 번을 찾아보십시오. 당신이 (인라인 UDF를가 외부 쿼리로 확장 매크로 단순히) 스칼라 또는 다중 문 UDF를 사용할 때 발생

   이 문제에 대한 많은 기사 중 하나는 "스칼라 함수, 인라인 및 성능 :는 지루한의 게시물에 대한 제목을 접대"입니다.

   서브 쿼리가 연관되고 행 단위 조작을 방지하기 위해 최적화 될 수있다.

   당신이 진정으로 원하는 것은 이것이다 :

   SELECT
      uc.id AS creator,
      uu.id AS updater,
      uo.id AS owner,
      a.[name]
   FROM
       asset a
       JOIN
       user uc ON uc.user_pk = a.created_by
       JOIN
       user uu ON uu.user_pk = a.updated_by
       JOIN
       user uo ON uo.user_pk = a.owned_by
   

   업데이트 2019년 2월

   SQL 서버 2019이 문제를 해결하기 시작합니다.

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

   2.다른 포스터 제안으로 사용하는 것은 확실히 당신에게 최고의 전반적인 성능을 줄 것이다 결합한다.

   다른 포스터 제안으로 사용하는 것은 확실히 당신에게 최고의 전반적인 성능을 줄 것이다 결합한다.

   비슷한 당신이 유지하는 두통 싶지 않아 50 틱 조인 또는 하위 쿼리 것을 주장했습니다 때문에, 다음과 같이 인라인 테이블 반환 함수를 사용해보십시오 :

   CREATE FUNCTION dbo.get_user_inline (@user_pk INT)
   RETURNS TABLE AS
   RETURN
   (
       SELECT TOP 1 id
       FROM ice.dbo.[user]
       WHERE user_pk = @user_pk
           -- AND active = 1
   )
   

   원래 쿼리는 다음과 같이 될 것입니다 :

   SELECT
       (SELECT TOP 1 id FROM dbo.get_user_inline(created_by)) AS creator,
       (SELECT TOP 1 id FROM dbo.get_user_inline(updated_by)) AS updater,
       (SELECT TOP 1 id FROM dbo.get_user_inline(owned_by)) AS owner,
       [name]
   FROM asset
   

   인라인 테이블 반환 함수는 스칼라 함수 또는 다중 문 테이블 반환 함수 중 하나보다 더 나은 성능을 가져야한다.

   성능은 원래 쿼리와 거의 비슷해야하지만, 미래의 변화를 만드는 UDF 훨씬 더 유지 보수 할 수있다.

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

   3.(사용자가 활성 삭제하거나되지 않은 경우 NULL을) 동일한 결과를 얻을 수 있습니다.

   (사용자가 활성 삭제하거나되지 않은 경우 NULL을) 동일한 결과를 얻을 수 있습니다.

    select 
       u1.id as creator,
       u2.id as updater,
       u3.id as owner,
       [a.name]
    FROM asset a
           LEFT JOIN user u1 ON (u1.user_pk = a.created_by AND u1.active=1) 
           LEFT JOIN user u2 ON (u2.user_pk = a.created_by AND u2.active=1) 
           LEFT JOIN user u3 ON (u3.user_pk = a.created_by AND u3.active=1) 
   

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

   4.나는 뭔가를 놓치고 있습니까? 수없는 이유는이 작품? 당신은 당신이 이미 테이블에있는 ID를 선택하는 :

   나는 뭔가를 놓치고 있습니까? 수없는 이유는이 작품? 당신은 당신이 이미 테이블에있는 ID를 선택하는 :

   select created_by as creator, updated_by as updater, 
   owned_by as owner, [name]
   from asset
   

   그런데, 설계, 당신은 정말 필드 이름으로, 이름처럼, 키워드 피해야한다.

from https://stackoverflow.com/questions/510743/why-is-a-udf-so-much-slower-than-a-subquery by cc-by-sa and MIT license