Запрос в цикле 1с как избежать

Как избежать запроса в цикле?

Иногда случается такое что получается что отправление идет по той же накладной но имеет еще заполненное поле досылка. И срок доставки изменился, но Меня интересует именно срок доставки который стоял первоначально.
Для примера срок доставки был 22 числа
Некоторые отправления идут как досылки и у них срок доставки стоит 25.

Идея такая при записи в регистр проверять самый первый документ, по дате отправки, находить срок доставки и если есть досылка и больше этой даты то записываем в регистр новую дату срок доставки.
Да вроде и решение написал, но это будет запрос в цикле. А каким образом Можно обойтись без этого.
Можно и при записи документа или формировании делать. Это понимаю. Но как саму логику сделать, может кто подсказать может были похожие случае. Запрос в цикле не хочу писать даже если если там будет всего 1-2 раза, потому что может быть и 200 и 300 и до 4000

Как избежать запроса в цикле?
Как известно запрос в цикле — это не есть хорошо) Я делаю проверку при проведении документа. В.

Как избежать погрешности в цикле for с десятичным шагом?
День добрый. У меня есть цикл for(double x=1;x&lt;=5;x+=0.01)< count++; .

Как при обновлении страницы избежать повторной отправки POST запроса с передачей переменных в шаблон?
Есть страничка юзера, на ней форма изменения роли(админ/юзер). Реализовано на радиокнопках и кнопке.

Как избежать пред-запроса OPTIONS, при этом не потеряв возможность добавлять headers к запросу
XHR, при использовании setRequestHeader, с начало идет запрос OPTIONS и только потом необходимый.

Как посчитать сумму в цикле запроса?
как можно в цикле посчитать 2 поля одновременно? WHILE @i &lt;&gt; (SELECT month(@Data_F) + 1.

Запрос в цикле 1с как избежать

Коллеги, подскажите, может, кто сталкивался. Есть табличная часть — запрашиваемая номенклатура (строка) и количество(число)

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

Например, пользователь вбил ААА31 Паста Блендамет.

В регистре есть строки:

Паста Блендамет ААА31
ААА31 Паста Блендамет
Паста Блендамет ААА31 со кусом клубники

Эти все строки он должен найти, запрос ПОДОБНо в лоб, естсенно не отрабатывает.

Пришла в голову мысль, для каждой строки ТЧ формировать свой текст Запроса с условием:
ПОДОБНО %Паста%
И ПОДОБНО %АА3%
И ПОДОБНО %БЛЕНДАМЕТ%

Искать нужно по нескольким колонкам.

Можно ли избежать запрос в цикле? Есть более изящное решение?

Паста Блендамет ААА31
ААА31 Паста Блендамет
Паста Блендамет ААА31 со кусом клубники

Тип: Паста
Марка: Блендамет
Артикул: ААА31
Свойство: со кусом клубники

Не понял. Почему это должно быть в цикле?

ПОДОБНО %Паста%
И ПОДОБНО %АА3%
И ПОДОБНО %БЛЕНДАМЕТ%

Без цикла разве не сработает?

(25) Вы уверены что это сработает?

Предположим у нас 25 строк в табличной части, у каждой строки по 3 -4 слова.

Я все это соберу в один запрос. Он и будет искать эти все слова разом, а не по отдельности. Или я не прав?

(32) я вот думаю тут запрос в цикле будет оптимален с точки зрения сложности кода) Не стоит прям всегда избегать запросов в цикле, главное решить задачу.

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

(0) Как сочетаются два утверждения из (0)

1. "Есть табличная часть — запрашиваемая номенклатура (строка) и количество(число)"
2. "Искать нужно по нескольким колонкам."

Собственно где взять несколько колонок, если их только 2-е?
О каком запросе в цикле идет речь?

(42) В итоге выяснится, что ТС нужно было понять, как

ААА31 Паста Блендамет

ПОДОБНО %Паста%
И ПОДОБНО %АА3%
И ПОДОБНО %БЛЕНДАМЕТ%

И даже может быть он бы и сам справился, но кто-то ему сказал, что "должен остаться кто-то один": либо цикл, либо запрос. Вот он и боится цикл рядом с запросом писать

(44) У вас де таблицы. Одна берется из регистра. А вторая таблица получена из строки за счет разбивки строки по словам с разделителем пробел.
Если условие "И", то для каждой строки регистра получаешь несколько строк за счет соединения.
Добавляешь расчетное поле в запросе, которое рассчитываешь по правилу: Если СтрокаВРегистре содержит СтрокаПоиска тогда 1 иначе 0 назовем это поле А. И второе поле всегда 1, назовем его Б.
Потом суммируешь эти два поля и где условие HAVING Сумма(А) = Сумма(Б)

Если условие ИЛИ, то второе поле И не нужно, а условие будет HAVING Сумма(А) > 0

(48) Простой пример
Есть тест со следующими строками
1)ААА31 Паста Блендамет
2)Батарейка ААА зайчик

Есть регистр со следующими строками
1)Паста Блендамет ААА31
2)ААА31 Паста Блендамет
3)Паста Блендамет ААА31 со кусом клубники
4)Батарейка Панасоник зайчик ААА
5)ААА зайчик филипс батарейка
6)Батарейка зайчик

Так вот для первой строки нужно вывести 1,2,3 регистра, для второй строки 4,5 соответственно судя по (0).

В Вашем же случае из (48) Вы просто получаете перечень слов без привязки к строкам, поэтому вряд ли это решение подойдет. Или я как-то не понял, что вы имели в виду за вторую таблицу.

(50) Из строк теста по строкам можно получить таблицу с двумя колонками. Или с тремя — как хотите.

Номер поиска | строкаПоиска
1 ААА31
1 Паста
1 Блендамет
2 Батарейка
2 ААА
2 зайчик

Раз условие И, тогда условие: HAVING Сумма(А) = Сумма(Б)

Собственно результат условие для первой строки поиска даст истину для строк регистра 1,2,3, а для 2-й даст истину для строк 4 и 5.

(51) Вот вам для тестов)
ТЗ=Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("НомерСтроки",Новый ОписаниеТипов("Число"));
ТипСтрока=Новый ОписаниеТипов("Строка". Новый КвалификаторыСтроки(20));
ТЗ.Колонки.Добавить("СтрокаПоиска",ТипСтрока);
НовСтрока=ТЗ.Добавить();
НовСтрока.НомерСтроки=1;
НовСтрока.СтрокаПоиска="%ААА31%";
НовСтрока=ТЗ.Добавить();
НовСтрока.НомерСтроки=1;
НовСтрока.СтрокаПоиска="%Паста%";
НовСтрока=ТЗ.Добавить();
НовСтрока.НомерСтроки=1;
НовСтрока.СтрокаПоиска="%Блендамет%";

НовСтрока=ТЗ.Добавить();
НовСтрока.НомерСтроки=2;
НовСтрока.СтрокаПоиска="%Батарейка%";
НовСтрока=ТЗ.Добавить();
НовСтрока.НомерСтроки=2;
НовСтрока.СтрокаПоиска="%ААА%";
НовСтрока=ТЗ.Добавить();
НовСтрока.НомерСтроки=2;
НовСтрока.СтрокаПоиска="%зайчик%";
//
Реестр=Новый ТаблицаЗначений;
Реестр.Колонки.Добавить("Наименование",Новый ОписаниеТипов("Строка". Новый КвалификаторыСтроки(40)));
НовСтрока=Реестр.Добавить();
НовСтрока.Наименование="Паста Блендамет ААА31";
НовСтрока=Реестр.Добавить();
НовСтрока.Наименование="ААА31 Паста Блендамет";
НовСтрока=Реестр.Добавить();
НовСтрока.Наименование="Паста Блендамет ААА31 со кусом клубники";
НовСтрока=Реестр.Добавить();
НовСтрока.Наименование="Батарейка Панасоник зайчик ААА";
НовСтрока=Реестр.Добавить();
НовСтрока.Наименование="ААА зайчик филипс батарейка";
НовСтрока=Реестр.Добавить();
НовСтрока.Наименование="Батарейка зайчик";

(57) В данной задаче не нужно знать где именно в строке находится подстрока. Важно знать есть она или нет. Так что LIKE тут более чем достаточно.
(60) Если очень примерно и для понимания, то запрос ниже. 1С запрос не поддерживает select (select . ). Вместо этого в 1С придется писать "inner join", как это сделать думаю понятно. Если Строка, в которой ищем является строкой ограниченной длины, то запрос проще. Нумеровать вторую таблицу тогда не нужно, а группировать прямо по S можно. Вторую таблицу тоже пронумеровал, только для информативности, что бы понятно было по какой строке поиска получен результат.

with t1 as
(SELECT 1 as npp, ‘ААА31’ as s
UNION ALL SELECT 1, ‘Паста’
UNION ALL SELECT 1, ‘Блендамет’
UNION ALL SELECT 2, ‘Батарейка’
UNION ALL SELECT 2, ‘ААА’
UNION ALL SELECT 2, ‘зайчик’
)

,t2 as
(SELECT
row_number() over() as npp, t.s as s
from
(SELECT ‘Паста Блендамет ААА31’ as s
UNION ALL SELECT ‘ААА31 Паста Блендамет’
UNION ALL SELECT ‘Паста Блендамет ААА31 со кусом клубники’
UNION ALL SELECT ‘Батарейка Панасоник зайчик ААА’
UNION ALL SELECT ‘ААА зайчик филипс батарейка’
UNION ALL SELECT ‘Батарейка зайчик’
) as t
)

SELECT
t.npp2 /*строка по порядку, в итоговом запросе нужна только для информативности*/
,(SELECT s from t2 as t2 WHERE t2.npp = t.npp2 LIMIT 1) as s /*так если строка неограниченной длины, иначе можно групировать по S и поле npp2 не нужно*/
,t.npp1 /*это номер поиска для информативности, когда понятно убрать*/
FROM
(SELECT
t2.npp as npp2
,t1.npp as npp1
,sum(iif(t2.s like ‘%’|| t1.s ||’%’, 1 , 0)) as A
,sum(1) as B
FROM t2 as t2
LEFT JOIN t1 as t1 on 1=1

GROUP by
t2.npp, t1.npp
HAVING
A=B
) as t

Это результат (батарейка с большой и маленькой буквы, поэтому 5 не попало, но можно отвязаться от заглавных и строчных букв):
npp2 s npp1
1 Паста Блендамет ААА31 1
2 ААА31 Паста Блендамет 1
3 Паста Блендамет ААА31 со кусом клубники 1
4 Батарейка Панасоник зайчик ААА 2

(60) В (62) запрос рабочий. По быстрому в SQLite набросал.
"||" это конкатенация строк. Заменить на "+".
"LIMIT 1" равноценно "TOP 1", но в 1С не понадобиться так как необходимо будет рисовать внизу "INNER JOIN t2 as t2 ON t2.npp = t.npp2" и в select напрямую брать t2.s.
with аналог в 1С "ПОМЕСТИТЬ"
"row_number() over() as npp" добавить колонку нумерации всех строк от первой до последней как есть без сортировки.
"iif(" это короткая запись "ВЫБОР КОГДА. ТОГДА . ИНАЧЕ.. КОНЕЦ", если по аналогии с обычным языком 1С то "?(условие, ЗначениеЕслиУсловие_ИСТИНА, ЗначениеЕслиУсловие_ЛОЖЬ)".

Собственно наверное всё пояснил. На языке запросов 1С это без проблем пишется.

Уточнение запросов в цикле. Отключить контроль на уровне запроса, а не цикла, #845

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

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

Аналогично — был код, которые брал данные по значению реквизита (допустим), теперь условия усложнились, и этот кусок переписали на запрос, и там всегда, по сути, будет возвращаться или одна строка, или ни одной, там даже ПЕРВЫЕ может стоять, в идеале. Так вот — теперь у меня по конфе вылезло 100 мест где вылезла ошибка, связанная с тем, что теперь есть запрос в цикле.

Влияние оптимизиатора запросов на производительность 1с

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

Встроенный язык 1С:Предприятие предназначен для запросов в терминах предметной области

Для того чтобы разработчик имел возможность использовать запросы для реализации собственных алгоритмов, в 1С:Предприятии реализован язык запросов. Этот язык основан на SQL, но при этом содержит значительное количество расширений (виртуальные таблицы, обращение через точку и т.п.), ориентированных на отражение специфики финансово-экономических задач и на максимальное сокращение усилий по разработке прикладных решений.
Модель работы с базой данных, реализованная в 1С:Предприятии 8, позволяет разработчику сосредоточиться на создании бизнес-логики приложения и не заботиться о структурах таблиц, преобразованиях типов данных и пр.
Платформа 1С:Предприятия обеспечивает операции исполнения запросов, описания структур данных и манипулирования данными, транслируя их в соответствующие команды. В случае клиент-серверного варианта работы — это команды MS SQL Server 2005, IBM DB2 или PostgreSQL.

«Внутренняя кухня» на примере MS SQL Server

Возьмем пример запроса 1С и проследим за путем его исполнения.

Воспользуюсь обработкой «Консоль запросов» с диска ИТС и выполню простенький запрос:

Большинство запросов, это инструкция «ВЫБРАТЬ …», поэтому мне кажется будет правильным дальше опираться на эту инструкцию.

Для знакомых с языком SQL команда «ВЫБРАТЬ» — это перевод на русский язык команды SELECT (кстати, если писать в 1С на английском языке, то запросы 1С становятся еще больше похожими на запросы SQL).

Давайте проследим путь выполнения этого запроса.

Путь прохождения запроса

Прежде всего не пугайтесь :). Расшифрую рисунок (он хоть и немного устарел, но суть сохранилась). Мы — это «пользователь» на рисунке. В консоли запросов я нажимаю кнопку «Выполнить».

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

Отслеживание запросов технологическим журналом

С помощью обработки (тоже с диска ИТС) «Настройка технологического журнала» я создаю файл logcfg.xml, который будет логировать запросы к субд.

Содержимое файла у меня было такое:

Текст logcfg.xml содержит фильтр dbmssql на сбор всех запросов к MS SQL Server.

затем открыл файл технологического лога

Для моего запроса в консоли запросов в логе появились такие строчки:
45:44.1727-1,DBMSSQL,3,process=rphost,p:processName=bi,t:clientID=15,t:applicationName=1CV8,t:computerName=TESTCENTER,t:connectID=10,Usr=Тест,Trans=0,dbpid=52,Sql=

‘SELECT
_InfoReg482_Q_000_T_001._Fld484 AS f_1
FROM
_InfoReg482 _InfoReg482_Q_000_T_001 WITH(NOLOCK)
WHERE
_InfoReg482_Q_000_T_001._Fld483RRef = ?
p_0: 0x9455005056C0000811DCE204C649E3E8
‘,Rows=1,RowsAffected=-1
45:44.2031-0,Context,1,process=rphost,p:processName=bi,t:clientID=15,t:applicationName=1CV8,t:computerName=TESTCENTER,t:connectID=10,Usr=Тест,Context=’
Обработка.КонсольЗапросов.Форма.Форма : 458 : мРезЗапроса = ОбъектЗапрос.Выполнить();’

У нас появился SQL запрос к «страшному и непонятному» имени таблицы базы данных «_InfoReg482 _InfoReg482_Q_000_T_001», плюс «загадочные» поля таблицы «_Fld484» и «_Fld483RRef».

Тут надо дать комментарий почему мы не увидели запроса 1С. У нас есть «контекст запроса» — 458 строка модуля Обработка.КонсольЗапросов.Форма — мРезЗапроса = ОбъектЗапрос.Выполнить();

Встаньте в эту строчку отладчиком конфигуратора и для ОбъектЗапрос.Текст содержимым будет запрос 1С.

Но это мы знаем исходный запрос, и поэтому можем догадаться, что за «_InfoReg482» таблица на самом деле.

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

Возвращаемся к нашей схеме прохождения запроса.

Сервер приложений 1С:Предприятие является клиентом для СУБД MS SQL Server и передает исследованный нами SQL запрос на сервер СУБД.

Запрос выполняет сервер СУБД и затем по обратной цепочки возвращает ответ.

В этой статье сконцентрируемся на подробностях исполнения запроса сервером субд, так как язык SQL предназначен для формулирования запросов в терминах СУБД (таблицы и поля).

При выполнении запроса:

SELECT
_InfoReg482_Q_000_T_001._Fld484 AS f_1
FROM
_InfoReg482 _InfoReg482_Q_000_T_001 WITH(NOLOCK)
WHERE
_InfoReg482_Q_000_T_001._Fld483RRef = ?

мы говорим, что нужно получить «_InfoReg482_Q_000_T_001._Fld484», но не говорим, «как это получить»!

Дело в том, что один и тот же запрос может быть выполнен множеством разных способов:

-Части запроса можно обрабатывать в разном порядке

-Можно использовать разные индексы

Это связано с механизмом субд — оптимизатором запросов.

Оптимизатор запросов СУБД

Оптимизатор запросов осуществляет поиск наиболее оптимального плана выполнения запросов из всех возможных для заданного запроса. Один и тот же результат может быть получен СУБД различными способами (планами выполнения запросов), которые могут существенно отличаться как по затратам ресурсов, так и по времени выполнения. Задача оптимизации заключается в нахождении оптимального способа.

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

Планы выполнения запроса сравниваются исходя из следующих факторов:

• потенциальное число строк, извлекаемое из каждой таблицы, получаемое из статистики;
• наличие индексов ( подразумевается, что вы уже прочли мою статью об индексах);
• возможность выполнения слияний (merge-join).

Что из всего этого следует:

Перед выполнением запроса формируется его ПЛАН выполнения.

1. Полностью определяет, как будет выполняться запрос
2. Формируется автоматически сервером СУБД
3. Повлиять на план средствами 1С:Предприятие можно только косвенно

Давайте «посмотрим» план выполнения нашего запроса.

Страшно и непонятно, если не знать английского ��

Давайте разберемся в этой схеме плана запроса.

Для выполнения выбора (SELECT …) поля _InfoReg482_Q_000_T_001._Fld484 (он находится в составе возвращаемых данных Output List) оптимизатор запросов принял решение выполнить перебор всех записей (Clustered Index Scan в заголовке операции (единственной) плана) кластерного индекса таблицы _InfoReg482_ByPeriod_TRN (об этом подсказывает раздел object).

Напоминаю, чтобы получить соответствие имен таблиц базы данных объектам метаданным используйте наш сервис.

Привожу для справки работу оператора сканирования (перебора) кластерного индекса:

Clustered Index Scan – логический и физический оператор сканирует кластеризованный индекс, определенный в колонке Argument.

Если есть опциональный параметр WHERE:(), то возвращаются только те строки, которые соответствуют параметру. Если колонка Argument содержит параметр ORDERED, процессор запроса предложит вывод строк в том порядке, в котором они отсортированы в кластерном индексе. Если упорядочения нет, индекс будет отсканирован самым оптимальным образом (но не гарантируется, что вывод будет отсортирован).

В нашем примере плана опциональный параметр отсутствует.

Основная причина недостаточной производительности запросов — неоптимальный план выполнения запроса.

Для того, чтобы устранять проблемы производительности, необходимо понять причины.

Причины, по которым СУБД может выбирать неоптимальный план (наиболее известные)

(проблема «покрывающих» запросов описана в моей статье)
• Некоренное использование параметров виртуальных таблиц
• Отсутствие функции ВЫРАЗИТЬ для полей составного типа
• Получение лишних полей через точку

Но прежде, чем перейдем к разбору софтверных нюансов, хочется ОБРАТИТЬ ВНИМАНИЕ, что

сильная загруженность ресурсов сервера на выбор плана влияет НЕПРЕДСКАЗУЕМО!

Это означает, что если у вас сервер «не тянет», то результат оптимизации кода конечно снизит нагрузку и улучшит производительность, но вот ВЫПОЛНИТЬ САМУ ОТЛАДКУ и оптимизировать код на таком сервере сложно и НЕТ ГАРАНТИЙ!

В качестве примера обычно рассказываю такую ситуацию. Допустим Вы нашли медленный запрос и знаете как его улучшить. Чтобы оценить на практике результаты, Вы должны сначала выполнить замер производительности на исходном коде, затем на оптимизированном. А как можно интерпретировать результаты, если в момент время замера исходного запроса сервер был загружен на 70%, а в момент второго на 99%. Можно ли доверять таким замерам? Нет.

Несоответствие индексов и условий запросов

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

Индексы наиболее подходят для задач следующего типа:

• Запросы, которые указывают «узкие» критерии поиска. Такие запросы должны считывать лишь небольшое число строк, отвечающих определенным критериям.
• Запросы, которые указывают диапазон значений. Эти запросы также должны считывать небольшое количество строк.
• Поиск, который используется в операциях связывания. Колонки, которые часто используются как ключи связывания, прекрасно подходят для индексов.
• Поиск, при котором данные считываются в определенном порядке. Если результирующий набор данных должен быть отсортирован в порядке кластеризованного индекса, то сортировка не нужна, поскольку результирующий набор данных уже заранее отсортирован. Например, если кластеризованный индекс создан по колонкам lastname (фамилия), firstname (имя), а для приложения требуется сортировка по фамилии и затем по имени, то здесь нет необходимости добавлять инструкцию ORDER BY.

Правда при всей полезности индексов, есть одно очень важное НО – индекс должен быть «эффективно используемым» и должен позволять находить данные с использованием меньшего числа операций ввода-вывода и объема системных ресурсов.

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

• ВЫБРАТЬ … ИЗ … ГДЕ <условие>
• СОЕДИНЕНИЕ … ПО <условие>
• ВЫБРАТЬ … ИЗ <ВиртуальнаяТаблица>(, <условие>)
• ИМЕЮЩИЕ <условие>

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

• Установить свойство «Индексировать»
• Изменить порядок следования измерений в регистре

Следует учитывать, что максимально эффективный индекс в самом начале содержит все поля, перечисленные в условии. Необходимо проанализировать все условия, имеющиеся в запросе и проверить, есть ли для них подходящие индексы.
Для определения создания индексов (индексирования) можно использовать:

1. Знание используемого количества строк в таблице (чаще всего записей в справочнике, регистре сведений, последовательности и т.д.)
2. Знание запроса, использующего таблицы (записи объектов метаданных)

Почему так происходит?

Предлагаю выполнить несколько примеров. Сначала займемся практикой, а потом сделаем выводы из наблюдений и познакомимся с теорией.

Пример 1

Создайте справочник с реквизитом. Пусть это будет «Склады» с реквизитом «МОЛ». Заполните справочник, сгенерировав например обработкой несколько тысяч записей, с разными значениями реквизита «МОЛ».

Выполните (например в консоли запросов) такой запрос, использующий отбор «ГДЕ»:

Для того, чтобы получить неискаженную информацию, выполните запрос Transact-SQL (здесь приложен файл) к базе данных с помощью SQL Server Management Studio:

USE MyDataBase1C
GO
exec sp_msforeachtable N’UPDATE STATISTICS ? WITH FULLSCAN’
GO
DBCC UPDATEUSAGE (MyDataBase1C)
GO
DBCC FREEPROCCACHE

,где MyDataBase1C — имя базы данных информационной базы 1С:Предприятие.

Далее используя SQL Server Profiler и приложенный здесь шаблон включите «трассировку» для вашей базы данных.

После выполнения запроса кода конфигурации в MS SQL Server будет «транслирован» запрос Transact-SQL.

Выделите событие Showplan XML Statistics Profile, и Вы уведете картинку плана запроса, похожу на мою.

Предлагаю обратить внимание на название операции физического исполнения логической команды SELECT (ВЫБРАТЬ) — Clustered Index Scan.

Теперь изменим у реквизита свойство Индексировать.

И после перезапуска конфигуратора (у нас будет создан индекс) повторить наблюдение за планом запроса.

Что произошло? Тот же самый запрос, выполняясь в MS SQL Server в этот раз выполняется другими физическим оператором — Clustered Index Seek. А если еще точнее, двумя операциями Clustered Index Seek и результаты объединяются физической операцией Nested Loops.

Даже если не смотреть на колонки Duration (длительность) и Read (чтение с диска), то можно сделать вывод — индексы влияют на способ выполнения логических операторов (в нашем случаи SELECT). При этом могут применять разные комбинации физических операторов.

Пример 2

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

Сначала выполним размещения измерения номенклатура последним по порядку, и выключим индексирование этого реквизита, выполним запрос. Затем включим индексирование и повторим запрос.

Для анализа используем профайлер с такими настройками:

Запрос к неиндексированной «Номенклатуре» регистра.

Длительность Duration в моем замере составила 60 мс.

Теперь запрос с индексированным вариантом:

Интересно, что в 1С:Предприятии код запроса мы не трогали, более того, и в MS SQL Server логический текст запроса тоже остался тем же.

А вот физические операторы изменились.

Почему же оптимизатор запросов ведет себя по-разному?

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

Сканирование таблицы или индекса

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

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

• Таблица содержит большое количество записей
• Запрос возвращает маленькое количество записей

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

Вид операции Table Scan. Значок нам как бы подсказывает, что происходит перебор всех записей таблицы.

Вид операции Clustered Index Scan

Отдельно выделим операцию поиска по кластеризованному индексы с условием.

Поиск в индексе по неполному условию

На выполнение поиска по неполному условию указывает наличие в плане запроса операции:

Данная операция аналогична сканированию таблицы или индекса, но при ее выполнении сканируется не вся таблица, а только ее часть.

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

• Сканируемая область (ограниченная условием SEEK) содержит большое количество записей
• Запрос возвращает маленькое количество записей

Операция поиска по неполному условию так же указывает на отсутствие подходящего индекса.

Вид операции Index Seek

Избегать конструкции ПОДОБНО (LIKE)

При работе с отборами удобно выполнять поиск контрагента не по наименованию, а путем установки отбора по условию «Содержит» по полю «Наименование». Однако это отрицательно сказывается на производительности из-за невозможности использования индексов и так приводит к запросам:

ВЫБРАТЬ .. ИЗ Справочник.Контрагенты
ГДЕ Наименование ПОДОБНО «%СтрокаПоиска%»

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

Примечание. В последних версиях MS SQL Server LIKE стал уметь в некоторых случаях использовать поиск, но это все равно не лучший оператор.

Большая вложенность подзапросов и соединения с вложенными подзапросами

Сервер СУБД с трудом оптимизирует такие конструкции как

Если в тексте встречаются конструкции вида:

ВЫБРАТЬ ИЗ <запрос1>
ЛЕВОЕ СОЕДИНЕНИЕ (
ВЫБРАТЬ ИЗ <подзапрос1>
) ПО …

особенно, если <запрос1> или <подзапрос1> содержат не физические таблицы, а также вложенные запросы, потому что:

1. Неизвестно, сколько записей вернет подзапрос
2. Неизвестно, какой способ соединения лучше использовать
3. Выбирается тот способ, который сам по себе проще (NESTED LOOPS)
4. Это приводит к сильному падению производительности

Рассмотрим более подробно суть проблемы.

Соединение в цикле

На выполнение соединения в цикле указывает наличие в плане запроса операции:

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

В этом случае следует попытаться упростить запрос:

• Использовать временные таблицы вместо подзапросов
• Уменьшить вложенность подзапросов
• По возможности исключить соединения с подзапросами

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

Большая вложенность запросов

Другой момент – это возможные ошибки в сложных вложенных запросах (на момент написания характерны для ЗУП). Если запрос выполняет неоправданно долго, упростите его с помощью временных таблиц.

МенеджерВТ = Новый МенеджерВременныхТаблиц;
Подзапрос = Новый Запрос;
Подзапрос.МенеджерВременныхТаблиц = МенеджерВТ;
Подзапрос.Текст = «
| ВЫБРАТЬ …
| ПОМЕСТИТЬ МояВременнаяТаблица
| ИЗ <подзапрос1>
| ГДЕ…
| ИНДЕКСИРОВАТЬ ПО…
«;

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

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

Если производительность запроса оказалась недостаточной, возможно это говорит о том, что подзапрос по-прежнему слишком сложен. То есть, необходимо его дальнейшее упрощение еще раз.

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

1. Заполняем временную таблицу результатом вложения
2. Выполняем основной запрос

Резюме. При решении вопросов производительности в MS SQL Server обратите внимание на физические операторы плана запроса:

• Сканирование таблицы или индекса
• Поиск в индексе по неполному условию
• Соединение в цикле

Оптимизатор СУБД выбирает и анализирует лишь некоторое количество вариантов выполнения запроса и выбирает “лучший из них”. При количестве таблиц больше 8 скорее всего все варианты точно не будут проверены — даже математически слишком много комбинаций за отведенное ограниченное время.

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

1. По возможности условие ИЛИ заменять операцией ОБЪЕДИНЕНИТЬ ВСЕ.Чтобы убедиться в том, что нужна замена, можно сделать так: выполнить запрос с условием на равенство по одиночному значению поля, а потом запрос с условиемИЛИ по этому полю на большой таблице. Если второй запрос выполняется намного дольше, чем первый, то данный запрос можно переписать.
2. Условие “не равно” (<>) тоже может отключать использование индекса. Например если необходимо исключить большую часть таблицы, а поле отбора проиндексировано, – есть смысл переписать запрос на вариант ОБЪЕДИНЕНИТЬ ВСЕ
3. В условиях соединений, стараться обходиться без использования вложенных запросов и виртуальных таблиц, а так же лишних разыменований (выражений через несколько точек). Вложенные запросы и виртуальные таблицы рекомендую заменить на временные таблицы.
4. Стараться избегать в условии “В ИЕРАРХИИ” содержания пустой ссылки. Возможно ситуация когда оптимизатор СУБД начнет проверять каждый элемент справочника на принадлежность корню (т.е. самому справочнику), теряя на этом время.
5. Следует использовать ОБЪЕДИНИТЬ ВСЕ вместо ОБЪЕДИНИТЬ, если наличие одинаковых записей не критично. Оператор ОБЪЕДИНИТЬ использует дополнительные операции, которые могут занять много времени.
6. Условие “ПОДОБНО” подавляет использование индекса.
7. Если запрос содержит несколько условий, то они должны располагаться в порядке уменьшения эффекта от выбора. То есть первым надо делать то условие, которое максимально уменьшит результирующую таблицу.
8. Максимально использовать индексы таблиц. Необходимо стараться чтобы для всех условий, использованных в запросе, имелись подходящие индексы. Подходящий индекс – это индекс, который содержит все поля перечисленные в условии, эти поля находятся в самом начале индекса и они расположены вместе, т.е между ними нет других полей.
9. Не рекомендуется фильтровать виртуальные таблицы при помощи условий в секции ГДЕ и т.п. Надо использовать только параметры.
10. Стараться для ссылочных полей, по которым будет вывод, получать представление сразу в запросе, т.е. использовать конструкцию “ПРЕДСТАВЛЕНИЕ(НашаСсылка)“
11. Если в запросе реализовано соединение двух и более таблиц, то эти таблицы должны стоять в запросе в порядке уменьшения количества записей в них, а в части условий (ГДЕ) первым должно стоять условие на первую таблицу.
12. Если запрос содержит условие для проиндексированного (относится к некластерному индексу) поля маленькой таблицы, которая может быть считана за одно обращение к памяти, то лучше убрать индекс с этого поля в конфигурации.
13. Условия вхождения значений полей в результаты вложенных запросов лучше заменять на внутренние соединение по равенству этого поля для ситуаций, когда есть вероятность получения больших размеров таблиц результатов вложенных запросов. Т.е. конструкцию “Поле1 В (Выбрать Поле1 из Таблица2)” заменить на “ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таблица2 По Таблица1.Поле1 = Таблица2.Поле1“
14. Для составных типов смотрите http://infostart.ru/public/184361/
15. Пример оптимизации тут

Влияние данных на выбор плана

Одним из критерием, которыми руководствуется оптимизатор при создании плана исполнения запроса — это количество строк в таблице. Мы не будем сейчас говорить, о том что механизм блокировок тоже меняет свое поведение и влияет на производительность. Сформулирую лишь собственные наблюдения, собранные «грабли».

Если в конфигурации есть «аналитика» (субконто, измерения) участвующая в итогах и она не используется, то возможны неоправдано излишнее торможение. Что делать: особенно если это типовая конфигурация, либо заполнить справочник сотней «фиктивных» строк, если позволяет бизнес-логика, либо отказаться от этой аналитики.

Время проведения документа с 50, 500, 5000 и 50000 строчками вряд ли будет иметь линейную зависимость замедления. В какой-то момент «где-то» система упрется в узкое место и скорость проведения резко упадет. В качестве рекомендаций можно посоветовать нагрузочное тестирование именно с тем количеством строк, которое реально используется на предприятии.

Влияние статистики на выбор плана

С объемом данных и его разнородности в рамках таблицы сильно связано «знание» оптимизатора с помощью статистик распределения. Как правило, СУБД не знает и не может знать точное число строк в таблице (даже для выполнения запроса SELECT COUNT(*) FROM TABLE выполняется сканирование первичного индекса), поскольку в базе могут храниться одновременно несколько образов одной и той же таблицы с различным числом строк. Сбор статистики для построения гистограмм осуществляется либо специальными командами СУБД, либо фоновыми процессами СУБД.

Из практических можно отметить уже данные рекомендации моей статьи по обслуживанию MS SQL Server выполнять принудительно или регламентной процедурой команду SQL:

exec sp_msforeachtable N’UPDATE STATISTICS ? WITH FULLSCAN’

Влияние механизмов кэширования запросов (их компиляции перед исполением).

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

При очень высокой загруженности СУБД может использовать скомпилированной запрос с устаревшей статистикой или другими неактуальными данными. Инструкция

используется для аккуратной очистки кэша планов. Внимание, просто так эту процедуру «гонять» не надо. Слишком частое использование, да еще при не интенсивном вводе данных скорее ухудшит работу, чем улучшит.

Влияние RLS

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

2. Разбивайте запрос на несколько частей. Старайтесь как можно большее количество участков запроса выносить в привилегированный модуль. Под «RLS» должна остаться только та таблица и кусок запроса, без которой потеряется смысл проверки доступа к записи.

Способы поиска проблемных запросов

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

Поиск длительных запросов опросом пользователей и отладчиком

Метод имеет кучу недостатков и ограничений. Пожалуй проще сказать, когда его можно использовать. Если пользователь жалуется на долгое проведение документа или построение отчета, и при этом нет блокировок, т.е. работает одинаково долго как в многопользовательском, так и в монопольном режиме. Кроме того, отладчиком по-умолчанию можно отлаживать только код клиентской части. Для включения отладки на сервере Вам придется перезапускать сервер приложений и лезть в реестр. А именно

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\1C:Enterprise 8.1 Server Agent]
«ImagePath«=

по умолчанию «C:\Program Files\1cv81\bin\ragent.exe» -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -d «C:\Program Files\1cv81\server»

надо «C:\Program Files\1cv81\bin\ragent.exe» -srvc -agent -regport 1541 -port 1540 -range 1560:1591 -debug -d «C:\Program Files\1cv81\server»

Таким образом, это самый дешевый и простой способ исследования медленных запросов. Он наиболее известен 1С специалистам.

Поиск длительных запросов технологическим журналом

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

Недостаток — нужно иметь навык работы с технологическим журналом или потратить время на его освоения. Облегчает работу обработка с диска ИТС «Настройка технологического журнала».

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

Поиск длительных запросов с помощью бесплатного сервиса Анализа долгих запросов

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

Поиск длительных запросов SQL Server Profiler-ом

Последний способ для страдающих ностальгией по 90м годам, когда администраторы баз данных куда больше ценились (и это было вполне обосновано). Способ не то чтобы для извращенцев, но квалификации требует наибольшей из всех перечисленных здесь способов, а эффективность не гарантируется.

Вариант настройки SQL Server Profiler

• Events:
  • Stored Procedures \ RPC:Comleted
  • TSQL \ SQL:BatchCompleted
  • Performance \ Showplan Statistics Profile
  • Performance \ Showplan XML Statistics Profile
  • Все
  • Все условия фильтрации, включенные по умолчанию
  • DatabaseName
    • Like <имя базы данных>
    • Greater then or equal <длительность запроса в миллисекундах>

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

    Имеется возможность автоматизировать ранжирование групп запросов. Для этого следует сохранить трассировку в таблицу SQL и выполнить, например, следующий запрос:

    select substring(TextData, 1, 100), SUM(Duration)

    group by substring(TextData, 1, 100)

    order by SUM(Duration) desc

    В результате будет получен список групп запросов (по совпадению первых 100 символов), отсортированный по убыванию суммарного времени выполнения всех запросов группы, то есть по убыванию ранга данного запроса.

    Ну, вообще Вы поняли, что будет не скучно :).

    Поиск отсутствующих индексов средствами MS SQL Server

    Так получилось, что задача поиска отсутствующих индексов характерна не только 1С-приложениям, но и вообще любым клиентам MS SQL Server. Задача вообще-то не очень простая, как вы уже могли заметить. Поэтому для «закоренелых sql-щиков» не могу не осветить еще несколько моментов.

    Помощник по настройке ядра СУБД (Database Engine Tuning Advisor)

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

    Поскольку мы работаем с 1С:Предприятие 8, то SQL-щики могут соблазниться на подобную кажущуюся легкость решения проблемы.

    Подводными камнями является «мягкая» привязка структуры информационной базы к именам таблиц в базе данных MS SQL Server. Именя таблиц генерируются платформой автоматически и нет гарантий в постоянном соответствии метаданных к именам таблиц.

    Вообщем такой подход решения проблемы методически неверен.

    Однако я бы не стал вообще о нем упоминать, если бы не одно но. Рекомендованные индексы помощником DTA можно расмотреть для создания подобных индексов уже средствами 1С:Предприятие. Более подробно об управлении индексов смотрите в моей статьте.

    Как пользоваться DTA.

    1. Вам необходимо профайлером собрать «рабочую нагрузку». Это означает, что вы не просто включаете на подробную запись запросов профайлер и из под своей сессии делаете одно-два проведений документов или построений отчетов. Вам нужна запись реальных действий пользователей за весь рабочий день. Разумеется слово рабочий день подразумевает не время с 10.00 до 18.00, а реальная работа пользователей в это время.

    2. В составе трассировки обязательно должны быть и выполненные запросы, и планы запросов.

    3. Сохранить трассировку в файл.

    

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

    

    После старта анализа мастер будет некоторое время оценивать данные нагрузки и выдаст рекомендации.

    

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

    

    Очень желательно создавать индексы не средствами СУБД, но если вы решите все таки использовать скрипт, советую продумывать отслеживание эффективности подобных индексов.

    Мегаэкстремальный способ препарирования запросов

    Есть такой прием — подсказка USE PLAN в запросе.

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

    Руководства планов SQL могут только применяются к инструкциям и пакетам, часто выполняющимся приложением с помощью системной хранимой процедуры sp_executesql (платформа 1С:Предприятие не все запросы генерирует в таким способом, но многие).

    Похожие публикации:

    1. 1с как изменить квалификатор строки в таблице
    2. 1с как получить владельца формы на сервере
    3. 1с консалтинг как получить статус
    4. 1с платформа последняя версия какая