sql - 사용법 - union 과 union all 성능




테이블 함수의 조건부 UNION ALL (4)

OPTION (RECOMPILE)은 함수가 테이블에 사용될 때 여기서 도움이되지 않습니다. 이 쿼리는 예를 들어 두 테이블을 모두 검색합니다.

-- 3rd table to test against
create table dbo.TEST3 (id int primary key, test bit);
insert dbo.TEST3 values(1,1),(2,1),(3,0),(4,1);
GO

select TEST3.* 
from TEST3 
CROSS APPLY dbo.f_TEST(test3.test) 
OPTION (RECOMPILE);

그래도 괜찮아. 시간이 부족합니다 (그렇지 않은 경우 스크린 샷이 포함될 것입니다). 그러나 실제 실행 계획으로이 세 가지 쿼리를 실행하면 옵티마이 저가 동일한 비용을 발생시키는 것으로 나타납니다.

DECLARE @test int = 1

select * from dbo.f_TEST(1)
select * from dbo.f_TEST(@test)
select * from dbo.f_TEST(@test) OPTION (RECOMPILE)

두 번째 쿼리는 첫 번째 및 두 번째 쿼리보다 두 배 비싸지 만 SELECT 연산자 위로 마우스를 가져 가면 옵티마이 저가 1 대신 2 개의 행을 계산하기 때문에 결과가 표시됩니다 (다른 두 경우와 마찬가지로 ).

성능 테스트를 수행하면 최적화 프로그램이 올바르다는 것을 알 수 있습니다.

코드에서 더 큰 문제는 두 쿼리 모두에 필터가 없기 때문에 각 테이블에 대해 테이블 ​​검색이 보장된다는 것입니다. 가능한 경우 필터를 추가하면 스캔 대신 검색이 발생하는 방식으로 두 테이블을 모두 인덱싱 할 수 있습니다.

따라서 유스 케이스는 다음과 같습니다. 하나의 테이블이나 다른 테이블에서 데이터를 선택하기 위해 어떤 매개 변수가 있습니다.

create table dbo.TEST1 (id int primary key, name nvarchar(128))
create table dbo.TEST2 (id int primary key, name nvarchar(128))

그래서 저는 다음과 같은 함수를 만들었습니다 :

create function [dbo].[f_TEST]
(
    @test bit
)
returns table
as
return (
    select id, name from TEST1 where @test = 1

    union all

    select id, name from TEST2 where @test = 0
)

상수를 사용하여 실행하면 실행 계획이 훌륭합니다. 하나의 테이블 만 스캔됩니다.

select * from dbo.f_TEST(1)

그러나 변수를 사용할 때 계획은 그리 좋지 않습니다. 두 테이블 모두 스캔됩니다.

declare @test bit = 1

select * from dbo.f_TEST(@test)

그래서 어떤 힌트 (또는 트릭)를 SQL Server가 특정 쿼리에서 하나의 테이블 만 스캔해야한다는 것을 이해하도록 강제 할 수 있습니까?


그것은 그대로 잘 작동합니다. 매개 변수 값을 변경하면 관련 테이블의 "실행 횟수"를 살펴보십시오. 제외 될 테이블은 고려해야하기 때문에 계획에 나타나지만 스캔되지는 않습니다.

또한 필터의 Startup Expression을 살펴보십시오.


테이블 함수 대신 저장 프로 시저를 사용하지만 매개 변수 스니핑에주의하십시오. 스토어드 프로 시저 내에서 대신 동적 SQL을 사용하여 테이블 함수를 사용하여 찾는 동일한 결과를 생성 할 수 있습니다.

이 기사는 당신이하는 일이 왜 그런 식으로 작동하는지 설명 할 것입니다. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/user-defined-functions

함수로 더 많은 것을하고 싶다면 저장 프로 시저를 대신 생성하고 싶지 않을 수도 있습니다. 쿼리에서 sporc 실행 결과를 사용하는 방법이 있습니다. 하지만, 여기에 기록한 것과는 다른 질문이 될 것입니다.


함수가 인라인 TVP 인 경우 (예와 같이) 다음을 사용할 수 있습니다.

declare @test bit = 1
select * from dbo.f_TEST(@test) OPTION (RECOMPILE);

그런 다음 두 경우 모두 단일 클러스터 된 인덱스 스캔을 얻게됩니다.

DBFiddle 데모

옵션에서 RECOMPILE :

쿼리 계획을 컴파일 할 때 RECOMPILE 쿼리 힌트는 쿼리의 모든 로컬 변수 값을 사용 하고 쿼리가 저장 프로 시저 내부에 있으면 현재 값을 모든 매개 변수에 전달합니다.





sql-execution-plan