افزایش سرعت کوئری‌نویسی JSON در SQL Server (پیش از 2025)JSON_VALUE

راهنمای جامع: افزایش سرعت کوئری‌نویسی JSON در SQL Server (پیش از 2025)JSON_VALUE

در SQL Server، هنگام کار با داده‌های JSON، دو تابع اصلی برای تجزیه JSON وجود دارد: JSON_VALUE() و OPENJSON(). همچنین توابع کمکی مانند JSON_QUERY() و ISJSON() نیز در دسترس هستند. این توابع به توسعه‌دهندگان و مدیران پایگاه داده اجازه می‌دهند تا به راحتی داده‌های JSON را در SQL Server ذخیره و بازیابی کنند.

استخراج مقادیر اسکالر (تک‌مقدار) از JSON نسبتاً ساده است و از تابع JSON_VALUE() استفاده می‌کند. در ادامه مثالی از نحوه استفاده از این تابع برای استخراج نام و نام خانوادگی از یک رشته JSON آورده شده است:

    SELECT
        JSON_VALUE(YourJsonColumn, '$.FirstName') AS FirstName,
        JSON_VALUE(YourJsonColumn, '$.LastName') AS LastName
    FROM YourTable;

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

مشکلات عملکردی JSON_VALUE() و OPENJSON()

JSON_VALUE() برای هر فراخوانی، نیاز به اسکان کامل رشته JSON دارد، حتی اگر بخواهید تنها یک مقدار را استخراج کنید. این تابع همچنین نمی‌تواند از ایندکس‌ها بهره ببرد، که منجر به اسکان کامل جدول برای هر کوئری می‌شود. این مشکل به خصوص در جداول بزرگ با ستون‌های JSON قابل توجه است.

تابع OPENJSON() نیز برای تجزیه کل رشته JSON، به ویژه در موارد پیچیده و درهم‌پیچیده، به زمان قابل توجهی نیاز دارد. در حالی که OPENJSON() می‌تواند داده‌های JSON را به صورت جدولی بازگرداند که می‌توان آن را ایندکس کرد، خود فرآیند تجزیه اولیه بدون کمک خارجی کند است.

این مقاله بر روی روش‌هایی برای بهبود عملکرد کوئری‌های JSON قبل از SQL Server 2025 تمرکز دارد. این رویکردها شامل استفاده از ستون‌های محاسباتی و ایندکس‌ها می‌شوند.

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

یکی از قدرتمندترین روش‌ها برای افزایش سرعت کوئری‌نویسی JSON، ایجاد ستون‌های محاسباتی (Computed Columns) است که مقادیر استخراج شده از JSON را ذخیره می‌کنند. سپس می‌توان این ستون‌ها را ایندکس‌گذاری کرد.

فرض کنید جدولی با یک ستون JSON به نام YourJsonColumn دارید. می‌توانید ستون محاسباتی برای استخراج یک ویژگی خاص (مثلاً FirstName) ایجاد کنید:

    ALTER TABLE YourTable
    ADD FirstName AS JSON_VALUE(YourJsonColumn, '$.FirstName');

سپس می‌توانید روی این ستون محاسباتی یک ایندکس غیرخوشه‌ای (Nonclustered Index) ایجاد کنید:

    CREATE NONCLUSTERED INDEX IX_YourTable_FirstName
    ON YourTable (FirstName);

با این کار، هنگام کوئری‌نویسی بر اساس FirstName، SQL Server می‌تواند از ایندکس ایجاد شده روی ستون محاسباتی بهره ببرد که به طور چشمگیری سرعت بازیابی داده‌ها را افزایش می‌دهد.

این روش به خصوص برای فیلتر کردن (WHERE clauses) و مرتب‌سازی (ORDER BY clauses) بر اساس مقادیر JSON مفید است.

نوع ستون محاسباتی: PERSISTED یا غیر PERSISTED

هنگام تعریف ستون‌های محاسباتی برای ایندکس‌گذاری JSON، می‌توان آنها را به صورت PERSISTED یا غیر PERSISTED ایجاد کرد.

• PERSISTED: این گزینه باعث می‌شود که SQL Server مقدار ستون محاسباتی را در دیسک ذخیره کند. هر بار که داده‌های ستون JSON اصلی تغییر می‌کند، مقدار ستون PERSISTED نیز به صورت خودکار به‌روز می‌شود. مزیت اصلی PERSISTED این است که نیازی به محاسبه مجدد مقدار در زمان کوئری نیست و عملکرد خواندن را به شدت بهبود می‌بخشد. همچنین ایندکس‌گذاری روی ستون‌های PERSISTED به طور کلی کارآمدتر است. اما این کار فضای ذخیره‌سازی بیشتری مصرف می‌کند و ممکن است بر عملکرد عملیات نوشتن (INSERT/UPDATE) تأثیر بگذارد.
• غیر PERSISTED: در این حالت، مقدار ستون محاسباتی در دیسک ذخیره نمی‌شود و هر بار که به آن دسترسی پیدا می‌شود، مقدار آن در زمان اجرا (runtime) محاسبه می‌شود. این روش فضای ذخیره‌سازی کمتری مصرف می‌کند اما برای کوئری‌هایی که به دفعات زیاد به این ستون دسترسی پیدا می‌کنند، ممکن است کندتر باشد. با این حال، می‌توان روی ستون‌های غیر PERSISTED نیز ایندکس ایجاد کرد، اما SQL Server باید مطمئن شود که تابع مورد استفاده (مانند JSON_VALUE()) قطعی (deterministic) است.

برای ایندکس‌گذاری روی ستون‌های محاسباتی که از JSON_VALUE() استفاده می‌کنند، معمولاً توصیه می‌شود که ستون به صورت PERSISTED تعریف شود. این امر اطمینان می‌دهد که مقادیر از قبل محاسبه شده و در دسترس ایندکس قرار دارند.

مثال ایجاد ستون محاسباتی PERSISTED و ایندکس آن:

    ALTER TABLE YourTable
    ADD FirstNamePersisted AS JSON_VALUE(YourJsonColumn, '$.FirstName') PERSISTED;

    CREATE NONCLUSTERED INDEX IX_YourTable_FirstNamePersisted
    ON YourTable (FirstNamePersisted);

در نظر گرفتن ایندکس‌های مبتنی بر XML برای JSON (رویکرد قدیمی)

قبل از معرفی توابع داخلی JSON در SQL Server 2016، برخی توسعه‌دهندگان از قابلیت‌های XML برای ایندکس‌گذاری داده‌های JSON استفاده می‌کردند. این رویکرد شامل تبدیل JSON به XML، سپس ایندکس‌گذاری XML بود.

    ALTER TABLE YourTable
    ADD JsonAsXml AS CAST(YourJsonColumn AS XML);

    CREATE PRIMARY XML INDEX PXML_YourTable_JsonAsXml
    ON YourTable (JsonAsXml);

این روش پیچیدگی زیادی به همراه داشت و از نظر عملکردی به اندازه ستون‌های محاسباتی مستقیم JSON بهینه نبود. با این حال، در محیط‌های قدیمی‌تر که پشتیبانی کامل JSON وجود نداشت، یک راه‌حل موقت محسوب می‌شد.

امروزه با وجود توابع قدرتمند JSON در SQL Server، استفاده از ایندکس‌های مبتنی بر XML برای JSON توصیه نمی‌شود. رویکرد ستون محاسباتی با JSON_VALUE() بسیار کارآمدتر و ساده‌تر است.

مقایسه عملکرد: بدون ایندکس در مقابل ستون محاسباتی

برای درک بهتر بهبود عملکرد، مثالی عملی را در نظر می‌گیریم. فرض کنید یک جدول با میلیون‌ها رکورد و یک ستون JSON دارید.

سناریو 1: کوئری بدون ستون محاسباتی و ایندکس

هنگامی که شما یک کوئری ساده برای استخراج یک مقدار از JSON بدون هیچ ایندکس یا ستون محاسباتی اجرا می‌کنید:

    SELECT COUNT(*)
    FROM YourTable
    WHERE JSON_VALUE(YourJsonColumn, '$.Status') = 'Active';

SQL Server مجبور است کل جدول را اسکان کند و برای هر ردیف تابع JSON_VALUE() را اجرا کند. این عملیات می‌تواند بسیار زمان‌بر باشد.

سناریو 2: کوئری با ستون محاسباتی و ایندکس

اگر یک ستون محاسباتی PERSISTED به نام StatusComputed برای استخراج $.Status ایجاد کرده و روی آن ایندکس گذاشته باشید:

    ALTER TABLE YourTable
    ADD StatusComputed AS JSON_VALUE(YourJsonColumn, '$.Status') PERSISTED;

    CREATE NONCLUSTERED INDEX IX_YourTable_StatusComputed
    ON YourTable (StatusComputed);

سپس کوئری قبلی را به این شکل تغییر می‌دهید:

    SELECT COUNT(*)
    FROM YourTable
    WHERE StatusComputed = 'Active';

در این حالت، SQL Server می‌تواند از ایندکس IX_YourTable_StatusComputed استفاده کند. ایندکس به سرعت ردیف‌هایی را که StatusComputed آنها برابر با ‘Active’ است پیدا می‌کند و به شدت زمان اجرای کوئری را کاهش می‌دهد.

نکات و ملاحظات برای بهینه‌سازی JSON

1. انتخاب ویژگی‌های مناسب برای ایندکس‌گذاری: فقط ویژگی‌هایی از JSON را ایندکس کنید که به طور مکرر در فیلترها، مرتب‌سازی‌ها یا اتصال‌ها استفاده می‌شوند. ایندکس‌های بیش از حد می‌توانند سربار عملیات نوشتن را افزایش دهند.
2. تأثیر تغییرات داده: به یاد داشته باشید که ستون‌های محاسباتی PERSISTED هنگام به‌روزرسانی ستون JSON اصلی، نیاز به به‌روزرسانی خود دارند که می‌تواند بر عملکرد UPDATE و INSERT تأثیر بگذارد.
3. اندازه JSON: برای اسناد JSON بسیار بزرگ، استخراج مقادیر می‌تواند به خودی خود کند باشد. سعی کنید ساختار JSON را بهینه‌سازی کنید و فقط داده‌های ضروری را در آن ذخیره کنید.
4. SQL Server 2025 و قابلیت‌های جدید: توجه داشته باشید که SQL Server 2025 و نسخه‌های بعدی احتمالاً شامل بهینه‌سازی‌های بومی بیشتری برای JSON و قابلیت ایندکس‌گذاری مستقیم‌تر خواهند بود. این مقاله بر روش‌های قبل از 2025 تمرکز دارد.
5. استفاده از JSON_QUERY() برای اشیاء/آرایه‌ها: تابع JSON_QUERY() برای استخراج اشیاء JSON یا آرایه‌ها استفاده می‌شود. اگر نیاز به استخراج یک زیر-شیء کامل دارید، از آن استفاده کنید. برای مثال:

   
               SELECT JSON_QUERY(YourJsonColumn, '$.Address') AS AddressObject
               FROM YourTable;
           

   این تابع برخلاف JSON_VALUE() یک رشته JSON معتبر را برمی‌گرداند و برای نمایش ساختارهای پیچیده‌تر مفید است.

با پیاده‌سازی این تکنیک‌ها، می‌توانید عملکرد کوئری‌نویسی JSON خود را در SQL Server، حتی در نسخه‌های قبل از 2025، به طور قابل توجهی بهبود بخشید. بهینه‌سازی صحیح می‌تواند تفاوت چشمگیری در پاسخگویی سیستم‌های شما ایجاد کند.