سلام و عرض ادب خدمت دوستان عزیزم
امیدوارم حالتون خوب باشه
یکی از مواردی که در بررسی کدهای سازمانها و شرکتهای مختلف دیدم وجود داره استفاده از Scalar function ها هست. امروز میخوام نکته ای رو بگم که بهتون کمک میکنه پرفورمنس این نوع توابع نیز افزایش پیدا کنه.
اگر در تعریف این تابع دقت کرده باشین این کلید ها وجود داره
<function_option>::=
{
[ ENCRYPTION ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
| [ EXECUTE_AS_Clause ]
}
یکی از این گزینه ها Returns null on null input هست . اگر د راستفاده از این تابع در جدول مورد نظر مقادیر Null وجود داره که میتونه Null برگردونه ، با اضافه کردن این گزینه به هدر تابع ، اسکیوال سرور مقادیر Null رو صرفنظر کرده و فقط مقادیر اصلی رو به این تابع ارسال ، و خروجی واحد دریافت خواهد کرد.
مثلا فرض کنید در یک جدولی تابعی نوشتید که میدونید اگر مقدار Null به پارامتر تابع ارسال بشه حتما خروجی Null خواهد بود و این جدول فرضا 40000 ردیف دارد. که 20000 ردیف آن Null و مابقی مقدار دارد.
اگر از این دستور استفاده نکنید و از تابعی که نوشتید در بدنه Select استفاده کنید ، این تابع به ازای هر 40000 ردیف موجود در جدول اجرا خواهد شد. ولی اگر از این دستور استفاده کنید تابع شما فقط 20000 مرتبه اجرا خواهد شد و برای مقادیر Null اجرا نمی شود.
این سبب خواهد شد سرعت پردازش و اجرای کدهای Scalar شما در صورتی که دارای شرط فوق باشه ، افزایش پیدا کنه.
در ضمن اینکه این گزینه از نسخه 2008 به بعد وجود داره.
برای تست این موضوع ، بر روی دیتابیس Adventureworks 2017 نیز میتونید با توابع زیر این رو تست کنید.
USE [AdventureWorks2017];
GO

CREATE FUNCTION [dbo].[ufnLeadingZeros_new](
@Value int
)
RETURNS varchar(8)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @ReturnValue varchar(8);
SET @ReturnValue = CONVERT(varchar(8), @Value);
SET @ReturnValue = REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @ReturnValue;
RETURN (@ReturnValue);
END;
GO

و سپس این کدهارو اجرا کنید.
SELECT SalesOrderID, dbo.ufnLeadingZeros(CurrencyRateID)
FROM Sales.SalesOrderHeader;
GO
SELECT SalesOrderID, dbo.ufnLeadingZeros_new(CurrencyRateID)
FROM Sales.SalesOrderHeader;
GO

برای مانیتور کردنش هم Event های زیر رو تعریف کنید.
CREATE EVENT SESSION [Session72] ON SERVER
ADD EVENT sqlserver.module_end(
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(125)))),
ADD EVENT sqlserver.sql_batch_completed(
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(125)))),
ADD EVENT sqlserver.sql_batch_starting(
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(125)))),
ADD EVENT sqlserver.sql_statement_completed(
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(125)))),
ADD EVENT sqlserver.sql_statement_starting(
WHERE ([package0].[equal_uint64]([sqlserver].[session_id],(125))))
WITH (TRACK_CAUSALITY=ON)
GO

فقط نکته اینکه مقدار 125 برای Session تستی من هست که فیلتر کنم که دقیقا همین کدهارو نمایش بده. شما اینرو براساس Session خودتون فیلتر کنید.

امیدوارم این نکته در افزایش سرعت کدهاتون کمکی بهتون بکنه.

ارادتمند شما
حمیدرضا صادقیان
ID:@Hamidreza_Sadeghian
Channel :@SQL_Server

#TSQL #Scalar_Funcion #UDF #Performance

دوره بسیار عالی با افرادی بسیار دقیق و باهوش در شرکت موتورسازان تراکتور سازی تبریز

#performance_tuning
#T_SQL
#SQL_Server