بهبود عملکرد SQL Server تفکیک رشته ها با TVP TVF OPENJSON و STRING SPLIT

بهبود عملکرد SQL: تفکیک رشته‌های جدا شده با کاما با TVP، TVF، OPENJSON و STRING_SPLIT

در توسعه دیتابیس SQL Server، یکی از رایج‌ترین چالش‌ها، نیاز به تفکیک رشته‌های جدا شده با کاما (Comma Separated Values – CSV) به مقادیر جداگانه برای درج، به‌روزرسانی یا گزارش‌گیری است. این عملیات می‌تواند تأثیر قابل توجهی بر عملکرد کوئری‌ها داشته باشد. در این مقاله، چندین روش متداول برای تجزیه این رشته‌ها را بررسی کرده و عملکرد هر یک را مقایسه می‌کنیم تا بهترین رویکرد را برای بهینه‌سازی SQL Server شناسایی کنیم.

ابتدا، اجازه دهید یک جدول کمکی (helper table) ایجاد کنیم که برای ذخیره‌سازی داده‌های اصلی ما استفاده می‌شود و سپس یک پروسیجر ذخیره شده (Stored Procedure) برای درج مقادیر در این جدول خواهیم ساخت. این ساختار به ما امکان می‌دهد تا سناریوهای مختلف تجزیه رشته را شبیه‌سازی و تست کنیم.

CREATE DATABASE TestDB;
GO
USE TestDB;
GO

CREATE TABLE EmployeeList
(
    EmployeeID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);
GO

CREATE PROCEDURE dbo.usp_InsertEmployeeList
    @FirstName VARCHAR(50),
    @LastName VARCHAR(50)
AS
BEGIN
    INSERT INTO EmployeeList (FirstName, LastName)
    VALUES (@FirstName, @LastName);
END;
GO

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

1. تفکیک رشته با استفاده از تابع تعریف شده توسط کاربر (UDF)

یکی از اولین روش‌هایی که به ذهن می‌رسد، ایجاد یک تابع اسکالر تعریف شده توسط کاربر (Scalar UDF) برای تفکیک رشته‌ها است. این تابع یک رشته ورودی را می‌گیرد و یک جدول از مقادیر تفکیک شده را برمی‌گرداند.

CREATE FUNCTION dbo.udf_SplitString (@InputString VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @OutputTable TABLE (Item VARCHAR(MAX))
AS
BEGIN
    DECLARE @StartIndex INT, @EndIndex INT;
    SET @StartIndex = 1;
    IF SUBSTRING(@InputString, LEN(@InputString) - 1, 1)  @Delimiter
    BEGIN
        SET @InputString = @InputString + @Delimiter;
    END;

    WHILE CHARINDEX(@Delimiter, @InputString, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @InputString, @StartIndex);
        INSERT INTO @OutputTable (Item)
        SELECT SUBSTRING(@InputString, @StartIndex, @EndIndex - @StartIndex);
        SET @StartIndex = @EndIndex + 1;
    END;
    RETURN;
END;
GO

سپس می‌توانیم این تابع را در یک پروسیجر ذخیره شده برای درج داده‌ها استفاده کنیم. این روش از نظر خوانایی ساده است، اما به دلیل ماهیت توابع اسکالر در SQL Server، می‌تواند مشکلات عملکردی، به خصوص با حجم بالای داده، ایجاد کند.

CREATE PROCEDURE dbo.usp_InsertEmployeeList_UDF
    @FullNameList VARCHAR(MAX)
AS
BEGIN
    INSERT INTO EmployeeList (FirstName, LastName)
    SELECT
        LEFT(Item, CHARINDEX(' ', Item) - 1),
        RIGHT(Item, LEN(Item) - CHARINDEX(' ', Item))
    FROM dbo.udf_SplitString(@FullNameList, ',');
END;
GO

2. تفکیک رشته با استفاده از تابع جدول‌محور (TVF)

یک جایگزین بهتر برای UDF اسکالر، استفاده از تابع جدول‌محور (Table-Valued Function – TVF) است. TVF‌ها عملکرد بهتری نسبت به UDF‌های اسکالر دارند زیرا می‌توانند مستقیماً در عبارت FROM کوئری‌ها استفاده شوند و بهینه‌سازی بیشتری را توسط SQL Server ممکن می‌سازند.

CREATE FUNCTION dbo.udf_SplitString_TVF (@InputString VARCHAR(MAX), @Delimiter CHAR(1))
RETURNS TABLE
AS
RETURN
(
    SELECT
        SUBSTRING(@InputString, Number,
                  CHARINDEX(@Delimiter, @InputString + @Delimiter, Number) - Number) AS Item
    FROM
    (
        SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
        FROM sys.all_objects
    ) AS a
    WHERE Number <= LEN(@InputString)
    AND SUBSTRING(@Delimiter + @InputString, Number, 1) = @Delimiter
);
GO

این TVF نیز می‌تواند در پروسیجر ذخیره شده مشابهی استفاده شود، اما با بهبود عملکرد مورد انتظار.

CREATE PROCEDURE dbo.usp_InsertEmployeeList_TVF
    @FullNameList VARCHAR(MAX)
AS
BEGIN
    INSERT INTO EmployeeList (FirstName, LastName)
    SELECT
        LEFT(Item, CHARINDEX(' ', Item) - 1),
        RIGHT(Item, LEN(Item) - CHARINDEX(' ', Item))
    FROM dbo.udf_SplitString_TVF(@FullNameList, ',');
END;
GO

3. استفاده از پارامترهای جدول‌محور (TVP)

پارامترهای جدول‌محور (Table-Valued Parameters – TVP) راه حل قدرتمندی برای ارسال مجموعه‌ای از رکوردها به یک پروسیجر ذخیره شده یا تابع است. این روش به جای ارسال یک رشته طولانی و تفکیک آن در سمت سرور، به شما اجازه می‌دهد تا داده‌های ساختار یافته را مستقیماً از برنامه کلاینت ارسال کنید. این کار سربار پردازش را در دیتابیس کاهش داده و عملکرد را به طور قابل توجهی بهبود می‌بخشد.

ابتدا، یک نوع جدول تعریف می‌کنیم:

CREATE TYPE dbo.EmployeeTableType AS TABLE
(
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);
GO

سپس، یک پروسیجر ذخیره شده ایجاد می‌کنیم که این نوع جدول را به عنوان پارامتر می‌پذیرد:

CREATE PROCEDURE dbo.usp_InsertEmployeeList_TVP
    @EmployeeList dbo.EmployeeTableType READONLY
AS
BEGIN
    INSERT INTO EmployeeList (FirstName, LastName)
    SELECT FirstName, LastName
    FROM @EmployeeList;
END;
GO

برای استفاده از این روش، در برنامه کلاینت (مثلاً C#)، باید یک `DataTable` یا `IEnumerable` از اشیاء `Employee` را ایجاد کرده و آن را به عنوان پارامتر به پروسیجر ارسال کنید. این روش به ویژه برای حجم بالای داده‌ها بسیار کارآمد است.

4. تفکیک رشته با استفاده از OPENJSON

SQL Server 2016 و نسخه‌های جدیدتر، تابع `OPENJSON` را معرفی کردند که به شما امکان می‌دهد داده‌های JSON را تجزیه کنید. از آنجایی که رشته‌های جدا شده با کاما را می‌توان به فرمت آرایه JSON تبدیل کرد، می‌توانیم از `OPENJSON` برای تفکیک آنها استفاده کنیم. این روش معمولاً عملکرد بسیار خوبی دارد.

ابتدا رشته را به یک آرایه JSON تبدیل می‌کنیم:

CREATE PROCEDURE dbo.usp_InsertEmployeeList_OPENJSON
    @FullNameList VARCHAR(MAX)
AS
BEGIN
    DECLARE @JsonArray NVARCHAR(MAX) = '[''' + REPLACE(@FullNameList, ',', ''',''') + ''']';

    INSERT INTO EmployeeList (FirstName, LastName)
    SELECT
        LEFT(value, CHARINDEX(' ', value) - 1),
        RIGHT(value, LEN(value) - CHARINDEX(' ', value))
    FROM OPENJSON(@JsonArray);
END;
GO

تابع `OPENJSON` یک جدول با سه ستون (key, value, type) برمی‌گرداند. ما ستون `value` را که شامل هر آیتم تفکیک شده است، انتخاب می‌کنیم.

5. تفکیک رشته با استفاده از STRING_SPLIT

`STRING_SPLIT` یک تابع جدول‌محور بومی است که در SQL Server 2016 معرفی شد و به طور خاص برای تفکیک رشته‌ها طراحی شده است. این تابع عملکرد فوق‌العاده‌ای دارد و ساده‌ترین راه برای تفکیک رشته‌ها با یک جداکننده است. این تابع بهینه‌سازی شده برای عملکرد بالا است و باید در اولویت قرار گیرد.

CREATE PROCEDURE dbo.usp_InsertEmployeeList_STRING_SPLIT
    @FullNameList VARCHAR(MAX)
AS
BEGIN
    INSERT INTO EmployeeList (FirstName, LastName)
    SELECT
        LEFT(value, CHARINDEX(' ', value) - 1),
        RIGHT(value, LEN(value) - CHARINDEX(' ', value))
    FROM STRING_SPLIT(@FullNameList, ',');
END;
GO

`STRING_SPLIT` یک جدول با یک ستون به نام `value` برمی‌گرداند که شامل مقادیر تفکیک شده است. این سادگی و کارایی، آن را به بهترین گزینه برای تفکیک رشته‌ها در SQL Server تبدیل می‌کند، البته در صورتی که نیاز به حفظ ترتیب ندارید. در SQL Server 2022، یک آرگومان اختیاری `ordinal` اضافه شد که امکان حفظ ترتیب را فراهم می‌کند.

مقایسه عملکرد

برای مقایسه عملکرد این روش‌ها، می‌توانیم از هر یک از پروسیجرهای ذخیره شده با حجم‌های مختلف داده استفاده کرده و زمان اجرای آنها را اندازه‌گیری کنیم. نتایج به طور کلی نشان می‌دهند:

* **UDF اسکالر:** معمولاً کندترین روش، به خصوص با افزایش حجم داده.
* **TVF (با استفاده از `sys.all_objects`):** بهتر از UDF اسکالر، اما هنوز هم می‌تواند سربار داشته باشد.
* **TVP:** بسیار کارآمد، به خصوص زمانی که داده‌ها از برنامه کلاینت به صورت ساختار یافته ارسال می‌شوند. این روش سربار پردازش در سمت دیتابیس را به حداقل می‌رساند.
* **OPENJSON:** عملکرد بسیار خوبی دارد و در مواردی که `STRING_SPLIT` در دسترس نیست یا نیاز به انعطاف‌پذیری JSON دارید، گزینه مناسبی است.
* **STRING_SPLIT:** به طور کلی، سریع‌ترین و بهینه‌ترین روش برای تفکیک رشته‌ها در SQL Server 2016 و بالاتر.

در نهایت، انتخاب بهترین روش بستگی به نسخه SQL Server، حجم داده، و معماری کلی برنامه شما دارد. با این حال، برای بهترین عملکرد و سادگی کد، در صورت امکان از `STRING_SPLIT` یا TVP استفاده کنید.