Пример выполнен на базе данных AdventureWorks (см. Примеры БД MS SQL Server); основная таблица – Sales.SalesOrderDetail.
Перед выполнением примера объем данных таблицы Sales.SalesOrderDetail увеличен в несколько раз (с 121 318 до 3 429 928 записей; пример скрипта есть в Примерах INSERT INTO).
Например, есть задача отобрать суммы заказов, введенных в базу в 2015-м году, по определенным типам спец. предложений и категориям продуктов, примерно такой отчет:
Для отчета нужны таблицы:
Sales.SalesOrderDetail – детали заказа (основная таблица, 3.5 млн. записей; из нее нужны только только те заказы, которые были введены в 2015-м году)
Sales.SalesOrderHeader – заголовок заказа;
Sales.SpecialOffer – справочник спец. предложений (нужны только заказы "без скидок" и "по новым продуктам");
Production.Product – справочник продуктов;
Production.ProductSubcategory – справочник подкатегорий продуктов;
Production.ProductCategory – справочник категорий продуктов (нужны только котегории "Одежда" и "Компоненты")
Традиционный SELECT отбора данных для отчета – результат 21 840 записей, выданных за 21 секунду
(SQL для копирования в конце статьи):
Оптимизация запроса с помощью CTE-выражений – результат 21 840 записей, выданных за 16 секунд
(SQL для копирования в конце):
Каждое CTE-выражение содержит только требуемые для отчета поля и записи, это оптимально для JOIN-ов
SQL-1:
USE AdventureWorks
GO
SELECT
soh.SalesOrderNumber
,sum(sod.OrderQty) AS OrderQty
,sum(sod.LineTotal) AS LineTotal
,so.Type AS SpecOrderType
,psc.Name AS ProductSubCategory
,pc.Name AS ProductCategory
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Sales.SpecialOffer so ON so.SpecialOfferID = sod.SpecialOfferID
INNER JOIN Production.Product p ON p.ProductID = sod.ProductID
INNER JOIN Production.ProductSubcategory psc ON psc.ProductSubcategoryID = p.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON pc.ProductCategoryID = psc.ProductCategoryID
WHERE
sod.ModifiedDate >= '2015-01-01' AND sod.ModifiedDate <= '2015-12-31' AND --ââåäåíû â áàçó â 2015-ì ãîäó
(pc.ProductCategoryID = 3 OR pc.ProductCategoryID = 2) AND --Îäåæäà è Êîìïîíåíòû
(so.Type = 'No Discount' OR so.Type = 'New Product') --áåç ñêèäîê èëè íîâûé ïðîäóêò
GROUP BY
soh.SalesOrderNumber
,so.Type
,psc.Name
,pc.Name
SQL-2:
USE AdventureWorks
GO
WITH
SalesOrder (SalesOrderNumber, LineTotal, OrderQty, SpecOrderType, ProductID) AS
(
SELECT
soh.SalesOrderNumber
,sod.LineTotal
,sod.OrderQty
,so.Type AS SpecOrderType
,sod.ProductID
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh ON soh.SalesOrderID = sod.SalesOrderID
INNER JOIN Sales.SpecialOffer so ON so.SpecialOfferID = sod.SpecialOfferID
WHERE sod.ModifiedDate >= '2015-01-01' AND sod.ModifiedDate <= '2015-12-31' AND
(so.Type = 'No Discount' OR so.Type = 'New Product')
), --SalesOrder
Product (ProductID, ProductSubCategory, ProductCategory) AS
(
SELECT
p.ProductID
,psc.Name AS ProductSubCategory
,pc.Name AS ProductCategory
FROM Production.Product p
INNER JOIN Production.ProductSubcategory psc ON psc.ProductSubcategoryID = p.ProductSubcategoryID
INNER JOIN Production.ProductCategory pc ON pc.ProductCategoryID = psc.ProductCategoryID
WHERE pc.ProductCategoryID = 3 OR pc.ProductCategoryID = 2
) --Product
SELECT
SalesOrder.SalesOrderNumber
,sum(SalesOrder.LineTotal) AS LineTotal
,sum(SalesOrder.OrderQty) AS OrderQty
,SpecOrderType
,Product.ProductSubCategory
,Product.ProductCategory
FROM SalesOrder
INNER JOIN Product ON Product.ProductID = SalesOrder.ProductID
GROUP BY
SalesOrder.SalesOrderNumber
,SpecOrderType
,Product.ProductSubCategory
,Product.ProductCategory