Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 JOIN по Between и индексы  [new]
Sevenfly
Member

Откуда:
Сообщений: 64
Добрый день.

Подскажите, есть ли какой-то best practice по JOIN двух таблиц по условию, например:

FROM table_1 as t1
JOIN table_2 as t2 ON t1.ID = t2.ID AND t1.dt BETWEN t2.dt_from AND t2.dt_to


В t2 стоит уникальный индекс на поля (id, dt_from, dt_to).

Но судя по всему планировщик запросов не может определить, что по такому запросу за JOIN'ится лишь одна запись и рисует больший план выполнения запроса, чем если делать структуру таблиц под запрос

FROM table_1 as t1
JOIN table_2 as t2 ON t1.ID = t2.ID AND t1.dt = t2.dt


Хотя во втором случае в таблице раз в 10 больше записей и по идее запрос должен выполняться дольше.

Можно ли как-то оптимизировать эту ситуацию?
13 ноя 15, 15:01    [18412568]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Sevenfly,

а в table_1 индексы есть?
13 ноя 15, 15:04    [18412597]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Sevenfly
Member

Откуда:
Сообщений: 64
iap,

Конечно, уникальный по ID
13 ноя 15, 15:07    [18412618]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
Sevenfly
iap,

Конечно, уникальный по ID
А в WHERE участвует и dt
13 ноя 15, 15:08    [18412634]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Sevenfly,

автор
Хотя во втором случае в таблице раз в 10 больше записей и по идее запрос должен выполняться дольше.

Не должен. А если при этом будет unique (id, dt), то будет совсем хорошо.

А в случае с BETWEEN, естественно, оптимизатор думает, что там может быть более 1 записи (ибо >=dt_from AND <=dt_to), и строит менее выгодный план, чем хотелось бы. А из-за того, что слева в условии стоит t1.dt, польза индекса в t2 сползает под плинтус и заливается горькими слезами. И максимум используется для связки по ID (если имеет смысл с точки зрения селективности).
13 ноя 15, 15:14    [18412684]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Sevenfly
Member

Откуда:
Сообщений: 64
Сид,

Ошибся, индекс конечно установлен unique (id, dt) в первой таблице

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

про то что date_to в индексе не играет роли, понятно. Можно ставить индекс Unique (ID, date_from) INCLUDE (date_to), но на выполнение запроса это особо не влияет
13 ноя 15, 15:33    [18412811]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
Sevenfly,

не очень уверен, но такая замена не поможет?
AND t1.dt >=t2.dt_from AND t1.dt <= t2.dt_to
13 ноя 15, 15:36    [18412835]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
Sevenfly,

Вы не написали - какие столбцы должны быть отобраны в запросе.
13 ноя 15, 15:37    [18412838]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
StarikNavy,

BETWEEN - это синтаксический сахар.
13 ноя 15, 15:38    [18412849]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Sevenfly
Member

Откуда:
Сообщений: 64
Владислав Колосов,

таблицы такие

table_1:

id,
val_t1

table_2:

id,
val_t2,
date_from,
date_to

выбирается в итоге:

SELECT
t1.val_t1,
t2.val_t2
FROM table_1 as t1
JOIN table_2 as t2 ON t1.ID = t2.ID AND t1.dt BETWEN t2.dt_from AND t2.dt_to
13 ноя 15, 15:47    [18412906]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Sevenfly
Member

Откуда:
Сообщений: 64
StarikNavy,

Это по сути просто разложение того, во что трансформируется BETWEEN. С точки зрения оптимизатора запроса они эквивалентны
13 ноя 15, 15:48    [18412916]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Sevenfly
Можно ли как-то оптимизировать эту ситуацию?

Вы про производительность или про оценки оптимизатора в плане ?
13 ноя 15, 15:59    [18413002]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Сид
Member

Откуда: Москва
Сообщений: 305
Sevenfly
Владислав Колосов,

таблицы такие

table_1:

id,
val_t1

table_2:

id,
val_t2,
date_from,
date_to

выбирается в итоге:

SELECT
t1.val_t1,
t2.val_t2
FROM table_1 as t1
JOIN table_2 as t2 ON t1.ID = t2.ID AND t1.dt BETWEN t2.dt_from AND t2.dt_to


По мне, этот запрос немного проще так читать (просто поменял местами t1 и t2):

SELECT
t1.val_t1,
t2.val_t2
FROM table_2 as t2
JOIN table_1 as t1 ON t1.ID = t2.ID AND t1.dt BETWEN t2.dt_from AND t2.dt_to


Соответственно, индекс по t1 unique(id, dt) include(val_t1)
Ведущая таблица всё равно получается t2, и по ней будет full scan. Это из запроса следует неизбежно.

Не удивлюсь, если окажется, что есть в конце какое-нибудь WHERE по t2. Тогда на основании этого можно будет строить индекс по t2.
13 ноя 15, 16:08    [18413070]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Sevenfly
Вопрос именно как оптимизировать вторую таблицу для условия BETWEEN, чтобы оптимизатор понимал, что будет одна запись.

про то что date_to в индексе не играет роли, понятно. Можно ставить индекс Unique (ID, date_from) INCLUDE (date_to), но на выполнение запроса это особо не влияет
Для задачи поиска интервала дат ничего лучше не придумал как построить индекс по (ID, date_to) INCLUDE (date_from). Оно (в моём случае) оказалось ощутимо лучше за счёт статистики обращения к данным: записи с более поздними датами нужны чаще, чем с более ранними, и при такой замене больше их обрезается индексом.
Только вряд ли от этого оптимизатор будет что-то лучше понимать.

На SQL2008 есть ещё пространственные (SPATIAL) индексы, я их не пробовал, но по отзывам здесь - они сами по себе жутко медленные.
13 ноя 15, 16:25    [18413185]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
Serg_77m
Для задачи поиска интервала дат ничего лучше не придумал как построить индекс по (ID, date_to) INCLUDE (date_from).

Придумали.
Если там целые даты - только дни -> сгенерить таблицу из t2 в которой будет столько строк сколько в t2 * на количество дней между
dt_from и dt_to.
т.е если была строка id - 111, dt_from - '2015-10-01', dt_to - '2015-10-03'
то надо сделать три - с датой dt_new = '2015-10-01' , с датой dt_new = '2015-10-02', и с датой dt_new = '2015-10-03'
Индекс уникальный само собой на id, dt_new и и джоин превращается в

FROM table_1 as t1
JOIN table_2_NEW as t2 ON t1.ID = t2.ID AND t1.dt = t2.dt_new;

что при нормальной селективности дат, даст ускорение в разы, даже с учетом большей таблицы t2_new
13 ноя 15, 16:40    [18413281]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8826
Я думаю, что единого рецепта для такого запроса нет, оптимизатор будет стоить разные планы для разной селективности.
13 ноя 15, 16:51    [18413347]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Ivan Durak
Member

Откуда: Minsk!!!
Сообщений: 3754
но в моем варианте как минимум решится вот эта проблема автора:
Sevenfly
Но судя по всему планировщик запросов не может определить, что по такому запросу за JOIN'ится лишь одна запись и рисует больший план выполнения запроса, чем если делать структуру таблиц под запрос
13 ноя 15, 17:06    [18413435]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Ivan Durak
Если там целые даты - только дни -> сгенерить таблицу из t2 в которой будет столько строк сколько в t2 * на количество дней между
dt_from и dt_to.
т.е если была строка id - 111, dt_from - '2015-10-01', dt_to - '2015-10-03'
то надо сделать три - с датой dt_new = '2015-10-01' , с датой dt_new = '2015-10-02', и с датой dt_new = '2015-10-03'
Индекс уникальный само собой на id, dt_new и и джоин превращается в

FROM table_1 as t1
JOIN table_2_NEW as t2 ON t1.ID = t2.ID AND t1.dt = t2.dt_new;

что при нормальной селективности дат, даст ускорение в разы, даже с учетом большей таблицы t2_new
А если интервал в три года, то записей на одну исходную надо создать больше тысячи?
А интервалы ещё и полуоткрытые бывают: от некоторой даты до бесконечности, например.
Может, в каких-то случаях такое решение и лучше будет, но, наверное, универсального рецепта, и правда, нет.
13 ноя 15, 17:07    [18413444]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Ivan Durak, а ведь получается интересная идея, если эту вспомогательную табличку сделать многоуровневой. Создать табличку с колонками: номер уровня, дата и ссылку на исходную таблицу. На первом уровне перечислить даты, попадающие в интервал (полностью или частично) с интервалом, например, год. На втором - даты с интервалом в месяц, которые попадают в исходный интервал, но не все, а только те, которые частично попали в интервал по годам на предыдущем уровне. На третьем - все дни, но опять-таки не все, а только которые частично накрыли месяцы. Если нужно со временем, можно дальше детализировать. По-моему, это можно сделать даже индексированным представлением, если задействовать табличку с числами. Собственно, это и получится некое подобие пространственного индекса, причём работающее и на более ранних версиях сервера. Правда, заниматься этим имеет смысл только если основное торможение именно на поиске интервалов... Ну да, поиск усложнится, но как оно в конечном итоге получится - вопрос интересный. Попробовать, что ли?
13 ноя 15, 17:42    [18413671]     Ответить | Цитировать Сообщить модератору
 Re: JOIN по Between и индексы  [new]
Serg_77m
Member

Откуда: Донецк
Сообщений: 237
Попробовал. Результаты выложил здесь: Методы поиска интервала дат
16 ноя 15, 19:07    [18425862]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить