Преамбула
Развернув обратно разработчику очередной 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)
qw1
29.07.2023 16:44Согласно моему пониманию индексов, оптимальное решение
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 LCREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_Idx ON tmp_analytics_with_date_range(AnalyticId, DateTo) INCLUDE (DateFrom);Понятно, то задача немного другая, т.к. в моей формулировке не выдаётся наружу найденный диапазон DateFrom-DateTo. Но на практике, вероятно, это и не нужно. Если же оно потребуется, принициально это решаемо. Видимо, придётся что-то выдумывать с оконными функциями, сейчас просто лень расписывать. Оно имеет смысл, только если это решение окажется заметно лучше.
ptr128 Автор
29.07.2023 16:44+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.qw1
29.07.2023 16:44Даже стало интересно, и я запустил тестовый скрипт.
2 млн. записей довольно долго вставлялось. Потом я сделал необходимые индексы (обыччные, не clustered).Не знаю, как вы получили отчёт производительности, я в SSMS нажал кнопку на тулбаре "Include client statistics", и всё 3 варианта, что ваш первый наивный, что второй оптимизировнный, что мой третий, все выдают примерно одинаковый результат порядка 38-40ms общего времени выполнения, с небольшими выбросами до 70ms, если не повезёт (вероятно, из-за того что на тестовом сервере может ещё что-то работать).
ptr128 Автор
29.07.2023 16:44Так как у меня гетерогенная среда, то локале у меня Linux, SSMS не пользуюсь, а с MS SQL я общаюсь через DBEaver. Статистики и планы запросов получал штатным образом:
SET STATISTICS TIME ON SET STATISTICS IO ON SET STATISTICS PROFILE ONqw1
29.07.2023 16:44На моих замерах мой вариант выигрывает и по времени, и по числу 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На 100000_10 незначительно может выиграть и я объяснял выше почему. На 100_10000 - я приводил выше результат его проигрыша.
qw1
29.07.2023 16:44А можно как-нибудь сбросить кеш?
Сейчас я на tmp_search_list_100_10000 запустил сначала свой, потом ваш, и получил 22 сек. и 11 сек. соответственно. Но воспроизвести не могу, потому что повторные запуски показывают околонулевое время, и я просто думаю, разница из-за первого подчитывания таблиц в кеши.
Или может быть, весь запрос кешируется. Но я немного его менял запрос, например, порядок столбцов, и результат всё равно моментальный.
ptr128 Автор
29.07.2023 16:44А можно как-нибудь сбросить кеш?
DBCC DROPCLEANBUFFERS
22 сек. и 11 сек
Какие-то жуткие времена. У меня худшие варианты больше 2 секунд не выполнялись. Смотрите результаты статистик. И лучше наоборот, когда все данные в кеше.
qw1
29.07.2023 16:44+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) и у вас могут быть другие тестовые данные.
Politura
Если есть возможность сделать кластерный индекс по 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 Автор
Странное предложение. Во-первых, в реальной жизни атрибутов и мер, обычно, десятки, а не одно поле Value. Во-вторых, подобный вариант индексации был рассмотрен в статье:
Чтобы не быть голословным, благо таблицы я еще не успел удалить, привожу статистику выполнения простого "тупого" запроса по второму миллиону (по 10 тыс. диапазонов на одно значение AnalyticId) с предложенным Вами индексом:
Как видим, чуда не произошло. Logical reads выросло более, чем на 500, как и ожидалось (индекс стал длиннее на 8*2 млн. = 16 млн. байт), а время выполнения возросло.
Остальные варианты не рассматриваю, так как там разницы на таких объемах даже не заметим.
Вообще я настоятельно не рекомендую отказываться от кластерного индекса у таблицы. В MS SQL ссылка из обычных индексов на кластерный индекс отрабатывается быстрее, чем ссылка на кучу, когда кластерного индекса нет. Да и само наличие кластерного индекса существенно расширяет возможности оптимизации запроса планировщиком.
Politura
Однако план запроса стал точно таким-же, что и в варианте с кластерным индексом, и время исполнения почти такое-же, чуть изменилось:
Из статьи:
Из вашего комментария:
Я не говорил, что что-то улучшится. Только про то, что получится также как и с кластерным индексом. Смысл в том, что в вашем запросе используется поле Value, которого нет в индексе, вы в include добавили только DateTo, поэтому приходится лезть в таблицу, а когда все поля есть в индексе (как оно всегда и бывает для кластерных индексов), то его достаточно.
Конечно, в вашем случае лучше пользоваться правильным запросом. Мой комментарий относился исключительно к той части, которую я заквотил. О том, что другим индексом нельзя достичь того же результата, что и кластерным.
В целом, это больше про то, что иногда, зная какие данные требуются в селекте, можно хорошо соптимизировать добавив эти данные в include нужного индекса. Не всегда это оправданно, т.к. чем больше индексов, тем дороже запись, но бывает, что требуется для ускорения каких-то критических селектов.
В свое время для меня стало открытием. Раньше для меня было нормой, что не важно какие поля перечисленны в разделе select (не если там нет вложенных позапросов), то, какие индексы будут использоваться, какой план запроса будет, зависит только от части запроса что идет во from и дальше. А потом когда появилось include для индексов, это прям перевернуло для меня некоторые вещи. Но это было много лет назад, я уже и не помню с какой версии ms sql добавили include в индексы.
ptr128 Автор
Простите, но я не понимаю, в чем смысл Вашего предложения. Доказано ведь, что производительность стала еще хуже.
Смысл в этом частный и теоретический. Я с этого и начал:
Вы что-ли предлагаете несколько десятков полей в INCLUDE перечислять?
Я рекомендую включать в INCLUDE только те поля, которые используются в WHERE/ON. Включение остальных полей очень редко оказывается выгодным, так как ничтожный прирост производительности одного запроса приводит к повышенному потреблению памяти и замедлению операций модификации/вставки.
Можете убедиться:
Впечатляет? Можно сказать, что одним махом уполовинили оперативку, доступную серверу.
vagon333
Если выборка по паре колонок, то эта пара как раз и кандидат в INCLUDE: фильтрация по индексу, а выборка по INCLUDE.
Кстати, в WHERE я бы INCLUDE не использовал - сканирование 2млн записей, только в индексе.
ptr128 Автор
Вы упрощаете до сферического коня в вакууме. Цену INCLUDE я уже показал выше. На одном запросе Вы ее не видите, а на нагруженном сервере, по суммарной его нагрузке - заметите сразу. А прирост от указания в INCLUDE мер и атрибутов - ничтожен. Например:
Первому простому запросу стало только хуже. Во втором сложном запросе разница в logical reads, но которая никак не отразилась на времени выполнения запроса. Зато 16 МБ оперативной памяти на Value в INCLUDE потеряли.
С WHERE тоже все не так просто, как Вы думаете. Подавляющее большинство отчетов имеют десяток другой параметров-фильтров, из которых лишь единицы обязательны. Вам просто не известно, какие именно параметры укажет пользователь. Не будете же Вы индексировать таблицу даже с пятью аналитиками 120 индексами по одним и тем же пяти полям, но с разной их последовательностью?
Вот в этом случае как раз в ключевые поля индекса включаются лишь эти единицы обязательных параметров и одно-два поля наиболее часто используемых параметров. А все остальные поля, используемые в параметрах выносятся в список INCLUDE.
Лично я PR с INCLUDE атрибутов или мер пропущу только в том случае, если абсолютно уверен, что данной таблицей пользуется строго ограниченное количество статических запросов и она очень редко модифицируется. Потому что ради единиц процентов выигрыша на одном запросе терять столько же или более на многих других, на мой взгляд, не оправдано. Даже на средних размерах БД (до 10 ТБ).
vagon333
Ваши тесты на узких записях.
Расширьте запись до 800 колонок, половина nvarchar (реальный случай, встречал и более) и замерьте заново.
ptr128 Автор
Если Вы хотите мне что-то доказать, то почему я должен что-то делать? Все запросы в статье. Хотите - расширьте, приведите в качестве аргументов, тогда и обсудим.
ptr128 Автор
Ага, ровно максимум - 1024 )))
А выгнать архитектора, допустившего такое не пробовали?
Для ClickHouse - допустимо и логично даже 8К колонок. Я уже молчу про Cassandra, где и под 100К встречал. Но в реляционной БД даже сотня колонок - уже повод задуматься над архитектурой.
Politura
Уфф, ну ладно, давайте с самого начала. Вы написали:
Проверьте, пожалуйста, как будет работать запрос с некластерным индексом у которого в include и DateTo и Value для этого сценария. Напомню, что из вашей статьи запрос с кластерным индексом получил такой результат:
Запрос с некластерным индексом получил такой результат:
Я ожидаю, что если вы в некластерный индкс добавите в include Value помимо DateTo, то получите результат сопоставимый с результатом с кластерным индексом.
ptr128 Автор
Вы забываете самое главное. Оптимизатор не знает, что у меня диапазоны дат не пересекаются. Поэтому в простом запросе он вынужден читать все DateTo записей, у которых DateFrom<=SearchDate. А тут уже поиск по кластерному индексу явно быстрее, чем по не кластерному, в который Вы еще предложили добавить дополнительные 16 МБ.
Выборка 10000_10 тут как раз совершенно не интересна. А вот выборка по 100_10000 наоборот, показательна:
Politura
Боже мой, я вас нисколько не уговариваю отказаться от такого классного запроса с дополнительным условием, который вы придумали, конечно он будет более оптимальным. Вы молодец!
Я писал совсем о другом.
О конкретном абзаце, где вы упомянули определенный узкий сценарий и что для него кластерный индекс будет работать. Я лишь написал, что некластерный индекс будет работать абсолютно точно также для этого узкого сценария, если его слегка изменить. Неужели так сложно это проверить и убедиться? Зачем вы все продолжаете выполнять запрос для другого сценария?
ptr128 Автор
Именно потому, что он, как Вы сами выразились "узкий". Когда у одной аналитике лишь десяток диапазонов дат, совершенно монопенисуально, каким запросом и с какими индексами оттуда выбирать.
Ну нет на нем заметной разницы:
Politura
Да и еще, там, где вы сравниваете индексы, вы слегка лукавите: вы сравниваете кластерный, и не кластерный индексы. Попробуйте создать два таких индекса и сравнить их между собой, разница будет конечно, но не такая огромная:
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 Автор
Я сравниваю два случая. Когда можно создать кластерный индекс именно по нужным в запросе полям и когда кластерный индекс уже есть по другим полям и там он нужнее.
Вариант, когда вообще кластерного индекса нет я даже не рассматриваю, так как в этой ситуации он явно надуман.
К чему приводят лишние 16 МБ индекса я уже показал на 100_10000. Там это весьма показательно.
Politura
Если у вас есть кластерный индекс по AnalyticId и DateFrom, вам уже никакой дополнительный индекс не нужен. Однако если кластерный индекс по Id, то вы все равно добавляете такой индекс:
CREATE UNIQUE INDEX tmp_analytics_with_date_range_AnalyticId_IdxON tmp_analytics_with_date_range(AnalyticId, DateFrom) INCLUDE (DateTo)
А раз так, то корректнее сравнивать этот индекс, с таким-же индексом куда добавленно поле Value в include.
Угу, показали, только сами не заметили, что показали. В сообщении https://habr.com/ru/articles/751238/#comment_25802744 ваш запрос с дополнительным
OUTER APPLYдля случая когда в некластерном индексе в include только полеDateToрезультат такой:А для случая когда там еще и поле
Valueрезультат такой:Оно и понятно - в первом случае после всех манипуляций надо лезть в кластерный индекс, чтоб оттуда забрать значение Value, а во втором случае значение Value уже есть в используемом запросом индексе и никаких дополнительных телодвижений делать не надо.
ptr128 Автор
Я как раз заметил, что показал и уже не раз заострял на этом внимание. Ничтожное ускорение второго запроса приводит к заметному замедлению первого. О чем я уже писал неоднократно:
Вы же по прежнему упорно хотите ускорить и так быстрый запрос, затормозив медленный.
Politura
Да, действительно не заметил про единицы процентов выигрыша, прошу прощения. Только вы план запроса-то посмотрели? Там эти единицы процентов выигрыша (на самом деле 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 Автор
Естественно смотрел. Но у меня нет под рукой настолько тормозного сервера, чтобы разница во времени выполнения сложного запроса по 100_10000 стабильно различалась. То без Value выдаст 8 мс, то с ним 10 мс. Или наоборот.
Да? Ну давайте ближе к реалиям. Пусть у нас есть не только Value, а еще и Value1-Value7. И есть несколько десятков запросов, которым нужны из этих восьми Value от одного до трех в разных комбинациях. Ваше предложение? Влепить все 8 Value в INCLUDE?
А если уже совсем про реалии, то в таблице, которая подвинула меня на эту статью, лишь заголовки документов и еще больше двух десятков полей, кроме ключевых. А у заголовков есть еще еще и строки в другой таблице, которые уже ссылаются на ID заголовка, который и есть кластерный индекс. А значит, в реальной жизни, хоть в лоб, хоть по лбу, этот кластерный индекс приходится читать, чтобы вытащить строки. Мы же с чего начинали? Что кластерный индекс по нашим ключевым полям построить не можем, так как он необходим для других целей! А если других целей нет и можем построить кластерный индекс, то все, я показал выше, что по кластерному индексу поиск будет быстрее, чем по не кластерному с INCLUDE.
А Вы попробуйте себе представить не сферического коня в вакууме, а реальную картину. Когда у Вас не одно Value и единственный запрос, а десятки атрибутов и мер и десятки разных запросов, которым нужны разные подмножества из этих атрибутов и мер. Вот тогда порочность включения в INCLUDE всего подряд сразу станет Вам понятней.
Легко. Смотрите выше про восемь разных Value. А если их 20? 50? 100?
С каких пор количество полей в таблице стало влиять на размер кластерного индекса?
Politura
Нет. Но если у вас есть очень частый запрос пары полей из этой таблицы, у которого фильтр идет по другим полям, для которых есть некластерный индекс, имеет смысл попробовать добавить эту пару полей в include оного индекса, запрос должен будет существенно ускорится, т.к. все данные будут в одном индексе и никуда больше лезть за данными не надо.
ptr128 Автор
Я уже показал выше, что ускорение запроса будет, но очень незначительное, а вот потребление оперативной памяти, замедление модификации таблицы и обслуживания ее индексов - значительное.
На практике, такое очень и очень редко встречается. А вот выстроившиеся в очередь на оперативку сервера запросы я у клиентов встречал не раз. Поэтому десять раз подумаю, прежде чем раздувать размер индекса.