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

30.10.15

Разработка - Запросы

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

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

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

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


// Дополнение по ГТД и стране происхождения
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ РАЗРЕШЕННЫЕ
| ТаблицаТоваров.Ссылка КАК СчетФактура,
| ТаблицаТоваров.СтранаПроисхождения.Код КАК СтранаПроисхождения,
| ТаблицаТоваров.НомерГТД,
| СУММА(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)

книга покупок

См. также

SALE! 20%

Infostart Toolkit: Инструменты разработчика 1С 8.3 на управляемых формах

Инструментарий разработчика Роли и права Запросы СКД Платформа 1С v8.3 Управляемые формы Запросы Система компоновки данных Конфигурации 1cv8 Платные (руб)

Набор инструментов программиста и специалиста 1С для всех конфигураций на управляемых формах. В состав входят инструменты: Консоль запросов, Консоль СКД, Консоль кода, Редактор объекта, Анализ прав доступа, Метаданные, Поиск ссылок, Сравнение объектов, Все функции, Подписки на события и др. Редактор запросов и кода с раскраской и контекстной подсказкой. Доработанный конструктор запросов тонкого клиента. Продукт хорошо оптимизирован и обладает самым широким функционалом среди всех инструментов, представленных на рынке.

10000 8000 руб.

02.09.2020    122335    673    389    

716

Для чего используют конструкцию запроса "ГДЕ ЛОЖЬ" в СКД на примере конфигурации 1С:ERP

Запросы СКД Платформа 1С v8.3 Запросы Система компоновки данных 1С:ERP Управление предприятием 2 Бесплатно (free)

В типовых конфигурациях разработчики компании 1С иногда используют в отчетах, построенных на СКД, такую конструкцию, как "ГДЕ ЛОЖЬ". Такая конструкция говорит о том, что данные в запросе не будут получены совсем. Для чего же нужен тогда запрос?

13.02.2024    5755    KawaNoNeko    23    

23

Набор-объект для СКД по тексту или запросу

Запросы СКД Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Есть список полей в виде текста, или запрос - закидываем в набор СКД.

1 стартмани

31.01.2024    2006    2    Yashazz    0    

29

Запрос 1С copilot

Инструментарий разработчика Запросы Платформа 1С v8.3 Управляемые формы Конфигурации 1cv8 Абонемент ($m)

Пишем на человеческом языке, что нам надо, и получаем текст запроса на языке 1С. Используются большие языковые модели (LLM GPT) от OpenAI или Яндекс на выбор.

5 стартмани

15.01.2024    6300    31    mkalimulin    25    

50

PrintWizard: поддержка представлений ЗУП в конструкторе

Инструментарий разработчика Запросы Платформа 1С v8.3 Бесплатно (free)

Одной из интересных задач, стоящих в процессе разработки, была поддержка механизма представлений в ЗУП. Но не просто возможность исполнения запросов с ними. Основная проблема была в том, чтобы с ними было удобно работать, а именно: создавать, модифицировать и отлаживать. Кратко о том, что в итоге получилось...

14.12.2023    1749    vandalsvq    7    

29

Объектная модель запроса "Схема запроса" 2

Запросы Платформа 1С v8.3 Запросы Конфигурации 1cv8 Бесплатно (free)

Далеко уже не новый тип данных "Схема запроса". Статья о том, как использовать его "попроще". Примеры создания текста запроса с нуля и изменение имеющегося запроса.

06.12.2023    5397    user1923546    26    

43

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16198    skovpin_sa    14    

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

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