На проведение данного исследования меня натолкнула статья «Срез на даты за период. Шаблон запроса» с комментариями. Суть статьи: автор привел два варианта решения известной задачи — традиционный и свой собственный («интервальный»). В комментариях оба варианта подверглись резкой критике за сложность — большой объем текста, временные таблицы. Цитирую критиков:
«давно уже подобную задачу решают одним запросом (без всяких временных таблиц) с двумя левыми соединениями»
«Не вникал в смысл задачи, но срез на дату чего-либо по периоду делается простым 1 запросом без всяких ВТ.»
Комментаторы оказались людьми слова и дела, предложили два своих варианта, без временных таблиц, в один запрос.
Изначально, я был сторонником традиционного способа — он давно известен, интуитивно понятен (но это не точно) и более-менее оптимальный по производительности (это тоже не точно). Вариант автора — тоже интересный, мне такая идея в голову не приходила, но, почему-то, он мне показался менее производительным.
Оба варианта из комментариев — короткие, лаконичные, но затратные по ресурсам, на мой взгляд.
Но вообще, интересно провести исследование, и цифрами показать, какой из вариантов лучше и на сколько.
Почти год я собирался с мыслями. Потом провел эксперимент. Еще почти год созревал изложить это в виде статьи. Наконец, созрел :)
По ходу изучения темы, мне попался еще один вариант в статье «Минимализмы» п.4. Тоже в один запрос, довольно диковинное решение, вряд ли применимое на практике, но интересное.
Итого получилось 5 вариантов.
На мой взгляд, все такие задачи в реальности примерно одного типа. Возьмем самую типичную:
- организация продает товары (документ Реализация товаров);
- их цена хранится в регистре (периодический регистр сведений Цены) и меняется во времени;
- продавец может устанавливать скидку в момент продажи, но не более некоторого процентного значения;
- в какой-то момент времени нужно проверить, что все скидки действительно не превышали этого порога;
- если превышали, то где, когда, на сколько;
- значения скидок в документах не хранятся (или хранятся, но мы им не доверяем), а считаем отношение цены из документа продажи к значению цены их регистра на дату.
Что ж, осталось собрать тестовый стенд и запустить наши запросы:
Железо (ноутбук):
- процессор AMD Ryzen 5 2500U,
- оперативная память 12 Gb,
- диск SSD Samsung PM871b SATA3
Программное обеспечение:
- Windows 10 Home (64)
- Microsoft SQL Server 2017 14.0.2002.14 (X64) Developer Edition (64-bit) (для наглядности, я отключил параллельное использование ядер процессора: Max degree of parallelism = 1)
- 1С:Предприятие 8.3 (8.3.15.1700) (64)
Создаем простую каркасную конфигурацию: «Номенклатура», «Контрагенты», «Продажа товаров», «Цены».
Конфигурация с обработкой инициализации опубликована здесь: github.com/boroda310/slice-slice-slice. В папке configuration — файлы конфигурации.
Инициализируем случайными данными.
1000 товаров:
Цены на начало периода, случайные переоценки в периоде:
Продажи в периоде:
Порог скидки для отбора данных принимаем равным 10, Чтобы не тратить время на вывод результатов, отобранные данные помещаем во временную таблицу и выводим только количество получившихся строк.
Файл для консоли запросов с текстами запросов опубликован здесь: github.com/boroda310/slice-slice-slice в папке requestconsole.
Запрос Вариант А (традиционный)
ВЫБРАТЬ РАЗЛИЧНЫЕ
НАЧАЛОПЕРИОДА(ПродажаТовара.Дата, ДЕНЬ) КАК Дата
ПОМЕСТИТЬ вт_ДатыПродаж
ИЗ
Документ.ПродажаТовара КАК ПродажаТовара
ГДЕ
ПродажаТовара.Дата МЕЖДУ &НачалоПериода И &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
МАКСИМУМ(ЦеныНоменклатуры.Период) КАК Период,
ЦеныНоменклатуры.Номенклатура КАК Номенклатура,
вт_ДатыПродаж.Дата КАК Дата
ПОМЕСТИТЬ вт_ДатыАктуальныхЦен
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вт_ДатыПродаж КАК вт_ДатыПродаж
ПО ЦеныНоменклатуры.Период <= вт_ДатыПродаж.Дата
СГРУППИРОВАТЬ ПО
ЦеныНоменклатуры.Номенклатура,
вт_ДатыПродаж.Дата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПродажаТовара.Ссылка КАК Ссылка,
ПродажаТовара.Контрагент КАК Контрагент,
ПродажаТовараТовары.Номенклатура КАК Номенклатура,
ПродажаТовараТовары.Количество КАК Количество,
100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена КАК Скидка
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
Документ.ПродажаТовара КАК ПродажаТовара
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПродажаТовара.Товары КАК ПродажаТовараТовары
ПО (ПродажаТовара.Ссылка = ПродажаТовараТовары.Ссылка)
ЛЕВОЕ СОЕДИНЕНИЕ вт_ДатыАктуальныхЦен КАК вт_ДатыАктуальныхЦен
ПО (ПродажаТовараТовары.Номенклатура = вт_ДатыАктуальныхЦен.Номенклатура)
И (НАЧАЛОПЕРИОДА(ПродажаТовара.Дата, ДЕНЬ) = вт_ДатыАктуальныхЦен.Дата)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО (ПродажаТовараТовары.Номенклатура = ЦеныНоменклатуры.Номенклатура)
И (вт_ДатыАктуальныхЦен.Период = ЦеныНоменклатуры.Период)
ГДЕ
ПродажаТовара.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И 100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена > 10
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Поле1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
Запрос Вариант Б (интервальный)
ВЫБРАТЬ
ЦеныНоменклатурыСрезПоследних.Период КАК Период,
ЦеныНоменклатурыСрезПоследних.Номенклатура КАК Номенклатура,
ЦеныНоменклатурыСрезПоследних.Цена КАК Цена
ПОМЕСТИТЬ вт_ЦеныВПериоде
ИЗ
РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НачалоПериода, ) КАК ЦеныНоменклатурыСрезПоследних
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ЦеныНоменклатуры.Период,
ЦеныНоменклатуры.Номенклатура,
ЦеныНоменклатуры.Цена
ИЗ
РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ГДЕ
ЦеныНоменклатуры.Период > &НачалоПериода
И ЦеныНоменклатуры.Период <= &КонецПериода
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЦеныЗаПериод_Начало.Номенклатура КАК Номенклатура,
ЦеныЗаПериод_Начало.Цена КАК Цена,
ЦеныЗаПериод_Начало.Период КАК НачалоПериода,
ЕСТЬNULL(МИНИМУМ(ЦеныЗаПериод_Конец.Период), &КонецПериода) КАК КонецПериода
ПОМЕСТИТЬ вт_ПериодыЦен
ИЗ
вт_ЦеныВПериоде КАК ЦеныЗаПериод_Начало
ЛЕВОЕ СОЕДИНЕНИЕ вт_ЦеныВПериоде КАК ЦеныЗаПериод_Конец
ПО ЦеныЗаПериод_Начало.Номенклатура = ЦеныЗаПериод_Конец.Номенклатура
И ЦеныЗаПериод_Начало.Период < ЦеныЗаПериод_Конец.Период
СГРУППИРОВАТЬ ПО
ЦеныЗаПериод_Начало.Номенклатура,
ЦеныЗаПериод_Начало.Цена,
ЦеныЗаПериод_Начало.Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПродажаТовара.Ссылка КАК Ссылка,
ПродажаТовара.Контрагент КАК Контрагент,
ПродажаТовараТовары.Номенклатура КАК Номенклатура,
ПродажаТовараТовары.Количество КАК Количество,
100 * (вт_ПериодыЦен.Цена - ПродажаТовараТовары.Цена) / вт_ПериодыЦен.Цена КАК Скидка
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
Документ.ПродажаТовара КАК ПродажаТовара
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПродажаТовара.Товары КАК ПродажаТовараТовары
ПО ПродажаТовара.Ссылка = ПродажаТовараТовары.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ вт_ПериодыЦен КАК вт_ПериодыЦен
ПО ПродажаТовара.Дата >= вт_ПериодыЦен.НачалоПериода
И ПродажаТовара.Дата < вт_ПериодыЦен.КонецПериода
И (ПродажаТовараТовары.Номенклатура = вт_ПериодыЦен.Номенклатура)
ГДЕ
ПродажаТовара.Дата МЕЖДУ &НачалоПериода И &КонецПериода
// И 100 * (вт_ПериодыЦен.Цена - ПродажаТовараТовары.Цена) / вт_ПериодыЦен.Цена > 10
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Поле1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
ГДЕ скидка > 10
Запрос Вариант В (в один запрос, без группировки)
ВЫБРАТЬ
ПродажаТовара.Ссылка КАК Ссылка,
ПродажаТовараТовары.Номенклатура КАК Номенклатура,
ПродажаТовара.Контрагент КАК Контрагент,
ПродажаТовараТовары.Количество КАК Количество,
100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена КАК Скидка
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
Документ.ПродажаТовара.Товары КАК ПродажаТовараТовары
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПродажаТовара КАК ПродажаТовара
ПО (ПродажаТовараТовары.Ссылка = ПродажаТовара.Ссылка)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО (ПродажаТовараТовары.Номенклатура = ЦеныНоменклатуры.Номенклатура)
И (ПродажаТовара.Дата >= ЦеныНоменклатуры.Период)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры2
ПО (ПродажаТовараТовары.Номенклатура = ЦеныНоменклатуры2.Номенклатура)
И (ПродажаТовара.Дата >= ЦеныНоменклатуры2.Период)
И (ЦеныНоменклатуры2.Период > ЦеныНоменклатуры.Период)
ГДЕ
ПродажаТовара.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И ЦеныНоменклатуры2.Период ЕСТЬ NULL и 100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена > 10
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Поле1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
Запрос Вариант Г (в один запрос, с группировкой)
ВЫБРАТЬ
ПродажаТовара.Ссылка КАК Ссылка,
ПродажаТовара.Контрагент КАК Контрагент,
ПродажаТовараТовары.Номенклатура КАК Номенклатура,
ПродажаТовараТовары.Количество КАК Количество,
100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена КАК Скидка
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
Документ.ПродажаТовара.Товары КАК ПродажаТовараТовары
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПродажаТовара КАК ПродажаТовара
ПО (ПродажаТовараТовары.Ссылка = ПродажаТовара.Ссылка)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО (ПродажаТовараТовары.Номенклатура = ЦеныНоменклатуры.Номенклатура)
И (ПродажаТовара.Дата >= ЦеныНоменклатуры.Период)
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры1
ПО (ПродажаТовараТовары.Номенклатура = ЦеныНоменклатуры1.Номенклатура)
И (ПродажаТовара.Дата >= ЦеныНоменклатуры1.Период)
ГДЕ
ПродажаТовара.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И 100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена > 10
СГРУППИРОВАТЬ ПО
ПродажаТовараТовары.Номенклатура,
ПродажаТовараТовары.Количество,
ПродажаТовара.Контрагент,
ЦеныНоменклатуры.Период,
ПродажаТовара.Ссылка,
100 * (ЦеныНоменклатуры.Цена - ПродажаТовараТовары.Цена) / ЦеныНоменклатуры.Цена,
ПродажаТовараТовары.НомерСтроки
ИМЕЮЩИЕ
МАКСИМУМ(ЦеныНоменклатуры1.Период) = ЦеныНоменклатуры.Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Поле1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
Запрос Вариант Д («эшелонный» из ст. «Минимализмы»)
ВЫБРАТЬ
ПродажаТовара.Ссылка КАК Ссылка,
ПродажаТовараТовары.Цена КАК ЦенаДокумента,
МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, ПродажаТовара.Дата, ДЕНЬ) * &Много + ЦеныНоменклатуры.Цена) - МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, ПродажаТовара.Дата, ДЕНЬ)) * &Много КАК Цена,
ПродажаТовара.Контрагент КАК Контрагент,
ПродажаТовараТовары.Номенклатура КАК Номенклатура,
ПродажаТовараТовары.Количество КАК Количество,
ПродажаТовараТовары.НомерСтроки КАК НомерСтроки
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
Документ.ПродажаТовара.Товары КАК ПродажаТовараТовары
ЛЕВОЕ СОЕДИНЕНИЕ Документ.ПродажаТовара КАК ПродажаТовара
ПО (ПродажаТовараТовары.Ссылка = ПродажаТовара.Ссылка)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО (ПродажаТовараТовары.Номенклатура = ЦеныНоменклатуры.Номенклатура)
И (ЦеныНоменклатуры.Период <= ПродажаТовара.Дата)
ГДЕ
ПродажаТовара.Дата МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
ПродажаТовара.Ссылка,
ПродажаТовараТовары.Номенклатура,
ПродажаТовараТовары.Цена,
ПродажаТовара.Контрагент,
ПродажаТовараТовары.Количество,
ПродажаТовараТовары.НомерСтроки
ИМЕЮЩИЕ
100 * (МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, ПродажаТовара.Дата, ДЕНЬ) * &Много + ЦеныНоменклатуры.Цена) - МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, ПродажаТовара.Дата, ДЕНЬ)) * &Много - ПродажаТовараТовары.Цена) / (МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, ПродажаТовара.Дата, ДЕНЬ) * &Много + ЦеныНоменклатуры.Цена) - МИНИМУМ(РАЗНОСТЬДАТ(ЦеныНоменклатуры.Период, ПродажаТовара.Дата, ДЕНЬ)) * &Много) > 10
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(*) КАК Поле1
ИЗ
ВременнаяТаблица КАК ВременнаяТаблица
Я запускал все эти запросы за два периода — первый квартал 2020 г. и первое полугодие 2020 г. — на эти периоды генерировались случайные документы и цены. Замерял время выполнения. В ходе анализа планов запросов определился еще один важный показатель — количество обрабатываемых строк. Другие показатели оказались неинформативными.
Вот пример планов запроса для варианта А и варианта В, как наилучшего из однозапросных:
Вот такие результаты у меня получились:
|
Вариант
|
Время выполнения, с.
|
Обработано строк
|
| |
3 мес. |
6 мес. |
3 мес. |
6 мес. |
| А (традиционный) |
4.9 |
10.2 |
1 626 647 |
3 195 284 |
| Б (интервальный) |
4.3 |
10.3 |
1 626 647 |
3 195 284 |
| В (без группировки) |
10.0 |
26.2 |
2 107 110 |
13 596 463 |
| Г (с группировкой) |
20.1 |
105.0 |
4 387 711 |
27 379 466 |
| Д (эшелонный) |
20.8 |
71.6 |
4 897 463 |
15 876 311 |
Варианты А и Б лидируют и не сильно отличаются друг от друга. Результат остальных довольно безрадостен.
Можно заключить: срез последних в один запрос реализовать можно. Но лучше — не надо.
Репозиторий тестовой конфигурации: github.com/boroda310/slice-slice-slice
Отсылки:
