بهینه سازی عملکرد SQL Server: خداحافظی با RBAR و راهکارهای نوین

بهینه‌سازی عملکرد SQL Server: وداع با RBAR و راهکارهای نوین

اگر یک توسعه‌دهنده یا مدیر پایگاه داده SQL Server هستید، حتماً با پرس و جوهای کند مواجه شده‌اید. در حالی که عوامل زیادی می‌توانند به این کندی کمک کنند، یکی از رایج‌ترین آن‌ها چیزی به نام RBAR یا پردازش “سطر به سطر و دردناک” است.

یکی از دلایل رایج RBAR، استفاده از توابع اسکالر (Scalar Functions) است. توابع اسکالر، ذاتاً داده‌ها را سطر به سطر پردازش می‌کنند. اگرچه می‌توانند برای کپسوله‌سازی منطق پیچیده مفید باشند، اما سربار عملکرد آن‌ها می‌تواند قابل توجه باشد، به خصوص زمانی که روی تعداد زیادی سطر اعمال شوند.

تابع زیر را برای محاسبه سن کارمند در نظر بگیرید:

CREATE FUNCTION GetEmployeeAge (@BirthDate DATETIME)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(year, @BirthDate, GETDATE()) -
           CASE WHEN MONTH(@BirthDate) > MONTH(GETDATE()) OR
                      (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) > DAY(GETDATE()))
                THEN 1
                ELSE 0
           END;
END;

این تابع، سن را بر اساس تاریخ تولد ورودی محاسبه می‌کند.

هنگامی که این تابع را مستقیماً در یک دستور SELECT برای هر سطر استفاده می‌کنید، به یک عملیات RBAR تبدیل می‌شود:

SELECT EmpId, dbo.GetEmployeeAge(BirthDate) AS Age
FROM Employees;

این رویکرد، برای هر سطر جدول Employees، تابع GetEmployeeAge را فراخوانی می‌کند که منجر به عملکرد ضعیف می‌شود.

برای کاهش تأثیر RBAR توابع اسکالر، یکی از تکنیک‌ها استفاده از عملگر APPLY است. اگرچه این روش همچنان تابع را یک بار برای هر سطر اجرا می‌کند، اما به بهینه‌ساز (optimizer) انعطاف‌پذیری بیشتری می‌دهد و گاهی اوقات منجر به طرح‌های اجرایی بهتری نسبت به فراخوانی مستقیم تابع در لیست SELECT می‌شود. این عملگر اساساً تابع اسکالر را تا حدی “درون‌خطی” می‌کند و آن را بیشتر شبیه یک جدول مشتق شده (derived table) در نظر می‌گیرد.

نمونه استفاده از CROSS APPLY:

SELECT EmpId, x.Age
FROM Employees
CROSS APPLY (SELECT dbo.GetEmployeeAge(BirthDate) AS Age) x;

در اینجا، تابع GetEmployeeAge در داخل یک جدول مشتق شده x قرار گرفته و با استفاده از CROSS APPLY به جدول Employees متصل می‌شود.

انتخاب بین یک متغیر جدولی (Table Variable) و یک جدول موقت (Temporary Table) یک معمای کلاسیک در بهینه‌سازی SQL Server است. هر کدام نقاط قوت و ضعف خود را دارند. متغیرهای جدولی (DECLARE @table TABLE (…)) عمدتاً در حافظه (در tempdb اما با حداقل لاگ‌برداری) قرار می‌گیرند، آمار (statistics) را نگهداری نمی‌کنند و برای مجموعه داده‌های کوچک که آمار در آن‌ها حیاتی نیست، مفید هستند. از طرف دیگر، جداول موقت (CREATE TABLE #table (…)) به طور کامل در tempdb لاگ‌برداری می‌شوند، آمار را نگهداری می‌کنند و عموماً برای مجموعه داده‌های بزرگتر یا پرس و جوهای پیچیده‌تر که بهینه‌ساز پرس و جو می‌تواند از آمارهای دقیق بهره‌مند شود، ارجحیت دارند.

نحوه تعریف هر کدام به صورت زیر است:

DECLARE @MyTableVariable TABLE (ID INT, Name VARCHAR(50));
CREATE TABLE #MyTempTable (ID INT, Name VARCHAR(50));

متغیر جدولی @MyTableVariable و جدول موقت #MyTempTable را مشاهده می‌کنید.

SQL Server 2019 یک ویژگی مهم به نام Scalar UDF Inlining (درون‌خطی کردن تابع اسکالر تعریف شده توسط کاربر) را معرفی کرد. این ویژگی می‌تواند به طور خودکار توابع اسکالر تعریف شده توسط کاربر را به عبارات اسکالر تبدیل کند و آن‌ها را به طور موثر در طرح اجرای پرس و جوی فراخوانی کننده ادغام کند. این کار به طور چشمگیری سربار RBAR مرتبط با بسیاری از UDFهای اسکالر را کاهش می‌دهد و عملکرد آن‌ها را به عملکرد عبارات T-SQL بومی نزدیک می‌کند. با این حال، همه UDFهای اسکالر را نمی‌توان درون‌خطی کرد؛ محدودیت‌ها شامل توابع غیرقطعی، توابعی که از CTEهای بازگشتی استفاده می‌کنند، یا توابع جدولی چنددستوری (MSTVF) در تعریف خود می‌شوند.

در حالی که LEFT JOIN معمولاً برای ترکیب سطرها از دو یا چند جدول استفاده می‌شود، CROSS APPLY یک جایگزین قدرتمند ارائه می‌دهد، به خصوص زمانی که سمت راست join یک عبارت جدولی باشد که به سطرهای سمت چپ وابسته است. CROSS APPLY سمت راست خود را برای هر سطری که توسط سمت چپ آن بازگردانده می‌شود، اجرا می‌کند، که آن را برای سناریوهایی ایده‌آل می‌کند که در آن نیاز دارید داده‌ها را به صورت پویا برای هر سطر از پرس و جوی بیرونی محاسبه یا بازیابی کنید.

نمونه‌ای از استفاده از LEFT JOIN برای یافتن حداکثر حقوق برای هر کارمند:

SELECT E.EmpID, E.Name, S.Salary
FROM Employees E
LEFT JOIN (SELECT EmpID, MAX(Salary) AS Salary FROM Salaries GROUP BY EmpID) S ON E.EmpID = S.EmpID;

این پرس و جو از یک زیرپرس و جو برای یافتن حداکثر حقوق هر کارمند استفاده می‌کند و سپس آن را به جدول Employees متصل می‌کند.

معادل آن با استفاده از CROSS APPLY می‌تواند به شکل زیر باشد که اغلب راه‌حلی خواناتر و گاهی اوقات با عملکرد بهتر ارائه می‌دهد، به خصوص با پرس و جوهای داخلی پیچیده:

SELECT E.EmpID, E.Name, S.Salary
FROM Employees E
CROSS APPLY (SELECT MAX(Salary) AS Salary FROM Salaries WHERE EmpID = E.EmpID) S;

در این حالت، CROSS APPLY برای هر کارمند، حداکثر حقوق را مستقیماً از جدول Salaries با فیلتر EmpID محاسبه می‌کند.

گزینه BATCHSIZE در BULK INSERT برای مدیریت استفاده از لاگ تراکنش و عملکرد در هنگام وارد کردن داده‌های بزرگ حیاتی است. BATCHSIZE تعداد سطرها در یک دسته از داده‌ها را مشخص می‌کند. هر دسته به عنوان یک تراکنش جداگانه در نظر گرفته می‌شود. BATCHSIZE کوچکتر به معنای commitهای مکرر است که می‌تواند رقابت بر روی لاگ تراکنش را کاهش داده و از رشد بیش از حد آن در طول وارد کردن داده جلوگیری کند. با این حال، BATCHSIZE بسیار کوچک می‌تواند به دلیل commitهای تراکنش مکرر، سربار را افزایش دهد. برعکس، BATCHSIZE بزرگ به معنای commitهای کمتر است، که در صورت توانایی لاگ تراکنش برای مدیریت بار، به طور بالقوه درج کلی سریع‌تر را به همراه دارد، اما خطر هزینه‌های rollback را در صورت بروز خطا در یک دسته بزرگ افزایش می‌دهد.

مثالی از BULK INSERT با استفاده از BATCHSIZE:

BULK INSERT TargetTable
FROM 'C:\Data\Source.txt'
WITH (
    FORMAT = 'CSV',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    BATCHSIZE = 1000
);

در این مثال، داده‌ها در دسته‌های ۱۰۰۰ تایی وارد جدول TargetTable می‌شوند و هر دسته یک تراکنش جداگانه است.

درک و اجتناب از عملیات RBAR، بهره‌گیری از ویژگی‌های مناسب مانند Scalar UDF Inlining، انتخاب هوشمندانه بین متغیرهای جدولی و جداول موقت، و بهینه‌سازی عملیات انبوه با BATCHSIZE، همگی برای نوشتن پرس و جوهای با عملکرد بالا در SQL Server حیاتی هستند. پذیرش تفکر مبتنی بر مجموعه، کلید دستیابی به پتانسیل کامل پایگاه داده شماست.