ptr128 Jul 29 2023 at 15:35

Аналитики с диапазонами дат в MS SQL

Easy

9 min

4.4K

SQL*Microsoft SQL Server*

Analytics

Comments 43

Politura Jul 29 2023 at 19:44

Если есть возможность сделать кластерный индекс по AnalyticId и DateFrom - это стоит делать. В этом случае, и только если для одного значения AnalyticId имеется в таблице очень небольшое (единицы или десятки) количество различных диапазонов дат, вполне допустимо использование простого запроса. Хотя и более сложный запрос остается вполне применим.

Не обязательно кластерный, попробуйте добавить индекс по полям AnalyticId и DateFrom, но с полями Value и DateTo в INCLUDE, что-то типа такого:

create nonclustered index ix_some_name on tmp_analytics_with_date_range (AnalyticId, DateFrom) include (Value, DateTo)

по-идее, должно сработать также как и для кластерного индекса.

ptr128 Jul 29 2023 at 21:14

Странное предложение. Во-первых, в реальной жизни атрибутов и мер, обычно, десятки, а не одно поле Value. Во-вторых, подобный вариант индексации был рассмотрен в статье:

CREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_Idx
  ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo)

Чтобы не быть голословным, благо таблицы я еще не успел удалить, привожу статистику выполнения простого "тупого" запроса по второму миллиону (по 10 тыс. диапазонов на одно значение AnalyticId) с предложенным Вами индексом:

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 24612, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 1875 ms,  elapsed time = 1879 ms.

Как видим, чуда не произошло. Logical reads выросло более, чем на 500, как и ожидалось (индекс стал длиннее на 8*2 млн. = 16 млн. байт), а время выполнения возросло.

Остальные варианты не рассматриваю, так как там разницы на таких объемах даже не заметим.

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

Politura Jul 29 2023 at 22:22

Однако план запроса стал точно таким-же, что и в варианте с кластерным индексом, и время исполнения почти такое-же, чуть изменилось:

Из статьи:

CPU time = 1812 ms, elapsed time = 1806 ms.

Из вашего комментария:

CPU time = 1875 ms, elapsed time = 1879 ms.

Я не говорил, что что-то улучшится. Только про то, что получится также как и с кластерным индексом. Смысл в том, что в вашем запросе используется поле Value, которого нет в индексе, вы в include добавили только DateTo, поэтому приходится лезть в таблицу, а когда все поля есть в индексе (как оно всегда и бывает для кластерных индексов), то его достаточно.

Конечно, в вашем случае лучше пользоваться правильным запросом. Мой комментарий относился исключительно к той части, которую я заквотил. О том, что другим индексом нельзя достичь того же результата, что и кластерным.

В целом, это больше про то, что иногда, зная какие данные требуются в селекте, можно хорошо соптимизировать добавив эти данные в include нужного индекса. Не всегда это оправданно, т.к. чем больше индексов, тем дороже запись, но бывает, что требуется для ускорения каких-то критических селектов.

В свое время для меня стало открытием. Раньше для меня было нормой, что не важно какие поля перечисленны в разделе select (не если там нет вложенных позапросов), то, какие индексы будут использоваться, какой план запроса будет, зависит только от части запроса что идет во from и дальше. А потом когда появилось include для индексов, это прям перевернуло для меня некоторые вещи. Но это было много лет назад, я уже и не помню с какой версии ms sql добавили include в индексы.

ptr128 Jul 29 2023 at 23:00

Простите, но я не понимаю, в чем смысл Вашего предложения. Доказано ведь, что производительность стала еще хуже.

Смысл в том, что в вашем запросе используется поле Value, которого нет в
индексе, вы в include добавили только DateTo, поэтому приходится лезть
в таблицу, а когда все поля есть в индексе (как оно всегда и бывает для
кластерных индексов), то его достаточно.

Смысл в этом частный и теоретический. Я с этого и начал:

в реальной жизни атрибутов и мер, обычно, десятки, а не одно поле Value

Вы что-ли предлагаете несколько десятков полей в INCLUDE перечислять?

Я рекомендую включать в INCLUDE только те поля, которые используются в WHERE/ON. Включение остальных полей очень редко оказывается выгодным, так как ничтожный прирост производительности одного запроса приводит к повышенному потреблению памяти и замедлению операций модификации/вставки.

Можете убедиться:

CREATE UNIQUE CLUSTERED INDEX tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom)
EXEC sp_spaceused 'tmp_analytics_with_date_range'

name                         |rows                |reserved|data    |index_size|unused|
-----------------------------+--------------------+--------+--------+----------+------+
tmp_analytics_with_date_range|2000000             |61832 KB|61352 KB|136 KB    |344 KB|

DROP INDEX IF EXISTS tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range
CREATE UNIQUE NONCLUSTERED INDEX tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo, Value)
EXEC sp_spaceused 'tmp_analytics_with_date_range'

name                         |rows                |reserved |data    |index_size|unused |
-----------------------------+--------------------+---------+--------+----------+-------+
tmp_analytics_with_date_range|2000000             |126864 KB|61352 KB|63760 KB  |1752 KB|

Впечатляет? Можно сказать, что одним махом уполовинили оперативку, доступную серверу.

vagon333 Jul 30 2023 at 00:01

Я рекомендую включать в INCLUDE только те поля, которые используются в WHERE/ON.

Если выборка по паре колонок, то эта пара как раз и кандидат в INCLUDE: фильтрация по индексу, а выборка по INCLUDE.

Кстати, в WHERE я бы INCLUDE не использовал - сканирование 2млн записей, только в индексе.

ptr128 Jul 30 2023 at 01:34

Вы упрощаете до сферического коня в вакууме. Цену INCLUDE я уже показал выше. На одном запросе Вы ее не видите, а на нагруженном сервере, по суммарной его нагрузке - заметите сразу. А прирост от указания в INCLUDE мер и атрибутов - ничтожен. Например:

CREATE UNIQUE CLUSTERED INDEX tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range(ID)
CREATE UNIQUE NONCLUSTERED INDEX tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo, Value)
DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value
INTO #t
FROM tmp_search_list_100_10000 L
LEFT JOIN tmp_analytics_with_date_range T ON T.AnalyticId=L.AnalyticId
  AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
OPTION (MAXDOP 1)

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 22097, physical reads 0, read-ahead reads 71, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 1828 ms,  elapsed time = 1817 ms.

|--Nested Loops ...
     |--Table Scan ...
     |--Index Seek ...

DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value 
INTO #t
FROM tmp_search_list_100_10000 L
OUTER APPLY (
  SELECT MAX(DateFrom) AS DateFrom
  FROM tmp_analytics_with_date_range T
  WHERE T.AnalyticId=L.AnalyticId AND T.DateFrom<=L.SearchDate) S
LEFT JOIN tmp_analytics_with_date_range T ON T.AnalyticId=L.AnalyticId AND T.DateFrom=S.DateFrom AND T.DateTo>=L.SearchDate
OPTION (MAXDOP 1)

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 6071, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 8 ms.

DROP INDEX IF EXISTS tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range
CREATE UNIQUE NONCLUSTERED INDEX tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo)
DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value
INTO #t
FROM tmp_search_list_100_10000 L
LEFT JOIN tmp_analytics_with_date_range T ON T.AnalyticId=L.AnalyticId
  AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
OPTION (MAXDOP 1)

Table 'tmp_analytics_with_date_range'. Scan count 1, logical reads 7688, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 843 ms,  elapsed time = 851 ms.

|--Hash Match ...
     |--Table Scan ...
     |--Clustered Index Scan ...

DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value 
INTO #t
FROM tmp_search_list_100_10000 L
OUTER APPLY (
  SELECT MAX(DateFrom) AS DateFrom
  FROM tmp_analytics_with_date_range T
  WHERE T.AnalyticId=L.AnalyticId AND T.DateFrom<=L.SearchDate) S
LEFT JOIN tmp_analytics_with_date_range T ON T.AnalyticId=L.AnalyticId AND T.DateFrom=S.DateFrom AND T.DateTo>=L.SearchDate
OPTION (MAXDOP 1)

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 6626, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 8 ms.

Первому простому запросу стало только хуже. Во втором сложном запросе разница в logical reads, но которая никак не отразилась на времени выполнения запроса. Зато 16 МБ оперативной памяти на Value в INCLUDE потеряли.

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

Вот в этом случае как раз в ключевые поля индекса включаются лишь эти единицы обязательных параметров и одно-два поля наиболее часто используемых параметров. А все остальные поля, используемые в параметрах выносятся в список INCLUDE.

Лично я PR с INCLUDE атрибутов или мер пропущу только в том случае, если абсолютно уверен, что данной таблицей пользуется строго ограниченное количество статических запросов и она очень редко модифицируется. Потому что ради единиц процентов выигрыша на одном запросе терять столько же или более на многих других, на мой взгляд, не оправдано. Даже на средних размерах БД (до 10 ТБ).

vagon333 Jul 30 2023 at 02:03

А прирост от указания в INCLUDE мер и атрибутов - ничтожен.

Ваши тесты на узких записях.
Расширьте запись до 800 колонок, половина nvarchar (реальный случай, встречал и более) и замерьте заново.

ptr128 Jul 30 2023 at 02:05

Если Вы хотите мне что-то доказать, то почему я должен что-то делать? Все запросы в статье. Хотите - расширьте, приведите в качестве аргументов, тогда и обсудим.

ptr128 Jul 30 2023 at 02:26

Расширьте запись до 800 колонок, половина nvarchar (реальный случай, встречал и более) и замерьте заново.

Ага, ровно максимум - 1024 )))

А выгнать архитектора, допустившего такое не пробовали?

Для ClickHouse - допустимо и логично даже 8К колонок. Я уже молчу про Cassandra, где и под 100К встречал. Но в реляционной БД даже сотня колонок - уже повод задуматься над архитектурой.

uaggster Aug 22 2023 at 19:06

Но-но-но!
Для DWH - 500-800 полей - это, скорее, норма, чем извращение.
Хотя, да, повод вопросить в сторону коллег: "Господа, вы в своем уме?"... ну, в переложении на арго, разумеется.

ptr128 Aug 22 2023 at 20:12

А Вы посчитайте, сколько из этих полей реально участвуют в одном запросе и сколько из них при этом занимают места в памяти и нагружают канал к СХД.

Я не говорю, что не встречал такое. Даже, изучая query store, распиливал по таблицам, заменяя одноименной VIEW. Со средним приростом производительности в разы. Часто используемые поля в корневую таблицу, остальные - в одну или несколько подчиненных.

Чтобы не быть голословным. Подготовим две таблицы с 10 полями и с 500 полями, кроме ID.

DECLARE @sql_str nvarchar(max)

SELECT @sql_str='DROP TABLE IF EXISTS tmp_small
  CREATE TABLE tmp_small (ID int PRIMARY KEY CLUSTERED,
  '+STRING_AGG('int'+CONVERT(nvarchar(max),C.number)+' int NOT NULL',',
  ')+')
  INSERT tmp_small (ID, '
  +STRING_AGG('int'+CONVERT(nvarchar(max),C.number),', ')+')
  SELECT H.number*1000+L.number,
  '+STRING_AGG('H.number*1000+L.number+'+CONVERT(nvarchar(max),C.number),', ')+'
  FROM master..spt_values H
  JOIN master..spt_values L ON L.number<1000 AND L.type=''P''
  WHERE H.number<1000 AND H.type=''P'' '
FROM master..spt_values C
WHERE C.number<10 AND C.type='P'

EXEC (@sql_str)

SELECT @sql_str='DROP TABLE IF EXISTS tmp_large
  CREATE TABLE tmp_large (ID int PRIMARY KEY CLUSTERED,
  '+STRING_AGG('int'+CONVERT(nvarchar(max),C.number)+' int NOT NULL',',
  ')+')
  INSERT tmp_large (ID, '
  +STRING_AGG('int'+CONVERT(nvarchar(max),C.number),', ')+')
  SELECT H.number*1000+L.number,
  '+STRING_AGG('H.number*1000+L.number+'+CONVERT(nvarchar(max),C.number),', ')+'
  FROM master..spt_values H
  JOIN master..spt_values L ON L.number<1000 AND L.type=''P''
  WHERE H.number<1000 AND H.type=''P'' '
FROM master..spt_values C
WHERE C.number<500 AND C.type='P'

EXEC (@sql_str)

А теперь проверим скорость выборки пяти полей, кроме ID, из 10% записей этих таблиц. Сначала по таблице с десятком полей:

DROP TABLE IF EXISTS #t
SELECT ID, int1, int3, int5, int7, int9
INTO #t
FROM tmp_small
WHERE ID BETWEEN 200000 AND 299999

Результат:

Table 'tmp_small'. Scan count 1, logical reads 678, 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 = 31 ms,  elapsed time = 28 ms.

Потом с пятью сотнями полей:

DROP TABLE IF EXISTS #t
SELECT ID, int1, int3, int5, int7, int9
INTO #t
FROM tmp_large
WHERE ID BETWEEN 200000 AND 299999

Результат:

Table 'tmp_large'. Scan count 17, logical reads 35670, 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 = 238 ms,  elapsed time = 138 ms

Даже когда таблицы целиком в оперативке, разница в производительности более чем в семь раз. А вот если бы они были на диске, то разница, как видим, была бы уже раз в 50 (35670/678).

Politura Jul 30 2023 at 00:44

Простите, но я не понимаю, в чем смысл Вашего предложения. Доказано ведь, что производительность стала еще хуже.

Уфф, ну ладно, давайте с самого начала. Вы написали:

Если есть возможность сделать кластерный индекс по AnalyticId и DateFrom - это стоит делать. В этом случае, и только если для одного значения AnalyticId имеется в таблице очень небольшое (единицы или десятки) количество различных диапазонов дат, вполне допустимо использование простого запроса. Хотя и более сложный запрос остается вполне применим.

Проверьте, пожалуйста, как будет работать запрос с некластерным индексом у которого в include и DateTo и Value для этого сценария. Напомню, что из вашей статьи запрос с кластерным индексом получил такой результат:

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 5916, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100000_10'. Scan count 1, logical reads 2, 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 = 16 ms, elapsed time = 8 ms.

Запрос с некластерным индексом получил такой результат:

Table 'Workfile'. 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.
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.
Table 'tmp_analytics_with_date_range'. Scan count 1, logical reads 7690, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100000_10'. Scan count 1, logical reads 2, 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 = 281 ms, elapsed time = 277 ms.

Я ожидаю, что если вы в некластерный индкс добавите в include Value помимо DateTo, то получите результат сопоставимый с результатом с кластерным индексом.

ptr128 Jul 30 2023 at 02:02

Вы забываете самое главное. Оптимизатор не знает, что у меня диапазоны дат не пересекаются. Поэтому в простом запросе он вынужден читать все DateTo записей, у которых DateFrom<=SearchDate. А тут уже поиск по кластерному индексу явно быстрее, чем по не кластерному, в который Вы еще предложили добавить дополнительные 16 МБ.

Выборка 10000_10 тут как раз совершенно не интересна. А вот выборка по 100_10000 наоборот, показательна:

CREATE UNIQUE CLUSTERED INDEX tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom)
DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value
INTO #t
FROM tmp_search_list_100_10000 L
LEFT JOIN tmp_analytics_with_date_range T ON T.AnalyticId=L.AnalyticId
  AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
OPTION (MAXDOP 1)

|--Nested Loops
     |--Table Scan
     |--Clustered Index Seek

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 24071, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 1765 ms,  elapsed time = 1772 ms.

DROP INDEX IF EXISTS tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range
CREATE UNIQUE NONCLUSTERED INDEX tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo, Value)

|--Nested Loops
     |--Table Scan
     |--Index Seek

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 24612, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 1875 ms,  elapsed time = 1879 ms.

Politura Jul 30 2023 at 02:20

Боже мой, я вас нисколько не уговариваю отказаться от такого классного запроса с дополнительным условием, который вы придумали, конечно он будет более оптимальным. Вы молодец!

Я писал совсем о другом.

О конкретном абзаце, где вы упомянули определенный узкий сценарий и что для него кластерный индекс будет работать. Я лишь написал, что некластерный индекс будет работать абсолютно точно также для этого узкого сценария, если его слегка изменить. Неужели так сложно это проверить и убедиться? Зачем вы все продолжаете выполнять запрос для другого сценария?

ptr128 Jul 30 2023 at 02:37

Зачем вы все продолжаете выполнять запрос для другого сценария?

Именно потому, что он, как Вы сами выразились "узкий". Когда у одной аналитике лишь десяток диапазонов дат, совершенно монопенисуально, каким запросом и с какими индексами оттуда выбирать.

Ну нет на нем заметной разницы:

CREATE UNIQUE CLUSTERED INDEX tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom)
DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value
INTO #t
FROM tmp_search_list_100000_10 L
LEFT JOIN tmp_analytics_with_date_range T ON T.AnalyticId=L.AnalyticId
  AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
OPTION (MAXDOP 1)

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 3237, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100000_10'. Scan count 1, logical reads 2, 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 = 16 ms,  elapsed time = 7 ms.

DROP INDEX IF EXISTS tmp_analytics_with_date_range_PK_Idx ON tmp_analytics_with_date_range
CREATE UNIQUE NONCLUSTERED INDEX tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo, Value)

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 3242, physical reads 0, read-ahead reads 49, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100000_10'. Scan count 1, logical reads 2, 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 = 16 ms,  elapsed time = 8 ms.

Politura Jul 30 2023 at 02:27

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

CREATE UNIQUE INDEX tmp_analytics_with_date_range1_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo)

CREATE UNIQUE INDEX tmp_analytics_with_date_range2_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo, Value)

ptr128 Jul 30 2023 at 02:41

Я сравниваю два случая. Когда можно создать кластерный индекс именно по нужным в запросе полям и когда кластерный индекс уже есть по другим полям и там он нужнее.

Вариант, когда вообще кластерного индекса нет я даже не рассматриваю, так как в этой ситуации он явно надуман.

К чему приводят лишние 16 МБ индекса я уже показал на 100_10000. Там это весьма показательно.

Politura Jul 30 2023 at 08:43

Если у вас есть кластерный индекс по AnalyticId и DateFrom, вам уже никакой дополнительный индекс не нужен. Однако если кластерный индекс по Id, то вы все равно добавляете такой индекс:
CREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo)

А раз так, то корректнее сравнивать этот индекс, с таким-же индексом куда добавленно поле Value в include.

К чему приводят лишние 16 МБ индекса я уже показал на 100_10000. Там это весьма показательно.

Угу, показали, только сами не заметили, что показали. В сообщении https://habr.com/ru/articles/751238/#comment_25802744 ваш запрос с дополнительным OUTER APPLY для случая когда в некластерном индексе в include только поле DateTo результат такой:

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 6626, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

А для случая когда там еще и поле Value результат такой:

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 6071, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Оно и понятно - в первом случае после всех манипуляций надо лезть в кластерный индекс, чтоб оттуда забрать значение Value, а во втором случае значение Value уже есть в используемом запросом индексе и никаких дополнительных телодвижений делать не надо.

ptr128 Jul 30 2023 at 10:13

только сами не заметили, что показали

Я как раз заметил, что показал и уже не раз заострял на этом внимание. Ничтожное ускорение второго запроса приводит к заметному замедлению первого. О чем я уже писал неоднократно:

ради единиц процентов выигрыша на одном запросе терять столько же или более на многих других, на мой взгляд, не оправдано

Вы же по прежнему упорно хотите ускорить и так быстрый запрос, затормозив медленный.

Politura Jul 30 2023 at 20:17

Да, действительно не заметил про единицы процентов выигрыша, прошу прощения. Только вы план запроса-то посмотрели? Там эти единицы процентов выигрыша (на самом деле 10%) за счет того, что убирается nested loop с кластерным индексом. А значит на других данных, не тестовых, а реальных, можно легко получить заметно бОльший выигрыш (больше записей аналитики - глубже индекс, больше записей в результирующей выборке - больше количество циклов, больше полей в таблице - больше места занимает кластерный индекс - сильнее забьется память его кусками).

терять столько же или более на многих других, на мой взгляд, не оправдано

Да, размер индекса увеличится, а значит и запросы его использующие станут чуть дольше. Но на мой взгляд, в реальности это будут доли процента. Если считаете, что это не так, приведите, пожалуйста, запрос с оптимальным планом выполнения, который по индексу tmp_analytics_with_date_range_AnalyticId_Idx без поля Value в include даст выигрыш в 10% по сравнению с тем-же индексом у которого Value в include. Я честно не могу такой прикинуть в голове. Не, я понимаю, что размер индекса больше, количество блоков больше, но запрос с потерей 10% из-за Value не вижу. А вот запрос который даст выигрыш в несколько раз на ваших-же данных могу. Например, нам надо по каждому типу аналитики сумму данных за какой-то период (не придирайтесь, пожалуйста, что период только по DateFrom, я хочу показать запрос, который даст выигрыш. В реальности это может быть, например, таблица заказов и вместо DateFrom будет дата заказа, а вместо Value - сумма заказа).

Типа такого (сорри, пишу навскидку, не могу проверить, может есть ошибки):

select t.AnalyticId, sum(Value) from tmp_analytics_with_date_range t inner join (select distinct AnalyticId from tmp_search_list_100_10000) v on t.AnalyticId = v.AnalyticId where DateFrom between '20000101' and '20230101' group by t.AnalyticId

Сложно по вашим данным оценить какой должен быть период, я взял +- на угад, если период будет такой, что в выборке до группировки не тысяча записей, как в вашем запросе, а тысяч 20-50, то выигрыш из-за поле Value в индексе уже будет в несколько раз, т.к. нам достаточно будет индекса tmp_analytics_with_date_range_AnalyticId_Idx, а если этого поля там нет, то уже не достаточно и надо либо лезть в кластерный индекс за данными для каждой из 20-50тыс записей, либо вообще этот индекс не использовать и делать скан по кластерному индексу.

Идем дальше. Если таблица будет реальная, то в ней десятки полей, а значит и кластерный индекс будет занимать гораздо больше блоков. А если еще и записей не несчастные 2млн, а 200млн и больше, то вот уже и индекс с полем Value в include даст пару порядков выигрыша.

Важно: Еще раз, я не призываю вас пихать в индекс всякие странные поля которые не учавствуют в where, или в соединениях, а есть только в секции select. Я только пытаюсь показать, что могут быть случаи, когда это реально оправданно.

ptr128 Jul 30 2023 at 21:41

Только вы план запроса-то посмотрели? Там эти единицы процентов выигрыша (на самом деле 10%) за счет того, что убирается nested loop с кластерным индексом.

Естественно смотрел. Но у меня нет под рукой настолько тормозного сервера, чтобы разница во времени выполнения сложного запроса по 100_10000 стабильно различалась. То без Value выдаст 8 мс, то с ним 10 мс. Или наоборот.

А значит на других данных, не тестовых, а реальных, можно легко получить заметно бОльший выигрыш

Да? Ну давайте ближе к реалиям. Пусть у нас есть не только Value, а еще и Value1-Value7. И есть несколько десятков запросов, которым нужны из этих восьми Value от одного до трех в разных комбинациях. Ваше предложение? Влепить все 8 Value в INCLUDE?

А если уже совсем про реалии, то в таблице, которая подвинула меня на эту статью, лишь заголовки документов и еще больше двух десятков полей, кроме ключевых. А у заголовков есть еще еще и строки в другой таблице, которые уже ссылаются на ID заголовка, который и есть кластерный индекс. А значит, в реальной жизни, хоть в лоб, хоть по лбу, этот кластерный индекс приходится читать, чтобы вытащить строки. Мы же с чего начинали? Что кластерный индекс по нашим ключевым полям построить не можем, так как он необходим для других целей! А если других целей нет и можем построить кластерный индекс, то все, я показал выше, что по кластерному индексу поиск будет быстрее, чем по не кластерному с INCLUDE.

Да, размер индекса увеличится, а значит и запросы его использующие станут чуть дольше. Но на мой взгляд, в реальности это будут доли процента.

А Вы попробуйте себе представить не сферического коня в вакууме, а реальную картину. Когда у Вас не одно Value и единственный запрос, а десятки атрибутов и мер и десятки разных запросов, которым нужны разные подмножества из этих атрибутов и мер. Вот тогда порочность включения в INCLUDE всего подряд сразу станет Вам понятней.

Если считаете, что это не так, приведите, пожалуйста, запрос с оптимальным планом выполнения, который по индексу tmp_analytics_with_date_range_AnalyticId_Idx без поля Value в include даст выигрыш в 10% по сравнению с тем-же индексом у которого Value в include. Я честно не могу такой прикинуть в голове.

Легко. Смотрите выше про восемь разных Value. А если их 20? 50? 100?

больше полей в таблице - больше места занимает кластерный индекс

С каких пор количество полей в таблице стало влиять на размер кластерного индекса?

Politura Jul 30 2023 at 01:05

в реальной жизни атрибутов и мер, обычно, десятки, а не одно поле Value
Вы что-ли предлагаете несколько десятков полей в INCLUDE перечислять?

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

ptr128 Jul 30 2023 at 01:45

Я уже показал выше, что ускорение запроса будет, но очень незначительное, а вот потребление оперативной памяти, замедление модификации таблицы и обслуживания ее индексов - значительное.

если у вас есть очень частый запрос пары полей из этой таблицы

На практике, такое очень и очень редко встречается. А вот выстроившиеся в очередь на оперативку сервера запросы я у клиентов встречал не раз. Поэтому десять раз подумаю, прежде чем раздувать размер индекса.

Politura Jul 30 2023 at 00:59

удалено, не туда ответил

qw1 Jul 30 2023 at 23:46

Согласно моему пониманию индексов, оптимальное решение

SELECT L.SearchDate, L.AnalyticId,
  (SELECT TOP(1) 1 T.Value
   FROM  tmp_analytics_with_date_range T 
   WHERE T.AnalyticId=L.AnalyticId 
   AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
   ORDER BY T.DateFrom DESC) as [Value]
FROM tmp_search_list_100000_10 L

при этом, индекс должен быть обратным:

CREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_Idx
  ON tmp_analytics_with_date_range(AnalyticId, DateFrom DESC) INCLUDE (DateTo);

Или, если не хочется обратных индексов, то создавать индекс надо по DateTo:

SELECT L.SearchDate, L.AnalyticId,
  (SELECT TOP(1) 1 T.Value
   FROM  tmp_analytics_with_date_range T 
   WHERE T.AnalyticId=L.AnalyticId 
   AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
   ORDER BY T.DateTo) as [Value]
FROM tmp_search_list_100000_10 L

CREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_Idx
  ON tmp_analytics_with_date_range(AnalyticId, DateTo) INCLUDE (DateFrom);

Понятно, то задача немного другая, т.к. в моей формулировке не выдаётся наружу найденный диапазон DateFrom-DateTo. Но на практике, вероятно, это и не нужно. Если же оно потребуется, принициально это решаемо. Видимо, придётся что-то выдумывать с оконными функциями, сейчас просто лень расписывать. Оно имеет смысл, только если это решение окажется заметно лучше.

ptr128 Jul 31 2023 at 10:41

Вот и Вы забыли, что оптимизатор не в курсе, что диапазоны дат у наc не пересекающиеся.

CREATE UNIQUE CLUSTERED INDEX tmp_analytics_with_date_range_PK_Idx
  ON tmp_analytics_with_date_range(ID)
CREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_Idx
  ON tmp_analytics_with_date_range(AnalyticId, DateFrom DESC)
  INCLUDE (DateTo)
DROP TABLE IF EXISTS #t
SELECT L.SearchDate, L.AnalyticId,
  ( SELECT TOP(1) T.Value
    FROM  tmp_analytics_with_date_range T 
    WHERE T.AnalyticId=L.AnalyticId 
      AND T.DateFrom<=L.SearchDate AND T.DateTo>=L.SearchDate
    ORDER BY T.DateFrom DESC) AS Value
INTO #t
FROM tmp_search_list_100_10000 L

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 11179, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tmp_search_list_100_10000'. Scan count 1, logical reads 2, 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 = 859 ms,  elapsed time = 862 ms.

qw1 Jul 31 2023 at 17:18

Даже стало интересно, и я запустил тестовый скрипт.
2 млн. записей довольно долго вставлялось. Потом я сделал необходимые индексы (обыччные, не clustered).

Не знаю, как вы получили отчёт производительности, я в SSMS нажал кнопку на тулбаре "Include client statistics", и всё 3 варианта, что ваш первый наивный, что второй оптимизировнный, что мой третий, все выдают примерно одинаковый результат порядка 38-40ms общего времени выполнения, с небольшими выбросами до 70ms, если не повезёт (вероятно, из-за того что на тестовом сервере может ещё что-то работать).

ptr128 Jul 31 2023 at 20:36

Так как у меня гетерогенная среда, то локале у меня Linux, SSMS не пользуюсь, а с MS SQL я общаюсь через DBEaver. Статистики и планы запросов получал штатным образом:

SET STATISTICS TIME ON
SET STATISTICS IO ON
SET STATISTICS PROFILE ON

qw1 Jul 31 2023 at 23:38

На моих замерах мой вариант выигрывает и по времени, и по числу logical reads:

https://habrastorage.org/webt/c2/v6/p_/c2v6p_vaz-5vtol7xv7v25uaxco.png

https://habrastorage.org/webt/k-/ep/pr/k-epprcu17b4qlszvtx1rixu-_q.png

ptr128 Jul 31 2023 at 23:47

На 100000_10 незначительно может выиграть и я объяснял выше почему. На 100_10000 - я приводил выше результат его проигрыша.

qw1 Aug 1 2023 at 00:00

А можно как-нибудь сбросить кеш?

Сейчас я на tmp_search_list_100_10000 запустил сначала свой, потом ваш, и получил 22 сек. и 11 сек. соответственно. Но воспроизвести не могу, потому что повторные запуски показывают околонулевое время, и я просто думаю, разница из-за первого подчитывания таблиц в кеши.

Или может быть, весь запрос кешируется. Но я немного его менял запрос, например, порядок столбцов, и результат всё равно моментальный.

ptr128 Aug 1 2023 at 00:20

А можно как-нибудь сбросить кеш?

DBCC DROPCLEANBUFFERS

22 сек. и 11 сек

Какие-то жуткие времена. У меня худшие варианты больше 2 секунд не выполнялись. Смотрите результаты статистик. И лучше наоборот, когда все данные в кеше.

qw1 Aug 1 2023 at 00:29

Интересная команда, полезная ))

Видимо, проблема в тестовых данных.
Вы думаете, что интервалы не пересекаются, а они пересекаются.

select * from tmp_analytics_with_date_range
where AnalyticId=100001 and DateFrom between '1901-05-01' and '1902-07-01'
order by DateFrom

Скрытый текст

Хотя, скрипт генерации нестабильный (основан на RAND) и у вас могут быть другие тестовые данные.

ptr128 Aug 1 2023 at 21:28

Виноват, скопировал не тот запрос. Сейчас исправлю в статье.

Вместо

DATEADD(day,(D.N+1)*(200*365/(@Ranges*1000)+1)+(200*365/(@Ranges*1000))
    *RAND(CONVERT(varbinary, newid())),'1900-01-01')

Должно быть

  DATEADD(day,(D.N+1)*(200*365/(@Ranges*1000))+(200*365/(@Ranges*1000))
    *RAND(CONVERT(varbinary, newid())),'1900-01-01')

+1 там был откровенно излишен.

Что интересно, у себя то я заполнил таблицу правильным запросом.

uaggster Aug 22 2023 at 19:19

Знаю, что можно было использовать для формирования последовательности натуральных чисел рекурсию, но в данном случае без нее получается немного быстрее, да и код понятней.

Select ROW_NUMBER() over (order by 1/0) N From string_split(Replicate(Cast(' ' as varchar(max)), 1000000), ' ') t

Не надо рекурсий. С 2016 (или 2017, когда там string_split появился?) - можно так.
С 2022 используйте GENERATE_SERIES

ptr128 Aug 22 2023 at 20:19

2016 сервер далеко не у всех до сих пор. У нас до сих пор кое-где 2012 остался. А у клиентов еще недавно встречал 2008.

uaggster Aug 22 2023 at 19:31

RAND(CONVERT(varbinary, newid()))

Если нужен целочисленный генератор случайных чисел, с равномерным распределением (а обычно нужен как раз целочисленный), лучше (для диапазона 1-10, например):

SELECT (ABS(CHECKSUM(NEWID())) % 10) + 1;

ptr128 Aug 22 2023 at 20:21

Чем лучше?

uaggster Aug 22 2023 at 20:32

Добротнее в математическом смысле (т.е. выдает более случайную последовательность).
Была статья в англоязычном интернете, слету не нагуглилось, но как то так.

Плюс она немного побыстрее, что актуально на миллионных генерациях.

Я например исходный пример уже полчаса дождаться не могу.

ptr128 Aug 22 2023 at 20:40

Плюс она немного побыстрее

Докажите, раз утверждаете. Я же доказывал свои утверждения выше.

выдает более случайную последовательность

В данном случае это вообще не имеет значения.

Я например исходный пример уже полчаса дождаться не могу.

NEWID() не параллелится по определению, потому и тормозит. А RAND() - псевдослучайный генератор. И очень сомневаюсь, что он медленней CHECKSUM()

uaggster Aug 23 2023 at 10:19

Ага. И этот генератор инициируется при каждом запуске. CHECKSUM же - это просто побитовая операция.

ptr128 Aug 23 2023 at 10:28

CHECKSUM же - это просто побитовая операция.

Пруф? Насколько я знаю, это все же криптографическая хеш функция, хоть и довольно слабая.

Еще раз, не надо лить воду. Хотите доказать свое утверждение - пишите на T-SQL наглядный пример, который можно повторить.

ptr128 Aug 23 2023 at 10:35

Все решил убедиться сам:

DROP TABLE IF EXISTS #t
SELECT CHECKSUM(H.number*1000+L.number) AS ID
INTO #t
FROM master..spt_values H
JOIN master..spt_values L ON L.number<1000 AND L.type='P'
WHERE H.number<1000 AND H.type='P'

Результат:

Table 'spt_values'. Scan count 23, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 6, logical reads 16036, 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 = 595 ms,  elapsed time = 205 ms.

И RAND()

DROP TABLE IF EXISTS #t
SELECT RAND(H.number*1000+L.number) AS ID
INTO #t
FROM master..spt_values H
JOIN master..spt_values L ON L.number<1000 AND L.type='P'
WHERE H.number<1000 AND H.type='P'

Результат:

Table 'spt_values'. Scan count 23, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 6, logical reads 16036, 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 = 577 ms,  elapsed time = 204 ms.

Резюме: различие на уровне статистической погрешности.