Eliminación de subexpresiones comunes explicada

APS CU7.3 mejora el rendimiento de las consultas con la eliminación de subexpresión común en el optimizador de consultas SQL. La mejora mejora las consultas de dos maneras. La primera ventaja es la capacidad de identificar y eliminar estas expresiones ayudan a reducir el tiempo de compilación de SQL. La segunda y más importante ventaja es que las operaciones de movimiento de datos para estas subexpresiones redundantes se eliminan, por lo que el tiempo de ejecución de las consultas se vuelve más rápido.

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
  ;

Tenga en cuenta la consulta mencionada arriba de las herramientas de pruebas comparativas TPC-DS. En la consulta anterior, la subconsulta es la misma, pero la cláusula *order by* con la función *rank() over* se ordena de dos maneras diferentes. Antes de CU7.3, esta subconsulta se evaluará y ejecutará dos veces, una para el orden ascendente y una para el orden descendente, incurriendo en dos operaciones de movimiento de datos. Después de instalar APS CU7.3, la parte de subconsulta se evaluará una sola vez, lo que reducirá el movimiento de datos y finalizará la consulta de manera más rápida.

Hemos introducido un modificador de características denominado "OptimizeCommonSubExpressions" que le permitirá probar la característica incluso después de actualizar a APS CU7.3. La característica está activada de forma predeterminada, pero se puede desactivar.

Puede probar la consulta de ejemplo mediante la creación de las tablas siguientes en el entorno de prueba y la evaluación del plan de explicación de la consulta mencionada anteriormente.

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

Si examina el plan de explicación de la consulta, verá que antes de CU7.3 (o cuando el conmutador de características está desactivado), la consulta tiene un total de 17 operaciones, y después de CU7.3 (o con el conmutador de características activado), la misma consulta muestra un total de 9 operaciones. Si solo cuenta las operaciones de movimiento de datos, verá que el plan anterior tiene cuatro operaciones de movimiento frente a dos operaciones de movimiento en el nuevo plan. El nuevo optimizador de consultas pudo reducir dos operaciones de movimiento de datos mediante la reutilización de la tabla temporal que ya creó con el nuevo plan, lo que reduce el tiempo de ejecución de consultas.