Inlining UDF skalar

Artikel
09/24/2024

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

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

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 inlining UDF skalar 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 lagi memiliki operator fungsi yang ditentukan pengguna, tetapi efeknya diamati dalam paket, seperti tampilan atau TVF sebaris.

Contoh

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

J. 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 berkinerja buruk, karena alasan yang diuraikan sebelumnya. Dengan inlining UDF skalar, ekspresi skalar dalam isi UDF digantikan langsung dalam kueri. Hasil menjalankan kueri ini diperlihatkan dalam tabel berikut ini:

Kueri:	Kueri tanpa UDF	Kueri dengan UDF (tanpa inlining)	Kueri dengan inlining UDF skalar
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.

B. 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 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 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, rencana dengan UDF bergaris terlihat sebagai berikut.

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 rencana sebelumnya:

SQL Server menyimpulkan gabungan implisit antara CUSTOMER dan ORDERS membuat itu 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 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 mungkin 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 disebarkan ke kedalaman tertentu.

³ Fungsi intrinsik yang hasilnya bergantung pada waktu sistem saat ini 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

Untuk informasi tentang perbaikan inlining UDF skalar T-SQL terbaru dan perubahan pada skenario kelayakan yang menginlining, lihat artikel Pangkalan Pengetahuan: MEMPERBAIKI: masalah inlining UDF skalar 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 juga memiliki nilai 1 untuk semua TVF sebaris. Untuk semua modul lainnya, nilainya adalah 0.

Jika UDF skalar tidak sebaris, itu tidak menyiratkan bahwa itu selalu sebaris. SQL Server 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 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 mencari tahu apakah inlining telah terjadi:

XML paket tidak memiliki <UserDefinedFunction> simpul XML untuk UDF yang berhasil di-inlin.
XEvent tertentu 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

Inlining UDF skalar dapat dinonaktifkan di database, pernyataan, atau cakupan UDF sambil tetap mempertahankan tingkat kompatibilitas database 150 dan 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;

Ketika ON, pengaturan ini muncul sebagai diaktifkan di sys.database_scoped_configurations.

Anda juga dapat menonaktifkan inlining UDF skalar 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'));

Inlining UDF skalar 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 sebelumnya dijalankan, UDF ini tidak 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. INLINE Jika klausul tidak ditentukan, klausa 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 muncul.

Keterangan

Seperti yang dijelaskan dalam artikel ini, skalar UDF inlining mengubah kueri dengan UDF skalar menjadi kueri dengan subkueri skalar yang setara. 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 dibagi dengan nol, dll.) yang mungkin sebelumnya disembunyikan, dapat muncul karena inlining.
Petunjuk gabungan tingkat kueri mungkin tidak valid lagi, karena inlining dapat 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 ditutupi.
Jika UDF mereferensikan fungsi bawaan seperti SCOPE_IDENTITY(), , @@ROWCOUNTatau @@ERROR, nilai yang dikembalikan oleh fungsi bawaan 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 menghasilkan kesalahan Msg 8622 yang menunjukkan bahwa prosesor Kueri tidak dapat menghasilkan rencana kueri karena petunjuk yang ditentukan dalam kueri.

Bagikan melalui

Inlining UDF skalar

Fungsi yang ditentukan pengguna skalar T-SQL

Performa UDF skalar

Inlining otomatis UDF skalar

Contoh

J. Pernyataan tunggal skalar UDF

B. UDF skalar multi-pernyataan

Persyaratan UDF skalar yang tidak sebaris

Periksa apakah UDF dapat di-inlin atau tidak

Periksa apakah inlining telah terjadi atau tidak

Mengaktifkan inlining UDF skalar

Menonaktifkan inlining UDF skalar tanpa mengubah tingkat kompatibilitas

Keterangan

Saran dan Komentar

Sumber Daya Tambahan:

Bagikan melalui

Inlining UDF skalar

Fungsi yang ditentukan pengguna skalar T-SQL

Performa UDF skalar

Inlining otomatis UDF skalar

Contoh

J. Pernyataan tunggal skalar UDF

B. UDF skalar multi-pernyataan

Persyaratan UDF skalar yang tidak sebaris

Periksa apakah UDF dapat di-inlin atau tidak

Periksa apakah inlining telah terjadi atau tidak

Mengaktifkan inlining UDF skalar

Menonaktifkan inlining UDF skalar tanpa mengubah tingkat kompatibilitas

Keterangan

Konten terkait

Saran dan Komentar

Sumber Daya Tambahan: