Skaler UDF satır içi yerleştirme

Şunlar için geçerlidir: SQL Server 2019 (15.x) Azure SQL VeritabanıAzure SQL Yönetilen ÖrnekMicrosoft Fabric'te SQL analiz uç noktasıMicrosoft Fabric'te AmbarMicrosoft Fabric'te SQL veritabanı

Bu makalede, SQL veritabanlarında akıllı sorgu işleme özellik paketinin altında yer alan skaler UDF inlining özelliği sunulmaktadır. Bu özellik, SQL Server 2019 (15.x) ve sonraki sürümlerde skaler UDF'leri çağıran sorguların performansını artırır.

T-SQL skaler kullanıcı tanımlı işlevler

Transact-SQL uygulanan ve tek bir veri değeri döndüren User-Defined İşlevleri (UDF), T-SQL Skaler User-Defined İşlevleri olarak adlandırılır. T-SQL UDF'leri, Transact-SQL sorgularda kod yeniden kullanımı ve modülerliği elde etmenin zarif bir yoludur. Bazı hesaplamaların (karmaşık iş kuralları gibi) kesinlik temelli UDF biçiminde ifade edilmesi daha kolaydır. UDF'ler karmaşık SQL sorguları yazma konusunda uzmanlığa gerek kalmadan karmaşık mantık oluşturmaya yardımcı olur. UDF'ler hakkında daha fazla bilgi için bkz. kullanıcı tanımlı işlevler oluşturma (Veritabanı Altyapısı) .

Skaler UDF'lerin performansı

Skaler UDF'ler genellikle aşağıdaki nedenlerden dolayı düşük performans gösterir:

• Yinelemeli çağrı. UDF'ler, uygun düşen tuple başına bir kez tekrarlı bir şekilde çağrılır. Bu, işlev çağrısı nedeniyle yinelenen bağlam değiştirmenin ek bir maliyetine neden olur. Özellikle, tanımlarında Transact-SQL sorguları yürüten UDF'ler ciddi şekilde etkilenir.

• Maliyet eksikliği. İyileştirme sırasında yalnızca ilişkisel işleçler maliyete mal olurken skaler işleçler maliyete mal olmaz. Skaler UDF'lerin kullanıma sunulmasından önce, diğer skaler işleçler genellikle ucuzdu ve maliyet gerektirmedi. Skaler işlem için eklenen küçük bir CPU maliyeti yeterliydi. Gerçek maliyetin önemli olduğu ve yine de az temsil edildiği senaryolar vardır.

• Yorumlanan yürütme. UDF'ler, bir dizi deyim olarak değerlendirilir ve her deyim tek tek yürütülür. Her bir deyim derlenir ve derlenen plan önbelleğe alınır. Bu önbelleğe alma stratejisi, yeniden derlemeleri önleyerek biraz zaman kazanmanızı sağlasa da, her deyim izole bir şekilde yürütülür. Çapraz ifade optimizasyonları yapılmaz.

• Seri yürütme. SQL Server, UDF'leri çağıran sorgularda sorgu içi paralelliğe izin vermez.

Skaler UDF'lerin otomatik olarak satıriçi yerleştirilmesi

Skaler UDF inlining özelliğinin amacı, UDF yürütmesinin ana performans sorunu olduğu T-SQL skaler UDF'leri çağıran sorguların performansını geliştirmektir.

Bu yeni özellik sayesinde skaler UDF'ler otomatik olarak UDF işleci yerine çağrı sorgusunda değiştirilen skaler ifadelere veya skaler alt sorgulara dönüştürülür. Bu ifadeler ve alt sorgular daha sonra iyileştirilmiştir. Sonuç olarak, sorgu planının artık kullanıcı tanımlı bir işlev işleci yoktur, ancak görünümler veya satır içi tablo değerli işlevler (TVF'ler) gibi etkileri planda gözlemlenir.

Microsoft Fabric Veri Ambarı'nda skaler UDF'lerin otomatik olarak çizilmesi

Microsoft Fabric Veri Ambarı'nda skaler UDF'ler (şu anda önizlemede), işlev gövdesi ve çağrı sorgusu, satır içine alma gereksinimlerini karşıladığında derleme zamanında otomatik olarak satır içine alınır. Daha fazla bilgi için bkz. CREATE FUNCTION ve Scalar UDF inlining.

Örnekler

Bu bölümdeki örneklerde TPC-H karşılaştırma veritabanı kullanılır. Daha fazla bilgi için bkz. TPC-H Giriş sayfası.

A. Tek ifadeli skaler UDF

Aşağıdaki sorguyu göz önünde bulundurun.

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;

Bu sorgu, satır öğeleri için indirimli fiyatların toplamını hesaplar ve sonuçları sevkiyat tarihine ve sevkiyat önceliğine göre gruplandırılmış olarak sunar. L_EXTENDEDPRICE *(1 - L_DISCOUNT) ifadesi, belirli bir satır öğesi için indirimli fiyatın formülüdür. Bu tür formüller, modülerlik ve yeniden kullanım için fonksiyonlara ayrılabilir.

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

Artık sorgu, bu UDF'yi çağırmak için değiştirilebilir.

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;

UDF ile sorgu, daha önce açıklanan nedenlerden dolayı kötü performans gösterir. Skaler UDF satır içi tekniği ile, UDF'nin gövdesindeki skaler ifade doğrudan sorguya yerleştirilir. Bu sorguyu çalıştırmanın sonuçları aşağıdaki tabloda gösterilmiştir:

Sorgu:	UDF olmadan sorgulama	UDF ile sorgulama (inlining olmadan)	Skaler UDF inlining ile sorgulama
Execution time:	1,6 saniye	29 dakika 11 saniye	1,6 saniye

Bu sayılar, SSD destekli çift işlemcili (12 çekirdekli), 96 GB RAM'e sahip bir makinede çalışan 10 GB CCI veritabanını (TPC-H şemasını kullanarak) temel alır. Sayılar, soğuk yordam önbelleği ve arabellek havuzu kullanılarak derleme ve yürütme zamanını içerir. Varsayılan yapılandırma kullanıldı ve başka dizin oluşturulmadı.

B. Çok ifadeli ölçekli UDF

Değişken atamaları ve koşullu dallanma gibi birden çok T-SQL deyimi kullanılarak uygulanan Skaler UDF'ler de satıriçi hale getirilebilir. Müşteri anahtarı verilip ilgili müşterinin hizmet kategorisini belirleyen aşağıdaki skaler UDF'yi göz önünde bulundurun. İlk olarak bir SQL sorgusu kullanarak müşteri tarafından verilen tüm siparişlerin toplam fiyatını hesaplayarak kategoriye ulaşır. Ardından, kategoriye toplam fiyat temelinde karar vermek için bir IF (...) ELSE mantığı kullanır.

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

Şimdi bu UDF'yi çağıran bir sorgu düşünün.

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

SQL Server 2017'de (14.x) (uyumluluk düzeyi 140 ve öncesi) bu sorgunun yürütme planı aşağıdaki gibidir:

Planda gösterildiği gibi, SQL Server burada basit bir strateji benimser: CUSTOMER tablosundaki her tanımlama grubu için UDF'yi çalıştırın ve sonuçları döndürün. Bu strateji toy ve verimsizdir. İç içe geçirme ile, bu tür UDF'ler eşdeğer skaler alt sorgulara dönüştürülerek, çağrı sorgusunda UDF'in yerine konur.

UDF'nin satır içi olduğu plan, aynı sorgu için aşağıdaki gibi görünür.

Daha önce belirtildiği gibi, sorgu planının artık kullanıcı tanımlı bir işlev işleci yoktur, ancak görünümleri veya satır içi TVF'ler gibi etkileri artık planda gözlemlenebilir. Önceki plandaki bazı önemli gözlemler şunlardır:

• SQL Server, CUSTOMER ile ORDERS arasındaki örtük birleştirmeyi çıkarsar ve bir join işleci aracılığıyla açık hale getirir.

• SQL Server ayrıca örtük GROUP BY O_CUSTKEY on ORDERS'ı çıkarır ve bunu uygulamak üzere IndexSpool + StreamAggregate kullanır.

• SQL Server artık tüm işleçler arasında paralellik kullanıyor.

UDF'deki mantığın karmaşıklığını bağlı olarak, sonuçta elde edilen sorgu planı da daha büyük ve daha karmaşık hale gelebilir. Gördüğümüz gibi, UDF'nin içindeki işlemler artık opak değildir ve bu nedenle sorgu iyileştiricisi bu işlemlerin maliyetini ve iyileştirmesini sağlayabilir. Ayrıca, UDF artık planda olmadığından yinelemeli UDF çağrısı, işlev çağrısı ek yükünü tamamen önleyen bir planla değiştirilir.

Satır içi hale getirilebilir skaler UDF gereksinimleri

İşlev tanımı izin verilen yapıları kullanıyorsa ve işlev, satır içi hale getirmeyi etkinleştiren bir bağlamda kullanılıyorsa, skaler T-SQL UDF'si satır içi hale getirilebilir.

UDF tanımı aşağıdaki koşulların tümü doğru olmalıdır:

• UDF aşağıdaki yapılar kullanılarak yazılır:
  • DECLARE, SET: Değişken bildirimi ve atamaları.
  • SELECT: 1 tek/birden çok değişken ataması içeren SQL sorgusu.
  • IF / ELSE: Rastgele iç içe yerleştirme düzeyleriyle dallanma.
  • RETURN: Tek veya birden fazla return ifadesi. SQL Server 2019 (15.x) CU5'den başlayarak UDF, ⁶için dikkate alınması gereken tek bir RETURN deyimi içerebilir.
  • UDF: İç içe/özyinelemeli işlev çağrıları ².
  • Diğerleri: EXISTSve IS NULLgibi ilişkisel işlemler.
• UDF zamana bağlı (örneğin GETDATE()gibi) veya yan etkileri olan (örneğin gibi) herhangi bir iç işlevi çağırmaz NEWSEQUENTIALID().
• UDF, EXECUTE AS CALLER yan tümcesini kullanır (EXECUTE AS yan tümcesi belirtilmezse varsayılan davranış).
• UDF tablo değişkenlerine veya tablo değerli parametrelere başvurmaz.
• UDF yerel olarak derlenmez (birlikte çalışma desteklenir).
• UDF, kullanıcı tanımlı türlere başvurmaz.
• UDF ⁹'ye eklenen imza yok.
• UDF bir bölümleme fonksiyonu değildir.
• UDF, Ortak Tablo İfadelerine (CTE) başvuru içermez.
• UDF, içine yerleştirildiğinde sonuçları değiştirebilecek yerleşik işlevlere başvurular içermez (örneğin @@ROWCOUNT) ⁴.
• UDF, parametre olarak geçirilen bir skaler UDF ⁴'deki toplama işlevlerini içermez.
• UDF, yerleşik görünümlere (OBJECT_IDgibi) ⁴başvurmaz.
• UDF, 5 XML yöntemlerine başvurmaz.
• UDF, ORDER BY içeren bir SELECT'i TOP 1 yan tümcesi olmadan 5 içermez.
• UDF, ORDER BY yan tümcesiyle (SELECT @x = @x + 1 FROM table1 ORDER BY col1gibi) atama gerçekleştiren bir SELECT sorgusu içermez (örneğin, ⁵).
• UDF, ⁶birden çok RETURN deyimi içermez.
• UDF, STRING_AGG işlevi ⁶'ye başvurmaz.
• UDF, ⁷uzak tablolara başvurmaz.
• UDF, 8 şifrelenmiş sütunlara başvurmaz.
• UDF, WITH XMLNAMESPACES⁸referanslar içermez.
• UDF tanımı binlerce kod satırıyla karşılaşırsa, SQL Server bunu satır içine almamayı seçebilir.

¹SELECT ve değişken birikimi/toplanması, tek satırda yerleştirme (örneğin SELECT @val += col1 FROM table1) için desteklenmez.

² Özyinelemeli UDF'ler yalnızca belirli bir derinliğe çizilir.

³ Sonuçları geçerli sistem zamanına bağlı olan iç işlevler zamana bağlıdır. Bazı iç genel durumu güncelleştirebilen iç işlev, yan etkileri olan bir işleve örnektir. Bu tür işlevler her çağrıldığında iç duruma göre farklı sonuçlar döndürür.

SQL Server 2019 (15.x) CU 2'de ⁴ Kısıtlaması eklendi

SQL Server 2019 (15.x) CU 4'te ⁵ Kısıtlaması eklendi

SQL Server 2019 (15.x) CU 5'te ⁶ Kısıtlaması eklendi

SQL Server 2019 (15.x) CU 6'da ⁷ Kısıtlaması eklendi

SQL Server 2019 (15.x) CU 11'de ⁸ Kısıtlaması eklendi

⁹ bir UDF oluşturulduktan sonra imzalar eklenip bırakılabildiğinden, skaler UDF'ye başvuran sorgu derlendiğinde satır içi yapılıp yapılmayacağı kararı alınır. Örneğin, sistem işlevleri genellikle bir sertifika ile imzalı. hangi nesnelerin imzalı olduğunu bulmak için sys.crypt_properties kullanabilirsiniz.

yürütme bağlamı aşağıdaki gereksinimlerin tümü doğru olmalıdır:

• UDF, ORDER BY yan tümcesinde kullanılmaz.
• Skaler UDF çağıran sorgu, GROUP BY bölümünde skaler UDF çağrısına yer vermez.
• Skaler UDF'yi seçme listesinde DISTINCT yan tümcesiyle çağıran sorgunun ORDER BY yan tümcesi yoktur.
• UDF, RETURN deyiminden ¹çağrılmaz.
• UDF'yi çağıran sorguda ortak tablo ifadeleri (CTE'ler) yoktur. ³
• UDF çağırma sorgusu GROUPING SETS, CUBEveya ROLLUP²kullanmaz.
• UDF çağırma sorgusu, atama için UDF parametresi olarak kullanılan bir değişken (örneğin, SELECT @y = 2, @x = UDF(@y)) ²içermez.
• UDF, hesaplanan sütunda veya denetim kısıtlaması tanımında kullanılmaz.

SQL Server 2019 (15.x) CU 5'te ¹ Kısıtlaması eklendi

SQL Server 2019 (15.x) CU 6'da ² Kısıtlaması eklendi

SQL Server 2019 (15.x) CU 11'de ³ Kısıtlaması eklendi

En son T-SQL skaler UDF satıriçi yerleştirme düzeltmeleri ve uygunluk senaryolarında yapılan değişiklikler hakkında bilgi için Bilgi Bankası makalesine göz atın: DÜZELTME: SQL Server 2019'da skaler Kullanıcı Tanımlı Fonksiyon (UDF) satıriçi yerleştirme sorunları.

Bir UDF'nin satıriçi yapılabilir olup olmadığını denetleyin.

Her T-SQL skaler UDF için sys.sql_modules katalog görünümü, UDF'nin satır içi olup olmadığını gösteren is_inlineableadlı bir özellik içerir.

is_inlineable özelliği, UDF tanımının içinde bulunan yapılardan türetilir. Derleme zamanında UDF'nin aslında satır içi olup olmadığını denetlemez. Daha fazla bilgi için, içinkoşullarına bakın.

1 değeri UDF'nin satır içi yapılabilir olduğunu, 0 ise aksini gösterir. Tüm satır içi TVF'ler için de bu özellik 1 değerine sahiptir. Diğer tüm modüller için değeri 0.

Bir skaler UDF satır içi yazılabilir durumda olduğunda, bu her zaman satır içine yerleştirileceği anlamına gelmez. SQL Server, bir UDF'yi sorgu başına, UDF temelinde satır içi işlemeye karar verir. Bu makalenin önceki bölümlerinde yer alan gereksinim listelerine bakın.

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

Inlining olup olmadığını denetleyin

Tüm önkoşullar karşılanırsa ve SQL Server iç hat oluşturmayı gerçekleştirmeye karar verirse, UDF'yi ilişkisel bir ifadeye dönüştürür. Sorgu planında, inlining işleminin gerçekleşip gerçekleşmediğini anlayabilirsiniz:

• Plan XML'sinde, başarıyla iç içe geçmiş bir UDF için <UserDefinedFunction> XML düğümü yoktur.
• Bazı Genişletilmiş Olaylar yayılır.

Skaler UDF inlining'i etkinleştirme

Veritabanı için uyumluluk düzeyi 150'yi etkinleştirerek iş yüklerinin skaler UDF inlining için otomatik olarak uygun olmasını sağlayabilirsiniz. Transact-SQL kullanarak bunu ayarlayabilirsiniz. Mesela:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

Bu adımın dışında, bu özellikten yararlanmak için UDF'lerde veya sorgularda başka bir değişiklik yapılması gerekmez.

Uyumluluk düzeyini değiştirmeden skaler UDF inlining'i devre dışı bırakma

Skaler UDF inlining veritabanı uyumluluk düzeyi 150 ve üzeri korunurken veritabanı, deyim veya UDF kapsamında devre dışı bırakılabilir. Veritabanı kapsamında skaler UDF inlining özelliğini devre dışı bırakmak için, ilgili veritabanı bağlamında aşağıdaki ifadeyi yürütün:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Veritabanı için skaler UDF inlining'i yeniden etkinleştirmek için, ilgili veritabanı bağlamında aşağıdaki ifadeyi yürütür:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

ONolduğunda, bu ayar sys.database_scoped_configurationsiçinde etkin olarak görünür.

Ayrıca, DISABLE_TSQL_SCALAR_UDF_INLININGUSE HINT sorgu ipucu olarak işaretleyerek belirli bir sorgu için skaler UDF inlining özelliğini devre dışı bırakabilirsiniz.

USE HINT sorgu ipucu, veritabanı kapsamlı yapılandırma veya uyumluluk düzeyi ayarından önceliklidir.

Mesela:

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

Belirli bir UDF için skaler UDF satır içi yerleştirme, CREATE FUNCTION veya ALTER FUNCTION deyimindeki INLINE ifadesi kullanılarak devre dışı bırakılabilir. Mesela:

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

Önceki deyim yürütüldükten sonra, bu UDF hiçbir zaman onu çağıran hiçbir sorgunun içine alınmaz. Bu UDF için inlining'i yeniden etkinleştirmek için şu ifadeyi yürütün:

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

INLINE maddesi zorunlu değil. INLINE yan tümcesi belirtilmezse, UDF'nin satıra dahil edilip edilemeyeceğine bağlı olarak otomatik olarak ON/OFF ayarlanır. INLINE = ON belirtilmişse ancak UDF, inlining için uygun değilse bir hata fırlatılır.

Açıklamalar

Bu makalede açıklandığı gibi, skaler UDF inlining, skaler UDF'leri olan bir sorguyu eşdeğer skaler alt sorguya sahip bir sorguya dönüştürür. Bu dönüştürme nedeniyle, aşağıdaki senaryolarda davranışta bazı farklılıklar fark edebilirsiniz:

• Sorgu iç bünyesine alma işlemi, aynı sorgu metni için farklı bir sorgu karması oluşmasına neden olur.

• Daha önce gizlenmiş olabilecek UDF içindeki deyimlerdeki (sıfıra bölme vb.) bazı uyarılar, inlining nedeniyle ortaya çıkabilir.

• Sorgu düzeyindeki bağlama ipuçları artık geçerli olmayabilir, çünkü iç içe yerleştirme yeni bağlamalar oluşturabilir. Bunun yerine yerel birleştirme ipuçları kullanılmalıdır.

• Satır içi skaler UDF'lere başvuran görünümler dizine alınamaz. Bu tür görünümler üzerinde dizin oluşturmanız gerekiyorsa, referans alınan UDF'ler için satır içi çalışmayı devre dışı bırakın.

• UDF inlining ile Dinamik veri maskeleme davranışında bazı farklılıklar olabilir.

  Belli durumlarda (UDF'deki mantığa bağlı olarak), satır içi hale getirme, çıkış sütunlarını maskeleme konusunda daha muhafazakar davranabilir. UDF'de başvuruda bulunan sütunların çıkış sütunları olmadığı senaryolarda bunlar maskelenmez.

• UDF, SCOPE_IDENTITY(), @@ROWCOUNTveya @@ERRORgibi yerleşik işlevlere başvuruda bulunuyorsa, yerleşik işlev tarafından döndürülen değer, satıriçi ile değişir. Bu davranış değişikliğinin nedeni, inlining'in UDF içindeki deyimlerin kapsamını değiştirmesidir. SQL Server 2019 (15.x) CU2'den başlayarak, UDF belirli yerleşik işlevlere (örneğin @@ROWCOUNT) başvuruda bulunuyorsa, inlining engellenir.

• Bir değişken, bir inlined UDF sonucuyla atanırsa ve index_column_nameFORCESEEK (Transact-SQL) olarak da kullanılırsa, sorgu işlemcisinin sorguda tanımlanan ipuçları nedeniyle sorgu planı oluşturamadığını belirten 8622 hatasıyla sonuçlanır.

İlgili içerik

• Kullanıcı tanımlı işlevler oluşturma (Veritabanı Altyapısı)
• SQL Server Veritabanı Altyapısı ve Azure SQL Veritabanı için Performans Merkezi
• Sorgu işleme mimarisi kılavuzu
• Mantıksal ve Fiziksel Showplan İşleç Referansı
• Birleşimleri (SQL Server)
• Akıllı Sorgu İşleme Gösterimi
• DÜZELTME: SQL Server 2019'da skaler UDF yerel hatta sorunlarını düzeltme