Skalar UDF Inlining

Artikel
05/30/2023

Berlaku untuk: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance

Artikel ini memperkenalkan Scalar UDF Inlining, fitur di bawah rangkaian fitur Pemrosesan Kueri Cerdas. Fitur ini meningkatkan performa kueri yang memanggil UDF skalar di SQL Server (dimulai dengan SQL Server 2019 (15.x)).

Fungsi yang ditentukan pengguna skalar T-SQL

Fungsi yang Ditentukan Pengguna (UDF) yang diimplementasikan dalam Transact-SQL dan mengembalikan satu nilai data disebut sebagai Fungsi yang Ditentukan Pengguna Skalar T-SQL. T-SQL UDF adalah cara elegan untuk mencapai penggunaan kembali kode dan modularitas di seluruh kueri Transact-SQL. Beberapa komputasi (seperti aturan bisnis yang kompleks) lebih mudah diekspresikan dalam bentuk UDF imperatif. UDF membantu dalam membangun logika yang kompleks tanpa memerlukan keahlian dalam menulis kueri SQL yang kompleks. Untuk informasi selengkapnya tentang UDF, lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).

Performa UDF skalar

UDF skalar biasanya berkinerja buruk karena alasan berikut:

Pemanggilan berulang. UDF dipanggil dengan cara berulang, sekali per tuple yang memenuhi syarat. Ini menimbulkan biaya tambahan pengalihan konteks berulang karena pemanggilan fungsi. Terutama, UDF yang menjalankan kueri Transact-SQL dalam definisinya sangat terpengaruh.
Kurangnya biaya. Selama pengoptimalan, hanya operator relasional yang dikenakan biaya, sementara operator skalar tidak. Sebelum pengenalan UDF skalar, operator skalar lainnya umumnya murah dan tidak memerlukan biaya. Biaya CPU kecil yang ditambahkan untuk operasi skalar sudah cukup. Ada skenario di mana biaya aktual signifikan, namun masih tetap kurang terwajibkan.
Ditafsirkan eksekusi. UDF dievaluasi sebagai batch pernyataan, dieksekusi pernyataan demi pernyataan. Setiap pernyataan itu sendiri dikompilasi, dan rencana yang dikompilasi di-cache. Meskipun strategi penembolokan ini menghemat waktu karena menghindari kompilasi ulang, setiap pernyataan dijalankan dalam isolasi. Tidak ada pengoptimalan lintas pernyataan yang dilakukan.
Eksekusi serial. SQL Server tidak mengizinkan paralelisme intra-query dalam kueri yang memanggil UDF.

Inlining otomatis UDF skalar

Tujuan dari fitur Scalar UDF Inlining adalah untuk meningkatkan performa kueri yang memanggil UDF skalar T-SQL, di mana eksekusi UDF adalah hambatan utama.

Dengan fitur baru ini, UDF skalar secara otomatis diubah menjadi ekspresi skalar atau subkueri skalar yang digantikan dalam kueri panggilan sebagai pengganti operator UDF. Ekspresi dan subkueri ini kemudian dioptimalkan. Akibatnya, rencana kueri tidak akan lagi memiliki operator fungsi yang ditentukan pengguna, tetapi efeknya akan diamati dalam rencana, seperti tampilan atau TVF sebaris.

Contoh 1 - pernyataan tunggal skalar UDF

Pertimbangkan kueri berikut.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT))
FROM LINEITEM
INNER JOIN ORDERS
  ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE;

Kueri ini menghitung jumlah harga diskon untuk item baris dan menyajikan hasil yang dikelompokkan berdasarkan tanggal pengiriman dan prioritas pengiriman. Ekspresi L_EXTENDEDPRICE *(1 - L_DISCOUNT) adalah rumus untuk harga diskon untuk item baris tertentu. Rumus tersebut dapat diekstrak ke dalam fungsi untuk kepentingan modularitas dan penggunaan kembali.

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2) AS
BEGIN
  RETURN @price * (1 - @discount);
END

Sekarang kueri dapat dimodifikasi untuk memanggil UDF ini.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
INNER JOIN ORDERS
  ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

Karena alasan yang diuraikan sebelumnya, kueri dengan UDF berkinerja buruk. Sekarang, dengan Scalar UDF Inlining, ekspresi skalar dalam isi UDF digantikan langsung dalam kueri. Hasil menjalankan kueri ini diperlihatkan dalam tabel di bawah ini:

Kueri:	Kueri tanpa UDF	Kueri dengan UDF (tanpa inlining)	Kueri dengan Scalar UDF Inlining
Waktu eksekusi:	1,6 detik	29 menit 11 detik	1,6 detik

Angka-angka ini didasarkan pada database CCI 10 GB (menggunakan skema TPC-H), berjalan pada mesin dengan prosesor ganda (12 inti), RAM 96 GB, didukung oleh SSD. Angka-angka tersebut termasuk kompilasi dan waktu eksekusi dengan cache prosedur dingin dan kumpulan buffer. Konfigurasi default digunakan, dan tidak ada indeks lain yang dibuat.

Contoh 2 - UDF skalar multi-pernyataan

UDF skalar yang diimplementasikan menggunakan beberapa pernyataan T-SQL seperti penetapan variabel dan pencabangan bersyarat juga dapat di-inlin. Pertimbangkan UDF skalar berikut yang, mengingat kunci pelanggan, menentukan kategori layanan untuk pelanggan tersebut. Ini tiba di kategori dengan terlebih dahulu menghitung harga total semua pesanan yang dilakukan oleh pelanggan menggunakan kueri SQL. Kemudian, menggunakan IF (...) ELSE logika untuk memutuskan kategori berdasarkan harga total.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)
RETURNS CHAR(10) AS
BEGIN
  DECLARE @total_price DECIMAL(18,2);
  DECLARE @category CHAR(10);

  SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

  IF @total_price < 500000
    SET @category = 'REGULAR';
  ELSE IF @total_price < 1000000
    SET @category = 'GOLD';
  ELSE
    SET @category = 'PLATINUM';

  RETURN @category;
END

Sekarang, pertimbangkan kueri yang memanggil UDF ini.

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

Rencana eksekusi untuk kueri ini di SQL Server 2017 (14.x) (tingkat kompatibilitas 140 dan yang lebih lama) adalah sebagai berikut:

Query Plan without inlining.

Seperti yang ditunjukkan oleh rencana, SQL Server mengadopsi strategi sederhana di sini: untuk setiap tuple dalam CUSTOMER tabel, panggil UDF dan keluarkan hasilnya. Strategi ini naif dan tidak efisien. Dengan inlining, UDF tersebut diubah menjadi subkueri skalar yang setara, yang digantikan dalam kueri panggilan sebagai pengganti UDF.

Untuk kueri yang sama, paket dengan UDF bergaris terlihat seperti di bawah ini.

Query Plan with inlining.

Seperti disebutkan sebelumnya, rencana kueri tidak lagi memiliki operator fungsi yang ditentukan pengguna, tetapi efeknya sekarang dapat diamati dalam paket, seperti tampilan atau TVF sebaris. Berikut adalah beberapa pengamatan utama dari paket di atas:

SQL Server telah menyimpulkan gabungan implisit antara CUSTOMER dan ORDERS membuat yang eksplisit melalui operator gabungan.
SQL Server juga telah menyimpulkan implisit GROUP BY O_CUSTKEY on ORDERS dan telah menggunakan IndexSpool + StreamAggregate untuk mengimplementasikannya.
SQL Server sekarang menggunakan paralelisme di semua operator.

Bergantung pada kompleksitas logika di UDF, rencana kueri yang dihasilkan mungkin juga menjadi lebih besar dan lebih kompleks. Seperti yang kita lihat, operasi di dalam UDF sekarang tidak lagi buram, sehingga pengoptimal kueri dapat menelan biaya dan mengoptimalkan operasi tersebut. Selain itu, karena UDF tidak lagi dalam rencana, pemanggilan UDF berulang digantikan oleh rencana yang sepenuhnya menghindari overhead panggilan fungsi.

Persyaratan UDF skalar yang tidak sebaris

UDF T-SQL skalar dapat di-inlin jika semua kondisi berikut ini benar:

UDF ditulis menggunakan konstruksi berikut:
- DECLARE, SET: Deklarasi variabel dan penugasan.
- SELECT: Kueri SQL dengan penetapan ^{variabel tunggal/beberapa 1}.
- IF/ELSE: Bercabang dengan tingkat bersarang sewenang-wenang.
- RETURN: Pernyataan pengembalian tunggal atau beberapa. Dimulai dengan SQL Server 2019 (15.x) CU5, UDF hanya dapat berisi satu pernyataan RETURN yang akan dipertimbangkan untuk inlining ⁶.
- UDF: Fungsi berlapis/rekursif memanggil ².
- Lainnya: Operasi relasional seperti EXISTS, IS NULL.
UDF tidak memanggil fungsi intrinsik apa pun yang bergantung pada waktu (seperti GETDATE()) atau memiliki efek ^{samping 3} (seperti NEWSEQUENTIALID()).
UDF menggunakan klausul EXECUTE AS CALLER (perilaku default jika EXECUTE AS klausul tidak ditentukan).
UDF tidak mereferensikan variabel tabel atau parameter bernilai tabel.
Kueri yang memanggil UDF skalar tidak mereferensikan panggilan UDF skalar dalam klausanya GROUP BY .
Kueri yang memanggil UDF skalar dalam daftar pemilihannya dengan DISTINCT klausa tidak memiliki ORDER BY klausa.
UDF tidak digunakan dalam ORDER BY klausa.
UDF tidak dikompilasi secara asli (interop didukung).
UDF tidak digunakan dalam kolom komputasi atau definisi batasan pemeriksaan.
UDF tidak mereferensikan jenis yang ditentukan pengguna.
Tidak ada tanda tangan yang ditambahkan ke UDF.
UDF bukan fungsi partisi.
UDF tidak berisi referensi ke Common Table Expressions (CTEs).
UDF tidak berisi referensi ke fungsi intrinsik yang dapat mengubah hasil ketika inlined (seperti @@ROWCOUNT) ⁴.
UDF tidak berisi fungsi agregat yang diteruskan sebagai parameter ke skalar UDF ⁴.
UDF tidak mereferensikan tampilan bawaan (seperti OBJECT_ID) ⁴.
UDF tidak mereferensikan ^{metode XML 5}.
UDF tidak berisi SELECT dengan ORDER BY tanpa TOP 1 klausul ⁵.
UDF tidak berisi kueri SELECT yang melakukan penugasan dengan ORDER BY klausa (seperti SELECT @x = @x + 1 FROM table1 ORDER BY col1) ⁵.
UDF tidak berisi beberapa pernyataan ^{RETURN 6}.
UDF tidak dipanggil dari pernyataan ^{RETURN 6}.
UDF tidak mereferensikan STRING_AGG fungsi ⁶.
UDF tidak mereferensikan ^{tabel jarak jauh 7}.
Kueri panggilan UDF tidak menggunakan GROUPING SETS, , CUBEatau ROLLUP⁷.
Kueri panggilan UDF tidak berisi variabel yang digunakan sebagai parameter UDF untuk penugasan (misalnya, SELECT @y = 2, @x = UDF(@y)) ⁷.
UDF tidak mereferensikan kolom ^{terenkripsi 8}.
UDF tidak berisi referensi ke WITH XMLNAMESPACES⁸.
Kueri yang memanggil UDF tidak memiliki Common Table Expressions (CTEs) ⁸.

¹SELECT dengan akumulasi/agregasi variabel tidak didukung untuk inlining (seperti SELECT @val += col1 FROM table1).

² UDF rekursif hanya akan disebarkan ke kedalaman tertentu.

³ Fungsi intrinsik yang hasilnya bergantung pada waktu sistem saat ini bergantung pada waktu. Fungsi intrinsik yang dapat memperbarui beberapa status global internal adalah contoh fungsi dengan efek samping. Fungsi tersebut mengembalikan hasil yang berbeda setiap kali dipanggil, berdasarkan status internal.

⁴ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU2

⁵ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU4

⁶ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU5

⁷ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU6

⁸ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU11

Untuk informasi tentang perbaikan T-SQL Scalar UDF Inlining terbaru dan perubahan pada skenario kelayakan yang menginlining, lihat artikel Pangkalan Pengetahuan: PERBAIKAN: Masalah Scalar UDF Inlining di SQL Server 2019.

Periksa apakah UDF dapat di-inlin atau tidak

Untuk setiap UDF skalar T-SQL, tampilan katalog sys.sql_modules menyertakan properti yang disebut is_inlineable, yang menunjukkan apakah UDF tidak sebaris atau tidak.

Properti is_inlineable berasal dari konstruksi yang ditemukan di dalam definisi UDF. Ini tidak memeriksa apakah UDF sebenarnya sebaris pada waktu kompilasi. Untuk informasi selengkapnya, lihat kondisi untuk inlining.

Nilai 1 menunjukkan bahwa nilai tersebut sebaris, dan 0 menunjukkan sebaliknya. Properti ini akan memiliki nilai 1 untuk semua TVF sebaris juga. Untuk semua modul lainnya, nilainya adalah 0.

Jika UDF skalar tidak sebaris, itu tidak menyiratkan bahwa itu akan selalu di-inlin. SQL Server akan memutuskan (berdasarkan per kueri, per-UDF) apakah akan menginline UDF atau tidak. Beberapa contoh kapan UDF mungkin tidak sebaris meliputi:

Jika definisi UDF berjalan ke ribuan baris kode, SQL Server mungkin memilih untuk tidak meng-sebariskannya.
Pemanggilan UDF dalam GROUP BY klausul tidak akan sebaris. Keputusan ini dibuat ketika kueri yang mereferensikan UDF skalar dikompilasi.
Jika UDF ditandatangani dengan sertifikat. Karena tanda tangan dapat ditambahkan dan dihilangkan setelah UDF dibuat, keputusan apakah akan sebaris atau tidak dilakukan ketika kueri yang merujuk UDF skalar dikompilasi. Misalnya, fungsi sistem biasanya ditandatangani dengan sertifikat. Anda dapat menggunakan sys.crypt_properties untuk menemukan objek mana yang ditandatangani.
```
SELECT *
FROM sys.crypt_properties AS cp
INNER JOIN sys.objects AS o ON cp.major_id = o.object_id;
```

Periksa apakah inlining telah terjadi atau tidak

Jika semua prasyarat terpenuhi dan SQL Server memutuskan untuk melakukan inlining, itu mengubah UDF menjadi ekspresi relasional. Dari rencana kueri, mudah untuk mengetahui apakah inlining telah terjadi atau tidak:

Xml paket tidak akan memiliki <UserDefinedFunction> simpul xml untuk UDF yang telah berhasil di-inlin.
XEvent tertentu dipancarkan.

Mengaktifkan Scalar UDF Inlining

Anda dapat membuat beban kerja memenuhi syarat secara otomatis untuk Scalar UDF Inlining dengan mengaktifkan tingkat kompatibilitas 150 untuk database. Anda dapat mengatur ini menggunakan Transact-SQL. Contohnya:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 150;

Selain itu, tidak ada perubahan lain yang perlu dilakukan pada UDF atau kueri untuk memanfaatkan fitur ini.

Nonaktifkan Scalar UDF Inlining tanpa mengubah tingkat kompatibilitas

Skalar UDF Inlining dapat dinonaktifkan di database, pernyataan, atau cakupan UDF sambil tetap mempertahankan tingkat kompatibilitas database 150 dan yang lebih tinggi. Untuk menonaktifkan Scalar UDF Inlining pada cakupan database, jalankan pernyataan berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Untuk mengaktifkan kembali Scalar UDF Inlining untuk database, jalankan pernyataan berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Ketika AKTIF, pengaturan ini akan muncul sebagai diaktifkan di sys.database_scoped_configurations. Anda juga dapat menonaktifkan Scalar UDF Inlining untuk kueri tertentu dengan menunjuk DISABLE_TSQL_SCALAR_UDF_INLINING sebagai USE HINT petunjuk kueri.

USE HINT Petunjuk kueri lebih diutamakan daripada pengaturan konfigurasi tercakup database atau tingkat kompatibilitas.

Contohnya:

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
INNER JOIN ORDERS
  ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

Scalar UDF Inlining juga dapat dinonaktifkan untuk UDF tertentu menggunakan klausa INLINE dalam CREATE FUNCTION pernyataan atau ALTER FUNCTION . Contohnya:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END;

Setelah pernyataan di atas dijalankan, UDF ini tidak akan pernah diinlin ke dalam kueri apa pun yang memanggilnya. Untuk mengaktifkan kembali inlining untuk UDF ini, jalankan pernyataan berikut:

CREATE OR ALTER FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))
RETURNS DECIMAL (12,2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Klausul INLINE ini tidak wajib. Jika INLINE klausul tidak ditentukan, klausul tersebut secara otomatis diatur ke ON/OFF berdasarkan apakah UDF dapat di-inlin. Jika INLINE = ON ditentukan tetapi UDF ditemukan tidak memenuhi syarat untuk inlining, kesalahan akan dilemparkan.

Catatan penting

Seperti yang dijelaskan dalam artikel ini, Scalar UDF Inlining mengubah kueri dengan UDF skalar menjadi kueri dengan subkueri skalar yang setara. Karena transformasi ini, pengguna mungkin melihat beberapa perbedaan perilaku dalam skenario berikut:

Inlining akan menghasilkan hash kueri yang berbeda untuk teks kueri yang sama.
Peringatan tertentu dalam pernyataan di dalam UDF (seperti dibagi dengan nol dll.) yang mungkin telah disembunyikan sebelumnya, mungkin muncul karena inlining.
Petunjuk gabungan tingkat kueri mungkin tidak valid lagi, karena inlining mungkin memperkenalkan gabungan baru. Petunjuk gabungan lokal harus digunakan sebagai gantinya.
Tampilan yang mereferensikan UDF skalar sebaris tidak dapat diindeks. Jika Anda perlu membuat indeks pada tampilan tersebut, nonaktifkan inlining untuk UDF yang dirujuk.
Mungkin ada beberapa perbedaan dalam perilaku masking Data Dinamis dengan UDF inlining.

Dalam situasi tertentu (tergantung pada logika di UDF), inlining mungkin lebih konservatif sehubungan dengan kolom output masking. Dalam skenario di mana kolom yang direferensikan dalam UDF bukan kolom output, kolom tersebut tidak akan diselubungi.
Jika UDF mereferensikan fungsi bawaan seperti SCOPE_IDENTITY(), , @@ROWCOUNTatau @@ERROR, nilai yang dikembalikan oleh fungsi bawaan akan berubah dengan inlining. Perubahan perilaku ini karena inlining mengubah cakupan pernyataan di dalam UDF. Dimulai dengan SQL Server 2019 (15.x) CU2, inlining diblokir jika UDF mereferensikan fungsi intrinsik tertentu (misalnya @@ROWCOUNT).
Jika variabel ditetapkan dengan hasil UDF sebaris dan juga digunakan sebagai index_column_name dalam petunjuk Kueri FORCESEEK, itu akan mengakibatkan kesalahan Msg 8622 yang menunjukkan bahwa prosesor Kueri tidak dapat menghasilkan rencana kueri karena petunjuk yang ditentukan dalam kueri.