بهبود عملکرد کوئری‌ها در SQL Server با توابع پنجره

بهبود عملکرد کوئری‌ها در SQL Server با توابع پنجره (Window Functions)

توابع پنجره (Window Functions) در SQL Server مدت‌هاست که وجود دارند. این توابع، مقداری را روی مجموعه‌ای از ردیف‌ها که “پنجره” نامیده می‌شوند، محاسبه می‌کنند. یک پنجره می‌تواند با استفاده از عبارت PARTITION BY و ORDER BY تعریف شود، به این شکل:

PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID

یا به عبارت دیگر، گروهی از ردیف‌ها که به ردیف فعلی مرتبط هستند. این توابع برای اولین بار در SQL Server 2005 معرفی شدند. در ابتدا، بسیاری از توسعه‌دهندگان از آن‌ها استفاده نمی‌کردند، زیرا با کارکردشان آشنا نبودند. اما بعدها، به دلیل کارایی بالا در حل مسائل پیچیده با کدی ساده‌تر، در بین توسعه‌دهندگان بسیار محبوب شدند. علاوه بر این، توابع پنجره عملکرد بهتری نسبت به روش‌های سنتی مانند ساب‌کوئری‌ها (Subqueries) و عبارات جدول مشترک (CTEs) ارائه می‌دهند.

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

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

https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

توابع پنجره (Window Functions) شامل سه دسته اصلی هستند:

• توابع رتبه‌بندی (Ranking Functions) مانند ROW_NUMBER()، RANK()، DENSE_RANK()، و NTILE().
• توابع تحلیلی (Analytic Functions) مانند LEAD()، LAG()، FIRST_VALUE()، LAST_VALUE()، CUME_DIST()، PERCENT_RANK() و PERCENTILE_CONT().
• توابع تجمیعی (Aggregate Functions) مانند SUM()، AVG()، MIN()، MAX()، و COUNT() که با عبارت OVER() استفاده می‌شوند. شما همچنین می‌توانید عملیات را روی زیرمجموعه‌ای از ردیف‌ها در یک گروه انجام دهید. به عنوان مثال، با استفاده از فریم‌های پنجره:

  ROWS BETWEEN ... AND ...

  که محدوده ردیف‌ها را مشخص می‌کند.

اکنون به مثالی از ROW_NUMBER() نگاهی می‌اندازیم. این تابع برای اختصاص یک شماره ردیف منحصر به فرد به هر ردیف در یک پنجره استفاده می‌شود. ابتدا، بیایید نگاهی به داده‌های نمونه خود در جدول Sales.SalesOrderDetail از AdventureWorks بیندازیم:

USE AdventureWorks2019;
GO

SELECT 
    SalesOrderID, 
    SalesOrderDetailID, 
    CarrierTrackingNumber, 
    OrderQty, 
    ProductID, 
    UnitPrice, 
    LineTotal
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID;

خروجی بالا ردیف‌های جدول Sales.SalesOrderDetail را نشان می‌دهد. اکنون، برای هر SalesOrderID، می‌خواهیم ردیف‌ها را بر اساس SalesOrderDetailID رتبه‌بندی کنیم. از تابع ROW_NUMBER() با عبارت OVER() استفاده می‌کنیم که بخش‌بندی بر اساس SalesOrderID و مرتب‌سازی بر اساس SalesOrderDetailID را تعریف می‌کند:

SELECT 
    SalesOrderID, 
    SalesOrderDetailID, 
    CarrierTrackingNumber, 
    OrderQty, 
    ProductID, 
    UnitPrice, 
    LineTotal,
    ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID) AS RowNum
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID;

در خروجی این کوئری، ستون جدید RowNum را مشاهده می‌کنید که یک شماره ردیف منحصر به فرد را به هر ردیف در هر گروه SalesOrderID اختصاص می‌دهد. وقتی SalesOrderID تغییر می‌کند، شماره ردیف مجدداً از 1 شروع می‌شود.

اکنون، بیایید RANK() و DENSE_RANK() را بررسی کنیم. این توابع نیز برای رتبه‌بندی ردیف‌ها استفاده می‌شوند، اما با تفاوت‌های ظریفی. RANK() در صورت وجود مقادیر یکسان، رتبه‌های مشابهی را به آن‌ها اختصاص می‌دهد و سپس رتبه بعدی را از دست می‌دهد. DENSE_RANK() نیز رتبه‌های مشابه را به مقادیر یکسان اختصاص می‌دهد اما رتبه بعدی را از دست نمی‌دهد و یک رتبه متوالی اختصاص می‌دهد.

SELECT 
    SalesOrderID, 
    SalesOrderDetailID, 
    CarrierTrackingNumber, 
    OrderQty, 
    ProductID, 
    UnitPrice, 
    LineTotal,
    ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY OrderQty DESC) AS RowNumber,
    RANK() OVER(PARTITION BY SalesOrderID ORDER BY OrderQty DESC) AS RankNum,
    DENSE_RANK() OVER(PARTITION BY SalesOrderID ORDER BY OrderQty DESC) AS DenseRankNum
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID;

در این مثال، رتبه‌بندی بر اساس OrderQty DESC انجام شده است تا تفاوت بین ROW_NUMBER، RANK و DENSE_RANK مشخص شود. می‌توانید ببینید که چگونه RANK با وجود مقادیر یکسان، یک پرش در رتبه‌ها ایجاد می‌کند، در حالی که DENSE_RANK این پرش را ندارد.

تابع NTILE(n) برای تقسیم ردیف‌ها به n گروه تقریباً برابر استفاده می‌شود. این تابع در سناریوهایی که نیاز به تقسیم داده‌ها به تعداد مشخصی از دسته‌ها دارید، بسیار مفید است.

SELECT 
    SalesOrderID, 
    SalesOrderDetailID, 
    OrderQty, 
    NTILE(4) OVER (PARTITION BY SalesOrderID ORDER BY OrderQty DESC) AS NtileGroup
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderID, NtileGroup;

در این کوئری، NTILE(4) ردیف‌های هر SalesOrderID را به 4 گروه تقسیم می‌کند.

اکنون به بخش مهم عملکرد توابع پنجره می‌رسیم. توابع پنجره معمولاً عملکرد بهتری نسبت به روش‌های سنتی (مانند ساب‌کوئری‌ها) برای انجام عملیات مشابه دارند. این بهبود عملکرد به دلیل بهینه‌سازی‌های داخلی SQL Server در پردازش داده‌ها در یک “پنجره” واحد است. به جای پیمایش چندباره داده‌ها، توابع پنجره داده‌ها را تنها یک بار پیمایش می‌کنند.

بیایید مقایسه‌ای بین ROW_NUMBER() و رویکرد سنتی با استفاده از TOP و ORDER BY انجام دهیم. هدف این است که برای هر SalesOrderID، ردیفی با SalesOrderDetailID کمترین را پیدا کنیم.

روش 1: استفاده از ROW_NUMBER()

SELECT 
    SalesOrderID, 
    SalesOrderDetailID, 
    OrderQty, 
    UnitPrice
FROM 
    (
        SELECT 
            SalesOrderID, 
            SalesOrderDetailID, 
            OrderQty, 
            UnitPrice,
            ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID ASC) as RN
        FROM Sales.SalesOrderDetail
    ) AS a
WHERE RN = 1;

روش 2: استفاده از ساب‌کوئری  (Correlated Subquery)

SELECT 
    s1.SalesOrderID, 
    s1.SalesOrderDetailID, 
    s1.OrderQty, 
    s1.UnitPrice
FROM Sales.SalesOrderDetail AS s1
WHERE s1.SalesOrderDetailID = 
    (SELECT MIN(s2.SalesOrderDetailID)
     FROM Sales.SalesOrderDetail AS s2
     WHERE s1.SalesOrderID = s2.SalesOrderID);

روش 3: استفاده از CROSS APPLY با TOP 1

SELECT 
    s1.SalesOrderID, 
    s1.SalesOrderDetailID, 
    s1.OrderQty, 
    s1.UnitPrice
FROM 
    (SELECT DISTINCT SalesOrderID FROM Sales.SalesOrderDetail) AS s1
CROSS APPLY
    (SELECT TOP 1 SalesOrderDetailID, OrderQty, UnitPrice
     FROM Sales.SalesOrderDetail AS s2
     WHERE s1.SalesOrderID = s2.SalesOrderID
     ORDER BY SalesOrderDetailID ASC) AS s3;

هنگام اجرای این سه کوئری در SQL Server Management Studio (SSMS) با Include Actual Execution Plan فعال، می‌توانیم تفاوت‌های عملکردی را مشاهده کنیم.

برای کوئری 1 (با استفاده از ROW_NUMBER()):

طرح اجرایی (Execution Plan) برای این کوئری نشان‌دهنده یک Clustered Index Scan یا Seek در جدول اصلی، و سپس یک Window Spool یا Segment و Sort است. این عملیات برای محاسبه توابع پنجره مورد نیاز است. هزینه‌ی این کوئری اغلب پایین‌تر از روش‌های سنتی است، زیرا داده‌ها را فقط یک بار پیمایش می‌کند و عملیات مرتب‌سازی را به صورت بهینه انجام می‌دهد. به عنوان مثال، ممکن است Cost (هزینه) آن حدود 10% باشد.

برای کوئری 2 (با استفاده از ساب‌کوئری ):

طرح اجرایی برای این کوئری اغلب شامل یک Nested Loops (Left Outer Join) است. این به این معنی است که برای هر ردیف از جدول بیرونی، ساب‌کوئری داخلی اجرا می‌شود که می‌تواند منجر به پردازش‌های مکرر و افزایش قابل توجه CPU time و I/O شود. Cost این کوئری ممکن است به طور قابل توجهی بالاتر باشد، به عنوان مثال حدود 45%، بسته به اندازه جدول و وجود ایندکس‌ها.

برای کوئری 3 (با استفاده از CROSS APPLY با TOP 1):

طرح اجرایی برای این کوئری معمولاً شامل یک Nested Loops (Inner Join) است که در آن TOP 1 در بخش APPLY بهینه می‌شود. این روش می‌تواند از ساب‌کوئری بهتر عمل کند، اما همچنان ممکن است هزینه‌ای بیشتر از توابع پنجره داشته باشد، به عنوان مثال حدود 45%.

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

برای بررسی دقیق‌تر، می‌توانیم از SET STATISTICS IO ON و SET STATISTICS TIME ON استفاده کنیم تا معیارهای ورودی/خروجی و زمان پردازش را مقایسه کنیم. معمولاً توابع پنجره، صفحات دیسک کمتری را می‌خوانند و زمان CPU کمتری مصرف می‌کنند.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

-- Query 1: ROW_NUMBER()
SELECT 
    SalesOrderID, 
    SalesOrderDetailID, 
    OrderQty, 
    UnitPrice
FROM 
    (
        SELECT 
            SalesOrderID, 
            SalesOrderDetailID, 
            OrderQty, 
            UnitPrice,
            ROW_NUMBER() OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderDetailID ASC) as RN
        FROM Sales.SalesOrderDetail
    ) AS a
WHERE RN = 1;

-- Query 2: Correlated Subquery
SELECT 
    s1.SalesOrderID, 
    s1.SalesOrderDetailID, 
    s1.OrderQty, 
    s1.UnitPrice
FROM Sales.SalesOrderDetail AS s1
WHERE s1.SalesOrderDetailID = 
    (SELECT MIN(s2.SalesOrderDetailID)
     FROM Sales.SalesOrderDetail AS s2
     WHERE s1.SalesOrderID = s2.SalesOrderID);

-- Query 3: CROSS APPLY with TOP 1
SELECT 
    s1.SalesOrderID, 
    s1.SalesOrderDetailID, 
    s1.OrderQty, 
    s1.UnitPrice
FROM 
    (SELECT DISTINCT SalesOrderID FROM Sales.SalesOrderDetail) AS s1
CROSS APPLY
    (SELECT TOP 1 SalesOrderDetailID, OrderQty, UnitPrice
     FROM Sales.SalesOrderDetail AS s2
     WHERE s1.SalesOrderID = s2.SalesOrderID
     ORDER BY SalesOrderDetailID ASC) AS s3;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
GO

با تجزیه و تحلیل خروجی STATISTICS IO و STATISTICS TIME، به طور معمول مشاهده خواهیم کرد که کوئری استفاده‌کننده از ROW_NUMBER() تعداد Logical Reads و زمان CPU کمتری را ثبت می‌کند که نشان‌دهنده کارایی بالای آن در مقایسه با روش‌های ساب‌کوئری و CROSS APPLY TOP 1 است. این نشان‌دهنده اهمیت توابع پنجره در بهینه‌سازی عملکرد SQL Server برای کوئری‌های پیچیده است.

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