Временные таблицы и индексы в запросах #677715

Видел в типовом коде (ЗУП) подобные конструкции запросов: Выбрать какой-то набор полей поместить втНеСутьВажноКакойПромежуточныйНаборДанных Выбрать вт.полеОтбора как ЗначениеОтбора поместить втСписокОтбора из втНеСутьВажноКакойПромежуточныйНаборДанных как вт индексировать по ЗначениеОтбора ; Выбрать ещё какой-то набор полей из какойтоисточникЕщёОдин как данные где данные.КакоеТоПоле В ( выбрать ЗначениеОтбора из втСписокОтбора ) При этом данные.КакоеТоПоле - не индексированное поле! Временная таблица втСписокОтбора используется лишь 1-2 раза внутри пакетного запроса. и вот, собственно вопрос - насколько эффективна такая конструкция (я имею в виду оператор В и предварительное индексирование временной таблицы втСписокОтбора (разве на внутренней выборке внутри В индекс не будет потерян)? и не эффективнее ли было использовать ВНУТРЕННЕЕ СОЕДИНЕНИЕ для таблицы втСписокОтбора (причём такой поход в ЗУП тоже используется и  в тех же пакетных запросах) Изменится ли как-нибудь ответ, если  запросы будут с составным набором полей Выбрать какой-то набор полей поместить втНеСутьВажноКакойПромежуточныйНаборДанных Выбрать вт.полеОтбора1 как ЗначениеОтбора1, вт.полеОтбора2 как ЗначениеОтбора2, вт.полеОтбора3 как ЗначениеОтбора3, вт.полеОтбора4 как ЗначениеОтбора4 поместить втСписокОтбора из втНеСутьВажноКакойПромежуточныйНаборДанных как вт индексировать по ЗначениеОтбора1, ЗначениеОтбора2 ; Выбрать ещё какой-то набор полей из какойтоисточникЕщёОдин как данные где (данные.КакоеТоПоле1, данные.КакоеТоПоле2, данные.КакоеТоПоле3, данные.КакоеТоПол4) В ( выбрать ЗначениеОтбора1, ЗначениеОтбора2, ЗначениеОтбора3, ЗначениеОтбора4 из втСписокОтбора )

ты уже тут чушь решил нести. Индексировать ПО - это как раз и есть индексирование созданной временной таблицы БД. А что ты имеешь ввиду под "а индексирование попавшего в отбор для дальнейшего использования" одному астралу известно.

По поводу вот этого (данные.КакоеТоПоле1, данные.КакоеТоПоле2, данные.КакоеТоПоле3, данные.КакоеТоПол4) В ( выбрать ЗначениеОтбора1, ЗначениеОтбора2, ЗначениеОтбора3, ЗначениеОтбора4 из втСписокОтбора ) на мой взгляд эффективнее будет внутреннее соединение. Тут человек сравнивал, ближе к концу первой страницы.

есть нюанс

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

Согласен.

Че это? Дубляж строк? Это мы осилим. А так - да, "ВНУТРЕННЕ" лучше использовать, чем "В".

ну давай рассказывай, что ты имел ввиду под "а индексирование попавшего в отбор для дальнейшего использования" ? и что понимаешь под "попавшего в отбор" , физически как хранится этот твой, так называемый отбор ?

Вряд ли ты что от него добьешься.

Забыл добавить  дирепктиву "РАЗЛМЧНЫЕ" при формировании временной таблтцы втСписокОтбора - для внутрннено соединения это очень важно, для оператора "В" - только оптимизация. Так что будем считать что мы заранее побеспокоились об этом и говорим лишь о производительности. Спасибо за линк про ещё одно обсуждение вреда ИЛИ - почитаю. Я тут не суть индкексирования таблиц обсуждаю - а реальную пользу от этих индексов в определённых конструкциях запросов. Расширя свой вопрос можно его дополнить так: Если таблица втСписокОтбора  - это не временная таблица, а реальная таблица ИБ, у которой указанное поле(я) проиндекированы в БД (допустим это измерения регистра с установленным параметров "Индексировать") - по сути от этого ведь ответ не изменится! Или изменится?

Давайте попробуем уточнить одну важную деталь, которую я хочу понять - при указанном использовании оператор "В" - индекс по полю(ям) втСписокОтбора даёт ли какое-то преимущество и вообще используется ли? То, что он дат положительный эффект в конструкции "ВНУТРЕННЕЕ СОЕДИНЕНИЕ" - это факт, мы не обсуждаем его наличие.

посмотри в плане запроса, во что твой "В" превращается.. усё станет понятней.

Тут есть нюанс "допустим это измерения регистра с установленным параметров "Индексировать")". Установленный признак "Индексировать" для обычной таблицы создает дополнительный отдельный индекс по этому полю. Соотвественно для двух измерений с признаком "Индексировать" создаются два отдельных индекса, поэтому при внутреннем соединении по этим нескольким полям индексы могут не использоваться. И у меня у самого попутно вопрос возник. При использовании Индексировать По для временной таблицы при указании нескольких полей, создается один составной индекс ?

запилил замер - в справочнике ~160к записей, отбор по 5% случайных из них. пока выполняется.

ОФТОП, ради любопытства - а как Вы отбирает именно 5% элементов справочника в запросе? Надеюсь это не банальное: "ну я заранее посчитал сколько 5% будет в шутках от всего числа элементов и поставил это значение в директиве "ПЕРВЫЕ"! Что кстати не даёт в общем смысле "случайные" записи - и повторный запуск с почти 100% вероятностью вернёт тот же набор (или очень близкий к нему).

Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через массив: " + (ТекущаяДата - ТекДата - 1)); Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через внутреннее соединение: " + (ТекущаяДата - ТекДата - 1)); Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через В: " + (ТекущаяДата - ТекДата - 1)); Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через В (Exists): " + (ТекущаяДата - ТекДата - 1)); Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через внутреннее соединение + индекс: " + (ТекущаяДата - ТекДата - 1)); Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через В + индекс: " + (ТекущаяДата - ТекДата - 1)); Пока ТекДата = ТекущаяДата Цикл КонецЦикла; Сообщить("Отбор через В (Exists) + индекс: " + (ТекущаяДата - ТекДата - 1));

результаты: Отбор через массив: 91 Отбор через внутреннее соединение: 65 Отбор через В: 72 Отбор через В (Exists): 68 Отбор через внутреннее соединение + индекс: 71 Отбор через В + индекс: 69 Отбор через В (Exists) + индекс: 71

А как же кластерный индекс и его преимущества при  правильном следовании индексированных полей?

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

вот тут недавно сняли режим совместимости - запрос с Выразить(Таблица.Поле как Документ.Документ) = значение перестал использовать индекс (в режиме совместимости 8.1 испоьлзовал)

О да, чувствуется фундаментальный подход. Да, у Вас уже есть соответствующие наработки. Круто.

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

А для чего вот это ТекДата = ТекущаяДата; Пока ТекДата = ТекущаяДата Цикл КонецЦикла;

ожидание начала секунды

Результат, скажем, неожиданный - индексы проиграли внутреннему соединению без индексов. А конструкция "В" с индексом выиграла у внутреннего соединения с индексом. Ладно, это понятно - для чистоты замеров. Но У вас  выборка одна и та же - как же кеш. Или это как раз то, что нужно - понятно что кеш, разбираем производительность разных подходов когда данные априори берутся из кеша, какой подход их эффективнее от-туда возьмёт.

Да, удивительный это 1С. Вот тоже недавно отлаживал оптимизацию одного запроса. Так у меня  условие вида Поле Ссылка ТипСсылки работало значительно медленнее чем ВЫРАЗИТЬ(Поле КАК ТипСсылки) НЕ ЕСТЬ NULL

Посмотрел планы запроса для Select id From sc72 Where id in (select id from #tempkl) и Select sc.id From sc72 as sc inner join #tempkl as t on t.id = sc.id оба почти одинаковые, в конце inner join / nested loops только для in дополнительно есть еще group by для #tempkl. для id в #tempkl создан индекс.

судя по всему в операторе "В" всё же наличие индекса играет роль. Замеры в тому подтверждение (думаю, что разница в 2 сек всё же погрешности и флукцтации самого SQL сервера). Но вот, то что без индексов ВНУТРЕННЕЕ СОЕДИНЕНИЕ обогнало все тесты с индексом? Хотя есть более простое объяснение всему этому - SQL просто забил на индекс при построении плана запроса и не использовал его. Поэтому все индексированные тесты прошли одинаково, и были медленнее ВНУТРЕННЕГО СОЕДИНЕНИЯ без индексов, т.к. уходило время  на создание индекса (правда, что уже очень много уходило  разница достаточно большая). Думаю, что данный тест нужно прогнать в цикле раз 100 хотя бы (на каждой итерации свой случайный набор выборки; естественно замеры усредняются по каждому непосредственному тесту). Вот, тогда будет более правильный результат. Да, и ещё, после основной выборки данных для отбора, я бы ещё 3 сделал таких же, но нигде далее не используемых - для сброса кеша и так прогнал бы ещё одну серию тестов для сравнения.

потому что создание индекса тожезанимает время

для 1% записей: Отбор через массив: 18 Отбор через внутреннее соединение: 12 Отбор через В: 13 Отбор через В (Exists): 13 Отбор через внутреннее соединение + индекс: 15 Отбор через В + индекс: 14 Отбор через В (Exists) + индекс: 14

Всё тоже самое, что и в

для 0,01% записей (1000 повторений): Отбор через массив: 4 Отбор через внутреннее соединение: 16 Отбор через В: 15 Отбор через В (Exists): 19 Отбор через внутреннее соединение + индекс: 23 Отбор через В + индекс: 25 Отбор через В (Exists) + индекс: 25

Что так массив круто вырвался вперёд? А индексы вообще отстали - ну тут то ясно дело, что затраты на индексирование небольших объёмов данных не оправданы.

Кстати, а есть вопросы к генератору случайной выборки отбора: 1. Не увидел фильтра на отсечение уже выбранных контрагентов (там же в несколько итераций это происходит) - т.е. весьма вероятны дубли - или это так и нужно? 2. И всё-таки сама выборка с использованием  директивы "ПЕРВЫЕ" (да ещё и с упорядоичванием) не такая уже и случайная получается, даже если инвертировать порядок. Наверное, надо было генериь два случайный числа, и на их основе искать по коду контрагентов, отсекая сверху и снизу (правда они строковые - значит нужно использовать оператор "ПОДОБНЫЕ" - а это очень медленно).

И кстатит в дополнение к , там не всегда наличие индекса для таблицы условий играет роль. В варианте в таблице условия где-то 5% записей. Поле id основной таблицы sc72 индексированно. В плfне запроса получается index scan / table scan (зависит от наличия индекса) для меньшей таблицы, и index seek по основной таблице для каждой записи таблицы условия. Я так подозреваю index scan от table scan особо не отличаются по скорости, разве что объемом чтения.

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

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

+ отчасти это ответ на вопрос в "при указанном использовании оператор "В" - индекс по полю(ям) втСписокОтбора даёт ли какое-то преимущество и вообще используется ли"

если по простому, то индексировать надо по полям которые часто используются в связях? Для 1000 документов есть смысл строить индекс?

Ок, кажется ясно Никакого смысла нет

И опять же нюанс. Скорее всего это для внутреннего соединения по одному полю подходит. А вот если условие соедиенния по двум полям, то учитывая что для обычной таблицы в 1С нельзя задать произвольный составной индекс, то лучше временную проиндексировать по нескольким полям, тогда может быть быстрее table scan основной и поиск по индексу во временной.

.1 да и пофиг .2 дело именно в случайности количества. мы выбираем из 1,2,3,4,5 первые случайное количество (допустим, 3), получаем 1,2,3. сортируем по убыванию, получаем первые 1 - итого 3

у меня небольшой справочник контрагентов (надо на регистре проводок по БУ попробовать - там много записей): Количество всего 4 237 отобрано 847 повторений 1000 Отбор через массив: 83 Отбор через внутреннее соединение: 52 Отбор через В: 61 Отбор через В (Exists): 59 Отбор через внутреннее соединение + индекс: 65 Отбор через В + индекс: 72 Отбор через В (Exists) + индекс: 72 Количество всего 4 237 отобрано 84 повторений 10 000 Отбор через массив: 40 Отбор через внутреннее соединение: 153 Отбор через В: 164 Отбор через В (Exists): 158 Отбор через внутреннее соединение + индекс: 222 Отбор через В + индекс: 262 Отбор через В (Exists) + индекс: 280

Ну оно и получается, что индексирование для временной таблицы таки особой роли не играет, даже немного замедляет. Это для использования ее в качестве ограничивающего условия. Еще немного поэкспериментировал. Для количества записей во временной таблице выше 10 % от основной, вместо nested loops используется merge join и для обеих таблиц index scan . Если для временной таблицы не делать индексирование, то для нее table scan плюс дополнительно сортировка по полю условия. Вот оценить что быстрее, создание индекса и затем index scan, или сортировка таблицы по полю без индекса не возьмусь.