Dijelaskan eliminasi subekspresi umum

APS CU7.3 meningkatkan performa kueri dengan eliminasi subekspresi umum dalam pengoptimal kueri SQL. Peningkatan meningkatkan kueri dengan dua cara. Manfaat pertama adalah kemampuan untuk mengidentifikasi dan menghilangkan ekspresi tersebut membantu mengurangi waktu kompilasi SQL. Manfaat kedua dan yang lebih penting adalah operasi pergerakan data untuk subekspresi redundan ini dihilangkan sehingga waktu eksekusi untuk kueri menjadi lebih cepat.

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
  ;

Pertimbangkan kueri di atas dari alat tolok ukur TPC-DS. Dalam kueri di atas, subkueri sama tetapi urutan berdasarkan klausa dengan peringkat() atas fungsi diurutkan dengan dua cara berbeda. Sebelumnya ke CU7.3, subkueri ini akan dievaluasi dan dijalankan dua kali, sekali untuk urutan naik dan sekali untuk urutan menurun, menimbulkan dua operasi pergerakan data. Setelah menginstal APS CU7.3, bagian subkueri akan dievaluasi setelah dengan demikian mengurangi pergerakan data dan menyelesaikan kueri lebih cepat.

Kami telah memperkenalkan sakelar fitur yang disebut 'OptimizeCommonSubExpressions' yang akan memungkinkan Anda menguji fitur bahkan setelah Anda meningkatkan ke APS CU7.3. Fitur ini aktif secara default tetapi dapat dinonaktifkan.

Catatan

Perubahan pada nilai pengalihan fitur memerlukan mulai ulang layanan.

Anda dapat mencoba kueri sampel dengan membuat tabel berikut di lingkungan pengujian Anda dan mengevaluasi rencana penjelasan untuk kueri yang disebutkan di atas.

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

Jika Anda melihat rencana penjelasan kueri, Anda akan melihat bahwa sebelum CU7.3 (atau ketika sakelar fitur nonaktif) kueri memiliki 17 jumlah total operasi dan setelah CU7.3 (atau dengan pengalihan fitur diaktifkan) kueri yang sama menunjukkan 9 jumlah total operasi. Jika Anda hanya menghitung operasi pergerakan data, Anda akan melihat bahwa paket sebelumnya memiliki empat operasi pemindahan vs. dua operasi pemindahan dalam rencana baru. Pengoptimal kueri baru dapat mengurangi dua operasi pergerakan data dengan menggunakan kembali tabel sementara yang sudah dibuat dengan rencana baru sehingga mengurangi runtime kueri.