Performance tune with materialized views

Tampilan materialisasi untuk kumpulan SQL khusus di Azure Synapse menyediakan metode pemeliharaan rendah untuk kueri analitik yang kompleks untuk mendapatkan performa cepat tanpa perubahan kueri apa pun. This article discusses the general guidance on using materialized views.

Tampilan materialisasi vs. tampilan standar

Dedicated SQL pool in Azure Synapse supports standard and materialized views. Keduanya adalah tabel virtual yang dibuat dengan ekspresi SELECT dan disajikan ke kueri sebagai tabel logis. Tampilan merangkum kompleksitas komputasi data umum dan menambahkan lapisan abstraksi ke perubahan komputasi sehingga tidak perlu menulis ulang kueri.

Tampilan standar menghitung datanya setiap kali tampilan digunakan. Tidak ada data yang disimpan pada disk. Orang biasanya menggunakan tampilan standar sebagai alat yang membantu mengatur objek dan kueri logis di kumpulan SQL khusus. Untuk menggunakan tampilan standar, kueri perlu membuat referensi langsung ke tampilan tersebut.

Tampilan materialisasi menghitung terlebih dahulu, menyimpan, dan memelihara datanya dalam kumpulan SQL khusus, sama seperti tabel. Tidak diperlukan penghitungan ulang setiap kali tampilan terwujud digunakan. Itulah sebabnya kueri yang menggunakan semua atau subset data dalam tampilan materialisasi bisa mendapatkan performa yang lebih cepat. Lebih baik lagi, kueri dapat menggunakan tampilan materialisasi tanpa membuat referensi langsung ke sana, sehingga tidak perlu mengubah kode aplikasi.

Sebagian besar persyaratan pada tampilan standar masih berlaku untuk tampilan materialisasi. Untuk detail tentang sintaks tampilan materialisasi dan persyaratan lainnya, lihat CREATE MATERIALIZED VIEW AS SELECT

Perbandingan	View	Materialized View
Lihat definisi	Disimpan di kumpulan SQL khusus.	Disimpan di kumpulan SQL khusus.
Menampilkan konten	Dihasilkan setiap kali tampilan digunakan.	Pra-proses dan disimpan di kumpulan SQL khusus selama pembuatan tampilan. Diperbarui saat data ditambahkan ke tabel dasar.
Pembaruan data	Selalu diperbarui	Selalu diperbarui
Speed to retrieve view data from complex queries	Slow	Cepat
Penyimpanan tambahan	Tidak.	Ya
Sintaksis	CREATE VIEW	CREATE MATERIALIZED VIEW AS SELECT

Benefits of using materialized views

Tampilan termaterialisasi yang dirancang dengan benar memberikan keuntungan berikut:

Kurangi waktu eksekusi untuk kueri kompleks dengan JOIN dan fungsi agregat. Makin kompleks kueri, makin tinggi potensi penghematan waktu eksekusi. Keuntungan terbesar diperoleh saat biaya komputasi kueri tinggi dan himpunan data yang dihasilkan kecil.
Pengoptimal di kumpulan SQL khusus dapat secara otomatis menggunakan tampilan materialisasi yang disebarkan untuk meningkatkan rencana eksekusi kueri. Proses ini transparan bagi pengguna, memberikan kinerja kueri yang lebih cepat dan tidak memerlukan kueri untuk merujuk langsung ke tampilan terwujud.
Require low maintenance on the views. Semua perubahan data bertahap dari tabel dasar secara otomatis ditambahkan ke tampilan materialisasi secara sinkron, yang berarti tabel dasar dan tampilan materialisasi diperbarui dalam transaksi yang sama. Desain ini memungkinkan pengkuerian tampilan terwujud untuk mengembalikan data yang sama seperti mengkueri tabel dasar secara langsung.
Data dalam tampilan terwujud dapat didistribusikan secara berbeda dari tabel dasar.
Data dalam tampilan terwujud mendapatkan manfaat ketersediaan dan ketahanan tinggi yang sama dengan data dalam tabel reguler.

Tampilan materialisasi yang diterapkan dalam kumpulan SQL khusus juga memberikan manfaat berikut:

Dibandingkan dengan penyedia gudang data lainnya, tampilan materialisasi yang diterapkan dalam kumpulan SQL khusus juga memberikan manfaat berikut:

Dukungan fungsi agregat yang luas. See CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
The support for query-specific materialized view recommendation. Lihat MENJELASKAN (Transact-SQL).
Refresh data otomatis dan sinkron dengan perubahan data di tabel dasar. Tidak diperlukan tindakan pengguna.

Skenario umum

Materialized views are typically used in the following scenarios:

Perlu meningkatkan performa kueri analitik kompleks terhadap ukuran data besar

Kueri analitik kompleks biasanya menggunakan lebih banyak fungsi agregat dan gabungan tabel, menyebabkan lebih banyak operasi komputasi berat seperti pengacakan dan gabungan dalam eksekusi kueri. Itulah sebabnya kueri analitik yang kompleks membutuhkan waktu lebih lama untuk diselesaikan, terutama pada tabel besar.

Pengguna dapat membuat tampilan materialisasi untuk data yang dikembalikan dari komputasi kueri umum, sehingga tidak ada komputasi ulang yang diperlukan ketika data ini diperlukan oleh kueri, memungkinkan biaya komputasi yang lebih rendah dan respons kueri yang lebih cepat.

Butuh performa lebih cepat dengan sedikit atau tanpa perubahan kueri

Perubahan skema dan kueri dalam kumpulan SQL khusus biasanya dijaga seminimal mungkin untuk mendukung operasi dan pelaporan ETL reguler. People can use materialized views for query performance tuning, if the cost incurred by the views can be offset by the gain in query performance.

Dibandingkan dengan opsi penyetelan lain seperti manajemen penskalakan dan statistik, ini adalah perubahan produksi yang kurang berdampak untuk membuat dan mempertahankan tampilan terwujud dan potensi perolehan performanya juga lebih tinggi.

Creating or maintaining materialized views does not impact the queries running against the base tables.
The query optimizer can automatically use the deployed materialized views without direct view reference in a query. Kemampuan ini mengurangi kebutuhan akan perubahan kueri dalam penyetelan performa.

Perlu strategi distribusi data yang berbeda untuk performa kueri yang lebih cepat

Kumpulan SQL khusus adalah sistem pemrosesan kueri terdistribusi. Data dalam tabel SQL didistribusikan hingga 60 simpul menggunakan salah satu dari tiga strategi distribusi (hash, round_robin, atau direplikasi).

Distribusi data ditentukan pada waktu pembuatan tabel dan tetap tidak berubah hingga tabel dihilangkan. Materialized view, being a virtual table on disk, supports hash and round_robin data distributions. Pengguna dapat memilih distribusi data yang berbeda dari tabel dasar tetapi optimal untuk performa kueri yang menggunakan tampilan.

Panduan desain

Berikut adalah panduan umum tentang menggunakan tampilan materialisasi untuk meningkatkan performa kueri:

Mendesain untuk beban kerja Anda

Sebelum Anda mulai membuat tampilan materialisasi, penting untuk memiliki pemahaman mendalam tentang beban kerja Anda dalam hal pola kueri, kepentingan, frekuensi, dan ukuran data yang dihasilkan.

Pengguna dapat menjalankan EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> untuk tampilan materialisasi yang disarankan oleh pengoptimal kueri. Karena rekomendasi ini khusus kueri, tampilan materialisasi yang menguntungkan satu kueri mungkin tidak optimal untuk kueri lain dalam beban kerja yang sama.

Evaluasi rekomendasi ini dengan pertimbangkan kebutuhan beban kerja Anda. The ideal materialized views are those that benefit the workload's performance.

Be aware of the tradeoff between faster queries and the cost

Untuk setiap tampilan materialisasi, ada biaya penyimpanan data dan biaya untuk mempertahankan tampilan. Saat data berubah dalam tabel dasar, ukuran tampilan materialisasi meningkat dan struktur fisiknya juga berubah. Untuk menghindari penurunan performa kueri, setiap tampilan materialisasi dipertahankan secara terpisah oleh mesin SQL.

Beban kerja pemeliharaan menjadi lebih tinggi ketika jumlah tampilan materialisasi dan perubahan tabel dasar meningkat. Users should check if the cost incurred from all materialized views can be offset by the query performance gain.

Anda dapat menjalankan kueri ini untuk menghasilkan daftar tampilan materialisasi di kumpulan SQL khusus:

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;

Opsi untuk mengurangi jumlah tampilan materialisasi:

Identifikasi himpunan data umum yang sering digunakan oleh kueri kompleks dalam beban kerja Anda. Buat tampilan materialisasi untuk menyimpan himpunan data tersebut sehingga pengoptimal dapat menggunakannya sebagai blok penyusun saat membuat rencana eksekusi.
Hilangkan tampilan materialisasi yang memiliki penggunaan rendah atau tidak lagi diperlukan. A disabled materialized view is not maintained but it still incurs storage cost.
Gabungkan tampilan materialisasi yang dibuat pada tabel dasar yang sama atau serupa meskipun datanya tidak tumpang tindih. Menggabungkan tampilan yang telah dimaterialisasi dapat menghasilkan tampilan yang lebih besar ukurannya dibandingkan dengan jumlah tampilan-tampilan terpisah, namun biaya untuk memelihara tampilan tersebut seharusnya berkurang. Contohnya:


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

Tidak semua pengoptimalan performa mengharuskan perubahan kueri

Pengoptimal kueri SQL dapat secara otomatis menggunakan tampilan materialisasi yang disebarkan untuk meningkatkan performa kueri. This support is applied transparently to queries that don't reference the views and queries that use aggregates unsupported in materialized views creation. Tidak diperlukan perubahan pencarian. You can check a query's estimated execution plan to confirm if a materialized view is used.

Monitor materialized views

Tampilan materialisasi disimpan di kumpulan SQL khusus seperti tabel dengan indeks penyimpan kolom berkluster (CCI). Membaca data dari tampilan materialisasi termasuk memindai segmen indeks CCI dan menerapkan perubahan bertahap dari tabel dasar. Ketika jumlah perubahan inkremental terlalu tinggi, menyelesaikan kueri dari tampilan materialisasi bisa memakan waktu lebih lama daripada langsung mengkueri tabel dasar.

To avoid query performance degradation, it's a good practice to run DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD to monitor the view's overhead_ratio (total_rows / max(1, base_view_row)). Users should REBUILD the materialized view if its overhead_ratio is too high.

Materialized view and result set caching

Kedua fitur ini dalam kumpulan SQL khusus digunakan untuk penyetelan performa kueri. Result set caching is used for getting high concurrency and fast response from repetitive queries against static data.

Untuk menggunakan hasil cache, bentuk kueri permintaan cache harus cocok dengan kueri yang menghasilkan cache. Selain itu, hasil yang di-cache harus berlaku untuk seluruh kueri.

Tampilan materialisasi memungkinkan perubahan data dalam tabel dasar. Data in materialized views can be applied to a piece of a query. Dukungan ini memungkinkan tampilan materialisasi yang sama digunakan oleh kueri berbeda yang berbagi beberapa komputasi untuk performa yang lebih cepat.

Contoh

Contoh ini menggunakan kueri seperti TPCDS yang menemukan pelanggan yang menghabiskan lebih banyak uang melalui katalog daripada di toko, mengidentifikasi pelanggan pilihan dan negara/wilayah asal mereka. The query involves selecting TOP 100 records from the UNION of three sub-SELECT statements involving SUM() and GROUP BY.

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

Periksa perkiraan rencana eksekusi kueri. There are 18 shuffles and 17 joins operations, which take more time to execute. Sekarang mari kita buat satu tampilan materialisasi untuk masing-masing dari tiga pernyataan sub-SELECT.

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

Periksa rencana eksekusi kueri asli lagi. Now the number of joins changes from 17 to 5 and there's no shuffle. Select the Filter operation icon in the plan, its Output List shows the data is read from the materialized views instead of the base tables.

Plan_Output_List_with_Materialized_Views

Dengan tampilan materialisasi, kueri yang sama berjalan lebih cepat tanpa perubahan kode.

Langkah berikutnya

Untuk tips pengembangan lainnya, lihat Gambaran umum pengembangan kumpulan SQL Khusus.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-04-12