gifts2017

Ускоряем формирование отчета "Книга покупок"

Опубликовал Александр Карасев (KAPACEB.AA) в раздел Программирование - Практика программирования

Было замечено (не без помощи дотошных бухгалтеров), что в базах с большим объемом ежемесячных операций книга покупок может формироваться довольно длительное время. В процессе недолгих разбирательств был найден и обезврежен виновник "тормозов".

Итак, с помощью отладчика была обнаружена функция "ПолучитьДополнительнуюИнформациюПоСФДляКнигиПокупок", располагающаяся в общем модуле "УчетНДС" (или непосредственно в модуле отчета, в зависимости от версии отчета). Эта функция запросом собирает данные по всем номерам ГТД и странам происхождения товаров по списку документов, переданному в качестве параметра. Запрос состоит из множества подзапросов по табличным частям документов различных типов, объединенных через "ОБЪЕДИНИТЬ ВСЕ" и в каждом из этих подзапросов повторяется одно единственное условие "... ТаблицаТоваров.Ссылка В (&СписокСФ)":


// Дополнение по ГТД и стране происхождения
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ РАЗРЕШЕННЫЕ
| ТаблицаТоваров.Ссылка КАК СчетФактура,
| ТаблицаТоваров.СтранаПроисхождения.Код КАК СтранаПроисхождения,
| ТаблицаТоваров.НомерГТД,
| СУММА(1) КАК КоличествоЭлементов
|ИЗ
| Документ.ПоступлениеТоваровУслуг.Товары КАК ТаблицаТоваров
|ГДЕ
| ТаблицаТоваров.Ссылка В(&СписокСФ)
|
|СГРУППИРОВАТЬ ПО
| ТаблицаТоваров.Ссылка,
| ТаблицаТоваров.СтранаПроисхождения,
| ТаблицаТоваров.НомерГТД,
| ТаблицаТоваров.СтранаПроисхождения.Код
|
|ОБЪЕДИНИТЬ ВСЕ
|
|ВЫБРАТЬ
| ТаблицаТоваров.Ссылка,
| ТаблицаТоваров.СтранаПроисхождения.Код,
| ТаблицаТоваров.НомерГТД,
| СУММА(1)
|ИЗ
| Документ.ПоступлениеТоваровУслуг.Оборудование КАК ТаблицаТоваров
|ГДЕ
| ТаблицаТоваров.Ссылка В(&СписокСФ)
|
|СГРУППИРОВАТЬ ПО
| ТаблицаТоваров.Ссылка,
| ТаблицаТоваров.СтранаПроисхождения,
| ТаблицаТоваров.НомерГТД,
| ТаблицаТоваров.СтранаПроисхождения.Код


Вот это условие меня и смутило. Ведь, как известно, условия с использованием "В" могут работать просто до неприличия долго, особенно, при большом количестве элементов в сравниваемом массиве.

Было решено немного переделать это безобразие, использовав для хранения списка документов проиндексированную временную таблицу, а вхождение документа в эту таблицу отрабатывать через "ВНУТРЕННЕЕ СОЕДИНЕНИЕ". Т.е. текст проблемного условия фактически заменяем на текст: "... ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТСписокСчетовФактур КАК СписокСчетовФактур ПО ТаблицаТоваров.Ссылка = СписокСчетовФактур.СчетФактура", где "СписокСчетовФактур" - это наша временная таблица.

Вот кусок кода, который преобразует исходный запрос:

 

Запрос.УстановитьПараметр("СписокСФ", СписокСчетовФактур);


//--> ДОРАБОТКА (замена в запросе условий "ГДЕ Документ В &СписокСФ" на "ВНУТРЕННЕЕ СОЕДИНЕНИЕ" к временной таблице)

//заменим тексты условий на наш текст с внутренним соединением

//если поле с именем "Ссылка"
Запрос.Текст = СтрЗаменить(Запрос.Текст, "ГДЕ
| ТаблицаТоваров.Ссылка В(&СписокСФ)",
"ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТСписокСчетовФактур КАК СписокСчетовФактур
| ПО ТаблицаТоваров.Ссылка = СписокСчетовФактур.СчетФактура");

//если поле с именем "СчетФактура"
Запрос.Текст = СтрЗаменить(Запрос.Текст, "ГДЕ
| ТаблицаТоваров.СчетФактура В(&СписокСФ)",
"ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТСписокСчетовФактур КАК СписокСчетовФактур
| ПО ТаблицаТоваров.СчетФактура = СписокСчетовФактур.СчетФактура");

//сформируем таблицу значений с одной единственной типизированной колонкой СчетФактура для передачи ее в качестве параметра запроса
ТаблицаСчетовФактур = Новый ТаблицаЗначений;
ТаблицаСчетовФактур.Колонки.Добавить("СчетФактура", Новый ОписаниеТипов(Документы.ТипВсеСсылки()));
Для Каждого СФ Из СписокСчетовФактур Цикл
       ТаблицаСчетовФактур.Добавить().СчетФактура = СФ;
КонецЦикла;

Запрос.УстановитьПараметр("ТаблицаСчетовФактур", ТаблицаСчетовФактур);

//добавим в начало исходного запрос текст создания временной таблицы на основании ТЗ ТаблицаСчетовФактур, не забывая добавить индекс по полю СчетФактура
Запрос.Текст = "
|ВЫБРАТЬ СчетФактура КАК СчетФактура
|ПОМЕСТИТЬ ВТСписокСчетовФактур
|ИЗ &ТаблицаСчетовФактур КАК ТаблицаСчетовФактур
|ИНДЕКСИРОВАТЬ ПО СчетФактура
|;
|
|" + Запрос.Текст;

//
ГТДпоСФ = Запрос.Выполнить().Выгрузить(ОбходРезультатаЗапроса.ПоГруппировкам);

 

Итак, результаты замеров производительности - ~600 секунд до оптимизации запроса против ~4 секунд после (см. картинку в блоке скриншотов). Результаты были получены при количестве строк в книге покупок около 15 000. Приведен результат лишь одного сравнительного замера, хотя замеры делались несколько раз для исключения фактора везения :) Замеры были сделаны  на "боевом" среднезагруженном SQL в рабочее время и результаты естественно немного разнились. Несмотря на все оговорки и прямолинейность методики тестирования, можно довольно уверенно констатировать, что профит от доработок довольно ощутим.

 

Примечания:

- информация, изложенная выше, скорее всего, имеет смысл только для довольно больших баз или при формировании книги за очень длительные периоды. Как уже упоминалось, в данном случае замеры делались на примере книги, состоящей из порядка 15 000 строк. На файловых базах не проверялось.

- в качестве подопытного выступал отчет "Книга покупок по Постановлению № 1137" (Бухгалтерия КОРП 2.0.46.5), но все изложенное, скорее всего, применимо и к другим версиям отчета, в т.ч. из других релизов. Во всяком случае, в Бухгалтерии 3.0 проблемная функция осталась неизменной.

 

См. также:

Ускоряем заполнение документа "Формирование записей книги покупок"

 

 К публикации приложены доработанные версии отчетов "Книга покупок", "Книга покупок по Постановлению № 1137" , "Книга продаж по Постановлению  № 1137" (Бухгалтерия КОРП 2.0).

Обновления:

30.10.2015 - добавлена версия отчета "Книга покупок по Постановлению № 1137" (Бухгалтерия предприятия КОРП 2.0.64.35)

Скачать файлы

Наименование Файл Версия Размер
Книга покупок и книга продаж (zip-архив) 49
.zip 227,02Kb
30.10.15
49
.zip 227,02Kb Скачать

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Александр Борисов (sanjabor) 17.10.13 22:52
В типовой книге продаж обращал внимание, что во многих местах тоже используется В. Надо попробовать переписать, намечается база с книгой строк на 50 000 за месяц.
2. Михаил Ражиков (tango) 17.10.13 22:59
отлично. (плюс не дает брозер поставить: завтра уж на рабочем месте)
3. Михаил Ражиков (tango) 18.10.13 10:15
апну, как образец достойной публикации
4. Михаил Кочнев (Mi4man) 18.10.13 17:14
Вы писали:
Ведь, как известно, условия с использованием "В" могут работать просто до неприличия долго
. Можете подсказать где можно почитать подобные приемы ?
5. Mu_meson (KAPACEB.AA) 18.10.13 17:48
(4) Mi4man,

Например, здесь:
http://its.1c.ru/db/metod81#content:2499:1
ojiojiowka; Aleskey_K; Mi4man; +3 Ответить
6. ддд ддд (friday1) 18.10.13 23:17
книга покупок у вас долго формируется? обычно довольно быстро..
7. Ден (den_valley) 05.11.13 12:08
Лови плюс, классная статья, все по полочкам разложил и отчет супер.
У меня с 3 часов до 2 минут время формирования уменьшилось!
8. Mu_meson (KAPACEB.AA) 05.11.13 12:37
(7) den_valley,
Спасибо! Рад, что пригодилось)
9. Ден (den_valley) 05.11.13 13:02
Книгу продаж тоже переделал, там конечно не так все паршиво было, но все-таки с 10 минут до одной уменьшилось.
Как всегда 1C-ка в своем репертуаре, умудряется не соблюдать собственные же рекомендации :)
ojiojiowka; vladir; the1; KAPACEB.AA; +4 Ответить
10. Dmitry Bas (b-dm) 16.01.14 14:47
скачал,попробовал применить ,не взлетело.скорее всего тормоза не в этом.
кроме того, сейчас уже релиз 2.0.54 на дворе и этот кусок переместился в общий модуль УчетНДС, а вызывается в УчетНДСКлиент в СоответствиеСтрокиДопИнформацииПоСчетуФактуре.
11. Mu_meson (KAPACEB.AA) 17.01.14 15:49
Добавил в приложенный к публикации архив доработанную версию отчета "Книга покупок по Постановлению № 1137" из Бухгалтерии 2.0.54.9.
12. 17031703 (SergeyRomanov) 27.01.14 12:33
Большое тебе спасибо! Выручил! У меня при формировании за год книги покупок ошибка Сеанс завершен администратором! (буха на 64x SQL и 64x 1C сервере - 160 Гб). Хотел уж было сам выискивать слабые места в отчёте - но правильный запрос в поисковике и твой отчёт сделали мои мучения не продолжительные. Отчёт теперь формируется за 15 минут, а то висел около часу, а потом ошибка. (помогает также в таких больших базах справиться с ошибкой - пере индексация с пересчётом итогов - но скорости тестирование особо не даёт).
Спасибо!
KAPACEB.AA; +1 Ответить
13. Борис (soap) 13.02.14 14:36
спасибо Очень логично и толково описан подход и метод
14. Eugen Krivylia (EugenLiquor) 06.03.14 17:53
В принципе все довольно понятно изложено, но опять же, могут возникнуть некоторые программные сбои
15. Дмитрий Башинский (bashinsky) 26.12.14 12:04
Тоже с эти столкнулся, переписал запрос как советует автор, теперь все летает, бухи в экстазе. Автору плюс
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа