ستون‌های محاسباتی پایدار (Persisted) در SQL Server بهینه‌سازی عملکرد و ایندکس

تأثیر پایدارسازی (Persisted) ستون‌های محاسباتی در SQL Server بر عملکرد و فهرست‌گذاری

ستون‌های محاسباتی در SQL Server امکان افزودن ستون‌هایی را به یک جدول فراهم می‌کنند که مقادیر آن‌ها بر اساس ستون‌های دیگر همان جدول یا توابع و ثابت‌ها محاسبه می‌شوند. این ستون‌ها می‌توانند مجازی (Virtual) باشند، به این معنی که فضای ذخیره‌سازی اضافی اشغال نمی‌کنند و هر بار که به آن‌ها ارجاع شود، مقدارشان محاسبه می‌شود. اما با استفاده از کلمه کلیدی PERSISTED، می‌توانیم یک ستون محاسباتی را پایدار (Persisted) کنیم. در این صورت، موتور پایگاه داده مقدار محاسبه شده را در دیسک ذخیره می‌کند و آن را مانند یک ستون معمولی می‌بیند. اما چه تفاوتی بین این دو حالت وجود دارد و استفاده از PERSISTED چه مزایایی به همراه دارد؟

پایدارسازی یک ستون محاسباتی، به ویژه در شرایط خاص، می‌تواند بهبود قابل توجهی در عملکرد کوئری‌ها ایجاد کند. این مزیت عمدتاً به دلیل امکان ایجاد ایندکس روی ستون‌های محاسباتی پایدار و همچنین عدم نیاز به محاسبه مجدد مقدار در هر بار دسترسی است. در ادامه، تأثیر پایدارسازی ستون‌های محاسباتی و چگونگی بهینه‌سازی عملکرد با استفاده از این ویژگی را بررسی خواهیم کرد.

ایجاد پایگاه داده و جدول

برای نشان دادن تفاوت بین ستون‌های محاسباتی مجازی و پایدار، ابتدا یک پایگاه داده جدید ایجاد کرده و سپس یک جدول ساده با چند ستون پایه مانند FirstName و LastName به همراه چند رکورد داده، می‌سازیم.

CREATE DATABASE ComputedColumnTest;
GO

USE ComputedColumnTest;
GO

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    Salary DECIMAL(10, 2)
);
GO

اکنون، چند رکورد نمونه را به جدول Employees اضافه می‌کنیم تا داده‌هایی برای آزمایش داشته باشیم.

INSERT INTO Employees (FirstName, LastName, Email, Salary) VALUES
('John', 'Doe', 'john.doe@example.com', 60000.00),
('Jane', 'Smith', 'jane.smith@example.com', 75000.00),
('Peter', 'Jones', 'peter.jones@example.com', 80000.00),
('Alice', 'Williams', 'alice.w@example.com', 62000.00),
('Robert', 'Brown', 'robert.b@example.com', 70000.00);
GO

افزودن ستون محاسباتی غیرپایدار (مجازی)

ابتدا یک ستون محاسباتی مجازی به نام FullName اضافه می‌کنیم. این ستون مقدار خود را از ترکیب FirstName و LastName به دست می‌آورد. تعریف آن به این شکل است:

ALTER TABLE Employees
ADD FullName AS (CAST(FirstName AS VARCHAR(50)) + ' ' + CAST(LastName AS VARCHAR(50)));
GO

این ستون مقدار FirstName و LastName را به رشته‌های متنی تبدیل کرده و با یک فاصله در بین آن‌ها به هم متصل می‌کند. برای مشاهده داده‌ها و ستون جدید، می‌توانیم یک کوئری SELECT اجرا کنیم:

SELECT EmployeeId, FirstName, LastName, FullName, Salary FROM Employees;
GO

همانطور که مشاهده می‌کنید، ستون FullName در نتیجه نمایش داده می‌شود. از آنجا که این ستون مجازی است، فضای ذخیره‌سازی اضافی مصرف نمی‌کند و مقدار آن هر بار که کوئری اجرا می‌شود، محاسبه می‌گردد. برای بررسی این موضوع، کوئری را با فعال کردن STATISTICS IO و EXPLAIN PLAN اجرا می‌کنیم.

SET STATISTICS IO ON;
GO
SELECT FullName FROM Employees WHERE FullName = 'John Doe';
GO
SET STATISTICS IO OFF;
GO

در این حالت، SQL Server نمی‌تواند از ایندکس روی FullName (چون ایندکسی وجود ندارد و روی ستون‌های مجازی نمی‌توان ایندکس ایجاد کرد) استفاده کند و مجبور است تمام رکوردها را اسکن کرده و مقدار FullName را برای هر کدام محاسبه کند تا شرط فیلتر را اعمال کند.

افزودن ستون محاسباتی پایدار (Persisted)

حالا یک ستون محاسباتی دیگر به نام FullNamePersisted اضافه می‌کنیم، اما این بار از کلمه کلیدی PERSISTED استفاده می‌کنیم. این بدان معناست که مقدار این ستون در دیسک ذخیره می‌شود و در هنگام درج یا به‌روزرسانی رکورد، محاسبه شده و نگهداری می‌شود.

ALTER TABLE Employees
ADD FullNamePersisted AS (CAST(FirstName AS VARCHAR(50)) + ' ' + CAST(LastName AS VARCHAR(50))) PERSISTED;
GO

تعریف این ستون نیز از مقادیر `FirstName` و `LastName` استفاده می‌کند، اما با افزودن `PERSISTED`، مقدار آن به صورت فیزیکی ذخیره می‌شود. این کار باعث می‌شود که دسترسی به آن سریع‌تر باشد، زیرا نیازی به محاسبه در زمان اجرا نیست. همچنین، اکنون می‌توانیم روی این ستون ایندکس ایجاد کنیم.

SELECT EmployeeId, FullName, FullNamePersisted FROM Employees;
GO

کوئری بالا هر دو ستون محاسباتی را نشان می‌دهد. از نظر ظاهر، هیچ تفاوتی بین آن‌ها نیست، اما از نظر نحوه ذخیره‌سازی و عملکرد، تفاوت‌های اساسی دارند.

فهرست‌گذاری روی ستون‌های محاسباتی پایدار

یکی از بزرگترین مزایای پایدارسازی ستون محاسباتی این است که می‌توان روی آن ایندکس ایجاد کرد. این کار می‌تواند عملکرد کوئری‌هایی را که ستون محاسباتی را در بند WHERE، ORDER BY یا GROUP BY خود استفاده می‌کنند، به شدت بهبود بخشد.

اکنون یک ایندکس غیرکلاسترد (Non-clustered index) روی ستون FullNamePersisted ایجاد می‌کنیم:

CREATE NONCLUSTERED INDEX IX_FullNamePersisted
ON Employees (FullNamePersisted);
GO

حالا اگر همان کوئری قبلی را با STATISTICS IO و EXPLAIN PLAN روی ستون پایدار اجرا کنیم:

SET STATISTICS IO ON;
GO
SELECT FullNamePersisted FROM Employees WHERE FullNamePersisted = 'John Doe';
GO
SET STATISTICS IO OFF;
GO

در این حالت، Execution Plan نشان خواهد داد که به جای Table Scan یا Clustered Index Scan، از Index Seek روی IX_FullNamePersisted استفاده شده است، که بسیار کارآمدتر است. این کاهش در حجم عملیات IO به خصوص در جداول بزرگ، تأثیر چشمگیری بر عملکرد خواهد داشت.

بیایید یک مثال دیگر از ستون محاسباتی پایدار را با فرمول پیچیده‌تر بررسی کنیم. فرض کنید می‌خواهیم ستونی برای نمایش افزایش حقوق احتمالی داشته باشیم که بر اساس یک فرمول خاص محاسبه می‌شود.

ALTER TABLE Employees
ADD SalaryIncrease AS ((Salary * 0.1) * 2) PERSISTED;
GO

این ستون، `SalaryIncrease` را بر اساس دو برابر 10 درصد حقوق فعلی محاسبه می‌کند. تعریف آن به شکل زیر است:

`((Salary * 0.1) * 2)`

این فرمول، 10 درصد از ستون `Salary` را محاسبه کرده و سپس نتیجه را دو برابر می‌کند.

اکنون می‌توانیم روی این ستون نیز ایندکس ایجاد کنیم:

CREATE NONCLUSTERED INDEX IX_SalaryIncrease
ON Employees (SalaryIncrease);
GO

و یک کوئری برای استفاده از این ستون و ایندکس:

SET STATISTICS IO ON;
GO
SELECT EmployeeId, Salary, SalaryIncrease FROM Employees WHERE SalaryIncrease > 14000;
GO
SET STATISTICS IO OFF;
GO

در این حالت نیز، ایندکس IX_SalaryIncrease برای فیلتر کردن داده‌ها مورد استفاده قرار می‌گیرد.

مهم است بدانید که وقتی ستون‌های مبنای یک ستون محاسباتی پایدار به‌روزرسانی می‌شوند، مقدار ستون محاسباتی نیز به صورت خودکار به‌روزرسانی می‌شود. این عملیات شامل به‌روزرسانی هر ایندکسی است که روی ستون محاسباتی پایدار ساخته شده است.

UPDATE Employees
SET Salary = 85000.00
WHERE EmployeeId = 1;
GO

SELECT EmployeeId, FirstName, LastName, Salary, SalaryIncrease FROM Employees WHERE EmployeeId = 1;
GO

پس از اجرای دستور UPDATE، مقدار SalaryIncrease برای EmployeeId = 1 به طور خودکار به 17000.00 (0.1 * 85000 * 2) به‌روزرسانی می‌شود و ایندکس مربوطه نیز همگام خواهد شد. این ویژگی تضمین می‌کند که داده‌های پایدار همواره دقیق و به‌روز هستند، اما باید توجه داشت که این عملیات به‌روزرسانی نیز سربار عملکردی (overhead) دارد که باید در نظر گرفته شود.