Пакетный запрос - передать результат одного запроса в ПАРАМЕТРЫ другого
Есть два регистра сведений - один большой, 20 млн. записей, другой поменьше - всего 350 тысяч.
Требуется в запросе по первому регистру отфильтровать период (Период МЕЖДУ &ПериодМин И &ПериодМакс) по результатам значений запроса по второму регистру (МИНИМУМ(Период) и МАКСИМУМ(Период)).
Лично я вижу 2 варианта решения:
1. Разбить пакет запросов на 2 части и установить параметры вручную:
2. Выгрузить первого запроса во временную таблицу с одной записью и поставить её слева в связи. Но 20 млн. записей это 20 млн. записей.
Может быть, есть метод лучше, который я не знаю?
Требуется в запросе по первому регистру отфильтровать период (Период МЕЖДУ &ПериодМин И &ПериодМакс) по результатам значений запроса по второму регистру (МИНИМУМ(Период) и МАКСИМУМ(Период)).
Лично я вижу 2 варианта решения:
1. Разбить пакет запросов на 2 части и установить параметры вручную:
ВторойЗапрос.УстановитьПараметр("ПериодМин", ВыборкаИзПервогоЗапроса.ПериодМин);
2. Выгрузить первого запроса во временную таблицу с одной записью и поставить её слева в связи. Но 20 млн. записей это 20 млн. записей.
Может быть, есть метод лучше, который я не знаю?
По теме из базы знаний
Найденные решения
(1) Из первого запроса пакета получить во временную таблицу МинПериод и МаксПериод. Всего одна запись.
Затем соединить эти записи с первой таблицей левым соединением.
Пример:
По производительности этот запрос (итоговый) будет аналогичен такому:
Затем соединить эти записи с первой таблицей левым соединением.
Пример:
ВЫБРАТЬ
МИНИМУМ(ВТ_Периоды.Период) КАК МинПериод,
МАКСИМУМ(ВТ_Периоды.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_ГраницыПериодов
ИЗ
ВТ_Периоды КАК ВТ_Периоды
;
//////////////////////////////////////////////////////////// ////////////////////
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
ВТ_ГраницыПериодов КАК ВТ_ГраницыПериодов
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_ГраницыПериодов.МинПериод <= ЦеныНоменклатуры.Период
И ВТ_ГраницыПериодов.МаксПериод >= ЦеныНоменклатуры.Период
ПоказатьПо производительности этот запрос (итоговый) будет аналогичен такому:
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.Период МЕЖДУ &МинПериод И &МаксПериод
Показать
(3) Вот тестовый пример, может быть это вам надо. Передаю результат одного запроса в виде таблицы в параметры другого запроса. Выполнен на УТ 11.4. Получаем цены услуг.
Есть важные нюансы:
1. Внешнюю таблицу обязательно нужно сразу помещать во временную.
2. При создании описания временной таблицы в конструкторе обязательно использовать & (амперсанд) перед именем таблицы.
3. Ну и, само собой, должны совпадать имена полей во внешней таблице и в описании.
&НаСервере
Процедура Команда1НаСервере()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Номенклатура
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.ТипНоменклатуры = ЗНАЧЕНИЕ(Перечисление.ТипыНоменклатуры.Услуга)";
ТаблицаТолькоУслуги = Запрос.Выполнить().Выгрузить();
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВТ_Услуги.Номенклатура КАК Номенклатура
|ПОМЕСТИТЬ ВТ_Услуги
|ИЗ
| &ВТ_Услуги КАК ВТ_Услуги
|;
|
|//////////////////////////////////////////////////////////// ////////////////////
|ВЫБРАТЬ
| ВТ_Услуги.Номенклатура КАК Номенклатура,
| ЦеныНоменклатуры.Цена КАК Цена
|ИЗ
| ВТ_Услуги КАК ВТ_Услуги
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
| ПО ВТ_Услуги.Номенклатура = ЦеныНоменклатуры.Номенклатура";
Запрос.УстановитьПараметр("ВТ_Услуги", ТаблицаТолькоУслуги);
ТаблицаЦенУслуг = Запрос.Выполнить().Выгрузить();
КонецПроцедуры
&НаКлиенте
Процедура Команда1(Команда)
Команда1НаСервере();
КонецПроцедуры
ПоказатьЕсть важные нюансы:
1. Внешнюю таблицу обязательно нужно сразу помещать во временную.
2. При создании описания временной таблицы в конструкторе обязательно использовать & (амперсанд) перед именем таблицы.
3. Ну и, само собой, должны совпадать имена полей во внешней таблице и в описании.
(13) Прим так:
ВЫБРАТЬ
МИНИМУМ(ВТ_Периоды.Период) КАК МинПериод,
МАКСИМУМ(ВТ_Периоды.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_ГраницыПериодов
ИЗ
ВТ_Периоды КАК ВТ_Периоды
;
//////////////////////////////////////////////////////////// ////////////////////
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
ВТ_ГраницыПериодов КАК ВТ_ГраницыПериодов,
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
Где ВТ_ГраницыПериодов.МинПериод <= ЦеныНоменклатуры.Период
И ВТ_ГраницыПериодов.МаксПериод >= ЦеныНоменклатуры.Период
ПоказатьОстальные ответы
Подписаться на ответы
Инфостарт бот
Сортировка:
Древо развёрнутое
Свернуть все
(1) Из первого запроса пакета получить во временную таблицу МинПериод и МаксПериод. Всего одна запись.
Затем соединить эти записи с первой таблицей левым соединением.
Пример:
По производительности этот запрос (итоговый) будет аналогичен такому:
Затем соединить эти записи с первой таблицей левым соединением.
Пример:
ВЫБРАТЬ
МИНИМУМ(ВТ_Периоды.Период) КАК МинПериод,
МАКСИМУМ(ВТ_Периоды.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_ГраницыПериодов
ИЗ
ВТ_Периоды КАК ВТ_Периоды
;
//////////////////////////////////////////////////////////// ////////////////////
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
ВТ_ГраницыПериодов КАК ВТ_ГраницыПериодов
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ВТ_ГраницыПериодов.МинПериод <= ЦеныНоменклатуры.Период
И ВТ_ГраницыПериодов.МаксПериод >= ЦеныНоменклатуры.Период
ПоказатьПо производительности этот запрос (итоговый) будет аналогичен такому:
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.Период МЕЖДУ &МинПериод И &МаксПериод
ПоказатьПо производительности этот запрос (итоговый) будет аналогичен такому:
Видел подобные конструкции в типовых. Но гложут сомнения что по производительности аналогично. Есть что почитать на эту тему? Может исследовал уже кто?
(9) достаточно посмотреть план выполнения запроса. Они почти идентичные.
Для первого запроса (в части итогового запроса):
NESTED OUTER LOOP
_INFORG35506 (T2) RANGE SCAN USING INDEX (_INFORG35506_1@) (2 fields)
WHERE
(T1._Q_001_F_000 <= T2.Период)
AND
(T1._Q_001_F_001 >= T2.Период)
Statistics: RecordsScanned = 1189, ParseTime = 0, ExecuteTime = 2, BuffersMemory = 95552, ResultRecords = 1188, RecordSize = 60
Для второго запроса:
_INFORG35506 (T1) RANGE SCAN USING INDEX (_INFORG35506_1@) (2 fields)
WHERE
(T1.Период >= 20150301000000)
AND
(T1.Период <= 20150304000000)
Statistics: RecordsScanned = 1188, ParseTime = 0, ExecuteTime = 2, BuffersMemory = 95552, ResultRecords = 1188, RecordSize = 60
Для первого запроса (в части итогового запроса):
NESTED OUTER LOOP
_INFORG35506 (T2) RANGE SCAN USING INDEX (_INFORG35506_1@) (2 fields)
WHERE
(T1._Q_001_F_000 <= T2.Период)
AND
(T1._Q_001_F_001 >= T2.Период)
Statistics: RecordsScanned = 1189, ParseTime = 0, ExecuteTime = 2, BuffersMemory = 95552, ResultRecords = 1188, RecordSize = 60
Для второго запроса:
_INFORG35506 (T1) RANGE SCAN USING INDEX (_INFORG35506_1@) (2 fields)
WHERE
(T1.Период >= 20150301000000)
AND
(T1.Период <= 20150304000000)
Statistics: RecordsScanned = 1188, ParseTime = 0, ExecuteTime = 2, BuffersMemory = 95552, ResultRecords = 1188, RecordSize = 60
(1)
2. Выгрузить первого запроса во временную таблицу с одной записью и поставить её слева в связи. Но 20 млн. записей это 20 млн. записей.
Вот разумная мысль, что 20 млн. записей во временной таблице - это не комильфо. И решается все наоборот - кладется во временную таблицу одна строчка с минимумом и максимумом из второго регистра (если нет больше никаких данных, кроме периода). Дальше обычная выборка из большого регистра, к ней присоединение (можно прям через запятую) этой одной строчки, в ГДЕ та же конструкция МЕЖДУ, в которой из этой одной строчки минимальная и максимальная дата. Все. Индекс на период обычно есть, так что будет достаточно оптимально.
(3) Вот тестовый пример, может быть это вам надо. Передаю результат одного запроса в виде таблицы в параметры другого запроса. Выполнен на УТ 11.4. Получаем цены услуг.
Есть важные нюансы:
1. Внешнюю таблицу обязательно нужно сразу помещать во временную.
2. При создании описания временной таблицы в конструкторе обязательно использовать & (амперсанд) перед именем таблицы.
3. Ну и, само собой, должны совпадать имена полей во внешней таблице и в описании.
&НаСервере
Процедура Команда1НаСервере()
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Ссылка КАК Номенклатура
|ИЗ
| Справочник.Номенклатура КАК Номенклатура
|ГДЕ
| Номенклатура.ТипНоменклатуры = ЗНАЧЕНИЕ(Перечисление.ТипыНоменклатуры.Услуга)";
ТаблицаТолькоУслуги = Запрос.Выполнить().Выгрузить();
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ВТ_Услуги.Номенклатура КАК Номенклатура
|ПОМЕСТИТЬ ВТ_Услуги
|ИЗ
| &ВТ_Услуги КАК ВТ_Услуги
|;
|
|//////////////////////////////////////////////////////////// ////////////////////
|ВЫБРАТЬ
| ВТ_Услуги.Номенклатура КАК Номенклатура,
| ЦеныНоменклатуры.Цена КАК Цена
|ИЗ
| ВТ_Услуги КАК ВТ_Услуги
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
| ПО ВТ_Услуги.Номенклатура = ЦеныНоменклатуры.Номенклатура";
Запрос.УстановитьПараметр("ВТ_Услуги", ТаблицаТолькоУслуги);
ТаблицаЦенУслуг = Запрос.Выполнить().Выгрузить();
КонецПроцедуры
&НаКлиенте
Процедура Команда1(Команда)
Команда1НаСервере();
КонецПроцедуры
ПоказатьЕсть важные нюансы:
1. Внешнюю таблицу обязательно нужно сразу помещать во временную.
2. При создании описания временной таблицы в конструкторе обязательно использовать & (амперсанд) перед именем таблицы.
3. Ну и, само собой, должны совпадать имена полей во внешней таблице и в описании.
(13) Прим так:
ВЫБРАТЬ
МИНИМУМ(ВТ_Периоды.Период) КАК МинПериод,
МАКСИМУМ(ВТ_Периоды.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_ГраницыПериодов
ИЗ
ВТ_Периоды КАК ВТ_Периоды
;
//////////////////////////////////////////////////////////// ////////////////////
ВЫБРАТЬ
ЦеныНоменклатуры.Период КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
ЦеныНоменклатуры.ВидЦены КАК ВидЦены,
ЦеныНоменклатуры.Цена КАК Цена
ИЗ
ВТ_ГраницыПериодов КАК ВТ_ГраницыПериодов,
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
Где ВТ_ГраницыПериодов.МинПериод <= ЦеныНоменклатуры.Период
И ВТ_ГраницыПериодов.МаксПериод >= ЦеныНоменклатуры.Период
Показать
(15) Идея хороша, только рекомендуют вместо >= и <= использовать МЕЖДУ - так оптимизатор лучше понимает, что от него хотят.
Теперь вопрос знатокам MS SQL - как эффективнее - через левое соединение с временной таблицей из одно записи или через ГДЕ? Не будет ли full scan в случае с ГДЕ? Или надо смотреть конкретно?
Теперь вопрос знатокам MS SQL - как эффективнее - через левое соединение с временной таблицей из одно записи или через ГДЕ? Не будет ли full scan в случае с ГДЕ? Или надо смотреть конкретно?
(16)
См. (4) и (10). Получается абсолютно одинаковое условие в плане выполнения запроса.
То же самое. Абсолютно одинаково в данном случае.
Я даже больше скажу. Для запроса из (15) будет такой- же план выполнения запроса (с небольшими вариациями).
только рекомендуют вместо >= и <= использовать МЕЖДУ - так оптимизатор лучше понимает, что от него хотят.
См. (4) и (10). Получается абсолютно одинаковое условие в плане выполнения запроса.
Теперь вопрос знатокам MS SQL - как эффективнее - через левое соединение с временной таблицей из одно записи или через ГДЕ? Не будет ли full scan в случае с ГДЕ? Или надо смотреть конкретно?
То же самое. Абсолютно одинаково в данном случае.
Я даже больше скажу. Для запроса из (15) будет такой- же план выполнения запроса (с небольшими вариациями).
Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот