Allgemeine Subexpression-Eliminierung erklärt

APS CU7.3 verbessert die Abfrageleistung mit häufig auftretender Eliminierung von Subexpressionen in SQL-Abfrageoptimierer. Die Verbesserung verbessert Abfragen auf zwei Arten. Der erste Vorteil ist die Möglichkeit, solche Ausdrücke zu identifizieren und zu beseitigen, um die SQL-Kompilierungszeit zu reduzieren. Der zweite und wichtigere Vorteil ist, dass Datenverschiebungsvorgänge für diese redundanten Unterausdrücke eliminiert werden, damit die Ausführungszeit für Abfragen schneller wird.

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
  ;

Betrachten Sie die oben genannte Abfrage von TPC-DS-Benchmark-Tools. In der obigen Abfrage ist die Unterabfrage identisch, aber die Reihenfolge nach Klausel mit rang() over-Funktion wird auf zwei verschiedene Arten sortiert. Vor CU7.3 wird diese Unterabfrage zweimal ausgewertet und ausgeführt, einmal für aufsteigende Reihenfolge und einmal für absteigende Reihenfolge, wobei zwei Datenverschiebungsvorgänge entstehen. Nach der Installation von APS CU7.3 wird der Teil der Unterabfrage ausgewertet, sobald die Datenverschiebung reduziert und die Abfrage schneller beendet wird.

Wir haben einen Feature-Switch namens "OptimizeCommonSubExpressions" eingeführt, mit dem Sie das Feature auch nach dem Upgrade auf APS CU7.3 testen können. Das Feature ist standardmäßig aktiviert, kann aber deaktiviert werden.

Hinweis

Änderungen an Featurewechselwerten erfordern einen Dienstneustart.

Sie können die Beispielabfrage ausprobieren, indem Sie die folgenden Tabellen in Ihrer Testumgebung erstellen und den Erläuterungsplan für die oben genannte Abfrage auswerten.

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

Wenn Sie sich den Erläuterungsplan der Abfrage ansehen, sehen Sie, dass vor CU7.3 (oder wenn der Featureschalter deaktiviert ist) die Abfrage über 17 Gesamtanzahl der Vorgänge verfügt und nach CU7.3 (oder wenn der Featureschalter aktiviert ist) dieselbe Abfrage 9 Gesamtanzahl von Vorgängen anzeigt. Wenn Sie nur die Datenverschiebungsvorgänge zählen, sehen Sie, dass der vorherige Plan vier Verschiebungsvorgänge im Vergleich zu zwei Verschiebungsvorgängen im neuen Plan aufweist. Der neue Abfrageoptimierer konnte zwei Datenverschiebungsvorgänge reduzieren, indem er die bereits mit dem neuen Plan erstellte temporäre Tabelle wiederverwenden und somit die Abfragelaufzeit reduziert.