В свое время, года 3 назад, возникла необходимость оптимизации конфигурации и устранения ее узких мест в одной компании. Одним из таких узких мест оказался, казалось бы, безобидный, механизм распределения товаров в реализации по сериям. Суть в том, что строк распределялось достаточно много и было это очень медленно. Не миллионы за раз, конечно, но на это самое распределение для одного документа могло уходить до минуты. От этого становилось всем очень грустно, т.к. параллельно бухгалтера препроводили документы, другие операторы тоже формировали документы отгрузки и когда отгружали «большого» клиента – жизнь на некоторое время замирала. К слову сказать, размер базы 1С за 2-3 года на тот момент составлял ~500 Гб, заказов от одного клиента за день могло прийти десяток-другой, а в некоторых из них строк могло быть более 1000, в общем «Реализация товаров и услуг» на 1000 строк — это не было ничем сверхъестественным. Реиндексация, обновление статистики, шринк и другие необходимые процедуры проводились регулярно, но сейчас речь не об этом. Вернемся к нашему объекту оптимизации. На тот момент механизм распределения был до банального прост:
- Запросом получали остатки по сериям (Номенклатура – Серия – Количество)
- Другим запросом получали таблицу товаров к отгрузке (Номенклатура – Заказ покупателя – Количество).
- Проходил обыкновенный перебор для каждой номенклатуры по принципу «Пока КоличествоКРаспределению > 0 Цикл …….. ».
Т.к. я всегда придерживался позиции, что сам факт перебора на больших объемах данных – это уже само по себе узкое место, то возможность «улучшения» алгоритма перебора я даже рассматривать не планировал. Нужна была альтернатива. Также на тот момент я уже давно набил руку в оптимизации сложных запросов и укрепился в выводе, что нет ни одной задачи, которую нельзя было бы решить исключительно запросом и точно знал, что качественный запрос (пакет запросов) в 99% случаев окажется самым эффективным решением, чем какая-либо пост-обработка результатов запроса. Вопрос оставался только в нахождении этого решения).
Выходил я на перекур с достаточно тривиальным условием задачи (распределить количество по измерениям одной таблицы на количество по измерениям из другой) и 2-мя тезисами:
- Мы имеем 2 таблицы, которые и так собираются запросом
- SQL не знает никакого «Распределить». SQL знает только «больше», «меньше», «равно» (утрированно). Надо дать ему некий параметр для сравнения. Числовой параметр, по которому будет понятно какое количество еще можно распределить в условную строку.
И в этот самый момент, когда я мысленно проговаривал второй тезис, слово «еще» и натолкнуло меня на решение. Далее, рисуя палочкой на снегу, я не успел докурить, как уже побежал пробовать свою гипотезу в консоли запросов.
Рассмотрим ниже простой пример:
У нас есть складские ячейки с количеством вмещаемого в них товара с одной стороны (A, B, C, D) и сам товар (X, Y, Z), который необходимо «как-то» разложить по этим ячейкам, но так, чтоб в ячейку не положили больше товара, чем может быть в ней места.
A – 10 мест
B – 1 место
C – 5 мест
D – 8 мест
X – 13 шт
Y – 1 шт
Z – 4 шт
Результатом должна стать таблица распределения:
A-X-10
B-X-1
C-X-2
C-Y-1
C-Z-2
D-Z-2
Для этого нам надо определить порядок распределения, сделать это оказалось до банального просто:
ВЫБРАТЬ
Ячейки.Ячейка КАК Ячейка,
Ячейки.Количество,
ЕСТЬNULL(СУММА(Ячейки1.Количество), 0) + 1 КАК ПорядокРаспределенияС,
ЕСТЬNULL(СУММА(Ячейки1.Количество), 0) + Ячейки.Количество КАК ПорядокРаспределенияПо
ИЗ
Ячейки КАК Ячейки
ЛЕВОЕ СОЕДИНЕНИЕ Ячейки КАК Ячейки1
ПО Ячейки.Ячейка > Ячейки1.Ячейка
СГРУППИРОВАТЬ ПО
Ячейки.Ячейка,
Ячейки.Количество
Кстати, здесь же можно учесть и порядок распределения, если, например, в какие-то ячейки товар надо класть в первую очередь. Решается изменением условия в соединении.
Тоже самое и с товарами:
ВЫБРАТЬ
Товары.Товар КАК Товар,
Товары.Количество,
ЕСТЬNULL(СУММА(Товары1.Количество), 0) + 1 КАК ПорядокРаспределенияС,
ЕСТЬNULL(СУММА(Товары1.Количество), 0) + Товары.Количество КАК ПорядокРаспределенияПо
ПОМЕСТИТЬ ТоварыПоПорядку
ИЗ
Товары КАК Товары
ЛЕВОЕ СОЕДИНЕНИЕ Товары КАК Товары1
ПО Товары.Товар > Товары1.Товар
СГРУППИРОВАТЬ ПО
Товары.Товар,
Товары.Количество
Для простоты понимания разложу все эти позиции поштучно в таблице и наложу одну на другую в порядке распределения:
Нам просто нужно написать граничные условия. А теперь осталось просто соединить эти таблицы и получим наш результат:
ВЫБРАТЬ
ЯчейкиПоПорядку.Ячейка КАК Ячейка,
ТоварыПоПорядку.Товар КАК Товар,
ВЫБОР
КОГДА ТоварыПоПорядку.ПорядокРаспределенияПо < ЯчейкиПоПорядку.ПорядокРаспределенияПо
ТОГДА ТоварыПоПорядку.ПорядокРаспределенияПо
ИНАЧЕ ЯчейкиПоПорядку.ПорядокРаспределенияПо
КОНЕЦ - ВЫБОР
КОГДА ТоварыПоПорядку.ПорядокРаспределенияС > ЯчейкиПоПорядку.ПорядокРаспределенияС
ТОГДА ТоварыПоПорядку.ПорядокРаспределенияС
ИНАЧЕ ЯчейкиПоПорядку.ПорядокРаспределенияС
КОНЕЦ + 1 КАК Количество
ИЗ
ЯчейкиПоПорядку КАК ЯчейкиПоПорядку
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТоварыПоПорядку КАК ТоварыПоПорядку
ПО (ЯчейкиПоПорядку.ПорядокРаспределенияС <= ТоварыПоПорядку.ПорядокРаспределенияПо
И ЯчейкиПоПорядку.ПорядокРаспределенияПо >= ТоварыПоПорядку.ПорядокРаспределенияС)
Сразу оговорюсь, что в запросе умышленно добавлено большее количество полей, чем надо. Можно было бы обойтись и одной границей распределения (нарастающим итогом) и не делать «+1», но как мне показалось – в таком виде это более наглядно для понимания. Оптимизацию запросов мы в этой теме не рассматриваем, поэтому и индексы здесь тоже не описаны. Ну а более сложные алгоритмы распределения (по нескольким измерениям, например) решаются только изменением условий соединения и проверки границ.
Вот и все. В итоге вместо минут ожидания на тех же объемах данных этот запрос выполнялся считанные милисекунды.
Прошу простить за обилие лирики в этой статье. Хотелось дать не математическое решение узкой задачи, а поделиться концептуальным подходом к решению подобных задач, именно ходом своих мыслей. Ну а также сделайте скидку на то, что это первая в моей жизни статья 🙂
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
Пытаюсь воспроизвести пример не получается, смущает, это сравнение «ПО Ячейки.Ячейка > Ячейки1.Ячейка» и «Товары.Товар > Товары1.Товар»
Это ссылки сравниваем?
(1) Можно сравнивать любой реквизит, если он уникален и его тип допускает операции сравнения, но только ссылки точно гарантируют уникальность. В моем примере сравнивались ссылки.
Ну а тестовый образец запроса можно сделать вообще на строковых данных )
выбрать «А» как Товар, 111 как Количество
поместить Товары
объединить
выбрать «Б» ….
и т.д.
Спасибо все работает, это просто я офигел немного от такого подхода к запросам, для меня это в новинку. Срин шот прикладываю, которым можно дополнить статью
(4) Рад, если статья оказалась полезная 😉
Это гениально!
Прочитав постановку задачу ожидал большую кучу подзапросов с временными запросами. Но решение оказалось на удивление элегантным. Сохраню себе, пригодится.
Хорошее решение. Рекомендую.
Была аналогичная задача при закрытии месяца в УПП.
В типовом алгоритме был фрагмент кода, который строил большую таблицу перехода номенклатуры из одного состояния другое. А реализовано это было через «цикл в цикле» по таблице в 20 тыс. строк, что в итоге давало 400 млн итераций, и в свою очередь общее время проведения документа составляло 3-4 часа по каждому из разделов учета (БУ, НУ, УПР).
Здесь даже была статья по этой проблеме и ее решению.
Попробовал решение из статьи — эффект был, но не скажу, что проблема ушла. выигрыш был порядка 10%.
Несколько дней ходил и крутил проблему в голове. Идея, как и у автора текущей статьи, осенила неожиданно.
В итоге переложил типовой цикл на запрос и время его выполнения составило что-то около 3-5 секунд, а общее время проведения документа — несколько минут.
(9) Разве не умеет? Сам не раз так делал — всегда отлично отрабатывает.
Например, для нумерации данных во временной таблице. Так как в 1С нет подобия ROW_NUMBER, то для нумерации можно соединять таблицу саму с собой с сравнением по Таблица1.Ссылка > Таблица2.Ссылка.
И все хорошо работало.
(9) Михаил, вопрос на засыпку: а SQL Server Engine умеет сравнивать UNIQUEIDENTIFIER на больше-меньше? 😉
а то вы только что открытие для меня сделали))
а вообще, ребята, спасибо всем за отзывы, прямо воодушевили меня покопаться в закромах, где-то есть у меня еще несколько старых примеров обработки больших объемов данных запросами.. может руки дойдут — напишу еще статейку )
Коллеги, запрос тоже может при исполнении запускать перебор.
Не обольщайтесь внешним видом.
Выигрыш может быть за счет переноса нагрузки на сервер
Или за счет использования поиска по индексу вместо перебора.
Например, в объектной модели программирования Таблицу значений индексировать командой ДобавитьИндекс и использовать НайтиСтроки(Отбор) вместо Для Каждого Строка ИЗ ТаблицаЗначений.
Статья не плюсовая, а скорее минусовая.
извините. ошибся . но с с другими типами данных ссылки не умеет сравнивать , хотя и не ругается
(13) Николай, я всегда за критику, но только когда она конструктивная. Если интересно — посмотри подобный запрос в профайлере и сравни с отбором по таблице значний (даже при добавлении индекса), результат кардинально будет отличаться. Это первое. Ну а второе — по поводу перебора в запросе — Не могу себе представить ситуацию, когда соединение по ключевым индексированным полям (Ref) вызовут в SQL перебор. Такого даже при желании не так просто добиться, или я не прав? 😉
а теперь смотрим. допустим у нас склад с 1000 ячеек. (или остатки с 1000 мелких партий или еще что-то). первым запросом мы получаем 1000000 строк и далее тот же цикл происходит на стороне скуля (nested loops в плане запросов), только не 1000 строк, а 1000000.
А можно в один проход обойти две одинаковым образом отсортированные таблицы.
дополню
SQL Server Engine
умеет сравнивать bynary(16) c числами
но
1с8 в запросе к субд заменяет конструкцию типа ссылка > число
на истина ()
и
ссылка < число — на ложь .
прошу еще раз прощения за замечание не по делу.
умеет ли SQL Server Engine сравнивать значения типа UNIQUEIDENTIFIER — точно не знаю. но думаю что умеет.
(14) Ах, речь о сравнении ссылок с другими типами… ну да, забавно ))
«Хозяйке на заметку»: запрос вида «выбрать датавремя(2016,1,1,1,1,30) < 3.14» тоже ошибки не вызовет, однако его целесообразность и практический смысл лично для меня сомнительны)
(16) Если склад с 1000 ячеек, то в первом запросе будет 1000 строк. Там просто значения ячеек склада дополняются значенями ПорядокРаспределенияС и ПорядокРаспределенияПо.
(16) Первым запросом мы получаем 1000 строк, смотрите внимательней.. и это в худшем случае, когда надо распределить ВСЕ на ВСЕ 😉
если будет интересно — потом напишу статью, как используя этот метод, одним запросом на лету собрать движения по партиям в базе 1С Розница > 50 Гб, где партионного учета нет как такового, а задача была сделать такой отчет, не меняя типовую конфигурацию) отчет отрабатывал секунд 10-20 за месяц (10 розничных магазинов, торговля продуктами — в общем движений много).конечно, это уже была модификация данного метода, более оптимизированная, но я бы посмотрел, как бы вы это делали через таблицы значений))
Мне понравилось. Автор запросами разложил поштучно ячейки и товары, а потом соединил, просто и логично.
(22) да, я ошибся. первым запросом мы просто делаем итерацию из 1000000 циклов, просто делает её скуль. На выходе количество строк не меняется, да.
про количество строк — например 1000 пустых ячеек при ячеистом хранении — не редкость. У меня на практике был склад с 50000 ячейками и 15к наименований на них.
обход двух таблиц одним циклом делается достаточно просто. просто вместо «Для каждого» используется «Пока Истина» и две переменных-счетчика.
Для тех, кому как и мне, чтобы понять нужно поиграться в консоле запросов:
Показать
Автору плюс!
О, нужно будет попробовать распределить сумму долга по документам. Отличая идея. если просто : Берем сумму долга на начало. Берем документы реализации и распределяем с конца на эту сумму долга, и получаем последние неоплаченные документы, и не требуется вести оплаты по документам.
(26) По одному контрагенту это будет приемлемо. А вот по 1000 уже печально…
(24) а запросом все равно будет быстрей) читайте мой предыдущий ответ Сергею )
(00) порадовал заголовое и количество звёзд. Стало приятно на душе — «побольше» бы таких специалистов! Но скоро расстроился, т.к.давно влился в ряды таких специалистов.
Вы ведь знаете, как на самом деле перераспеделяется нагрузка между сервером 1С и сервером SQL(если не файловая)? Я обеими руками поддерживаю Вашу идею, если быть до конца правдивым.
(28)(29) Вы очень глубоко заблуждаетесь!
Это достаточно просто доказать. Просто посмотрев время выполнения запроса в зависимости от числа строк в исходной таблице.
При расчете нарастающего итога:
N Число строк Затрачено миллисекунд
1 1 000 302
2 2 000 998
3 3 000 2 263
4 4 000 3 840
5 5 000 5 971
6 6 000 8 443
7 7 000 12 817
8 8 000 14 846
9 9 000 18 851
10 10 000 23 346
По вашей логике зависимость должна быть линейной и расчет нарастающего итога для десяти тысяч строк должен занять в десять раз больше времени, чем для тысячи, то есть примерно три секунды, а на самом деле расчет занимает двадцать две секунды. То есть зависимость нелинейная, близка к квадратичной, что видно, если построить график.
Но главное, что практические прогоны предлагаемого запроса не показывают его преимуществ по времени выполнения перед работой с таблицами значений ни на каких значениях числа строк! Ни на маленьких, ни на больших. И чем больше число строк, тем хуже и хуже!
Что прямо противоречит озвученным вами выводам.
Вот таблица:
N Метод Число строк Затрачено миллисекунд
1 Скрипт 100 8
2 Запрос 100 48
3 Скрипт 200 8
4 Запрос 200 56
5 Скрипт 1000 37
6 Запрос 1000 716
7 Скрипт 2000 75
8 Запрос 2000 2457
9 Скрипт 10000 379
10 Запрос 10000 52873
11 Скрипт 20000 774
12 Запрос 20000 227231
Посмотрите на результат при двадцати тысячах строк: 0,7 секунд для кода против 227(!!!) секунд для запроса.
Для распределения по таблицам значений использовалась взятая из «Минимализмов» ( функция (задача 7).
Если захотите перепроверить, вот обработка, которой я пользовался:
Попробовал использовать дробные числа, запрос работает неправильно…
Показать
(32) нечего пенять на запрос. Его идея в целых числах, значит Вам нужно множить все на 1000
Статья хорошая, хотя техника распределения нарастающего итога конечно не нова.
Как я был расстроен и опечален, когда немного более подробно изучил T-SQL… Мы здесь с вами изворачиваемся, чтобы посчитать нарастающий итог, таблицы сами с собой соединяем и т.д. А у sql-щиков есть уже «по умолчанию» ранжирование в запросах, расчет нарастающего итога и прочие плюшки — все буквально одной-двумя строками кода… Мечтательно представил, как бы парочку своих мегазапросов переписал на куда как более компактно…. Эх ))))
(33) (34) да я пробовал вместо 1 использовать 0.001, просто я заменил только в 2-местах вместо 3-х((
Я проверил обработку из (31), разница к сожалению большая и она не в пользу запроса. Я по ЗУПу еще помню, этот нарастающий итог не такой уж хороший, под конец года НДФЛ считался дольше чем в начале года (правда как ща не знаю).
Я собственно за то, что СУБД нам отдает данные, а логика выполняется на сервере приложений. Когда логика выполняется на СУБД, этот код со временем превращается в неподдерживаемый хаос.
(32 (33) (34) Не знаю, зачем там что-то прибавлять-вычитать, работает вообще без этого. Вот в этом, чуть более коротком варианте запрос работает для чисел вообще какой угодно разрядности:
Показать
Идея запроса вовсе не в целых числах, а в том, чтобы построить друг против друга линейку товаров и ячеек и найти пересечение отрезков на этих линейках.
Вот в этом обсуждении есть картинка: .
(25)
«у»,
1
Большое спасибо за запрос, очень помогли разобраться. Только у вас вот тут буква «у» русская, поэтому результат не совпадает с результатом топикстартера.
(31) да, действительно, спору нет, скрипт достаточно качественный, работает быстрее, мне понравился, по поводу скорости — поиграюсь на выходных со ссылочными типами, давно уже планы запроса не смотрел, снова стало интересно
В целом неплохо. Однако же как обстоит дело с ошибками округлений? На практике чаще приходится сталкиваться с подобными задачами немного видоизмененного рода, например с примесью механизмов FIFO/LIFO. Одна таблица содержит строки, которые можно разделить на группы по ряду признаков. (Например перечень номенклатуры с количествами, но еще неизвестной стоимостью). Вторая таблица содержит значения, которые нужно распределить и при этом не прихватить лишнего (Например остатки номенклатуры по количеству и сумме из внешнего источника), При этом нужно избегать ситуации, когда остаются копейки без количества (т.е. ошибки из-за округлений). С перебором подобные задачи на ура решаюся с помощью методов НайтиСтроки (Для таблицы значений) и НайтиСледующий(Для Выборки). Запросом к сожалению решить не удавалось. Сейчас пришел к тому, что многоие задачи распределения подлежат унификации.
(40)
«Фифо для любопытных»
(40) Посмотри в 1С:Бухгалтерии распределение 25 счета по 20 в регламентной операции, делается только запросами, и погрешностей округления нет.
(22) интересно, буду ждать!
Спасибо, Александр!
Ваш код не очевиден. И тем ценен. Можно сказать, что вначале своей статьи вы описали инсайт (То есть, как на самом деле люди делают открытия — получают новое знание).
А спасибо, потому что Вы решили просто сложную задачу и поделились с нами.
Большое спасибо за статью. Раньше я, как раз, распределял одну таблицу на другую через нарастающий итог и всякие условия.
После наглядного представления с порядком распределения теперь проще распределять таблицы.
Нужно сделать распределение с нужным порядком.
Меня запутало слово порядок в вашем запросе!
Недавно решал похожую задачу:
Вот здесь корректный пример запроса для распределения по ФИФО.
У вас не порядок, а метод ФИФО.
Правила для распределения я не нашел.
У меня задача такая.
Есть таблица 1 колонки ячейка, номенклатура, характеристика, назначение, упаковка, количество
Есть таблица 2 колонки ячейка, номенклатура, упаковка, количество.
И нужно распределить итоги таблицы 2, по данным таблицы 1 с нужным порядком. В частности ФИФО, но с оговоркой.
ФИФО по характеристике, но приоритет над назначением.
Как приоритет делать?