Spiegazione dell'eliminazione delle sottoespressioni comuni
APS CU7.3 migliora le prestazioni delle query con l'eliminazione delle sottoespressioni comuni nell'ottimizzatore di query SQL. Il miglioramento migliora le query in due modi. Il primo vantaggio è che la capacità di identificare ed eliminare tali espressioni aiuta a ridurre i tempi di compilazione di SQL. Il secondo e più importante vantaggio è che le operazioni di spostamento dei dati per queste sottoespressioni ridondanti vengono eliminate, quindi il tempo di esecuzione per le query diventa più veloce.
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
;
Si consideri la query precedente dagli strumenti di benchmark TPC-DS. Nella query precedente la sottoquery è la stessa, ma la clausola order by con funzione rank() over viene ordinata in due modi diversi. Prima di CU7.3, questa sottoquery verrà valutata ed eseguita due volte, una per l'ordine crescente e una volta per l'ordine decrescente, con due operazioni di spostamento dei dati. Dopo aver installato APS CU7.3, la parte della sottoquery verrà valutata una volta riducendo così lo spostamento dei dati e completando la query più velocemente.
È stato introdotto uncommutatore di funzionalità chiamato "OptimizeCommonSubExpressions" che consentirà di testare la funzionalità anche dopo l'upgrade a APS CU7.3. La funzionalità è attiva per impostazione predefinita ma è possibile disattivarla.
Nota
Le modifiche apportate ai valori dei commutatori di funzionalità richiedono un riavvio del servizio.
È possibile provare la query di esempio creando le tabelle seguenti nell'ambiente di test e valutando il piano di spiegazione per la query indicata in precedenza.
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);
Se si esamina il piano di spiegazione della query, si noterà che prima di CU7.3 (o quando il commutatore di funzionalità è disattivata) la query ha un totale di 17 operazioni e dopo CU7.3 (o con l'opzione di funzionalità attivata) la stessa query mostra un totale di 9 operazioni. Se si contano solo le operazioni di spostamento dei dati, si noterà che il piano precedente ha quattro operazioni di spostamento rispetto a due operazioni di spostamento nel nuovo piano. Il nuovo query optimizer è stato in grado di ridurre due operazioni di spostamento dei dati riutilizzando la tabella temporanea già creata con il nuovo piano riducendo così il runtime di query.
Commenti e suggerimenti
Presto disponibile: Nel corso del 2024 verranno gradualmente disattivati i problemi di GitHub come meccanismo di feedback per il contenuto e ciò verrà sostituito con un nuovo sistema di feedback. Per altre informazioni, vedere https://aka.ms/ContentUserFeedback.
Invia e visualizza il feedback per