جداول Derived Tables: افزایش کارایی و خوانایی کوئریها

افزایش کارایی و خوانایی کوئری‌ها با Derived Tables در SQL Server

جداول Derived Tables که به عنوان SubQuery در قسمت  `FROM` نیز شناخته می‌شوند، ابزاری قدرتمند در SQL Server برای بهبود ساختار، خوانایی و منطق کوئری‌های پیچیده هستند. این جداول موقتی در طول اجرای یک کوئری ایجاد شده و نتایج حاصل از یک زیرپرس‌وجو را به عنوان یک جدول قابل ارجاع در دسترس قرار می‌دهند. استفاده از Derived Tables به ویژه در سناریوهایی که نیاز به پردازش میانی داده‌ها پیش از پیوستن (JOIN) یا فیلتر نهایی دارید، کارآمد است.

مبانی و کاربرد Derived Tables

Derived Tables راهی برای ساختن کوئری‌های ماژولارتر ارائه می‌دهند. به جای نوشتن یک کوئری monolithic و پیچیده، می‌توانید بخش‌های مختلف منطق را به SubQuery های جداگانه تقسیم کنید که هر یک به عنوان یک جدول مجازی عمل می‌کند. این امر به افزایش خوانایی کد کمک شایانی می‌کند و نگهداری آن را آسان‌تر می‌سازد. ساختار کلی یک Derived Table به شرح زیر است:

این ساختار پایه‌ای برای ایجاد یک Derived Table است:

در این ساختار، خروجی `SELECT` داخلی به عنوان `DerivedTableName` نام‌گذاری شده و در کوئری بیرونی قابل استفاده است.

یک مثال ساده از کاربرد Derived Tables را در نظر بگیرید که اطلاعات محصول و دسته‌بندی را از جداول `Products` و `Categories` استخراج می‌کند:

SELECT
    dt.CategoryName,
    COUNT(dt.ProductName) AS ProductCount
FROM
    (SELECT p.ProductName, c.CategoryName
     FROM Products p
     JOIN Categories c ON p.CategoryID = c.CategoryID
     WHERE p.IsActive = 1) AS dt
GROUP BY
    dt.CategoryName;

در این مثال، Derived Tables عه`dt` ابتدا محصولات فعال را با دسته‌بندی‌هایشان ترکیب می‌کند و سپس کوئری بیرونی تعداد محصولات را در هر دسته‌بندی می‌شمارد.

افزایش خوانایی و پیچیدگی کوئری

یکی از مزایای اصلی Derived Tables، توانایی آن‌ها در شکستن منطق پیچیده به گام‌های ساده‌تر است. به جای تودرتو کردن چندین `JOIN` یا `WHERE` در یک کوئری واحد، می‌توانید هر گام میانی را در یک Derived Table جداگانه انجام دهید. این روش باعث می‌شود کوئری‌ها ساختاریافته‌تر و قابل فهم‌تر شوند، به خصوص زمانی که چندین عملیات `JOIN` و تجمیع (aggregation) وجود دارد.

پیش‌تجمیع داده‌ها (Pre-aggregation)

Derived Tables در سناریوهایی که نیاز به تجمیع داده‌ها پیش از پیوستن به جداول دیگر دارید، بسیار مفید هستند. این کار می‌تواند عملکرد کوئری را بهبود بخشد، زیرا حجم داده‌های ورودی برای `JOIN` را کاهش می‌دهد.

فرض کنید می‌خواهید فروش کل را برای هر مشتری در یک دوره خاص محاسبه کرده و سپس آن را با اطلاعات مشتری ادغام کنید:

SELECT
    c.CustomerName,
    dt.TotalSales
FROM
    Customers c
JOIN
    (SELECT CustomerID, SUM(OrderTotal) AS TotalSales
     FROM Orders
     WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
     GROUP BY CustomerID) AS dt
ON c.CustomerID = dt.CustomerID
WHERE dt.TotalSales > 1000;

در اینجا، Derived Table عه `dt` ابتدا مجموع فروش هر مشتری را در سال 2023 محاسبه می‌کند. سپس، این نتایج با جدول `Customers` پیوست می‌شوند تا نام مشتریان نیز نمایش داده شود، و تنها مشتریانی با فروش بیش از 1000 دلار فیلتر می‌شوند.

صفحه‌بندی و رتبه‌بندی نتایج (Paging and Ranking)

Derived Tables می‌توانند برای پیاده‌سازی منطق پیچیده صفحه‌بندی یا رتبه‌بندی داده‌ها، مانند یافتن N رکورد برتر یا پیاده‌سازی یک سیستم صفحه‌بندی سفارشی، استفاده شوند. این کاربرد به ویژه با توابع پنجره‌ای (Window Functions) مانند `ROW_NUMBER()`، `RANK()` و `DENSE_RANK()` قدرتمند می‌شود.

برای مثال، اگر بخواهید 3 محصول برتر پرفروش از هر دسته‌بندی را پیدا کنید:

SELECT
    CategoryName,
    ProductName,
    SalesRank
FROM
    (SELECT
        c.CategoryName,
        p.ProductName,
        SUM(oi.Quantity * oi.UnitPrice) AS TotalSales,
        ROW_NUMBER() OVER (PARTITION BY c.CategoryName ORDER BY SUM(oi.Quantity * oi.UnitPrice) DESC) AS SalesRank
     FROM Products p
     JOIN Categories c ON p.CategoryID = c.CategoryID
     JOIN OrderItems oi ON p.ProductID = oi.ProductID
     GROUP BY c.CategoryName, p.ProductName) AS RankedProducts
WHERE SalesRank <= 3
ORDER BY CategoryName, SalesRank;

در این کوئری، `RankedProducts` یک Derived Table است که ابتدا رتبه فروش محصولات را در هر دسته‌بندی محاسبه می‌کند. سپس کوئری بیرونی فقط رتبه‌های 1 تا 3 را برای هر دسته‌بندی انتخاب می‌کند.

استفاده در دستورات UPDATE و DELETE

Derived Tables می‌توانند در دستورات `UPDATE` و `DELETE` برای فیلتر کردن یا تعیین رکوردهایی که باید تغییر یا حذف شوند، به کار روند. این قابلیت زمانی مفید است که نیاز به استفاده از یک کوئری `SELECT` پیچیده برای شناسایی رکوردهای هدف داشته باشید.

مثلاً، برای به‌روزرسانی وضعیت سفارشاتی که توسط مشتریان خاصی انجام شده‌اند:

UPDATE o
SET o.OrderStatus = 'Completed'
FROM Orders o
JOIN (SELECT CustomerID FROM Customers WHERE CustomerType = 'Premium') AS PremiumCustomers
ON o.CustomerID = PremiumCustomers.CustomerID
WHERE o.OrderStatus = 'Processing';

در این مثال، Derived Table `PremiumCustomers` ابتدا مشتریان “Premium” را شناسایی می‌کند. سپس دستور `UPDATE`، وضعیت سفارشات در حال پردازش این مشتریان را به “Completed” تغییر می‌دهد.

Derived Tables در مقابل CTE ها (Common Table Expressions)

Derived Tables و CTE ها (Common Table Expressions) هر دو برای شکستن کوئری‌های پیچیده به بخش‌های منطقی‌تر استفاده می‌شوند. تفاوت اصلی در نحوه تعریف و قابلیت استفاده مجدد آن‌ها است.

یک CTE با کلمه کلیدی `WITH` تعریف می‌شود و می‌تواند چندین بار در همان کوئری ارجاع داده شود، که قابلیت خوانایی و سازماندهی کد را بیشتر می‌کند. ساختار CTE به شرح زیر است:

در حالی که یک Derived Table تنها یک بار در بند `FROM` تعریف می‌شود و نمی‌تواند در قسمت‌های دیگر کوئری (به جز کوئری بیرونی خود) مجدداً استفاده شود. برای اکثر سناریوها، CTE ها به دلیل قابلیت خوانایی و سازماندهی بهتر، اغلب ترجیح داده می‌شوند، به خصوص اگر نیاز به ارجاع چندین باره به یک زیرپرس‌وجو در همان کوئری داشته باشید. با این حال، Derived Tables همچنان ابزاری ارزشمند هستند و در بسیاری از موارد به سادگی و کارایی لازم را ارائه می‌دهند.

ملاحظات عملکردی (Performance Considerations)

هم Derived Tables و هم CTE ها معمولاً تأثیر مستقیمی بر عملکرد کوئری ندارند، زیرا بهینه‌ساز کوئری SQL Server هر دو را به روش‌های مشابهی پردازش می‌کند. عملکرد نهایی بیشتر به طراحی کوئری داخلی، ایندکس‌گذاری مناسب جداول زیرین و ساختار کلی پایگاه داده بستگی دارد. با این حال، در برخی موارد، استفاده نادرست یا بیش از حد از Derived Tables یا CTE ها می‌تواند منجر به کوئری‌های غیربهینه شود. همیشه توصیه می‌شود از `EXPLAIN PLAN` یا `Actual Execution Plan` برای تحلیل عملکرد کوئری‌های خود استفاده کنید.

نتیجه‌گیری

Derived Tables ابزاری قدرتمند برای بهبود خوانایی، سازماندهی و مدیریت کوئری‌های پیچیده در SQL Server هستند. چه برای پیش‌تجمیع داده‌ها، صفحه‌بندی، رتبه‌بندی یا ساده‌سازی `JOIN`های پیچیده، درک و استفاده صحیح از Derived Tables می‌تواند به شما در نوشتن کوئری‌های کارآمدتر و قابل نگهداری‌تر کمک کند. با وجود قابلیت‌های مشابه CTE ها، Derived Tables همچنان جایگاه خود را در جعبه ابزار توسعه‌دهندگان SQL حفظ کرده‌اند. با ترکیب این تکنیک‌ها و استفاده از بهترین شیوه‌ها، می‌توانید به حداکثر بهره‌وری و کارایی در تحلیل داده‌های خود دست یابید.