Explication de l’élimination des sous-expressions courantes
APS CU7.3 améliore les performances des requêtes avec l’élimination courante des sous-expressions dans l’optimiseur de requête SQL. L’amélioration améliore les requêtes de deux façons. Le premier avantage est la possibilité d’identifier et d’éliminer ces expressions pour réduire le temps de compilation SQL. Le deuxième avantage et plus important est les opérations de déplacement des données pour ces sous-expressions redondantes, ce qui permet d’éliminer le temps d’exécution des requêtes plus rapidement.
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
;
Considérez la requête ci-dessus à partir des outils de benchmark TPC-DS. Dans la requête ci-dessus, la sous-requête est la même, mais la clause order by avec rank() sur la fonction est triée de deux façons différentes. Avant CU7.3, cette sous-requête sera évaluée et exécutée deux fois, une fois pour l’ordre croissant et une fois pour l’ordre décroissant, ce qui entraîne deux opérations de déplacement de données. Après avoir installé APS CU7.3, la partie sous-requête est évaluée une fois ce qui réduit le déplacement des données et termine la requête plus rapidement.
Nous avons introduit un commutateur de fonctionnalité appelé « OptimizeCommonSubExpressions » qui vous permettra de tester la fonctionnalité même après la mise à niveau vers APS CU7.3. La fonctionnalité est activée par défaut, mais peut être désactivée.
Note
Les modifications apportées aux valeurs de commutateur de fonctionnalité nécessitent un redémarrage du service.
Vous pouvez essayer l’exemple de requête en créant les tableaux suivants dans votre environnement de test et en évaluant le plan d’explication de la requête mentionnée ci-dessus.
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 vous examinez le plan d’explication de la requête, vous verrez qu’avant CU7.3 (ou lorsque le commutateur de fonctionnalité est désactivé), la requête a 17 opérations totales et après CU7.3 (ou avec le commutateur de fonctionnalité activé), la même requête affiche 9 nombre total d’opérations. Si vous comptez simplement les opérations de déplacement des données, vous verrez que le plan précédent a quatre opérations de déplacement par rapport à deux opérations de déplacement dans le nouveau plan. Le nouvel optimiseur de requête a pu réduire deux opérations de déplacement de données en réutilisant la table temporaire qu’elle a déjà créée avec le nouveau plan, réduisant ainsi le runtime de requête.