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

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

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

29.07.2023 12:35

ptr128 31 1300 Источник

Преамбула

Развернув обратно разработчику очередной Pull Request с поиском по аналитике, принимающей разные значения в разные промежутки времени, я решил на планерке обсудить этот вопрос. И был удивлен, что подавляющее большинство разработчиков не понимают, как эффективно искать на SQL в таких случаях. Погуглив, ради интереса, обнаружил, что этот вопрос как-то обходится стороной сообществом. В итоге решил написать статью, заодно ссылаясь на нее самому.

Сразу хочу уточнить, что речь идет именно об MS SQL, так как, например, в PostgreSQL уже есть диапазонные типы и виды индексов, позволяющие их индексировать.

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

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

Необходимо, зная AnalyticID и конкретную дату, максимально эффективно найти значение, привязанное к аналитике (в частном случае оно может быть NULL).

На входе у нас будет такая таблица с AnalyticID, диапазонами дат и связанным с ними значением:

CREATE TABLE tmp_analytics_with_date_range (
  ID         int   NOT NULL IDENTITY(1,1),
  AnalyticId int   NOT NULL,
  DateFrom   date  NOT NULL,
  DateTo     date  NULL,
  Value      float NOT NULL
)

Для массива искомых значений создадим две таблицы с одинаковой структурой:

CREATE TABLE tmp_search_list_100000_10 (
  AnalyticId int  NOT NULL,
  SearchDate date NOT NULL
)

CREATE TABLE tmp_search_list_100_10000 (
  AnalyticId int  NOT NULL,
  SearchDate date NOT NULL
)

Почему две - будет объяснено ниже.

Подготовка к тестированию

Заполним tmp_analytics_with_date_range двумя наборами строк, по миллиону записей в каждом. В первом наборе создадим 100 тыс. разных аналитик (AnalyticId) и по 10 различных диапазонов дат (DateFrom - DateTo) со значениями (Value) для каждой аналитики. Во втором наборе - сотню разных аналитик и по 10 тыс. различных диапазонов дат для каждой аналитики.

Так же сразу произведем генерацию строк для таблиц tmp_search_list_100000_10 и tmp_search_list_100_10000. Формируем две таблицы, чтобы сделать по ним в запросе полное сканирование без индексов, что положительно влияет на чистоту эксперимента.

DECLARE
  @Analytics       int=100000,
  @Ranges          int=10,
  @SearchAnalytics int=1000

;WITH IntSequence AS (
  SELECT N = (((((V6.N*10+V5.N)*10+V4.N)*10)+V3.N)*10+V2.N)*10+V1.N
  FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V1(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V2(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V3(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V4(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V5(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V6(N) )
INSERT tmp_analytics_with_date_range (AnalyticID, DateFrom, DateTo, Value)
SELECT A.N+1, DATEADD(day,(D.N+1)*(200*365/@Ranges),'1900-01-01'),
  DATEADD(day,(D.N+1)*(200*365/@Ranges)+(200*365/@Ranges)
    *RAND(CONVERT(varbinary, newid())),'1900-01-01'),
  RAND(CONVERT(varbinary, newid()))
FROM IntSequence A
JOIN IntSequence D ON D.N<@Ranges
WHERE A.N<@Analytics
UNION ALL
SELECT A.N+1, DATEADD(day,(D.N+1)*(200*365/(@Ranges*1000)),'1900-01-01'),
  DATEADD(day,(D.N+1)*(200*365/(@Ranges*1000)+1)+(200*365/(@Ranges*1000))
    *RAND(CONVERT(varbinary, newid())),'1900-01-01'),
  RAND(CONVERT(varbinary, newid()))
FROM IntSequence A
JOIN IntSequence D ON D.N<@Ranges*1000
WHERE A.N>=@Analytics AND A.N<@Analytics+@Analytics/1000 

;WITH IntSequence AS (
  SELECT N = (((((V6.N*10+V5.N)*10+V4.N)*10)+V3.N)*10+V2.N)*10+V1.N
  FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V1(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V2(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V3(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V4(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V5(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V6(N) )
INSERT tmp_search_list_100000_10 (AnalyticId, SearchDate)
SELECT CONVERT(int,A.N*(RAND(CONVERT(varbinary, newid()))
    *@Analytics/@SearchAnalytics)+1) AS AnalyticId,
  DATEADD(day,200*365
    *RAND(CONVERT(varbinary, newid())),'1900-01-01') AS SearchDate
FROM IntSequence A
WHERE A.N<@SearchAnalytics

;WITH IntSequence AS (
  SELECT N = (((((V6.N*10+V5.N)*10+V4.N)*10)+V3.N)*10+V2.N)*10+V1.N
  FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V1(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V2(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V3(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V4(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V5(N)
  CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) V6(N) )
INSERT tmp_search_list_100_10000 (AnalyticId, SearchDate)
SELECT CONVERT(int,@Analytics+A.N*(RAND(CONVERT(varbinary, newid()))
    *@Analytics/(@SearchAnalytics*1000))+1) AS AnalyticId,
  DATEADD(day,200*365
    *RAND(CONVERT(varbinary, newid())),'1900-01-01') AS SearchDate
FROM IntSequence A
WHERE A.N<@SearchAnalytics

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

Тестирование первого варианта индексации

В первом варианте мы предполагаем, что в таблице tmp_analytics_with_date_range можно и удобно сделать кластерный индекс не по ID, а по AnalyticId и DateFrom. Что мы и делаем:

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

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

DROP TABLE IF EXISTS #t

Теперь попробуем выборку из первого миллиона записей таблицы, где у нас у одной аналитики всего 10 диапазонов дат.

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.

Приведен результат не первого выполнения, а второго, в котором показатели physical reads и read-ahead reads нулевые (все таблицы уже в оперативной памяти сервера).

Теперь попробуем сделать такую же выборку, но более сложным запросом

SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value 
INTO #t
FROM tmp_search_list_100000_10 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)

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

Статистика выполнения следующая:

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.

Как видим, в случае, когда для одного значения AnalyticId имеется лишь 10 диапазонов дат, разница в производительности этих двух запросов несущественная.

А теперь я протестирую выборку уже из второго миллиона строк, где у меня лишь 100 аналитик, но для каждой определено 10 тыс. диапазонов дат.

Сначала опять простейший запрос:

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 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 = 1812 ms,  elapsed time = 1806 ms.

А потом второй, более сложный запрос:

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 = 16 ms,  elapsed time = 8 ms.

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

Тестирование второго варианта индексации

Во втором варианте будем исходить из того, что кластерный индекс уже у нас есть и создать второй невозможно. Бывают такие случаи. Поэтому индексируем таблицу tmp_analytics_with_date_range иначе:

DROP INDEX IF EXISTS tmp_analytics_with_date_range_PK_Idx
  ON tmp_analytics_with_date_range
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) INCLUDE (DateTo)

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

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.

Для сложного запроса по тем же данным:

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 1000, logical reads 7282, 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 17, 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.

Как видно, более сложный запрос тут существенно опередил более простой.

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

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_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 = 875 ms,  elapsed time = 878 ms.

Второй запрос:

Table 'tmp_analytics_with_date_range'. Scan count 1000, logical reads 7736, 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 = 16 ms,  elapsed time = 10 ms.

Выводы

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

Именно поэтому, когда я в Pull Request увидел запрос вида

SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value
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

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

SELECT L.SearchDate, L.AnalyticId, T.DateFrom, T.DateTo, T.Value 
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

Спасибо, если сумели дочитать! Критика приветствуется.

P.S. В оригинальном случае были не даты, а datetime. Поэтому DateFrom включался в диапазон, а DateTo - уже нет. При этом DateTo предыдущего диапазона мог совпадать с DateFrom следующего. Именно поэтому в коде не был использован BETWEEN, как не универсальный.

Комментарии (31)

Politura
29.07.2023 16:44
#25801826
1. Если есть возможность сделать кластерный индекс по 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)

по-идее, должно сработать также как и для кластерного индекса.
1. ptr128 Автор
  29.07.2023 16:44
  #25802098
  Странное предложение. Во-первых, в реальной жизни атрибутов и мер, обычно, десятки, а не одно поле 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 ссылка из обычных индексов на кластерный индекс отрабатывается быстрее, чем ссылка на кучу, когда кластерного индекса нет. Да и само наличие кластерного индекса существенно расширяет возможности оптимизации запроса планировщиком.
  1. Politura
    29.07.2023 16:44
    #25802288
    Однако план запроса стал точно таким-же, что и в варианте с кластерным индексом, и время исполнения почти такое-же, чуть изменилось:
    
    Из статьи:
    
    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 Автор
    29.07.2023 16:44
    #25802378
    Простите, но я не понимаю, в чем смысл Вашего предложения. Доказано ведь, что производительность стала еще хуже.
    
    Смысл в том, что в вашем запросе используется поле 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
    29.07.2023 16:44
    #25802514
    Я рекомендую включать в INCLUDE только те поля, которые используются в WHERE/ON.
    
    Если выборка по паре колонок, то эта пара как раз и кандидат в INCLUDE: фильтрация по индексу, а выборка по INCLUDE.
    
    Кстати, в WHERE я бы INCLUDE не использовал - сканирование 2млн записей, только в индексе.
    
    ptr128 Автор
    29.07.2023 16:44
    #25802744
    Вы упрощаете до сферического коня в вакууме. Цену 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
    29.07.2023 16:44
    #25802830
    А прирост от указания в INCLUDE мер и атрибутов - ничтожен.
    
    Ваши тесты на узких записях.
    Расширьте запись до 800 колонок, половина nvarchar (реальный случай, встречал и более) и замерьте заново.
    
    ptr128 Автор
    29.07.2023 16:44
    #25802838
    Если Вы хотите мне что-то доказать, то почему я должен что-то делать? Все запросы в статье. Хотите - расширьте, приведите в качестве аргументов, тогда и обсудим.
    
    ptr128 Автор
    29.07.2023 16:44
    #25802880
    Расширьте запись до 800 колонок, половина nvarchar (реальный случай, встречал и более) и замерьте заново.
    
    Ага, ровно максимум - 1024 )))
    
    А выгнать архитектора, допустившего такое не пробовали?
    
    Для ClickHouse - допустимо и логично даже 8К колонок. Я уже молчу про Cassandra, где и под 100К встречал. Но в реляционной БД даже сотня колонок - уже повод задуматься над архитектурой.
    
    Politura
    29.07.2023 16:44
    #25802654
    Простите, но я не понимаю, в чем смысл Вашего предложения. Доказано ведь, что производительность стала еще хуже.
    
    Уфф, ну ладно, давайте с самого начала. Вы написали:
    
    Если есть возможность сделать кластерный индекс по 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 Автор
    29.07.2023 16:44
    #25802822
    Вы забываете самое главное. Оптимизатор не знает, что у меня диапазоны дат не пересекаются. Поэтому в простом запросе он вынужден читать все 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
    29.07.2023 16:44
    #25802856
    +1
    Боже мой, я вас нисколько не уговариваю отказаться от такого классного запроса с дополнительным условием, который вы придумали, конечно он будет более оптимальным. Вы молодец!
    
    Я писал совсем о другом.
    
    О конкретном абзаце, где вы упомянули определенный узкий сценарий и что для него кластерный индекс будет работать. Я лишь написал, что некластерный индекс будет работать абсолютно точно также для этого узкого сценария, если его слегка изменить. Неужели так сложно это проверить и убедиться? Зачем вы все продолжаете выполнять запрос для другого сценария?
    
    ptr128 Автор
    29.07.2023 16:44
    #25802892
    Зачем вы все продолжаете выполнять запрос для другого сценария?
    
    Именно потому, что он, как Вы сами выразились "узкий". Когда у одной аналитике лишь десяток диапазонов дат, совершенно монопенисуально, каким запросом и с какими индексами оттуда выбирать.
    
    Ну нет на нем заметной разницы:
    
    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
    29.07.2023 16:44
    #25802884
    Да и еще, там, где вы сравниваете индексы, вы слегка лукавите: вы сравниваете кластерный, и не кластерный индексы. Попробуйте создать два таких индекса и сравнить их между собой, разница будет конечно, но не такая огромная:
    
    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 Автор
    29.07.2023 16:44
    #25802896
    Я сравниваю два случая. Когда можно создать кластерный индекс именно по нужным в запросе полям и когда кластерный индекс уже есть по другим полям и там он нужнее.
    
    Вариант, когда вообще кластерного индекса нет я даже не рассматриваю, так как в этой ситуации он явно надуман.
    
    К чему приводят лишние 16 МБ индекса я уже показал на 100_10000. Там это весьма показательно.
    
    Politura
    29.07.2023 16:44
    #25803114
    Если у вас есть кластерный индекс по 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 Автор
    29.07.2023 16:44
    #25803264
    только сами не заметили, что показали
    
    Я как раз заметил, что показал и уже не раз заострял на этом внимание. Ничтожное ускорение второго запроса приводит к заметному замедлению первого. О чем я уже писал неоднократно:
    
    ради единиц процентов выигрыша на одном запросе терять столько же или более на многих других, на мой взгляд, не оправдано
    
    Вы же по прежнему упорно хотите ускорить и так быстрый запрос, затормозив медленный.
    
    Politura
    29.07.2023 16:44
    #25804826
    Да, действительно не заметил про единицы процентов выигрыша, прошу прощения. Только вы план запроса-то посмотрели? Там эти единицы процентов выигрыша (на самом деле 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 Автор
    29.07.2023 16:44
    #25805130
    Только вы план запроса-то посмотрели? Там эти единицы процентов выигрыша (на самом деле 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
    29.07.2023 16:44
    #25802688
    
    в реальной жизни атрибутов и мер, обычно, десятки, а не одно поле Value
    
    Вы что-ли предлагаете несколько десятков полей в INCLUDE перечислять?
    
    Нет. Но если у вас есть очень частый запрос пары полей из этой таблицы, у которого фильтр идет по другим полям, для которых есть некластерный индекс, имеет смысл попробовать добавить эту пару полей в include оного индекса, запрос должен будет существенно ускорится, т.к. все данные будут в одном индексе и никуда больше лезть за данными не надо.
    
    ptr128 Автор
    29.07.2023 16:44
    #25802784
    Я уже показал выше, что ускорение запроса будет, но очень незначительное, а вот потребление оперативной памяти, замедление модификации таблицы и обслуживания ее индексов - значительное.
    
    если у вас есть очень частый запрос пары полей из этой таблицы
    
    На практике, такое очень и очень редко встречается. А вот выстроившиеся в очередь на оперативку сервера запросы я у клиентов встречал не раз. Поэтому десять раз подумаю, прежде чем раздувать размер индекса.

Politura
29.07.2023 16:44
#25802674
удалено, не туда ответил

qw1
29.07.2023 16:44
#25805462
Согласно моему пониманию индексов, оптимальное решение
```
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. Но на практике, вероятно, это и не нужно. Если же оно потребуется, принициально это решаемо. Видимо, придётся что-то выдумывать с оконными функциями, сейчас просто лень расписывать. Оно имеет смысл, только если это решение окажется заметно лучше.
1. ptr128 Автор
  29.07.2023 16:44
  #25806660
  +1
  Вот и Вы забыли, что оптимизатор не в курсе, что диапазоны дат у на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.
  1. qw1
    29.07.2023 16:44
    #25808318
    Даже стало интересно, и я запустил тестовый скрипт.
    2 млн. записей довольно долго вставлялось. Потом я сделал необходимые индексы (обыччные, не clustered).
    
    Не знаю, как вы получили отчёт производительности, я в SSMS нажал кнопку на тулбаре "Include client statistics", и всё 3 варианта, что ваш первый наивный, что второй оптимизировнный, что мой третий, все выдают примерно одинаковый результат порядка 38-40ms общего времени выполнения, с небольшими выбросами до 70ms, если не повезёт (вероятно, из-за того что на тестовом сервере может ещё что-то работать).
    
    ptr128 Автор
    29.07.2023 16:44
    #25809174
    Так как у меня гетерогенная среда, то локале у меня Linux, SSMS не пользуюсь, а с MS SQL я общаюсь через DBEaver. Статистики и планы запросов получал штатным образом:
    
    SET STATISTICS TIME ON SET STATISTICS IO ON SET STATISTICS PROFILE ON
    
    qw1
    29.07.2023 16:44
    #25809594
    На моих замерах мой вариант выигрывает и по времени, и по числу 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 Автор
    29.07.2023 16:44
    #25809612
    На 100000_10 незначительно может выиграть и я объяснял выше почему. На 100_10000 - я приводил выше результат его проигрыша.
    
    qw1
    29.07.2023 16:44
    #25809632
    А можно как-нибудь сбросить кеш?
    
    Сейчас я на tmp_search_list_100_10000 запустил сначала свой, потом ваш, и получил 22 сек. и 11 сек. соответственно. Но воспроизвести не могу, потому что повторные запуски показывают околонулевое время, и я просто думаю, разница из-за первого подчитывания таблиц в кеши.
    
    Или может быть, весь запрос кешируется. Но я немного его менял запрос, например, порядок столбцов, и результат всё равно моментальный.
    
    ptr128 Автор
    29.07.2023 16:44
    #25809666
    А можно как-нибудь сбросить кеш?
    
    DBCC DROPCLEANBUFFERS
    
    22 сек. и 11 сек
    
    Какие-то жуткие времена. У меня худшие варианты больше 2 секунд не выполнялись. Смотрите результаты статистик. И лучше наоборот, когда все данные в кеше.
    
    qw1
    29.07.2023 16:44
    #25809682
    +1
    Интересная команда, полезная ))
    
    Видимо, проблема в тестовых данных.
    Вы думаете, что интервалы не пересекаются, а они пересекаются.
    
    select * from tmp_analytics_with_date_range where AnalyticId=100001 and DateFrom between '1901-05-01' and '1902-07-01' order by DateFrom
    
    Скрытый текст
    
    Хотя, скрипт генерации нестабильный (основан на RAND) и у вас могут быть другие тестовые данные.

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

Преамбула

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

Подготовка к тестированию

Тестирование первого варианта индексации

Тестирование второго варианта индексации

Выводы

Комментарии (31)

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор

ptr128 Автор