일반적인 하위 식 제거 설명

APS CU7.3은 SQL 쿼리 최적화 프로그램에서 일반적인 하위 식 제거를 통해 쿼리 성능을 향상시킵니다. 향상된 기능은 두 가지 방법으로 쿼리를 향상시킵니다. 첫 번째 이점은 SQL 컴파일 시간을 줄이는 데 도움이 되는 이러한 식을 식별하고 제거하는 기능입니다. 두 번째이자 더 중요한 이점은 이러한 중복 하위 식에 대한 데이터 이동 작업이 제거되어 쿼리에 대한 실행 시간이 더 빨라진다는 것입니다.

select top 100 asceding.rnk, i1.i_product_name best_performing, i2.i_product_name worst_performing
  from(select *
       from (select item_sk,rank() over (order by rank_col asc) rnk
             from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col
                   from store_sales ss1
                   where ss_store_sk = 8
                   group by ss_item_sk
                   having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col
                                                    from store_sales
                                                    where ss_store_sk = 8
                                                      and ss_hdemo_sk is null
                                                    group by ss_store_sk))V1)V11
       where rnk  < 11) asceding,
      (select *
       from (select item_sk,rank() over (order by rank_col desc) rnk
             from (select ss_item_sk item_sk,avg(ss_net_profit) rank_col
                   from store_sales ss1
                   where ss_store_sk = 8
                   group by ss_item_sk
                   having avg(ss_net_profit) > 0.9*(select avg(ss_net_profit) rank_col
                                                    from store_sales
                                                    where ss_store_sk = 8
                                                      and ss_hdemo_sk is null
                                                    group by ss_store_sk))V2)V21
       where rnk  < 11) descending,
  item i1,
  item i2
  where asceding.rnk = descending.rnk
    and i1.i_item_sk=asceding.item_sk
    and i2.i_item_sk=descending.item_sk
  order by asceding.rnk
  ;

TPC-DS 벤치마크 도구에서 위의 쿼리를 고려합니다. 위의 쿼리에서 하위 쿼리는 동일하지만 함수에 대한 rank()가 있는 절의 순서는 두 가지 방법으로 정렬됩니다. CU7.3 이전의 이 하위 쿼리는 오름차순으로 한 번, 내림차순으로 한 번 평가되고 두 번 실행되어 두 개의 데이터 이동 작업이 발생합니다. APS CU7.3을 설치한 후 하위 쿼리 부분은 한 번 평가되므로 데이터 이동을 줄이고 쿼리를 더 빠르게 완료합니다.

APS CU7.3으로 업그레이드한 후에도 기능을 테스트할 수 있는 'OptimizeCommonSubExpressions'라는 기능 스위치 가 도입되었습니다. 이 기능은 기본적으로 켜져 있지만 해제할 수 있습니다.

참고 항목

기능 스위치 값을 변경하려면 서비스를 다시 시작해야 합니다.

테스트 환경에서 다음 테이블을 만들고 위에서 언급한 쿼리에 대한 설명 계획을 평가하여 샘플 쿼리를 시도할 수 있습니다.

CREATE TABLE [dbo].[store_sales] (
    [ss_sold_date_sk] int NULL, 
    [ss_sold_time_sk] int NULL, 
    [ss_item_sk] int NOT NULL, 
    [ss_customer_sk] int NULL, 
    [ss_cdemo_sk] int NULL, 
    [ss_hdemo_sk] int NULL, 
    [ss_addr_sk] int NULL, 
    [ss_store_sk] int NULL, 
    [ss_promo_sk] int NULL, 
    [ss_ticket_number] int NOT NULL, 
    [ss_quantity] int NULL, 
    [ss_wholesale_cost] decimal(7, 2) NULL, 
    [ss_list_price] decimal(7, 2) NULL, 
    [ss_sales_price] decimal(7, 2) NULL, 
    [ss_ext_discount_amt] decimal(7, 2) NULL, 
    [ss_ext_sales_price] decimal(7, 2) NULL, 
    [ss_ext_wholesale_cost] decimal(7, 2) NULL, 
    [ss_ext_list_price] decimal(7, 2) NULL, 
    [ss_ext_tax] decimal(7, 2) NULL, 
    [ss_coupon_amt] decimal(7, 2) NULL, 
    [ss_net_paid] decimal(7, 2) NULL, 
    [ss_net_paid_inc_tax] decimal(7, 2) NULL, 
    [ss_net_profit] decimal(7, 2) NULL
)
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([ss_item_sk]),  PARTITION ([ss_sold_date_sk] RANGE RIGHT FOR VALUES (2450815, 2451180, 2451545, 2451911, 2452276, 2452641, 2453006)));

CREATE TABLE [dbo].[item] (
    [i_item_sk] int NOT NULL, 
    [i_item_id] char(16) COLLATE Latin1_General_100_CI_AS_KS_WS NOT NULL, 
    [i_rec_start_date] date NULL, 
    [i_rec_end_date] date NULL, 
    [i_item_desc] varchar(200) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_current_price] decimal(7, 2) NULL, 
    [i_wholesale_cost] decimal(7, 2) NULL, 
    [i_brand_id] int NULL, 
    [i_brand] char(50) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_class_id] int NULL, 
    [i_class] char(50) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_category_id] int NULL, 
    [i_category] char(50) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_manufact_id] int NULL, 
    [i_manufact] char(50) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_size] char(20) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_formulation] char(20) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_color] char(20) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_units] char(10) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_container] char(10) COLLATE Latin1_General_100_CI_AS_KS_WS NULL, 
    [i_manager_id] int NULL, 
    [i_product_name] char(50) COLLATE Latin1_General_100_CI_AS_KS_WS NULL
)
WITH (CLUSTERED INDEX ( [i_item_sk] ASC ), DISTRIBUTION = REPLICATE);

쿼리의 설명 계획을 살펴보면 CU7.3 이전(또는 기능 스위치가 꺼져 있는 경우) 쿼리의 총 작업 수가 17개이고 CU7.3(또는 기능 스위치가 켜져 있는 경우) 동일한 쿼리에 총 9개의 작업이 표시됩니다. 데이터 이동 작업만 계산하면 이전 계획에 새 계획의 이동 작업 4개와 이동 작업 2개가 있음을 알 수 있습니다. 새 쿼리 최적화 프로그램은 새 계획으로 이미 만든 임시 테이블을 다시 사용하여 두 개의 데이터 이동 작업을 줄여 쿼리 런타임을 줄일 수 있었습니다.