How to become an author
Search
Write a publication
Pull to refresh

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

Level of difficultyEasy
Reading time9 min
Views4.4K
SQL*Microsoft SQL Server*
Analytics

Преамбула

Развернув обратно разработчику очередной 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))+(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.

Выводы

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

  2. Во всех остальных случаях более сложный запрос существенно выигрывает в производительности у более простого.

Именно поэтому, когда я в 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, как не универсальный.

Tags:
Hubs:
Total votes 3: ↑1 and ↓20
Comments43
47
Karma
0.2
Rating
Вадим Петряев @ptr128

Архитектор ИС

Send message

Articles

Your account

Sections

Information

Services

Support
© 2006–2024, Habr