Objaśnienie eliminacji wspólnych podwyrażeń

Usługa APS CU7.3 poprawia wydajność zapytań dzięki eliminacji wspólnych podwyrażeń w optymalizatorze zapytań SQL. Poprawa poprawia zapytania na dwa sposoby. Pierwszą korzyścią jest możliwość identyfikowania i eliminowania takich wyrażeń, co pomaga skrócić czas kompilacji SQL. Druga i ważniejsza korzyść polega na tym, że operacje przenoszenia danych dla tych nadmiarowych podwyrażeń są usuwane, dzięki czemu czas wykonywania zapytań staje się szybszy.

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
  ;

Rozważ powyższe zapytanie z narzędzi do testów porównawczych TPC-DS. W powyższym zapytaniu podzapytanie jest takie samo, ale klauzula order by z funkcją rank() over jest uporządkowana na dwa różne sposoby. Przed CU7.3 to podzapytanie było oceniane i wykonywane dwa razy: raz dla kolejności rosnącej, a raz dla kolejności malejącej, co powoduje wykonanie dwóch operacji przenoszenia danych. Po zainstalowaniu aktualizacji CU7.3 usługi APS część podzapytania zostanie obliczona raz, co zmniejsza przenoszenie danych i szybsze kończenie zapytania.

Wprowadziliśmy przełącznik funkcji o nazwie "OptimizeCommonSubExpressions", który umożliwi przetestowanie funkcji nawet po uaktualnieniu do APS CU7.3. Funkcja jest domyślnie włączona, ale można jej wyłączyć.

Możesz wypróbować przykładowe zapytanie, tworząc poniższe tabele w środowisku testowym i oceniając plan wyjaśnienia dla powyższego zapytania.

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);

Jeśli przyjrzysz się planowi objaśnienia zapytania, zobaczysz, że przed aktualizacją CU7.3 (lub po wyłączeniu funkcji) zapytanie ma 17 całkowitą liczbę operacji i po włączeniu aktualizacji CU7.3 (lub z włączonym przełącznikiem funkcji) to samo zapytanie pokazuje 9 całkowitej liczby operacji. Jeśli po prostu zliczysz operacje przenoszenia danych, zobaczysz, że poprzedni plan ma cztery operacje przenoszenia a dwie operacje przenoszenia w nowym planie. Nowy optymalizator zapytań był w stanie zmniejszyć dwie operacje przenoszenia danych, ponownie używając tabeli tymczasowej, która została już utworzona przy użyciu nowego planu, zmniejszając w ten sposób środowisko uruchomieniowe zapytań.