Клиент обратился с проблемой долгого проведения документа Возврат ТМЗ от покупателя. В общем-то проблема старая, и известная, но до этого момента я никогда не углублялся в нее так далеко.
Клиент использует типовую конфигурацию Управление Торговым Предприятием для Казахстана, ред. 2.0 (Некий аналог КА для РФ, то есть по факту УТ+БП). Конфигурация была дописана, но незначительно, а сам документ и модули которые он использовал, были на поддержке и не изменены.
Хорошо, начал с первичного анализа ситуации, и замера производительности. База была большая, и создание и проведение документа Возврат ТМЗ от покупателя на 10 позиций заняло 669 секунд (повторное проведение документа заняло около 230 секунд) . Сказать что это много, значит ничего не сказать. Узким местом являлся запрос в общем модуле, который выполнялся 10 раз, но к этому мы еще вернемся:
Запрос был в функции общего модуля УправлениеЗапасамиПартионныйУчет.СформироватьТаблицуВозвращенныхПартий(). После детального осмотра, стало ясно, что функция ищет партии товара, списанные документом реализации, на основании которого и сделан возврат, для того чтобы вернуть товар по списанной себестоимости. Функция выполняется для каждой строки документа в цикле. Казалось бы, избавиться от запроса в цикле, и дело в шляпе, но не все так просто.
Решил пока оставить запрос в цикле, и посмотреть на сам запрос, который ищет эти партии. И тут я ужаснулся:
Проблемный запрос
Здесь использовались подзапросы (с которыми 1С не очень эффективно работает), запросы к виртуальной таблице Обороты регистра бухгалтерии с отбором по регистратору, соединения с регистрами накоплений, группировки. Запросы к виртуальным таблицам регистра бухгалтерии были толком без параметров, то есть факту, получалось так, что выбирались все обороты по товару и счету, и только потом делался отбор по регистратору в секции ГДЕ, что, собственно, было не оптимально. Также запросы учитывали использование ордерной схемы, и реализации товаров принятых на комиссию. У клиента не было ордерной схемы, комиссионного товара тоже, также себестоимость рассчитывалась по средней. Учитывая эти факты, было решено упростить запрос, в итоге получилось так:
ВЫБРАТЬ
NULL КАК Партия,
ОборотыДтКт.СуммаОборот КАК СуммаСписания,
ОборотыДтКт.КоличествоОборотКт КАК Количество,
ОборотыДтКт.СчетКт КАК СчетУчета,
0 КАК СуммаНДСПередачи,
0 КАК КоличествоПередачи,
1 КАК ЧислоСтатусПартии
ИЗ
РегистрБухгалтерии.Типовой.ОборотыДтКт(&Период, &Период, Регистратор, , , СчетКт В (&СчетКт), &МассивВидовСубконто, СубконтоКт1 = &Товар) КАК ОборотыДтКт
ГДЕ
ОборотыДтКт.Регистратор = &Регистратор
И ОборотыДтКт.КоличествоОборотКт >= 0
По факту остался запрос к виртуальной таблице регистра бухгалтерии, но, был добавлен параметр на период регистратора (то есть брались записи с отбором по дате документа реализации, и уже потом происходил отбор по регистратору), и параметр на субконто (особого прироста это не дало, но субконто желательно типизировать).Также была после дальнейшего анализа был оптимизирован запрос в процедуре ПроверитьКоличествоВозвратаПоРеализацииНУ(). Она выполняет контроль возврата по другому регистру бухгалтерии (Налоговый). Там изменений уже было не так много, добавлен параметр на период:
ВЫБРАТЬ
ОборотыДтКт.СуммаОборот КАК Сумма,
ОборотыДтКт.КоличествоОборотКт КАК Количество
ИЗ
РегистрБухгалтерии.Налоговый.ОборотыДтКт(
&Период, //Это было добавлено
&Период, //Это было добавлено
Регистратор,
,
,
СчетКт = &СчетКт,
&ВидыСубконто,
СубконтоКт1 = &Товар
И ВидУчетаКт = &ВидУчета) КАК ОборотыДтКт
ГДЕ
ОборотыДтКт.Регистратор = &Регистратор
И ОборотыДтКт.КоличествоОборотКт >= 0
УПОРЯДОЧИТЬ ПО
Количество УБЫВ
Для чистоты эксперимента созданный документ возврата был удален, и копией был создан новый. Также до этого момента были проведены другие возвраты и документы реализации. Вот сколько заняло времени проведение документа после оптимизации:
Предыдущий запрос, который занимал 90% времени проведения, теперь стал исполняться доли секунды (предпоследняя строка). Общая скорость проведения сократилась практически в 100 раз. Вот таким, нехитрым способом у меня получилось ускорить проведения документа Возврат ТМЗ от покупателя. База была файловая, и возможно, переход на SQL хоть как-то бы помог с этой проблемой, но для клиента это было бы дорого. Также еще можно было избавиться от запроса в цикле, но результат уже был достигнут хороший, и было принято решение остановиться пока что на этом.
Спасибо за внимание.
Related Posts
Восстановление последовательности документов при закрытии месяца в Бухгалтерия 3.0 не завершается
Заполнение табличных частей
Формирование сводных актов выполненных работ
Ввод поступления в переработку на основании передачи сырья (между организациями)- Конспект по установке сервера 1С на linux
Получение имени компьютера и его IP локально и в терминале
У клиента не было ордерной схемы, комиссионного товара тоже, также себестоимость рассчитывалась по средней. Учитывая эти факты, было решено упростить запрос, в итоге получилось так:
А если клиент начнёт использовать Ордерную схему?
(1) Он был об этом предупрежден, и обещал не начинать.
Ну а вообще можно будет доделать с учетом нее, запросы для ордерной схемы к виртуальным таблицам без параметров все равно выглядели ужасно, чтобы их не оптимизировать, я просто их убрал.
(1)вызовут опять Армана
Была уже статья про это
скорее всего он не будет использовать ордерную схему небольшой промежуток времени, а потом когда начнет … и не вспомнит и не заметит и даже не позвонит Арману. пройдёт пол года, год или месяцы после использования ордеров … в бд будет кака и вот тогда позовут кого то разбираться …
(5) При ордерной схеме, товар списанный накладной не будет найден, соответственно сразу выйдет ошибка, и документ не будет проведен. Не утрируйте.
Это типовой запрос был? Архитектурное решение мягко говоря — не очень
(7) Да, полностью типовой, собственно, что меня и удивило.
Ну можно было добавить константу — «ИспользоватьОрдернуюСхему» тип булево
тогда если клиент бы начал ордерную схему все бы работало как раньше
(9) Она есть. Просто ордерную схему используют около 0,01% клиентов, и она не очень популярна, так как надо делать 2 документа вместо одного.
Оптимизация «пальцем в небо».
Если бы автор понимал какие запросы формирует платформа, какие индексы имеют таблицы РБ, то решение было бы иное.
Но похоже этого понимания нет.
(11) и? профайлер SQL пока не всех ежедневный инструмент. А данной статье найдена проблема и эта проблема была решена. И пусть без понимания какие запросы формирует платформа. Автор молодец, что решил и поделился.
Тем не менее запрос в цикле это плохо, как бы «шустро» он выполнялся — это избыточные вызовы к СУБД и нагрузка процессы, отвечающие за запросы к базе: лучше сделать 1 запрос, то есть отобрать все «регистраторы» и выбрать нужный интервал отбора оборотов (&НачалоПериода — &КонецПериода). После чего результат запроса скомпилировать — и в цикле делать выборку, или обращение к ТЗ, (если результат выгужен в ТЗ). Это просто «ремарка», статья очень практически полезная.
тоже всегда начинаю оптимизацию с того что тупо отсекаю тот функционал который заведомо не используется
Короче берем типовую УТ 11, КА 2, ЕРП 2, волшебным ножом отрезаем все лишнее, получаем маленькую и скоростную базу, аналог УТ 10.3 и наслаждаемся жизнью )))
(11)Расскажите, как правильно оптимизировать этот запрос?
(16)
1. можно не удалять функционал, а, например, если это возможно, доппараметрами или «сборным» запросом отключать его. Это как бы надежнее с точки зрения «забыл что мы это удалили». Но если уверены, что пригодится — пусть будет так.
2. не совсем правильно при отборе по регистратору ставить период = Дата документа. В большинстве случаем это годится, но существуют более сложные ситуации, когда движения документа могут быть разбросаны во времени одновременно и до и после даты. Об этом нужно помнить.
3. Если мы используем периодичность регистратор, то таблицы итогов СУБД никогда задействовать не будет. Значит можно смело переделать на запрос к физическим таблицам, чтобы не путать планировщик запросов. ДвиженияССубконто — дефакто это две физтаблицы без лишнего «колдовства». Сделайте запрос к ней. И обратите внимание то, что все параметры идут внутри таблицы. Даже ПЕРВЫЕ, даже РЕГИСТРАТОР.
В нашем распоряжении есть два кластерных индекса (у каждой физтаблицы) Период+Регистратор+***. Поэтому в идеале было бы использовать отбор по (Период=Дата)+Регистратор (Период при возможности задавать не на больше-меньше, а на РАВЕНСТВО — ДвиженияССубконто это позволяет). Но есть также и некластерный Регистратор+***. Поэтому можно для сравнения протестировать и без отбора по дате. Думаю оба варианта уложатся в 0.1 сек.
(17)
1. Подстраховаться это конечно хорошо, но все предусмотреть невозможно. Отключение механизма было оговорено заранее, а не просто потому что так захотелось. Работа с учетом комиссионного товара и ордерной схемы заняла бы больше времени, и денег для клиента.
2. В данном случае используется частный случай — период записи никогда не будет отличен от даты документа. Себестоимость списания может быть изменена Закрытием месяца, но записи все равно будут датой документа.
3. Таблицы итогов не используются — согласен (не только из-за периодичности, но и из-за отбора по субконто). Но ДвиженияССубконто тоже виртуальная таблица, как и ОборотыДтКт она будет строиться на таблице движений. Да, в параметрах ОборотыДтКт нельзя сразу поставить отбор на регистратор, но разница по производительности при использовании запроса к ОборотыДтКт и ДвиженияССубконто не будет существенной.
Опять же повторюсь, рассматривался частный случай, а не глобальное решение, и там можно было много еще чего оптимизировать, но данный вариант всех устроил.
(18)
проверили или так думаете?
ДвиженияССубконто условно можно отнести к виртуальной ). Простейшее соединение — поэтому работает быстро. Можете сами его написать. Тут важно правильно выстроить производительный план запроса. А какие (виртуальные или нет) таблицы будут использоваться — не важно.
(19) Ну вот результаты, от запроса через ДвиженияССубконто:
Показать
Как бы разницу можно списать на погрешность, при разных итерациях будут разные результаты в любом случае. Может я конечно что не так делаю. Либо надо тестировать разницу в специально выстроенных условиях, но в данном случае для меня разница несущественна.
(21) Хммм. Наверно не внимательно читал. Я полагал, что речь об оптимизации — т.е. о борьбе с самыми длинными, а не с самыми короткими запросами )))))
Думал речь о топе = 6 сек. Так если речь о сотых долях, то дискуссию можно прекращать. )
О запросах в цикле. В теории это плохо. Но должен быть паритет между увеличением сложности кода и профитом в производительности. Это не олимпиада. Если вы будете выбирать между 0,02 и 0,002 сек, то вы заработались и пора отдыхать.
(22) Да, борьба с самыми длинными запросами, и он указан на первом скрине (600 сек), на втором скрине он в топ не попал, так как уже был оптимизирован.
6 сек — это другой запрос, который вышел в топ после оптимизации. На втором скрине я не выделил ту строку, которую оптимизировал, но написал что она предпоследняя (видимо это ввело в заблуждение).
Запросы в цикле я тоже оставил в покое, так как результат меня устроил вполне.
Так что да, подведем итог, если разница несущественна, то смысла в споре нет.
Когда бюджет не ограничен, можно сделать так -померять, сяк- померять. Подумать, потом еще померять…
Потом вспомнить про ТЖ и SQL Менеджмент студио…
Думаю, что автор сделал ровно на столько, за сколько заплатили. Задача решена, все остальное — лирика.
(24) автор работал с файловой базой.
(25) Об авторе только последняя строка. Все что выше — собственный опыт.
(17)
Можете подсказать в каких «более сложных ситуациях» — «движения документа могут быть разбросаны во времени одновременно и до и после даты»
(27) Например, документы планирования — дата документа в текущем месяце, а период в его движениях по регистрам следующим месяцем.
(27) переводил один из проектов по автоматизации производства на упр блокировки, переписывал движения документов с Удалять автоматически на Удалять при отмене…
Оказалось что собственные движения один документ делал очень по «особенному». Разброс дат был чуть ли не два месяца. Поэтому ни какими остатками на момент документа не отделаешься, приходилось вычитать запросами движения из общих цифр.
Что именно за документ — толком не вспомню. Что-то вроде цепочки сырьевых движений. Есть такие бзики у предприятий. Не вводить 10-30 документов, а только на промежуточных этапах строить цепочки.
(28), (29) Всегда считал это не правильным. Документ и его движения должны быть в одном периоде! Надо несколько периодов => несколько документов. Пользователь не хочет делать 30 документов — вот обработка… Документ в сентябре, а движения в мае — *** потом что разберешь )
По мне это бардак )
(30) Регистр может быть с периодичностью Месяц, тогда период будет началом месяца, такое тоже бывает.
(31) Ну я не против ) Документ от 15.05, в регистре 01.05 (т.к. периодичность МЕСЯЦ) — норм, все в одном периоде.
Ну я на самом деле не совсем упорот ))) возможны ситуации, но пытаюсь таких ситуаций избежать/не допускать — т.к. считаю что это бардак, когда (пример из жизни, УПП) проводят документ Поступления от мая, он двигает остатки чуть чуть январе, чуть чуть в феврале и т.п. — была какая-то безумная логика в этом, и как следствие трэш в учете
(30) Как бы там ни было, но платформа не запрещает этого делать, у каждой организации и бизнес-процесса может быть своя специфика, у каждого свой взгляд на решение той или иной задачи, именно поэтому каждый сам выбирает для себя использовать эту возможность или нет.
(33) Не спорю, каждый сам выбирает как делать, платформа многое что позволяет. Но, по моему мнению, это бардак. Поэтому решил уточнить, может бывают какие-то ситуации в которых действительно так надо делать.
(30) Заказчик требует ввод одного документа, а движения — 10 разных «процессов» в различных периодах. Ваши предложения??
Автоматически создавать еще 9 документов с проводками в своих периодах? И в общей транзакции их отменять, перезаполнять, перепроводить??? Ну-ну
Один месяц — это не обязательно «один период». Дискретность учета может быть самая разная. И не всегда типовые знания и методологии ложатся на все бизнес-процессы.
Правильная ли это методика учета — решать владельцу бизнеса. Будете держать в руках 6 зеленых цифр — еще не так раскорячитесь.
(35) «Правильная ли это методика учета» и «правильная архитектура решения» разные вещи. Владельцу бизнеса глубоко и с высокой колокольни, сколько там документов. Ему нужен результат (выполненная задача).
Заказчик может много что требовать — но это не значит что это правильно и не принесет в последствие боль и страдания.
Не вижу проблем, документ в котором хранятся ссылки на ваши нужные документы — очень бородатое решение. и крутите этот документ как хотите. И заказчику норм, и вам (или тем кто после вас) проблем меньше
(36) бла бла бла
Достаточно производительное решение (в части параллельности, нагрузки оборудования, оптимизации действий пользователей) уже давно работает. Работает очень быстро. И в части архитектуры все прекрасно, логика данных ни сколько не хромает. У меня никаких замечаний к авторам не возникло.
То что не вкладывается в рамки поминания не означает того что так нельзя делать.
(37)
Если Вы про автора данной статьи — то у меня к ним вопросов нет.
Красавчик!
К сожалению, несколько раз приходилось чинить после «То что не вкладывается в рамки поминания не означает того что так нельзя делать.» — трэш, но по деньгам отлично ))) Возможно автор такого решения гениален — но тогда не уходи с конторы, после твоего ухода начинается ад )
Возможно, где-то это оправдано (поэтому и спросил, интересно). Но по моему опыту, такие реализации приводили к неприятным ситуациям.