یکی از مواردی که در بررسی کدهای سازمانها و شرکتهای مختلف وجود دارد استفاده از Scalar function ها هست. در این مطلب درباره افزایش سرعت کدهای این نوع توابع نکاتی را بیان خواهیم کرد.

Scalar function چگونه تابعی است؟

اگر در تعریف تابع Scalar function دقت کرده باشین این کلید ها وجود دارد:

<function_option>::=   
  {  
      [ ENCRYPTION ]  
    | [ SCHEMABINDING ]  
    | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
    | [ EXECUTE_AS_Clause ]  
  }

یکی از این گزینه ها Returns null on null input هست . اگر دراستفاده از تابع Scalar function در جدول مورد نظر مقادیر Null وجود دارد که میتواند Null برگرداند، با اضافه کردن این گزینه به هدر تابع، اسکیوال سرور مقادیر Null را صرفنظر کرده و فقط مقادیر اصلی را به این تابع ارسال و خروجی واحد دریافت خواهد کرد.

روش افزایش سرعت پردازش و اجرای کدهای Scalar

مثلا فرض کنید در یک جدولی تابعی نوشتید که میدانید اگر مقدار 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 خودتان فیلتر کنید.

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