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

[akakoychenko]

Ох уж эти костыли, которые натирают при езде на велосипеде...

Бесспорно, колоночный индекс - удивительно хорошо сделанная фича в MSSQL. Но, если вдуматься, то автор предлагает завязаться на фуллскан всего колоночного индекса для поиска. Причём, потом выборка пойдёт из обычной, строчной, таблицы. Решение пугающее. Оно и неприменимо к оперативным запросам (ибо, даже при поиске всего 1 значения, фуллскан есть фуллскан), так ещё и производительность будет деградировать с ростом базы, хотя, в этом и суть секционирования, чтобы производительность операций с, условно, последним месяцем оставалась константной, несмотря на общий объем базы.

Ну и да, раз уже ставка на колоночный индекс, то чего не идти до конца, и не заложиться на сильную сторону колоночного хранения - независимость производительности выборки N колонок от общего количества колонок в таблице? Зачем городить франкенштейна?

[ptr128]

Зачем городить франкенштейна?

Я ответил на этот вопрос в статье:

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

Что касается

при поиске всего 1 значения, фуллскан есть фуллскан

Это Вы зря.

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)

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 25 ms.
Table 'SomeOperations'. Scan count 29, logical reads 9386, physical reads 0, read-ahead reads 0, lob logical reads 1426907, lob physical reads 34, lob read-ahead reads 5443492.
Table 'SomeOperations'. Segment reads 1239, segment skipped 0.
Table '#OpIds___________________________________________________________________________________________________________000000181C56'. Scan count 9, logical reads 1839, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 14265 ms,  elapsed time = 3351 ms.

(1141051 rows affected)

А так:

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

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 9 ms.
Table '#OperationIds____________________________________________________________________________________________________000000182ADE'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SomeOperations'. Scan count 14, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#OpIds___________________________________________________________________________________________________________000000181C56'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

(1 row affected)

То есть, планировщик запросов MS SQL далеко не так туп, как Вы думаете. И использует COLUMNSTORE индекс только когда это оправдано.

производительность будет деградировать с ростом базы

Будет. Но за это время вполне можно завершить рефакторинг базы. В моем случае, за 10 лет накопилось полтора миллиарда строк. Так что еще лет пять на неторопливый рефакторинг вполне можно себе позволить.

[BeceJlb4ak]

Не могли бы вы более подробно описать ваше решение

[silvercaptain]

я так подозреваю, что создание матерелизованной вьюхи с (WITH SCHEMABINDING) привело бы к похожим результатам

[ptr128]

Вы правы. Для случаев, когда WITH SCHEMABINDING допустим, кластерный индекс на VIEW более эффективное решение.