gifts2017

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

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

В одной из своих публикаций я уже затрагивал вопрос неоптимальной работы подсистемы учета НДС в условиях больших объемов данных. На этот раз попробуем ускорить заполнение документа "Формирование записей книги покупок", а, точнее, один из ее разделов "Вычет по приобретенным ценностям".

Буду краток. В процедуре УчетНДС.ОпределитьНаличиеСчетовФактурПолученных был изменен способ обращения к временной таблице счетов-фактур, использующейся для отбора при выборке из физических таблиц документов. Условие "ГДЕ Ссылка В (ВЫБРАТЬ ...)" было преобразовано во внутреннее соединение:

 

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


(пример исправления привел только для одного блока процедуры, самого критичного для производительности в моем случае - все остальные изменения по тексту процедуры отднотипны)

В результате этих изменений MSQL Server стал строить эффективный план запроса, с использованием всех доступных ему индексов, чего, к сожалению, не удавалось добиться при типовом написании запроса.

План запроса до оптимизации:

План запроса до оптимизации

Видим, что на каждом этапе происходит сканирование таблиц без использования индексов. Две большие таблицы 121 тыс. строк и 48 тыс. строк соединяются перебором в цикле и количество обработанных строк доходит до 6 миллиардов при всего 3-х строках в конечной выборке.

План запроса после оптимизации:

План запроса после оптимизации

Здесь все красиво - исходные таблицы сразу отфильтрованы по индексу и на всем протяжении происходит обработка всего 3-х строк.

На моей базе (SQL Server 2005) время выполнения запроса уменьшилось с порядка 2000-3000 секунд до 10-30 секунд. Как на Вашей конкретной базе сработает оптимизатор - это вопрос, но, если скорость заполнения у Вас катастрофически низкая, то попробовать методику стоит.

Для себя из этой истории я сделал один простой вывод - использовать условие "ГДЕ В ()" надо очень и очень аккуратно, особенно в условиях неявных соединений нескольких физических таблиц (в данном случае соединялись таблицы документа и его табличной части ДокументыОснования). Не помог даже тот факт, что временная таблица СФ была проиндексирована - оптимизатор совершенно проигнорировал этот факт и не воспользовался преимуществом индекса.

 

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


К публикации приложена внешняя обработка заполнения табличной части "Вычет по приобретенным ценностям" документа (создана на базе релиза Бухгалтерия предприятия КОРП 2.0.64.35) . 

 

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

Наименование Файл Версия Размер
Обработка заполнения "Вычет по приобретенным ценностям" 2
.epf 18,60Kb
03.11.15
2
.epf 18,60Kb Скачать

См. также

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

Комментарии

1. Александр Майоров (Eksterulo) 25.11.15 15:20
Спасибо. Это интересная идея из разряда "шаманство". Проверил на БП 3.0.42 (под 8.3.6) - действительно эффект есть.
Однако эффект вроде бы не поражает воображение. Наверное моя база не такая впечатляющая: заполнение документа полторы минуты длится.
  • На MSSQL 2008 и PostGre у меня и так всё срабатывало за 2-3 секунды.
  • В Oracle всё страшно. Но "1С" сказала, что в 8.3.7 как раз "В (&Параметр)" они страшно оптимизировали, и всё шустро залетает.
  • Те, кто пользуются файловой базой, лежащей в сети, тоже могут увидеть эффект. Но у них столько данных не наберётся, чтобы невооружённым глазом заметить ускорение на десяток процентов.
2. Александр Карасев (KAPACEB.AA) 02.12.15 08:29
(1) Eksterulo,
Спасибо за проявленный интерес к публикации. На самом деле не такое уж и шаманство :) Скорее, это очередное подтверждение известной рекомендации избегать явных и неявных (в случае виртуальных таблиц) вложенных запросов как при соединении так и в условиях. К сожалению, SQL сервер не всегда оптимально их отрабатывает, путаясь с вычислением предполагаемого количества записей при формировании плана запроса. На этапе разработки, на "маленьких" данных, все может прекрасно работать, а при разрастании базы быстрый запрос вдруг становится неадекватным.
3. Алексей Роза (DoctorRoza) 08.12.15 13:46
Автор, молоток! Плюсанул, в том числе и по Книге покупок! :)
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа