Операторы Spool в SQL Server могут оказывать как положительное, так и отрицательное влияние на производительность. Хотя они позволяют временно размещать отфильтрованные и преобразованные наборы результатов, они также могут приводить к повторной обработке данных и негативно сказываться на производительности при записи данных на диск в tempdb.

В этой статье мы рассмотрим технику, называемую “Разделение и Покорение”, которая может помочь улучшить производительность запросов без добавления постоянных индексов. Эта техника включает использование временных столовых таблиц для имитации операций Spool и оптимизации повторного использования данных.

Давайте рассмотрим пример запроса, который извлекает смещенные результаты для разных значков из определенного месяца данных:

WITH January2010Badges AS ( 
SELECT 
UserId,
Name,
Date
FROM 
dbo.Badges 
WHERE 
Date >= '2010-01-01' 
AND Date <= '2010-02-01' 
), Next10PopularQuestions AS ( 
SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM January2010Badges WHERE Name = 'Popular Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10NotableQuestions AS ( 
SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM January2010Badges WHERE Name = 'Notable Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10StellarQuestions AS ( 
SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM January2010Badges WHERE Name = 'Stellar Question' ORDER BY Date OFFSET 10 ROWS) t
)
SELECT UserId, Name FROM Next10PopularQuestions 
UNION ALL 
SELECT UserId, Name FROM Next10NotableQuestions
UNION ALL 
SELECT UserId, Name FROM Next10StellarQuestions

Хотя этот запрос использует общую таблицу выражений (CTE) для фильтрации данных и вычисления смещений, SQL Server не сохраняет результаты выражения January2010Badges в tempdb для повторного использования. В результате запросу необходимо повторно сканировать весь кластеризованный индекс таблицы dbo.Badges, что приводит к большому количеству логических чтений.

Чтобы оптимизировать этот запрос без добавления постоянных индексов, мы можем использовать технику “Разделение и Покорение”. Сначала мы создаем временную таблицу для хранения отфильтрованных результатов января 2010 года:

DROP TABLE IF EXISTS #January2010Badges;
CREATE TABLE #January2010Badges
(
UserId int,
Name nvarchar(40),
Date datetime
CONSTRAINT PK_NameDateUserId PRIMARY KEY CLUSTERED (Name,Date,UserId)
);
INSERT INTO #January2010Badges
SELECT
UserId,
Name,
Date
FROM 
dbo.Badges
WHERE 
Date >= '2010-01-01' 
AND Date <= '2010-02-01';

В этой временной таблице мы добавляем кластеризованный первичный ключ, который индексирует данные в порядке, удобном для фильтрации. Затем мы изменяем остальную часть запроса для чтения из временной таблицы:

WITH Next10PopularQuestions AS ( 
SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM #January2010Badges WHERE Name = 'Popular Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10NotableQuestions AS ( 
SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM #January2010Badges WHERE Name = 'Notable Question' ORDER BY Date OFFSET 10 ROWS) t
), Next10StellarQuestions AS ( 
SELECT TOP 10 * FROM (SELECT UserId, Name, Date FROM #January2010Badges WHERE Name = 'Stellar Question' ORDER BY Date OFFSET 10 ROWS) t
)
SELECT UserId, Name FROM Next10PopularQuestions 
UNION ALL 
SELECT UserId, Name FROM Next10NotableQuestions 
UNION ALL 
SELECT UserId, Name FROM Next10StellarQuestions

Используя временную столовую таблицу, SQL Server нужно сканировать кластеризованный индекс только один раз и сохранять данные во временной таблице. Последующие операторы SELECT затем выполняют поиск из этой меньшей таблицы, что снижает общее количество логических чтений и улучшает производительность.

Важно отметить, что эту технику следует использовать осторожно и по хорошим причинам. Запись и чтение избыточных данных из tempdb может привести к проблемам с конкуренцией. Однако, при правильной реализации, временные столовые таблицы могут значительно улучшить производительность определенных запросов.

В заключение, операторы Spool в SQL Server могут быть как благословением, так и проклятием для производительности запросов. Используя временные столовые таблицы и технику “Разделение и Покорение”, вы можете оптимизировать повторное использование данных и улучшить производительность запросов без добавления постоянных индексов. Помните, что следует использовать эту технику осмотрительно и учитывать конкретные требования ваших запросов.