Как не потерять производительность при секционировании в MS SQL

Постановка задачи

Рассмотрим достаточно распространенную ситуацию. Имеется огромная таблица примерно следующей структуры:

CREATE TABLE SomeOperations (
  OperationId bigint NOT NULL,
  OperDate datetime NOT NULL,
  SomeDataMultiplyColumns nvarchar(max)
)

Нам интересно только то, что уникальный ключ записей в таблице - OperationId и каждая операция имеет дату и, возможно, время. Ворох остальных полей в таблице я условно объединил в одно поле SomeDataMultiplyColumns. Так же будем считать, что данные в таблицу попадают совсем не обязательно по возрастанию OperDate и вполне могут добавляться задним числом.

Когда такая таблица разрастается, возникает вполне резонное желание разбить её на секции (разделы, partitions). Разбивать по OperationId можно, но очень не удобно. Намного чаще такая таблица разбивается по дате операции, например, по годам. То есть, секционирование выглядит, примерно так (создание файловых групп и файлов опущено):

CREATE PARTITION FUNCTION PF_SomeOperations_OperDate (datetime)  
AS RANGE RIGHT FOR VALUES (
  '2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01',  
  '2023-05-01', '2023-06-01', '2023-07-01', '2023-08-01',   
  '2023-09-01', '2023-10-01', '2023-11-01', '2023-12-01')
GO
CREATE PARTITION SCHEME PS_SomeOperations
AS PARTITION PF_SomeOperations_OperDate TO (
  SomeOperations_FG_ARH,
  SomeOperations_FG01, SomeOperations_FG02, SomeOperations_FG03,
  SomeOperations_FG04, SomeOperations_FG05, SomeOperations_FG06,
  SomeOperations_FG07, SomeOperations_FG08, SomeOperations_FG09,
  SomeOperations_FG10, SomeOperations_FG11, SomeOperations_FG12)
GO
ALTER TABLE SomeOperations
  ADD CONSTRAINT PK_SomeOperations PRIMARY KEY CLUSTERED (
	OperationId ASC, OperDate ASC
) ON PS_SomeOperations(OperDate)
GO

Теперь, мы можем сделать множество индексов по нужным нам полям, но обязательно содержащих OperDate, чтобы сервер мог понять, по каким именно секциям следует производить поиск.

Описание проблемы

Если бы изначально архитектура БД предусматривала секционирование таблицы SomeOperations, то все ссылки на эту таблицу содержали бы не только OperationId, но и OperDate. Но так как об этом не подумали вовремя, то это сделано не было. В результате имеем множество ссылок на OperationId без OperDate в целом ряде таблиц.

Можно производить глобальный рефакторинг БД. И даже, возможно, нужно. Но это долго и не всегда имеет смысл.

Нет ничего более постоянного, чем временное

При соединении (JOIN) с таблицей SomeOperations по OperationId миллион искомых строк выбирался и заливался во временную таблицу, примерно, 100 секунд на 8 ядрах (OPTION(MAXDOP 8)).

При соединении (JOIN) с таблицей SomeOperations по OperationId и OperDate миллион искомых строк выбирался и заливался во временную таблицу, примерно, 5 секунд на 8 ядрах. Разница очень существенная. Но откуда взять OperDate?

И тут мы вспоминаем о том, что MS SQL поддерживает COLUMNSTORE индексы. Строго говоря, это не совсем индекс, а колоночное хранилище, автоматически обновляемое при добавлении или модификации записей в связанной таблице. Создаем такой индекс:

CREATE NONCLUSTERED COLUMNSTORE INDEX
  IXCS_SomeOperations_OperationId_OperDate
  ON SomeOperations(OperationId, OperDate)
  ON PS_SomeOperations(OperDate)

Теперь сначала ищем по нему OperDate для каждого OperationId и сохраняем результат во временную таблицу #OperationIds. В примере считается, что временная таблица #OpIds уже содержит миллион OperationId, которые нужно найти.

CREATE TABLE #OperationIds(
  OperationID bigint,
  OperDate    datetime)
INSERT #OperationIds (CarOperationID, OperDate)
SELECT I.OperationId, O.OperDate
FROM #OpIds AS I
JOIN SomeOperations AS O ON O.OperationId=I.OperationId
OPTION(MAXDOP 8)

Такой поиск по COLUMNSTORE индексу занял всего лишь 5 секунд. При том что в таблице SomeOperations полтора миллиарда строк.

Итого, вместо 100 секунд, разбив запрос на два (поиск по COLUMNSTORE за 5 секунд и выборка уже по результатам этого поиска за 5 секунд), получили выигрыш в производительности на порядок.