マテリアライズドビューを使用したパフォーマンスのチューニング

Azure Synapse の専用 SQL プールの具体化されたビューでは、複雑な分析クエリのメンテナンス方法が低く、クエリを変更することなく高速なパフォーマンスを得ることができます。 この記事では、具体化されたビューの使用に関する一般的なガイダンスについて説明します。

具体化されたビューと標準ビュー

Azure Synapse の専用 SQL プールでは、標準ビューと具体化されたビューがサポートされます。 どちらも SELECT 式を使用して作成され、論理テーブルとしてクエリに表示される仮想テーブルです。 ビューは、一般的なデータ計算の複雑さをカプセル化し、計算の変更に抽象化レイヤーを追加するため、クエリを書き換える必要はありません。

標準ビューでは、ビューが使用されるたびにデータが計算されます。 ディスクにデータが格納されません。 通常、標準ビューは、専用 SQL プール内の論理オブジェクトとクエリを整理するのに役立つツールとして使用されます。 標準ビューを使用するには、クエリで直接参照する必要があります。

具体化されたビューでは、テーブルと同様に、データが事前に計算され、格納され、専用 SQL プールに保持されます。 具体化されたビューが使用されるたびに再計算は必要ありません。 そのため、具体化されたビューでデータのすべてまたはサブセットを使用するクエリのパフォーマンスが向上します。 さらに、クエリでは、直接参照しなくても具体化されたビューを使用できるため、アプリケーション コードを変更する必要はありません。

標準ビューの要件のほとんどは、具体化されたビューに引き続き適用されます。 具体化されたビューの構文とその他の要件の詳細については、CREATE MATERIALIZED VIEW AS SELECT を参照してください。

具体化されたビューを使用する利点

適切に設計された具体化されたビューには、次の利点があります。

• JOIN と集計関数を使用した複雑なクエリの実行時間を短縮します。 クエリが複雑になるほど、実行時間を節約できる可能性が高くなります。 クエリの計算コストが高く、結果のデータ セットが小さい場合、最も大きな利点が得られます。
• 専用 SQL プールのオプティマイザーは、デプロイされた具体化されたビューを自動的に使用して、クエリ実行プランを改善できます。 このプロセスは、より高速なクエリ パフォーマンスを提供するユーザーに対して透過的であり、具体化されたビューへの直接参照を行うためにクエリを必要としません。
• ビューのメンテナンスを最小限に抑える必要があります。 基本テーブルからのすべての増分データ変更は、同期的にマテリアライズド ビューに自動的に追加されます。つまり、ベース テーブルとマテリアライズド ビューの両方が同じトランザクションで更新されます。 この設計により、具体化されたビューに対してクエリを実行すると、ベース テーブルに直接クエリを実行する場合と同じデータが返されます。
• 具体化されたビューのデータは、ベース テーブルとは異なる方法で分散できます。
• 具体化されたビューのデータには、通常のテーブルのデータと同じ高可用性と回復性の利点があります。

専用 SQL プールに実装された具体化されたビューには、次の利点もあります。

他のデータ ウェアハウス プロバイダーと比較して、専用 SQL プールに実装された具体化されたビューには、次の利点もあります。

• 広範な集計関数のサポート。 「 CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)」を参照してください。
• クエリ固有のマテリアライズドビュー推奨のサポート。 EXPLAIN (Transact-SQL) を参照してください。
• ベース テーブルのデータ変更による自動および同期データ更新。 ユーザーによる操作は不要です。

一般的なシナリオ

具体化されたビューは、通常、次のシナリオで使用されます。

サイズの大きいデータに対する複雑な分析クエリのパフォーマンスを向上させる必要がある

通常、複雑な分析クエリでは、より多くの集計関数とテーブル結合が使用されるため、クエリ実行でのシャッフルや結合などのコンピューティング負荷の高い操作が発生します。 そのため、複雑な分析クエリは、特に大きなテーブルでは完了に時間がかかります。

ユーザーは、クエリの一般的な計算から返されるデータの具体化されたビューを作成できるため、クエリでこのデータが必要な場合に再計算は必要ないため、コンピューティング コストを削減し、クエリの応答を高速化できます。

クエリの変更なしで、または最小限のクエリ変更を行って、より高速なパフォーマンスを必要とする

通常、専用 SQL プールでのスキーマとクエリの変更は、通常、ETL の通常の操作とレポートをサポートするために最小限に抑えられます。 ビューによって発生するコストがクエリ パフォーマンスの向上によって相殺される場合は、具体化されたビューを使用してクエリ パフォーマンスのチューニングを行うことができます。

スケーリングや統計管理などの他のチューニング オプションと比較して、具体化されたビューを作成して維持することは影響の少ない運用環境の変更であり、潜在的なパフォーマンス向上も高くなります。

• 具体化されたビューを作成または維持しても、ベース テーブルに対して実行されているクエリには影響しません。
• クエリ オプティマイザーは、クエリ内の直接ビュー参照なしで、デプロイされた具体化されたビューを自動的に使用できます。 この機能により、パフォーマンス チューニングでのクエリ変更の必要性が軽減されます。

クエリパフォーマンスを向上させるために異なるデータ分散戦略が必要

専用 SQL プールは、分散クエリ処理システムです。 SQL テーブル内のデータは、3 つの 分散戦略 (ハッシュ、round_robin、またはレプリケート) のいずれかを使用して最大 60 ノードに分散されます。

データ分散はテーブル作成時に指定され、テーブルが削除されるまで変更されません。 具体化されたビューは、ディスク上の仮想テーブルであり、ハッシュとround_robinデータ分散をサポートします。 ユーザーは、ベース テーブルとは異なるデータ分散を選択できますが、ビューを使用するクエリのパフォーマンスに最適です。

設計ガイダンス

具体化されたビューを使用してクエリのパフォーマンスを向上させる一般的なガイダンスを次に示します。

ワークロードの設計

具体化されたビューの作成を開始する前に、クエリ パターン、重要度、頻度、結果データのサイズに関するワークロードを深く理解することが重要です。

ユーザーは、クエリ オプティマイザーによって推奨される具体化されたビューの EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> を実行できます。 これらの推奨事項はクエリ固有であるため、1 つのクエリにメリットがある具体化されたビューは、同じワークロード内の他のクエリには最適でない場合があります。

ワークロードのニーズを念頭に置いて、これらの推奨事項を評価します。 理想的な具体化されたビューは、ワークロードのパフォーマンスにメリットがあるビューです。

より高速なクエリとコストのトレードオフに注意してください

具体化されたビューごとに、ビューを維持するためのデータ ストレージ コストとコストが発生します。 ベース テーブルのデータが変わると、具体化されたビューのサイズが大きくなり、その物理的な構造も変わります。 クエリ パフォーマンスの低下を回避するために、具体化された各ビューは SQL エンジンによって個別に維持されます。

具体化されたビューとベース テーブルの変更の数が増えると、メンテナンス ワークロードが増加します。 ユーザーは、すべての具体化されたビューから発生したコストが、クエリのパフォーマンス向上によってオフセットできるかどうかを確認する必要があります。

このクエリを実行して、専用 SQL プールに具体化されたビューの一覧を生成できます。

SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

具体化されたビューの数を減らすオプション:

• ワークロード内の複雑なクエリで頻繁に使用される一般的なデータ セットを特定します。 実行プランの作成時にオプティマイザーがそれらを構成要素として使用できるように、それらのデータ セットを格納する具体化されたビューを作成します。

• 使用率が低い、または不要になった具体化されたビューを削除します。 マテリアライズドビューを無効にしても、メンテナンスは行われませんが、ストレージ コストは引き続き発生します。

• データが重複しない場合でも、同じまたは類似のベース テーブルに作成された具体化されたビューを結合します。 具体化されたビューを組み合わせると、個別のビューの合計よりも大きなサイズになる可能性があります。ただし、ビューのメンテナンス コストは削減されます。 例えば次が挙げられます。

-- Query 1 would benefit from having a materialized view created with this SELECT statement

SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2 would benefit from having a materialized view created with this SELECT statement

SELECT C, SUM(D)
FROM T
GROUP BY C

-- You could create a single materialized view of this form

SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C

すべてのパフォーマンス チューニングでクエリの変更が必要なわけではありません

SQL クエリ オプティマイザーでは、デプロイされた具体化されたビューを自動的に使用して、クエリのパフォーマンスを向上させることができます。 このサポートは、ビューを参照しないクエリと、具体化されたビューの作成でサポートされていない集計を使用するクエリに対して透過的に適用されます。 クエリの変更は必要ありません。 クエリの推定実行プランを確認して、具体化されたビューが使用されているかどうかを確認できます。

具体化されたビューを監視する

具体化されたビューは、クラスター化列ストア インデックス (CCI) を持つテーブルと同じように、専用 SQL プールに格納されます。 具体化されたビューからのデータの読み取りには、CCI インデックス セグメントのスキャンと、ベース テーブルからの増分変更の適用が含まれます。 増分変更の数が多すぎる場合、具体化されたビューからクエリを解決するには、ベース テーブルに直接クエリを実行するよりも時間がかかる場合があります。

クエリ パフォーマンスの低下を回避するには、 DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD を実行してビューのoverhead_ratio (total_rows/max(1, base_view_row)) を監視することをお勧めします。 overhead_ratioが大きすぎる場合は、マテリアライズドビューを再構築してください。

具体化されたビューと結果セットのキャッシュ

専用 SQL プールのこれら 2 つの機能は、クエリパフォーマンスのチューニングに使用されます。 結果セットのキャッシュは、静的データに対する反復的なクエリから高いコンカレンシーと高速応答を取得するために使用されます。

キャッシュされた結果を使用するには、キャッシュ要求クエリの形式が、キャッシュを生成したクエリと一致する必要があります。 さらに、キャッシュされた結果はクエリ全体に適用される必要があります。

具体化されたビューでは、基本テーブルのデータを変更できます。 具体化されたビューのデータは、クエリの一部に適用できます。 このサポートにより、パフォーマンスを向上させるために、計算を共有する異なるクエリで同じ具体化されたビューを使用できます。

例

この例では、TPCDS のようなクエリを使用して、店舗よりもカタログ経由でより多くのお金を使う顧客を見つけ、優先顧客とその出身国/地域を特定します。 このクエリでは、SUM() と GROUP BY を含む 3 つのサブ SELECT ステートメントの UNION から TOP 100 レコードを選択します。

WITH year_total AS (
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
       ,'s' sale_type
FROM customer
     ,store_sales
     ,date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
       ,'c' sale_type
FROM customer
     ,catalog_sales
     ,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
       ,'w' sale_type
FROM customer
     ,web_sales
     ,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  SELECT TOP 100
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_c_secyear.customer_id
   AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
   AND t_s_firstyear.sale_type = 's'
   AND t_c_firstyear.sale_type = 'c'
   AND t_w_firstyear.sale_type = 'w'
   AND t_s_secyear.sale_type = 's'
   AND t_c_secyear.sale_type = 'c'
   AND t_w_secyear.sale_type = 'w'
   AND t_s_firstyear.dyear+0 =  1999
   AND t_s_secyear.dyear+0 = 1999+1
   AND t_c_firstyear.dyear+0 =  1999
   AND t_c_secyear.dyear+0 =  1999+1
   AND t_w_firstyear.dyear+0 = 1999
   AND t_w_secyear.dyear+0 = 1999+1
   AND t_s_firstyear.year_total > 0
   AND t_c_firstyear.year_total > 0
   AND t_w_firstyear.year_total > 0
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');

クエリの推定実行プランを確認します。 18 個のシャッフルと 17 個の結合操作があり、実行に時間がかかります。 次に、3 つのサブ SELECT ステートメントごとに 1 つの具体化されたビューを作成してみましょう。

CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.store_sales
     ,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
          , count_big(*) as cb
FROM dbo.customer
     ,dbo.catalog_sales
     ,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.web_sales
     ,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

元のクエリの実行プランをもう一度確認します。 結合の数が 17 から 5 に変わり、シャッフルはありません。 プランで [フィルター操作] アイコンを選択すると、その出力リストに、基本テーブルではなく具体化されたビューからデータが読み取られたと表示されます。

具体化されたビューでは、コードを変更することなく、同じクエリの実行速度が速くなります。

次のステップ

開発に関するその他のヒントについては、 専用 SQL プールの開発の概要に関するページを参照してください。