SQL Server N سطر برتر APPLY و ROW_NUMBER راهنمای بهینه سازی

بازگشت N سطر برتر در SQL Server: راهنمای جامع APPLY و ROW_NUMBER برای بهینه‌سازی پرس‌وجوها

بازیابی N سطر برتر از یک جدول، یک وظیفه رایج و حیاتی در SQL Server است که بهینه‌سازی آن می‌تواند عملکرد کلی سیستم را بهبود بخشد. روش‌های مختلفی برای انجام این کار وجود دارد، از جمله استفاده از عبارت TOP، تابع ROW_NUMBER() یا اپراتور APPLY. این مقاله به بررسی عمیق و کاربردی نحوه استفاده از اپراتور APPLY و تابع پنجره‌ای ROW_NUMBER() برای بازگرداندن سطرهای برتر از یک جدول در سناریوهای مختلف می‌پردازد تا به شما در نوشتن پرس‌وجوهای بهینه‌تر کمک کند.

برای شروع و نمایش مثال‌های عملی، یک پایگاه داده و جدول نمونه ایجاد می‌کنیم. اسکریپت SQL زیر یک پایگاه داده به نام TestDB، یک جدول به نام Products با ستون‌های ProductID, ProductName, CategoryID و Price ایجاد کرده و آن را با داده‌های نمونه پر می‌کند. این ساختار داده به ما امکان می‌دهد تا سناریوهای مختلف بازیابی سطرها را با سهولت آزمایش کنیم.

USE master;
GO
IF DB_ID('TestDB') IS NOT NULL
    DROP DATABASE TestDB;
GO
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
CREATE TABLE Products (
    ProductID INT IDENTITY(1,1) PRIMARY KEY,
    ProductName NVARCHAR(100),
    CategoryID INT,
    Price DECIMAL(10, 2)
);
GO
INSERT INTO Products (ProductName, CategoryID, Price) VALUES
('Laptop', 1, 1200.00),
('Desktop', 1, 1500.00),
('Monitor', 1, 300.00),
('Keyboard', 2, 75.00),
('Mouse', 2, 50.00),
('Printer', 2, 200.00),
('Smartphone', 3, 800.00),
('Tablet', 3, 500.00),
('Smartwatch', 3, 250.00),
('Headphones', 2, 120.00),
('Webcam', 2, 60.00),
('External SSD', 1, 180.00),
('Speaker', 3, 150.00),
('Router', 1, 90.00);
GO

اپراتور APPLY برای فراخوانی یک تابع با مقادیر جدولی (table-valued function) برای هر سطر بازگردانده شده توسط یک عبارت جدول خارجی استفاده می‌شود. این اپراتور عملکردی مشابه JOIN دارد، اما مزیت اصلی آن زمانی آشکار می‌شود که سمت راست اپراتور (table-valued function) به ستون‌هایی از سمت چپ (outer table expression) وابسته باشد. دو نوع اصلی از APPLY وجود دارد که هر کدام کاربردهای خاص خود را در بازیابی داده‌ها دارند.

CROSS APPLY

CROSS APPLY تنها سطرهایی را از عبارت جدول خارجی بازمی‌گرداند که یک مجموعه نتیجه غیرخالی از تابع با مقادیر جدولی تولید می‌کنند. این اپراتور در عملکرد و نتیجه‌دهی بسیار شبیه به یک INNER JOIN است، اما انعطاف‌پذیری بیشتری را در کار با توابع جدولی فراهم می‌کند.

فرض کنید می‌خواهیم 2 محصول گران‌قیمت برتر در هر دسته را پیدا کنیم. می‌توانیم این کار را با استفاده از CROSS APPLY و یک زیرپرس‌وجو که برای هر CategoryID به صورت جداگانه اجرا می‌شود، به سادگی انجام دهیم.

SELECT
    p.CategoryID,
    p.ProductName,
    p.Price
FROM
    (SELECT DISTINCT CategoryID FROM Products) AS c
CROSS APPLY
    (SELECT TOP 2 ProductName, Price
     FROM Products
     WHERE CategoryID = c.CategoryID
     ORDER BY Price DESC) AS p;
GO

OUTER APPLY

OUTER APPLY تمام سطرها را از عبارت جدول خارجی بازمی‌گرداند، صرف نظر از اینکه تابع با مقادیر جدولی نتیجه‌ای تولید کند یا خیر. اگر تابع با مقادیر جدولی برای یک سطر خاص نتیجه‌ای تولید نکند، ستون‌های بازگردانده شده از آن تابع NULL خواهند بود. این رفتار شبیه به یک LEFT OUTER JOIN است.

بیایید مثال قبلی را تغییر دهیم تا دسته‌ها را حتی اگر 2 محصول برتر نداشته باشند، یا اگر یک دسته کمتر از 2 محصول داشته باشد، نمایش دهد. برای سادگی، ما فرض می‌کنیم که ممکن است دسته‌هایی وجود داشته باشند که هیچ محصولی ندارند (اگرچه جدول Products ما در حال حاضر چنین دسته‌هایی ندارد). این مثال به خوبی رفتار OUTER APPLY را نشان می‌دهد.

SELECT
    c.CategoryID,
    p.ProductName,
    p.Price
FROM
    (SELECT DISTINCT CategoryID FROM Products) AS c
OUTER APPLY
    (SELECT TOP 2 ProductName, Price
     FROM Products
     WHERE CategoryID = c.CategoryID
     ORDER BY Price DESC) AS p;
GO

تابع ROW_NUMBER() یک تابع پنجره‌ای (window function) قدرتمند است که یک عدد ترتیبی منحصر به فرد را به هر سطر در یک پارتیشن خاص از مجموعه نتایج اختصاص می‌دهد. شماره‌گذاری از 1 برای اولین سطر در هر پارتیشن آغاز می‌شود و برای استفاده از آن، حتماً باید یک عبارت ORDER BY در داخل عبارت OVER() آن مشخص شود تا ترتیب شماره‌گذاری سطرها به درستی تعیین گردد.

برای یافتن N محصول برتر در هر دسته با استفاده از ROW_NUMBER()، ابتدا داده‌ها را بر اساس CategoryID پارتیشن‌بندی می‌کنیم و سپس بر اساس Price به ترتیب نزولی مرتب می‌کنیم. پس از اختصاص شماره سطر با استفاده از RowNum، می‌توانیم با یک شرط ساده فیلتر کنیم تا تنها سطرهایی را نگه داریم که شماره سطر آن‌ها کمتر یا مساوی N مورد نظر ما باشد.

WITH RankedProducts AS (
    SELECT
        ProductID,
        ProductName,
        CategoryID,
        Price,
        ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Price DESC) AS RowNum
    FROM
        Products
)
SELECT
    ProductID,
    ProductName,
    CategoryID,
    Price
FROM
    RankedProducts
WHERE
    RowNum <= 2; -- Get top 2 products per category
GO

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

هر دو اپراتور APPLY و تابع ROW_NUMBER() ابزارهای قدرتمندی برای بازیابی N سطر برتر در هر گروه در SQL Server هستند. با این حال، عملکرد آن‌ها می‌تواند بر اساس فاکتورهای متعددی مانند حجم داده، استراتژی ایندکس‌گذاری و پیچیدگی کلی پرس‌وجو به طور قابل توجهی متفاوت باشد.

اپراتور APPLY: این اپراتور اغلب زمانی که زیرپرس‌وجو یا تابع با مقادیر جدولی بسیار انتخابی باشد (یعنی تعداد کمی سطر را برگرداند) عملکرد خوبی از خود نشان می‌دهد. با این حال، اگر پرس‌وجوی بیرونی تعداد زیادی سطر را بازگرداند و پرس‌وجوی داخلی پیچیده باشد یا به درستی بهینه نشده باشد، می‌تواند کارایی کمتری داشته باشد. بهینه‌ساز SQL Server ممکن است یک nested loop join را انتخاب کند که در آن پرس‌وجوی داخلی برای هر سطر از پرس‌وجوی بیرونی اجرا می‌شود. ایندکس‌گذاری مناسب روی ستون‌های کلیدی مانند CategoryID و Price برای بهینه‌سازی عملکرد این روش بسیار حیاتی است.

تابع ROW_NUMBER(): به طور کلی، استفاده از ROW_NUMBER() در ترکیب با یک CTE (Common Table Expression) یا یک زیرپرس‌وجو، به ویژه با مجموعه‌های داده بزرگ، عملکرد خوبی از خود نشان می‌دهد. موتور پایگاه داده می‌تواند داده‌ها را یک بار به طور کارآمد مرتب و پارتیشن‌بندی کند. با این حال، استفاده از یک عبارت ORDER BY بزرگ روی ستون ایندکس‌نشده در داخل عبارت OVER() می‌تواند منجر به عملیات مرتب‌سازی سنگین و مصرف بالای منابع شود که بر عملکرد کلی تأثیر منفی می‌گذارد. بنابراین، اطمینان از ایندکس‌گذاری صحیح ستون‌هایی که در عبارت‌های PARTITION BY و ORDER BY استفاده می‌شوند، برای دستیابی به بهترین کارایی ضروری است.