От экспертов «1С‑Рарус»: Что делать, когда MS SQL не может построить план запроса. Отличия PostgreSQL

Коллеги, в наших статьях мы часто говорим об оптимизации медленно выполняющихся запросов. Как правило, расследование подобных проблем начинается со сбора плана запроса. Но бывает и так, что запрос настолько сложен, что СУБД просто не в состоянии скомпилировать план. Попробуем исследовать один такой случай и поделимся наблюдениями.

MS SQL не смог сформировать план запроса

Как и многие из экспертных расследований, текущее началось с обращения пользователя, у которого при выполнении отчета в одной из внутренних баз, программа выдала такую ошибку:

СУБД нам сообщает буквально следующее — «нет возможности построить план из-за недостаточности ресурсов», и предполагает, что это связано с очень сложным запросом или слишком большим количеством таблиц.

Не часто можно увидеть такое сообщение. Тем более удивительно то, что разработчики достаточно давно не меняли отчет. То есть он перестал выполняться как бы «вдруг». Чем это можно объяснить? Одна из гипотез — усложнением системы. Отчет не менялся, но конфигурация базы изменяется постоянно. Например, могло неявно увеличиться количество таблиц, участвующих в запросе. Ввиду чего мы превысили некий «порог» возможностей планировщика запросов.

Расскажем немного о сути выполняемого отчета. Пользователь работает в базе 1С, построенной на сплаве УТ + CRM. В рассматриваемой системе есть специальный механизм консолидации данных, с помощью которого движения множества разных управленческих регистров «сливаются» в один регистр по определенным правилам.

Отчет предназначен для выявления расхождений между движениями упр. регистров и консолидированного регистра. Отсюда проистекает одна из критичных особенностей — он должен выбирать достаточно широкие данные.

Отчет построен на СКД, причем работает как некий «сборник отчетов» — содержит в себе список макетов СКД по разным разделам учета. Перед формированием отчета пользователь выбирает макет на нужный раздел.

В нашем случае проблемы наблюдаются при выборе с макетом «ПрочиеРасходы»:

Выборка, как правило, осуществляется за период равный месяцу.

Ошибка СУБД появлялась только у пользователя с ограниченными правами.

При выполнении под полными правами отчет не падал с ошибкой, но выполнялся невообразимо долго — ~2 часа.

Ещё один любопытное наблюдение, если сначала выполнить отчет с малым периодом, например, за 1 день, а после снова за полный период в месяц — отчет формировался за приемлемое время.

Параметры системы

База данных

Платформа 1С:Предприятие: 8.3.21.1895 64х.
База данных: Microsoft SQL Server 2019.
Программный продукт: Конфигурация на основе УТ + CRM.
Размер базы: > 1,2 TB.

Рабочий сервер

Операционная система: Windows Server 2019 Standart.
Процессор: 2х Intel Xeon E5-2667v3@3.2GHz.
Оперативная память: 1 TB.
Диски: база SSD 2 TB, лог 0,5 TB, TempDB 0,7 TB.

Характеристики тестового сервера

Основные манипуляции по расследованию мы проводили на тестовом сервере.
Операционная система: Windows Server 2019 Standart.
Процессор: Intel Xeon E5-2690v2@2.9GHz.
Оперативная память: 384 GB.
SSD: 16 TB, RAID5 (11 дисков).

Локализуем ошибку формирования плана запроса

Раз СУБД говорит что ей не хватает ресурсов, давайте проверим это. Настраиваем минимальный набор счетчиков в performance monitor:

• Занятая оперативная память.
• Нагрузка на процессор.
• Ожидаемое время жизни страницы.

Попутно пробуем выловить план запроса, для этого настраиваем трассировку Extended Events по событиям:

• rpc_starting — начало удаленного вызова.
• query_post_compilation_showplan — событие, которое показывает план запроса после компиляции. Не возникает, если план уже скомпилирован и сохранен в кэше.
• query_pre_execution_showplan — событие, содержащее план до исполнения запроса.
• query_post_execution_showplan — событие, содержащее план после исполнения запроса.
• rpc_completed — окончание удаленного вызова.

И устанавливаем отбор по имени базы sqlserver.database_name:

Включаем замер счетчиков и трассировку, запускаем отчет под пользователем с ограниченными правами и спустя 20 сек падаем с той же ошибкой:

Что мы видим на графике perfmon:

Можно однозначно сказать, что со счетчики памяти и PLE не изменились.

Также видим небольшой скачок нагрузки на процессор в конце вызова, но счетчик Processor time показывает в некотором роде «среднюю температуру по больнице», поэтому мы решили также снять счетчик в разрезе ядер процессора. Повторяем замер:

Тут видно, что у некоторых ядер в момент выполнения нагрузка доходит до 100%.

Во время выполнения отчета нам удалось также получить текст запроса в Activity monitor из блока Active Expensive Queries.

Запрос состоит из почти 12 тысяч строк. Отметим для себя, что выборка происходит из таблицы _AccumRg20520, это пригодится нам в дальнейшем для идентификации пакетов:

В трассировке extended events наблюдаем, что последнее событие rpc_starting:

Причем текст запроса в этом событии при сравнении практически идентичен тексту из Activity Monitor, но словно оборван на полуслове на 6178-й строке, там, где должно продолжаться условие соединения, с незакрытой скобкой внезапно начинается текст параметров:

Что ж, ошибку мы воспроизвели, но у СУБД похоже действительно не получается построить план. Ни одного события «query_..._showplan» в трассировке не сформировалось. Поэтому мы решили исследовать в запросе каждый пакет отдельно, обрезая часть запроса. С целью облегчить задачу планировщику и заодно по замерам оценить, с какими частями у него возникает больше всего сложностей.

Расследование

Проблемный запрос формируется из макета СКД «ПрочиеРасходы». Мы получили его конечную форму из макета компоновки и проанализировали. Полный текст запроса слишком велик, чтобы привести его в статье, при желании вы можете ознакомиться с ним по ссылке:

Макет компоновки для статьи «Что делать, когда MS SQL не может построить план запроса. Отличия PostgreSQL»

27.02.2024, 28.97 КБ

Скачать

Основные пакеты, которые у нас вызвали подозрение:

• врСебестоимость — выборка из РегистрНакопления.СебестоимостьТоваров;
• врНалоги — выборка из РегистрНакопления.ПрочиеАктивыПассивы;
• ВсеОстатки — выборка из РегистрНакопления.ПрочиеРасходы.

Некоторые пакеты содержат в себе по несколько объединенных выборок из одного и того же регистра.

Для начала мы провели простые замеры выполнения этих пакетов — под полными и под ограниченными правами. При этом отдельно измерялись:

• Выполнение запроса с компиляцией плана — запрос предварительно выполнялся для «прогрева кэша», после выполнялась очистка кэша планов (DBCC FREEPROCCACHE) и после этого выполнялся замер. Тем самым мы получали время выполнения запроса вместе с временем компиляции плана, но без учета времени кэширования данных.
• Последующие запуски — уже без очистки кэша планов.

Результатов выполнения пакета «ВсеОстатки» под ограниченными правами в таблице нет, т.к. его выполнение выдало ту же ошибку СУБД.

Было понятно, что основная проблема кроется в пакете ВсеОстатки, но другие пакеты также создают нагрузку и было решено проанализировать их все.

Рассмотрим их ближе.

Пакет «врСебестоимость»

Пакет состоит из 3-х запросов объединения, во всех производится выборка из таблицы регистра накопления «Себестоимость товаров».

Состав полей регистра:

Пример одного из пакетов:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	Т.Регистратор КАК Регистратор,
	Т.Период КАК Период,
	СУММА(Т.СтоимостьБезНДС) КАК СтоимостьБезНДСРасход,
	СУММА(Т.Стоимость) КАК СтоимостьРасход,
	Т.Организация КАК ОрганизацияРегистр
ПОМЕСТИТЬ врСебестоимость
ИЗ
	РегистрНакопления.СебестоимостьТоваров КАК Т
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Организации КАК Организации
		ПО Т.Организация = Организации.Ссылка
ГДЕ
	Т.Регистратор ССЫЛКА Документ.ВнутреннееПотреблениеТоваров
	И Т.Период МЕЖДУ &НачалоПериода И &КонецПериода
	И Т.ВидДвижения = &ВДРасход
	И НЕ Т.АналитикаРасходов.СчетУчета = &РБП
	И Организации.Офис = &П
СГРУППИРОВАТЬ ПО
	Т.Регистратор,
	Т.Период,
	Т.Организация

На что здесь стоит обратить внимание:

• Выборка осуществляется из физической таблицы движений.
• Отбор установлен по индексируемым полям Регистратор и Период, остальные поля не участвуют в индексах.

Запрос, который получился на стороне СУБД под полными правами ничем особо не примечателен, стоит только отметить:

• Что условие по типу регистратора превратилось отбор по полю таблицы регистра _AccumRg20673:
  • _RecorderTRef = 0×00000180.
• Никаких соединений с таблицей документа «Внутреннее потребление товаров» нет.

SELECT
	T1._RecorderTRef,
	T1._RecorderRRef,
	T1._Period,
	CAST(SUM(T1._Fld20680) AS NUMERIC(21, 2)),
	CAST(SUM(T1._Fld20679) AS NUMERIC(21, 2)),
	T1._Fld20677RRef
FROM dbo._AccumRg20673 T1
	LEFT OUTER JOIN dbo._Reference214 T2
		ON ((T1._Fld20677RRef = T2._IDRRef)) AND (T2._Fld898 = @P1)
	LEFT OUTER JOIN dbo._Reference184 T3
		ON (T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x000000B8 AND T1._Fld20695_RRRef = T3._IDRRef) AND (T3._Fld898 = @P2)
	LEFT OUTER JOIN dbo._Reference271 T4
		ON (T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x0000010F AND T1._Fld20695_RRRef = T4._IDRRef) AND (T4._Fld898 = @P3)
WHERE ((T1._Fld898 = @P4)) AND ((T1._RecorderTRef = 0x00000180) AND (T1._Period >= @P5) AND (T1._Period <= @P6)
	AND (T1._RecordKind = @P7) 
	AND (NOT (((CASE WHEN T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x000000B8 THEN CASE WHEN T3._Fld33700 IS NOT NULL THEN 0x05 END WHEN T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x0000010F THEN CASE WHEN T4._Fld27774RRef IS NOT NULL THEN 0x08 END ELSE CAST(NULL AS BINARY(1)) END = 0x08 AND CASE WHEN T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x000000B8 THEN T3._Fld33700 WHEN T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x0000010F THEN CASE WHEN T4._Fld27774RRef IS NOT NULL THEN @P8 END ELSE CAST(NULL AS NVARCHAR(10)) END = @P9 AND CASE WHEN T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x000000B8 THEN CASE WHEN T3._Fld33700 IS NOT NULL THEN 0x00000000000000000000000000000000 END WHEN T1._Fld20695_TYPE = 0x08 AND T1._Fld20695_RTRef = 0x0000010F THEN T4._Fld27774RRef ELSE CAST(NULL AS BINARY(16)) END = @P10)))) AND (T2._Fld42886RRef = @P11))
GROUP BY T1._RecorderTRef,
	T1._RecorderRRef,
	T1._Period,
	T1._Fld20677RRef

Также мы собрали план под полными правами:

В плане видно, что существенную часть времени занимает оператор поиска в кластерном индексе AccumRg20673_ByPeriod_TRN регистра накопления «Себестоимость товаров».

Кластерный индекс состоит из следующих полей:

И в условиях оператора Clustered index seek видно, во что превратились условия запроса по ссылке, периоду и виду движения:

• Т.Регистратор ССЫЛКА Документ.ВнутреннееПотреблениеТоваров.
• Т.Период МЕЖДУ &НачалоПериода И &КонецПериода.
• Т.ВидДвижения = &ВДРасход.

Условие по разделителю и периоду попало в раздел Seek Predicate:

• [_AccumRg20673]._Fld898 = Scalar Operator([@P4]).
• [_AccumRg20673]._Period > Scalar Operator([Expr1049]).
• [_AccumRg20673]._Period < Scalar Operator([Expr1050]).

А вот условие по виду движения и ссылке (то есть типу регистратора) попало в раздел Predicate:

• [_AccumRg20673].[_RecordKind] as [T1].[_RecordKind]=[@P7].
• [_AccumRg20673].[_RecorderTRef] as [T1].[_RecorderTRef]=0×00000180.

По ним СУБД пришлось просканировать индекс. Как правило, оператор с такими условиями называют «seek..where» — он может быть довольно опасен, т.к. маскирует сканирование.

Постойте, но поле _RecorderTRef есть в индексе и идет сразу после периода, почему же СУБД сканирует по нему?

Дело в том, что условия вида «больше/меньше» не позволяют использовать индекс для поиска по нижеследующим полям.

Существуют рекомендации ставить такое условие только для последних полей в индексе.

Вот например из системы стандартов и методик разработки от 1С: its.1c.ru/db/v8std/content/658/hdoc.

Дабы удостовериться в этом мы в качестве эксперимента изменили в запросе условие по периоду на равенство. Как итог, условие по типу регистратора попало в раздел Seek Predicates:

Но, очевидно, в нашем отчете такое изменение не подойдет.

В двух других пакетах объединения помимо условия по типу регистратора:

• Т.Регистратор ССЫЛКА Документ.СписаниеНедостачТоваров

Также присутствуют условия и на реквизиты регистратора:

• НЕ ВЫРАЗИТЬ(Т.Регистратор КАК Документ.СписаниеНедостачТоваров).НеОтражатьПоДоходам
• ВЫРАЗИТЬ(Т.Регистратор КАК Документ.СписаниеНедостачТоваров).СтатьяРасходов В ИЕРАРХИИ (&СтатьиСебестоимости)

И при формировании запроса SQL платформа такие условия преобразовывает к левому соединению с таблицей документа:

FROM dbo._AccumRg20673 T5
LEFT OUTER JOIN dbo._Document480 T7
ON (T5._RecorderTRef = 0x000001E0 AND T5._RecorderRRef = T7._IDRRef) AND (T7._Fld898 = @P13)

Планировщик их преобразовал в соединения циклами:

Также стоит отметить несколько моментов:

1. Вместо использования кластерного индекса по периоду планировщик выбрал сочетание поиска из индекса по регистратору с такими условиями:

   

   И оператора Key Lookup по кластерному индексу для досчета строк по условию по виду движения:

   

2. Также интересно, что в двух пакетах выборка данных по документу сделана разными операторами — Clustered Index Seek и Clustered Index Scan. Но стоимость их примерно одинакова, так как Index Seek на самом деле тот же seek..where с условием только по разделителю, который в данном случае во всей таблице один:

   

В плане для ограниченных прав, касательно условий что мы рассмотрели, используются аналогичные операторы. Единственное, но важное отличие — это внушительная rls-ная добавка:

Тогда мы провели ещё один эксперимент. В продолжение вопроса об отборе по типу регистратора (поле _RecorderTRef) — добавили «ручной» индекс прямо в СУБД, аналогичный кластерному по составу полей. Но сместили поле типа регистратора выше периода:

Увы, новый индекс так и не задействовался, план запроса не изменился.

Что ж, в этом пакете мы не нашли потенциала для улучшения. И в целом, единственное что в нём смущало — это время компиляции плана для ограниченных прав, об этом поговорим ниже.

Пакет «врНалоги»

Запрос состоит из одного пакета с выборкой из таблицы регистра накопления «ПрочиеАктивыПассивы». Состав полей:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	Т.Регистратор КАК Регистратор,
	СУММА(Т.Сумма) КАК СуммаПриход
ПОМЕСТИТЬ врНалоги
ИЗ
	РегистрНакопления.ПрочиеАктивыПассивы КАК Т
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Организации КАК Организации
		ПО Т.Регистратор.Организация = Организации.Ссылка
ГДЕ
	Т.Регистратор ССЫЛКА Документ.СписаниеБезналичныхДенежныхСредств
	И Т.Регистратор.ХозяйственнаяОперация = &ПеречислениеНалога
	И Т.Период МЕЖДУ &НачалоПериода И &КонецПериода
	И Т.ВидДвижения = &ВДПриход
	И Т.Статья.СчетФинансовогоУчета В(&Счета)
	И Организации.Офис = &П
СГРУППИРОВАТЬ ПО
	Т.Регистратор

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

• Т.Регистратор ССЫЛКА Документ.СписаниеБезналичныхДенежныхСредств

Из-за следующих условий:

• «Т.Регистратор.Организация = ...»
• «Т.Регистратор.ХозяйственнаяОперация = ...»

Платформа добавляет в запрос множество соединений с таблицами документов, которые входят в тип регистратора, что мы и увидели в запросе SQL. Приведем фрагмент:

Однако планировщик в данном случае «разруливает» эту ситуацию и оставляет оператор выборки только из таблицы с нужным документом:

• Document478 — Документ.СписаниеБезналичныхДенежныхСредств.

При выполнении запроса под ограниченными правами, запрос в SQL разрастается неимоверно, т. к. теперь для каждого соединения с таблицами документов добавляется надстройка RLS в виде вложенного запроса. Привести его в статье не представляется возможным. Для сравнения текст состоит из 3677 строк, против 127 строк запроса для полных прав.
Но если мы посмотрим план, то и здесь планировщик «переварил» эту ситуацию, и оставил только один документ:

Хотя планировщик и подстраховал нас в этом месте, запрос необходимо было исправить. Мы заменили все условия вида:

• «Т.Регистратор.Реквизит = ...»

На условия:

• «ВЫРАЗИТЬ(Т.Регистратор КАК Документ.СписаниеБезналичныхДенежныхСредств).Реквизит = ...»

Как итог, количество строк текста запроса SQL сократилось:

• Для полных прав 127 → 15.
• Для ограниченных прав 3677 → 93.

Приводим текст запроса SQL для полных прав:

Как ни странно планы запросов изменились незначительно:

• Полные права:

  

• Ограниченные права:

  

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

Пакет «ВсеОстатки»

Следующий и самый сложный пакет «ВсеОстатки». Он состоит из 11 запросов объединения, в 3 из них берутся данные временных таблиц, а в остальных 8 производится выборка из таблицы регистра накопления «Прочие расходы».

Состав полей регистра:

Мы замерили отдельное выполнение каждого подпакета выборки из регистра под полными правами:

Пакеты №6 и №7 по замерам показали наихудшие результаты, поэтому всё внимание решили направить на них.

Текст запроса пакет №6:

ВЫБРАТЬ
	Т.Регистратор,
	СУММА(ВЫБОР
			КОГДА УчетнаяПолитикаОрганизацийСрезПоследних.УчетнаяПолитика.СистемаНалогообложения = &Общая
					И Т.Регистратор.Дата >= &ДатаНДС_2
				ТОГДА Т.СуммаБезНДС
			ИНАЧЕ Т.Сумма
		КОНЕЦ),
	0,
	0
ИЗ
	РегистрНакопления.ПрочиеРасходы КАК Т
		ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.УчетнаяПолитикаОрганизаций.СрезПоследних(&КонецПериода, ) КАК УчетнаяПолитикаОрганизацийСрезПоследних
		ПО (ВЫРАЗИТЬ(Т.Регистратор КАК Документ.ПоступлениеТоваровУслуг).Организация = УчетнаяПолитикаОрганизацийСрезПоследних.Организация)
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Организации КАК Организации
		ПО Т.Организация = Организации.Ссылка
ГДЕ
	Т.Период МЕЖДУ &НачалоПериода И &КонецПериода
	И Т.Активность
	И Т.Организация В ИЕРАРХИИ(&Филиал)
	И Т.ХозяйственнаяОперация = &ЗакупкаУПоставщика
	И НЕ Т.Проект В (&ИсклПроекты)
	И НЕ Т.СтатьяРасходов.ВариантРаспределенияРасходов = &РБП1
	И НЕ Т.АналитикаРасходов В
				(ВЫБРАТЬ
					ПрочиеРасходыНеДИР.Ссылка
				ИЗ
					ПрочиеРасходыНеДИР КАК ПрочиеРасходыНеДИР)
	И Организации.Офис = &П
СГРУППИРОВАТЬ ПО
	Т.Регистратор

Одна из ключевых проблем 6-го и 7-го пакетов, как и в пакете «врНалоги» в использовании разыменования реквизитов регистратора составного типа через точку:

• Т.Регистратор.Дата

Как и в прошлый раз в тексте запроса SQL под полным правами мы видим множество левых соединений с таблицами документов:

И в этот раз, в отличии от пакета «врНалоги», планировщик не ограничил выборку, план под полными правами представляет классическую «лесенку» с множеством операторов получения данных документов и соединений типа «Nested loops» / «Hash match»:

Небольшой фрагмент содержащий оператор выборки из регистра и несколько операторов чтения таблиц документов + соединения:

А под ограниченными правами план проблематично даже охватить взглядом, не то что привести в статье. Для оценки масштаба приведем картинку с половиной плана:

Тут хочется упомянуть об одной особенности extended events. При ряде замеров у нас в трассировке не появлялось событие query_post_execution_showplan, с нужным запросом в логе были только события rpc_starting и rpc_complited:

Такое может происходить в случае, если размер данных события очень большой. В нашем случае помогло увеличение настройки «Max memory size» с 4 до 40МБ. После этого события с планами удалось собрать:

Способ исправления данного запроса, как и в прошлый раз, — замена условий на связку «ССЫЛКА» и «ВЫРАЗИТЬ».

В запросе стоит условие по хоз. операции:

• Т.ХозяйственнаяОперация = &ЗакупкаУПоставщика

При анализе регистра видно, что состав регистраторов с этой хоз. операцией ограничен двумя документами:

• ПоступлениеТоваровУслуг
• ПереоценкаСебестоимостиТоваров

Конечный вариант с исправлениями выглядит так:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	Т.Регистратор КАК Регистратор,
	СУММА(ВЫБОР
			КОГДА УчетнаяПолитикаОрганизацийСрезПоследних.СистемаНалогообложения = &Общая
					И ВЫБОР
						КОГДА Т.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
							ТОГДА ВЫРАЗИТЬ(Т.Регистратор КАК Документ.ПоступлениеТоваровУслуг).Дата
						КОГДА Т.Регистратор ССЫЛКА Документ.ПереоценкаСебестоимостиТоваров
							ТОГДА ВЫРАЗИТЬ(Т.Регистратор КАК Документ.ПереоценкаСебестоимостиТоваров).Дата  
					КОНЕЦ >= &ДатаНДС_2
				ТОГДА Т.СуммаБезНДС
			ИНАЧЕ Т.Сумма
		КОНЕЦ) КАК Поле1,
	0 КАК Поле2,
	0 КАК Поле3
ПОМЕСТИТЬ ВсеОстатки
ИЗ
	РегистрНакопления.ПрочиеРасходы КАК Т
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ УчетнаяПолитикаОрганизаций КАК УчетнаяПолитикаОрганизацийСрезПоследних
		ПО (ВЫБОР
				КОГДА Т.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
					ТОГДА ВЫРАЗИТЬ(Т.Регистратор КАК Документ.ПоступлениеТоваровУслуг).Организация = УчетнаяПолитикаОрганизацийСрезПоследних.Организация
				КОГДА Т.Регистратор ССЫЛКА Документ.ПереоценкаСебестоимостиТоваров
					ТОГДА ВЫРАЗИТЬ(Т.Регистратор КАК Документ.ПереоценкаСебестоимостиТоваров).Организация = УчетнаяПолитикаОрганизацийСрезПоследних.Организация
				ИНАЧЕ ЛОЖЬ
			КОНЕЦ)
		ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Организации КАК Организации
		ПО Т.Организация = Организации.Ссылка
ГДЕ
	Т.Период МЕЖДУ &НачалоПериода И &КонецПериода
	И Т.Активность
	И Т.Организация В ИЕРАРХИИ(&Филиал)
	И Т.ХозяйственнаяОперация = &ЗакупкаУПоставщика
	И НЕ Т.Проект В (&ИсклПроекты)
	И НЕ Т.СтатьяРасходов.ВариантРаспределенияРасходов = &РБП1
	И НЕ Т.АналитикаРасходов В
				(ВЫБРАТЬ
					ПрочиеРасходыНеДИР.Ссылка
				ИЗ
					ПрочиеРасходыНеДИР КАК ПрочиеРасходыНеДИР)
	И Организации.Офис = &П
	И (Т.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
			ИЛИ Т.Регистратор ССЫЛКА Документ.еис_ПереоценкаСебестоимостиТоваров)  
СГРУППИРОВАТЬ ПО
	Т.Регистратор

Как и прежде, тексты запросов уменьшились в разы, а планы запросов стали куда лаконичнее:

• Полные права:

  

• Ограниченные права:

  

Итоговые замеры:

Конечно, анализ по хоз. операции мы сделали однократно, в идеале его необходимо выполнять динамически, т. к. состав регистраторов может меняться. Поэтому у нас в планах переделать отчет на генерацию части запроса перед выполнением.

Ещё один проблемный по замерам пакет — №7. Причины замедления и способ исправления в нём аналогичны, поэтому подробно на нём останавливаться не будем.

Финальный пакет

Обойдя промежуточные пакеты мы пришли к итоговой выборке. И тут обнаружили неприятность — в структуре СКД в качестве выбранного поля и поля упорядочивания выбран реквизит Дата через точку от поля Документ:

Из-за чего платформа добавила дополнительные поля в запрос:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
	ВсеОстатки.Документ КАК Документ,
	СУММА(ВсеОстатки.СуммаПредв) КАК Сумма,
	СУММА(ВсеОстатки.СуммаЦПредв) КАК СуммаЦ,
	СУММА(ВсеОстатки.СуммаЦКорр) КАК СуммаЦКорр,
	СУММА(ВсеОстатки.СуммаПредв - ВсеОстатки.СуммаЦПредв) КАК Разница,
	ПРЕДСТАВЛЕНИЕССЫЛКИ(ВсеОстатки.Документ) КАК ДокументПредставление,
	ВсеОстатки.Документ.Дата КАК ДокументДата,
	ВЫБОР
		КОГДА ВсеОстатки.Документ.Дата ЕСТЬ NULL
			ТОГДА 0
		ИНАЧЕ 1
	КОНЕЦ КАК ДокументДатаПолеУпорядочивания1  
ИЗ
	врВсе КАК ВсеОстатки

В части «проблемных» запросов выше мы смогли ограничить выборки по типам регистраторов, но в других выборках такого ограничения нет. Мы уже упоминали в самом начале, что данный отчет должен брать максимально широкие данные. И по итогу всё равно платформа вынуждена добавлять множество левых соединений с документами, на каждое из которых добавляется блок rls — запрос усложняется кратно и планировщик не справляется с задачей.

Оптимизируем

Но раз такие пироги и от выборки реквизитов документов нам не уйти, дабы облегчить планировщику труд, внесем в запрос пару глобальных изменений:

1. Дата документа нам так или иначе нужна, поэтому вместо того, чтобы выбирать её через точку в финальном запросе, мы решили получить её как можно раньше — в исходных таблицах движений. При этом, убрав такие соединения в дочерних таблицах, которые мы рассматривали выше. Тем самым уменьшая количество неявных левых соединений.
2. Выборки из регистров «Себестоимость товаров» и «Прочие расходы» сейчас используются в нескольких запросах и в каждом из них платформой добавляется надстройка rls. Мы вынесли обращение к регистру в отдельную временную таблицу, тем самым чтение из каждого регистра производится только один раз.

Примерная схема изменений:

Напомним, с чего начинали. До оптимизации отчет:

• Под полными правами — зависал на 2 с лишним часа.
• Под ограниченными правами — и вовсе не формировался, падая с ошибкой построения плана.

Теперь пробуем сформировать отчет после всех внесенных нами изменений — и отчет формируется, причем за вполне приемлемое время:

Посмотрим события трассировки под ограниченными правами:

По ним видно, что наибольшую сложность для компиляции планировщиком по-прежнему представляют запросы с неявными левыми соединениями, из таблиц:

• _AccumRg20520 — РегистрНакопления.ПрочиеРасходы:
• По полю «Регистратор», которое насчитывает 65 типов документов.
• _AccumRg27453 — РегистрНакопления.ОборотыФинансовогоПланаСчетов:
• По полю «ДокументДвижения», которое насчитывает 62 типа документов.

Так что не удивительно, что планировщик в этом месте напрягается. Но теперь таких выборок минимальное количество и запрос должен вести себя более предсказуемо.

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

Но это уже совсем другая история.

Поведение PostgreSQL в аналогичных условиях

С подобными ошибками не часто встречаемся. И раз у нас запрос настолько «тяжелый», мы решили заодно «проверить на прочность» планировщик PostgreSQL —как он справится с отчетом ДО наших оптимизаций?

Параметры машины, на которой проводился тест

СУБД: PostgreSQL 11.8.
Операционная система: Ubuntu 18.04.6.
Процессор: Intel Xeon CPU E5-2637 v2@3.5GHz.
Оперативная память: 188 GB.
SSD: 5 TB.

Утилита ibcmd

Для воспроизведения этой же ситуации в PostgreSQL необходимо выполнить те же действия при тех же условиях. Другими словами необходимо перенести базу c MSSQL в PostgreSQL. Раньше для этого мы бы воспользовались выгрузкой-загрузкой dt, что заняло бы значительное время. Но начиная с версии платформы 8.3.23 появилась возможность переноса базы с помощью утилиты автономного сервера напрямую (its.1c.ru/db/v8323doc#bookmark:adm:TI000000894).

Давайте рассмотрим, как выглядит этот процесс на примере демо-базы БП 3.0 КОРП.

Необходимо открыть командную строку. Например, в Windows это выполняется через Пуск → Выполнить → cmd

Перейти в каталог 23-й платформы (или старше):

Само копирование выполняется одной командой (описание параметров с ИТС its.1c.ru/db/v8323doc#bookmark:adm:TI000001162):

Где:

Далее идет процесс переноса:

Процесс создания индексов:

И завершение репликации:

Теперь проверим результат копирования. Создаем новую базу на сервере приложений 1С:

В целях демонстрации возможностей мы специально взяли базу на платформе 8.3.21. Как мы видим сама база не обязательно должна быть на 23-ей платформе, т. к. репликация производится напрямую между СУБД.

Что можно сказать в итоге:

• Инструмент удобен в использовании, одной командой можно получить работоспособную базу на СУБД отличной от источника.
• Что касается времени:
• Копирование демо базы БП КОРП заняло 10 минут.
• Копирование продуктивной базы БП КОРП 3.0 объемом 250 ГБ отработало за 1,5 часа.
• Копирование продуктивной базы УТ + CRM объемом 600 Гб выполнилось за 4 часа.

Тесты

Далее на этой перенесенной на PostgreSQL базе, мы по простому попробовали выполнить исходный отчет ДО изменений. Тот самый, который на MS SQL падал с ошибкой компиляции. И на наше удивление PostgreSQL с этим хитрым отчетом справился.

Результаты замеров:

Для сравнения снова приведем замеры на MS SQL ПОСЛЕ оптимизации:

Планы запросов в PostgreSQL тоже получились немаленькие. К примеру, план самого «убойного» пакета «ВсеОстатки» выглядит вот так:

• Полные права:

  

• Ограниченные права:

  

Проверяем гипотезу о возникновении ошибки из-за превышения ограничения в 256 таблиц в старых версиях MS SQL

А помните, когда-то в 1С при работе с MS SQL встречалась такая ошибка:

Судя по документации learn.microsoft.com/en-us/previous-versions/sql/sql-server-2005/ms143432(v=sql.90) она могла встречаться в Microsoft SQL Server 2005:

Для нашей версии Microsoft SQL Server 2019 learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver15 теперь эта спецификация выглядит так:

Это навело на мысль, а что если ограничение всё таки связано с количество таблиц, просто его немного завуалировали.

Мы взяли самый тяжелый пакет «ВсеОстатки» до оптимизации, его текст, который получился на SQL под ограниченными правами, и подсчитали количество таблиц.

Получилась следующая картина:

• Уникальных таблиц в запросе = 87, с временными таблицами 93.
• От вида СУБД это не зависит — и на MS SQL и на PostgreSQL количество одинаковое.
• Если брать не уникальные таблицы, а общее количество использований, то мы увидим что ТОП-5 это таблицы блока ограничений доступа:

  

Заключение

В статье мы рассмотрели некоторые подходы к построению расследования и оптимизации запросов. Основные тезисы, которые включает в себя статья:

• Может перестать работать запрос, что работал ранее.
• Время на компиляцию планов запроса может превышать время выборки данных.
• Планы запросов для MS SQL и PostgreSQL строяся по-разному и, порой, PostgreSQL выигрывает.