Inlining UDF skalar

Berlaku untuk: SQL Server 2019 (15.x) Azure SQL Database Azure SQL Managed Instance Titik akhir analitik SQL di Microsoft Fabric Gudang dalam Microsoft Fabric SQL database dalam Microsoft Fabric

Artikel ini memperkenalkan inlining UDF skalar, sebuah fitur dalam rangkaian fitur pemrosesan kueri cerdas di database SQL. Fitur ini meningkatkan performa kueri yang memanggil UDF skalar di SQL Server 2019 (15.x) dan versi yang lebih baru.

Fungsi skalar yang ditentukan pengguna 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 Anda membangun logika tersebut tanpa memerlukan keahlian dalam menulis kueri SQL. Untuk informasi selengkapnya tentang UDF, lihat Membuat fungsi yang ditentukan pengguna (Mesin Database).

Kinerja UDF skalar

UDF skalar biasanya berkinerja buruk karena alasan berikut:

Pemanggilan berulang. SQL Database Engine memanggil UDF secara berulang, sekali per tuple yang memenuhi syarat. Proses ini menimbulkan biaya tambahan akibat pengalihan konteks berulang yang disebabkan oleh pemanggilan fungsi. UDF yang menjalankan kueri Transact-SQL dalam definisinya sangat terpengaruh.
Kurangnya biaya. Selama pengoptimalan, mesin basis data hanya memperhitungkan biaya operator relasional, sedangkan biaya operator skalar tidak diperhitungkan. 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, tetapi pengoptimal masih kurang merepresentasikannya.
Eksekusi terinterpretasi Mesin basis data mengevaluasi UDF sebagai sekumpulan pernyataan dan menjalankannya satu per satu. Setiap pernyataan dikompilasi, dan rencana yang dikompilasi di-cache. Meskipun strategi penyimpanan cache ini menghemat waktu dengan menghindari kompilasi ulang, setiap pernyataan dieksekusi secara terpisah. Mesin database tidak melakukan pengoptimalan lintas pernyataan.
Eksekusi serial. SQL Server tidak mengizinkan paralelisme intra-query dalam kueri yang memanggil UDF.

Penyisipan sebaris otomatis untuk UDF skalar

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

Dengan menggunakan fitur inlining UDF, mesin database secara otomatis mengubah UDF skalar menjadi ekspresi skalar atau subkueri skalar. Mesin database menggantikan ekspresi atau subkueri ini dalam kueri panggilan sebagai pengganti operator UDF. Pengoptimal kueri kemudian mengoptimalkan ekspresi dan subkueri ini. Akibatnya, rencana kueri tidak lagi memiliki operator fungsi yang ditentukan pengguna, tetapi efeknya dapat Anda amati dalam rencana eksekusi, seperti pada tampilan atau fungsi bernilai tabel inline (TVF).

Inlining otomatis UDF skalar pada Gudang Data Microsoft Fabric

Di Gudang Data Microsoft Fabric, UDF skalar (saat ini dalam pratinjau) secara otomatis diinlin pada waktu kompilasi ketika isi fungsi dan kueri panggilan memenuhi persyaratan untuk inlining. Untuk informasi selengkapnya, lihat CREATE FUNCTION dan Scalar UDF inlining.

Contoh

Contoh di bagian ini menggunakan database tolok ukur TPC-H. Untuk informasi selengkapnya, lihat Beranda TPC-H.

A. 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;

Kueri dengan UDF memiliki kinerja yang buruk karena alasan yang telah diuraikan sebelumnya. Dengan inlining UDF skalar, ekspresi skalar dalam isi UDF digantikan langsung dalam kueri. Hasil menjalankan kueri ini diperlihatkan dalam tabel berikut ini:

Pertanyaan	Kueri tanpa UDF	Kueri dengan UDF (tanpa inlining)	Kueri dengan penyisipan sebaris UDF skalar
`Execution time`	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 tersebut mencakup waktu kompilasi dan eksekusi dengan cache prosedur dan pool buffer dalam keadaan dingin. Konfigurasi default digunakan, dan tidak ada indeks lain yang dibuat.

B. UDF skalar multi-pernyataan

Anda juga dapat membuat UDF skalar menjadi sebaris dengan beberapa pernyataan T-SQL, seperti penugasan variabel dan percabangan bersyarat. Pertimbangkan UDF skalar berikut yang, berdasarkan kunci pelanggan, menentukan kategori layanan untuk pelanggan tersebut. Ini tiba di kategori dengan terlebih dahulu menghitung harga total semua pesanan yang dilakukan oleh pelanggan dengan 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 AS DECIMAL (18, 2);
    DECLARE @category AS 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:

Seperti yang ditunjukkan rencana, SQL Server mengadopsi strategi dasar berikut: untuk setiap tuple dalam CUSTOMER tabel, panggil UDF dan keluarkan hasilnya. Strategi ini naif dan tidak efisien. Dengan menggunakan inlining, Anda dapat mengubah UDF tersebut menjadi subkueri skalar yang setara, yang menggantikan kueri panggilan sebagai pengganti UDF.

Untuk kueri yang sama, rencana dengan UDF yang di-inline adalah sebagai berikut.

Seperti yang disebutkan sebelumnya, rencana kueri tidak lagi memiliki operator fungsi yang ditentukan pengguna, tetapi kini Anda dapat melihat efeknya dalam rencana tersebut, seperti halnya pada view atau TVF inline. Berikut adalah beberapa pengamatan utama dari rencana sebelumnya:

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

Bergantung pada kompleksitas logika dalam UDF, rencana kueri yang dihasilkan mungkin juga menjadi lebih besar dan lebih kompleks. Seperti yang Anda lihat, operasi di dalam UDF sekarang tidak lagi bersifat black box, sehingga pengoptimal kueri dapat memperkirakan biayanya dan mengoptimalkan operasi tersebut. Selain itu, karena UDF tidak lagi menjadi bagian dari rencana eksekusi, pemanggilan UDF secara iteratif digantikan dengan rencana eksekusi yang sepenuhnya menghindari overhead panggilan fungsi.

Persyaratan UDF skalar yang dapat di-inline

T-SQL UDF skalar dapat di-inlin jika definisi fungsi menggunakan konstruksi yang diizinkan, dan fungsi digunakan dalam konteks yang memungkinkan inlining:

Semua kondisi definisi UDF berikut harus benar:

UDF ditulis menggunakan konstruksi berikut:
- DECLARE, SET: Deklarasi variabel dan penugasan.
- SELECT: Kueri SQL dengan penugasan variabel tunggal/jamak ¹.
- IF / ELSE: Percabangan dengan tingkat penyarangan yang tidak terbatas.
- 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.
UDF tidak dikompilasi secara asli (interop didukung).
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 tersebut tidak berisi referensi ke fungsi intrinsik yang dapat mengubah hasil saat disisipkan secara inline (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 yang berisi ORDER BY tanpa klausa TOP 1⁵.
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 ⁶.
UDF tidak mereferensikan STRING_AGG fungsi ⁶.
UDF tidak mereferensikan ^{tabel jarak jauh 7}.
UDF tidak mereferensikan kolom ^{terenkripsi 8}.
UDF tidak berisi referensi ke WITH XMLNAMESPACES⁸.
Jika definisi UDF mencapai ribuan baris kode, SQL Server mungkin memilih untuk tidak menjadikannya inline.

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

² UDF rekursif hanya di-inline-kan hingga kedalaman tertentu.

³ Fungsi intrinsik yang hasilnya bergantung pada waktu sistem saat ini bersifat bergantung pada waktu. Fungsi intrinsik yang mungkin 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) CU 2

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

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

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

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

⁹ Karena tanda tangan dapat ditambahkan dan dihilangkan setelah UDF dibuat, keputusan apakah akan sebaris 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.

Semua persyaratan konteks eksekusi berikut harus benar:

UDF tidak digunakan dalam ORDER BY klausa.
Kueri yang memanggil UDF skalar tidak mereferensikan panggilan UDF skalar dalam klausanya GROUP BY .
Kueri yang memanggil UDF skalar dalam daftar SELECT dengan klausa DISTINCT tidak memiliki klausa ORDER BY.
UDF tidak dipanggil dari pernyataan RETURN ¹.
Kueri yang memanggil UDF tidak memiliki ekspresi tabel umum (CTA) ³.
Kueri panggilan UDF tidak menggunakan GROUPING SETS, , CUBEatau ROLLUP².
Kueri panggilan UDF tidak berisi variabel yang Anda gunakan sebagai parameter UDF untuk penugasan (misalnya, SELECT @y = 2, @x = UDF(@y)) ².
Anda tidak menggunakan UDF dalam kolom komputasi atau definisi batasan pemeriksaan.

¹ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU 5

² Pembatasan ditambahkan di SQL Server 2019 (15.x) CU 6

³ Pembatasan ditambahkan di SQL Server 2019 (15.x) CU 11

Untuk informasi tentang perbaikan terbaru untuk inlining UDF skalar T-SQL dan perubahan pada skenario kelayakan untuk inlining, lihat artikel Basis Pengetahuan: Perbaikan: masalah inlining UDF skalar di SQL Server 2019.

Periksa apakah UDF dapat di-inline

Untuk setiap UDF skalar T-SQL, tampilan katalog sys.sql_modules menyertakan properti bernama is_inlineable, yang menunjukkan apakah UDF dapat dijadikan inline.

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 UDF tidak sebaris, dan 0 menunjukkan sebaliknya. Properti ini memiliki nilai 1 untuk semua TVF inline juga. Untuk semua modul lainnya, nilainya adalah 0.

Jika UDF skalar dapat di-inline, itu tidak berarti bahwa fungsi tersebut selalu di-inline. SQL Server memutuskan (untuk setiap kueri dan setiap UDF) apakah akan menyisipkan UDF secara inline. Lihat daftar persyaratan sebelumnya di artikel ini.

SELECT b.name,
       b.type_desc,
       a.is_inlineable
FROM sys.sql_modules AS a
     INNER JOIN sys.objects AS b
         ON a.object_id = b.object_id
WHERE b.type IN ('IF', 'TF', 'FN');

Periksa apakah penyisipan sebaris sudah dilakukan

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

XML rencana tidak memiliki node XML <UserDefinedFunction> untuk UDF yang berhasil di-inline.
Peristiwa Tertentu yang Diperluas dipancarkan.

Mengaktifkan inlining UDF skalar

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

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Terlepas dari langkah ini, tidak ada perubahan lain yang diperlukan untuk dilakukan pada UDF atau kueri untuk memanfaatkan fitur ini.

Menonaktifkan inlining UDF skalar tanpa mengubah tingkat kompatibilitas

Anda dapat menonaktifkan inlining skalar UDF pada tingkat database, pernyataan, atau UDF, namun tetap mempertahankan tingkat kompatibilitas database 150 atau yang lebih tinggi. Untuk menonaktifkan inlining UDF skalar pada cakupan database, jalankan pernyataan berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Untuk mengaktifkan kembali inlining UDF skalar untuk database, jalankan pernyataan berikut dalam konteks database yang berlaku:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Saat Anda mengatur opsi ini ke ON, opsi ini muncul sebagai diaktifkan di sys.database_scoped_configurations.

Anda juga dapat menonaktifkan inlining UDF skalar untuk kueri tertentu dengan menetapkan DISABLE_TSQL_SCALAR_UDF_INLINING sebagai petunjuk kueri USE HINT.

Petunjuk kueri USE HINT didahulukan daripada pengaturan konfigurasi cakupan 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'));

Anda juga dapat menonaktifkan inlining UDF skalar untuk UDF tertentu dengan menggunakan klausa INLINE dalam pernyataan CREATE FUNCTION 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 Anda menjalankan pernyataan sebelumnya, UDF ini tidak akan pernah disisipkan secara inline 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 Anda tidak menentukan klausul INLINE, klausul tersebut secara otomatis disetel ke ON atau OFF, bergantung pada apakah UDF dapat di-inline-kan. Jika Anda menentukan INLINE = ON tetapi UDF ditemukan tidak memenuhi syarat untuk inlining, kesalahan akan muncul.

Keterangan

Seperti yang dijelaskan dalam artikel ini, scalar UDF inlining mentransformasi kueri yang berisi UDF skalar menjadi kueri dengan subkueri skalar ekuivalen. Karena transformasi ini, Anda mungkin melihat beberapa perbedaan perilaku dalam skenario berikut:

Inlining menghasilkan hash kueri yang berbeda untuk teks kueri yang sama.
Peringatan tertentu dalam pernyataan di dalam UDF (seperti pembagian dengan nol) yang sebelumnya mungkin tersembunyi dapat muncul karena inlining.
Petunjuk gabungan tingkat kueri mungkin tidak valid lagi, karena inlining dapat memperkenalkan gabungan baru. Anda harus menggunakan petunjuk join lokal sebagai gantinya.
Anda tidak dapat mengindeks tampilan yang mereferensikan UDF skalar sebaris. Jika Anda perlu membuat indeks pada tampilan tersebut, nonaktifkan inlining untuk UDF yang dirujuk.
Mungkin ada beberapa perbedaan pada perilaku Dynamic data masking dengan UDF inlining.

Dalam situasi tertentu (tergantung pada logika dalam UDF), inlining dapat bersifat lebih konservatif dalam hal pemaskeran kolom output. Dalam skenario di mana kolom yang direferensikan dalam UDF bukan kolom output, kolom tersebut tidak ditutupi.
Jika UDF merujuk ke fungsi bawaan seperti SCOPE_IDENTITY(), @@ROWCOUNT, atau @@ERROR, nilai yang dikembalikan oleh fungsi bawaan berubah saat inlining diterapkan. 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 Anda menetapkan variabel dengan hasil UDF yang di-inline dan juga menggunakannya sebagai index_column_name dalam FORCESEEKPetunjuk kueri (Transact-SQL), hal ini akan menyebabkan error 8622. Kesalahan ini menunjukkan bahwa prosesor kueri tidak dapat menghasilkan rencana kueri karena petunjuk yang ditentukan dalam kueri.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2026-06-30