Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
Mengoptimalkan subkueri berkorelasi di Babelfish
Sebuah kolom tabel referensi subquery berkorelasi dari kueri luar. Ini dievaluasi sekali untuk setiap baris yang dikembalikan oleh kueri luar. Dalam contoh berikut, subquery referensi kolom dari tabel t1. Tabel ini tidak termasuk dalam FROM klausa subquery, tetapi direferensikan dalam klausa kueri luar. FROM Jika tabel t1 memiliki 1 juta baris, subquery perlu dievaluasi 1 juta kali.
SELECT col1, col2 FROM t1 WHERE col1 < (SELECT sum(col1) FROM t2 WHERE t1.col2 = t2.col2);
Meningkatkan kinerja kueri Babelfish menggunakan transformasi subquery
Babelfish dapat mempercepat subkueri yang berkorelasi dengan mengubahnya menjadi gabungan luar yang setara. Optimalisasi ini berlaku untuk dua jenis subkueri berkorelasi berikut:
-
Subquery yang mengembalikan nilai agregat tunggal, dan muncul dalam daftar. SELECT Untuk informasi selengkapnya, lihat SELECTklausul
dalam dokumentasi Microsoft Transact-SQL. SELECT ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) FROM outer_sb_t1; -
Subquery yang mengembalikan nilai agregat tunggal dan muncul dalam klausa. WHERE
SELECT * FROM outer_sb_t1 WHERE ( SELECT avg(a) FROM inner_sb_t2 WHERE inner_sb_t2.a = outer_sb_t1.a) > 1.0;
Mengaktifkan transformasi dalam subquery
Untuk mengaktifkan transformasi subkueri yang berkorelasi menjadi gabungan luar yang setara, atur parameternya ke. apg_enable_correlated_scalar_transform ON Parameter ini tersedia di Babelfish 4.2.0 dan versi yang lebih baru. Nilai default dari parameter ini adalahOFF.
Anda dapat memodifikasi kelompok parameter cluster atau instance untuk mengatur parameter. Untuk mempelajari selengkapnya, lihat .
Atau, Anda dapat mengonfigurasi pengaturan hanya untuk sesi saat ini dengan memanggil fungsiset_config. Misalnya, jalankan perintah berikut untuk mengaktifkan cache subquery di Babelfish. Untuk mempelajari selengkapnya, lihat Fungsi Pengaturan Konfigurasi
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Memverifikasi transformasi
Gunakan EXPLAIN perintah untuk memverifikasi apakah subquery yang berkorelasi telah diubah menjadi gabungan luar dalam rencana kueri. Untuk informasi selengkapnya, lihat Menggunakan rencana explain untuk meningkatkan performa kueri Babelfish.
Ketika transformasi diaktifkan, bagian subquery berkorelasi yang berlaku akan diubah menjadi gabungan luar. Sebagai contoh:
1>select set_config('apg_enable_correlated_scalar_transform', 'true', false);2>GO1>set BABELFISH_STATISTICS PROFILE on2>GO1>select customer_name, ( select max(o.cost) from correlated_orders o2>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount3>from correlated_customers c order by customer_name ;4>GOQUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Query Text: select customer_name, ( select max(o.cost) from correlated_orders o where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10 ) AS max_order_amount from correlated_customers c order by customer_name Sort (cost=88.23..90.18 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Hash Left Join (cost=30.90..50.76 rows=780 width=40) Hash Cond: (c.customer_id = o.customer_id) -> Seq Scan on correlated_customers c (cost=0.00..17.80 rows=780 width=36) -> Hash (cost=28.73..28.73 rows=174 width=12) -> HashAggregate (cost=26.99..28.73 rows=174 width=12) Group Key: o.customer_id -> Seq Scan on correlated_orders o (cost=0.00..25.30 rows=338 width=12) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10)) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Kueri yang sama tidak diubah ketika GUC parameter diputarOFF. Rencana tidak akan memiliki gabungan luar tetapi subplan sebagai gantinya.
1>select set_config('apg_enable_correlated_scalar_transform', 'false', false);2>GO1>select customer_name, ( select max(o.cost)2>from correlated_orders o3>where o.customer_id = c.customer_id and o.total_amount > 10 and o.customer_id != 10) AS max_order_amount4>from correlated_customers c order by customer_name ;5>GOQUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Sort (cost=21789.97..21791.92 rows=780 width=40) Sort Key: c.customer_name NULLS FIRST -> Seq Scan on correlated_customers c (cost=0.00..21752.50 rows=780 width=40) SubPlan 1 -> Aggregate (cost=27.86..27.87 rows=1 width=8) -> Seq Scan on correlated_orders o (cost=0.00..27.85 rows=2 width=8) Filter: (((total_amount)::numeric > 10) AND (customer_id <> 10) AND (customer_id = c.customer_id))
Batasan
-
Subquery harus berada di select_list atau di salah satu kondisi di klausa where. Jika tidak, itu tidak akan diubah.
-
Subquery harus mengembalikan fungsi agregat. Fungsi agregat yang ditentukan pengguna tidak didukung untuk transformasi.
-
Subquery yang ekspresi pengembaliannya bukan fungsi agregat sederhana tidak akan diubah.
-
Kondisi berkorelasi dalam WHERE klausa subquery harus menjadi referensi kolom sederhana. Jika tidak, itu tidak akan diubah.
Kondisi berkorelasi dalam subquery di mana klausa harus berupa predikat kesetaraan biasa.
Subquery berkorelasi yang berisi TOP klausa tidak dapat diubah.
-
Subquery tidak dapat berisi klausa A HAVING atau GROUP BY.
-
Klausa where dalam subquery mungkin berisi satu atau lebih predikat yang dikombinasikan dengan. AND Jika WHERE klausa berisi klausa OR, klausa tersebut tidak dapat diubah.
Menggunakan cache subquery untuk meningkatkan kinerja kueri Babelfish
Dimulai dengan versi 4.2.0, Babelfish mendukung cache subquery untuk menyimpan hasil subquery yang berkorelasi. Fitur ini melewatkan eksekusi subquery berkorelasi berulang saat hasil subquery sudah ada di cache.
Memahami cache subquery
Node Memoize Postgre SQL adalah bagian penting dari cache subquery. Node Memoize mempertahankan tabel hash di cache lokal untuk memetakan dari nilai parameter input ke baris hasil kueri. Batas memori untuk tabel hash adalah produk dari work_mem dan hash_mem_multiplier. Untuk mempelajari lebih lanjut, lihat Konsumsi Sumber Daya
Selama eksekusi kueri, cache subquery menggunakan Cache Hit Rate (CHR) untuk memperkirakan apakah cache meningkatkan kinerja kueri dan memutuskan pada waktu proses kueri apakah akan terus menggunakan cache. CHRadalah rasio jumlah klik cache dengan jumlah total permintaan. Misalnya, jika subquery yang berkorelasi perlu dieksekusi 100 kali, dan 70 dari hasil eksekusi tersebut dapat diambil dari cache, itu adalah 0,7. CHR
Untuk setiap jumlah cache apg_subquery_cache_check_interval yang hilang, manfaat cache subquery dievaluasi dengan memeriksa apakah cache lebih besar dari apg_subquery_cache_hit_rate_threshold. CHR Jika tidak, cache akan dihapus dari memori, dan eksekusi kueri akan kembali ke eksekusi ulang subquery asli yang tidak di-cache.
Parameter yang mengontrol perilaku cache subquery
Tabel berikut mencantumkan parameter yang mengontrol perilaku cache subquery.
|
Parameter |
Deskripsi |
Default |
Diizinkan |
|---|---|---|---|
apg_enable_subquery_cache |
Mengaktifkan penggunaan cache untuk subquery skalar berkorelasi. |
OFF |
PADA, OFF |
apg_subquery_cache_check_interval |
Menetapkan frekuensi, dalam jumlah kesalahan cache, untuk mengevaluasi tingkat hit cache subquery. |
500 |
0–2147483647 |
apg_subquery_cache_hit_rate_threshold |
Menetapkan ambang batas untuk hit rate cache subquery. |
0,3 |
0,0—1,0 |
catatan
Nilai yang lebih besar
apg_subquery_cache_check_intervaldapat meningkatkan akurasi estimasi manfaat cache CHR berbasis, tetapi akan meningkatkan overhead cache, karena tidak CHR akan dievaluasi hingga tabel cache memiliki baris.apg_subquery_cache_check_intervalNilai
apg_subquery_cache_hit_rate_thresholdbias yang lebih besar untuk meninggalkan cache subquery dan kembali ke eksekusi ulang subquery asli yang tidak di-cache.
Anda dapat memodifikasi kelompok parameter cluster atau instance untuk mengatur parameter. Untuk mempelajari selengkapnya, lihat Bekerja dengan grup parameter.
Atau, Anda dapat mengonfigurasi pengaturan hanya untuk sesi saat ini dengan memanggil fungsiset_config. Misalnya, jalankan perintah berikut untuk mengaktifkan cache subquery di Babelfish. Untuk mempelajari selengkapnya, lihat Fungsi Pengaturan Konfigurasi
1> SELECT set_config('apg_enable_subquery_cache', 'on', false) 2> GO
Mengaktifkan cache subquery di Babelfish
Saat ini, cache subquery secara OFF default. Seperti disebutkan di atas, Anda dapat menyalakannya dengan memodifikasi grup parameter Anda. apg_enable_subquery_cacheKapanON, Babelfish menerapkan cache subquery untuk menyimpan hasil subquery. Rencana kueri kemudian akan memiliki node Memoize di bawah SubPlan.
Misalnya, urutan perintah berikut menunjukkan perkiraan rencana eksekusi kueri dari subquery berkorelasi sederhana tanpa cache subquery. Untuk mempelajari lebih lanjut, lihat Menggunakan rencana penjelasan untuk meningkatkan kinerja kueri Babelfish.
1>CREATE TABLE outer_table (outer_col1 INT, outer_col2 INT)2>CREATE TABLE inner_table (inner_col1 INT, inner_col2 INT)3>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'off'2>GO1>SET BABELFISH_SHOWPLAN_ALL ON2>GO1>SELECT outer_col1, (2>SELECT inner_col13>FROM inner_table4>WHERE inner_col2 = outer_col25>) FROM outer_table6>GOQUERY PLAN ------------------------------------------------------------ Query Text: SELECT outer_col1, ( SELECT inner_col1 FROM inner_table WHERE inner_col2 = outer_col2 ) FROM outer_table Seq Scan on outer_table SubPlan 1 -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
1>SET BABELFISH_SHOWPLAN_ALL OFF2>GO1>EXEC sp_babelfish_configure 'babelfishpg_tsql.explain_costs', 'on'2>GO
Setelah dihidupkanapg_enable_subquery_cache, rencana kueri akan berisi node Memoize di bawah SubPlan node, yang menunjukkan bahwa subquery berencana untuk menggunakan cache.
Seq Scan on outer_table SubPlan 1 -> Memoize Cache Key: outer_table.outer_col2 Cache Mode: logical -> Seq Scan on inner_table Filter: (inner_col2 = outer_table.outer_col2)
Rencana eksekusi kueri yang sebenarnya berisi rincian lebih lanjut dari cache subquery, termasuk klik cache dan kesalahan cache. Output berikut menunjukkan rencana eksekusi query aktual dari contoh query di atas setelah memasukkan beberapa nilai ke tabel.
Seq Scan on outer_table (actual rows=10 loops=1) SubPlan 1 -> Memoize (actual rows=1 loops=10) Cache Key: outer_table.outer_col2 Cache Mode: logical Hits: 4 Misses: 6 Evictions: 0 Overflows: 0 Memory Usage: 1kB -> Seq Scan on inner_table (actual rows=1 loops=6) Filter: (inner_col2 = outer_table.outer_col2) Rows Removed by Filter: 4
Total nomor hit cache adalah 4, dan total nomor cache miss adalah 6. Jika jumlah total hit dan misses kurang dari jumlah loop di node Memoize, itu berarti CHR evaluasi tidak lulus dan cache dibersihkan dan ditinggalkan di beberapa titik. Eksekusi subquery kemudian kembali ke eksekusi ulang asli yang tidak di-cache.
Batasan
Cache subquery tidak mendukung pola tertentu dari subquery yang berkorelasi. Jenis kueri tersebut akan dijalankan tanpa cache, bahkan jika cache subquery diaktifkan:
-
DALAM// EXISTSANY/subkueri ALL berkorelasi
-
Subquery berkorelasi yang berisi fungsi nondeterministik.
-
Subquery berkorelasi yang mereferensikan kolom tabel luarBIT, VARBINARY atau tipe data. BINARY
