--- title: "Common subexpression" description: "Displays example query improvement that was introduced in Analytics Platform System CU7.3" author: "mzaman1" ms.prod: "sql" ms.technology: data-warehouse ms.topic: "conceptual" ms.date: "12/17/2018" ms.author: "murshedz" ms.reviewer: "martinle" ms.custom: seo-dt-2019 monikerRange: ">= aps-pdw-2016-au7 || = sqlallproducts-allversions" --- # Common subexpression elimination explained APS CU7.3 improves query performance with common subexpression elimination in SQL query optimizer. The improvement improves queries in two ways. The first benefit is the ability to identify and eliminate such expressions help reduce SQL compilation time. The second and more important benefit is data movement operations for these redundant subexpressions are eliminated thus execution time for queries becomes faster. ```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 ; ``` Consider the above query from TPC-DS benchmark tools. In the above query, the subquery is the same but the order by clause with rank() over function is sorted in two different ways. Previous to CU7.3, this subquery will get evaluated and executed twice, once for ascending order and once for descending order, incurring two data movement operations. After installing APS CU7.3, the subquery part will get evaluated once thus reducing data movement and finishing the query faster. We have introduced a [feature switch](appliance-feature-switch.md) called 'OptimizeCommonSubExpressions' that will allow you test the feature even after you upgrade to APS CU7.3. The feature is on by default but can be turned off. > [!NOTE] > Changes to feature switch values require a service restart. You can try the sample query by creating the following tables in your test environment and evaluating the explain plan for the above mentioned query. ```sql 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); ``` If you take a look at the explain plan of the query, you will see that before CU7.3 (or when the feature switch is off) the query has 17 total number of operations and after CU7.3 (or with the feature switch turned on) the same query shows 9 total number of operations. If you just count the data movement operations, you will see that the previous plan has four move operations vs. two move operations in the new plan. The new query optimizer was able to reduce two data movement operations by reusing the temp table it already created with the new plan thus reducing query runtime.