Дмитрий Никитин , Руководитель отдела информационных технологий

28 Сентября 2018 Настройка Актуальность проверена: 20 Февраля 2024 110336 ≈13 мин

Язык запросов 1С 8.3

Содержание статьи

Особенности некоторых операторов языка запросов
Работа с пакетными запросами
Виртуальные таблицы
Конструктор запросов
Причины неоптимальной работы запросов

Данная статья рассчитана на читателей, которые знакомы с языком SQL.

Язык запросов в 1С, применяющийся начиная с версии 8, сегодня стал полезным инструментом для работы с базами данных, который позволяет читать из них, но не записывать. Синтаксически язык запросов очень схож с языком SQL, но на русском языке.

Ниже представлена таблица соответствия основных операторов языка запросов и SQL:

Операторы языка запросов 1С	Оператор SQL
ВЫБРАТЬ	SELECT
ГДЕ	WHERE
РАЗЛИЧНЫЕ	DISTINCT
ПОДОБНО	LIKE
ПЕРВЫЕ	TOP
ВЫБОР	CASE
СОЕДИНЕНИЕ	JOIN
СГРУППИРОВАТЬ ПО	GROUP BY
ОБЪЕДИНИТЬ	UNION
УПОРЯДОЧИТЬ ПО	ORDER BY
ИМЕЮЩИЕ	HAVING
ИЗ	FROM
В	IN

И это далеко не полный список. Более полную справочную информацию по доступным операторам языка запросов можно получить в конструкторе запросов, о котором будет рассказано ниже.

Выполнение запроса 1С из программного кода осуществляется при помощи объекта встроенного языка «Запрос». Пример написания запроса к базе данных с использованием встроенного языка программирования:


	 Запрос = Новый Запрос;
	 Запрос.Текст =
	                         "ВЫБРАТЬ
	                         | Синоним.Ссылка КАК Ссылка
	                         |ИЗ
	                         | Справочник.Справочник1 КАК Синоним";
	 Выборка = Запрос.Выполнить().Выбрать();
	 Пока Выборка.Следующий() Цикл
	          // Вставить обработку выборки ВыборкаДетальныеЗаписи
	 КонецЦикла;

Метод «Выполнить» выполняет запрос, метод «Выбрать» возвращает значение типа «ВыборкаИзРезультатаЗапроса». Также можно использовать метод «Выгрузить», который возвращает таблицу значений.

Параметры запроса хранятся в свойстве «Параметры» (в данном случае это структура, поэтому все методы структуры тут применимы – вставить, удалить и т.д.).

Пример установки параметра «Запрос.Параметры.Вставить» («Справочник», СправочникСсылка). В запросе обратиться к параметрам можно через амперсанд «&Справочник». Ниже пример запроса с использованием параметров:


	Запрос = Новый Запрос;
	Запрос.Текст =
				"ВЫБРАТЬ
				| 	Пользователи.Ссылка КАК Ссылка,
				| 	Пользователи.Родитель КАК Родитель,
				| 	Пользователи.Наименование КАК Наименование
				|ИЗ
				| 	Справочник.Пользователи КАК Пользователи
				|ГДЕ
				| 	Пользователи.Ссылка = &Справочник";
	 Запрос.Параметры.Вставить("Справочник", СправочникСсылка);
	 Выборка = Запрос.Выполнить().Выбрать();
	 Пока Выборка.Следующий() Цикл
		// Вставить обработку выборки ВыборкаДетальныеЗаписи
	 КонецЦикла;

Напомним, что язык запросов предназначен только для чтения данных из базы, поэтому в нем отсутствуют аналоги таких операторов SQL, как INS ERT и UPDATE. Данные можно модифицировать только через объектную модель встроенного языка программирования 1С. Также в языке запросов 1С существуют операторы, аналогов которых нет в SQL, например:

В ИЕРАРХИИ
ПОМЕСТИТЬ
ИНДЕКСИРОВАТЬ ПО

В ИЕРАРХИИ – позволяет выбрать все элементы иерархического справочника, которые входят в иерархию переданной ссылки. Пример запроса с использованием В ИЕРАРХИИ:


	ВЫБРАТЬ
		Товары.Ссылка,
		Товары.Артикул
	ИЗ
		Справочник.Товары КАК Товары
	ГДЕ
		Товары.Ссылка В ИЕРАРХИИ(&Цитрусовые)"

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

Также, к примеру, стоит задача найти товар с именем «Ручка». Товар должен входить в иерархию «Канц. Товаров», то есть нам не надо искать дверную ручку. Структура номенклатуры в этом случае такова:

…

Канцелярия


	|_ Ручки перьевые
        |_ Ручка красная
		|_ Ручка синяя
	|_ Ручки чернильные
	|_ Линейки

Фурнитура


	|_ Ручки дверные
		|_ Ручка дверная простая
		|_ Ручка дверная люкс

Пишем такой запрос:


	ВЫБРАТЬ
		Товары.Ссылка,
		Товары.Артикул
	ИЗ
		Справочник.Товары КАК Товары
	ГДЕ
		Товары.Наименование Подобно "Ручка%" И Товары.Ссылка В ИЕРАРХИИ(&Канцелярия)"

…где параметр «Канцелярия» – это ссылка на группу «Канцелярия». В результате мы получим Ручка красная и Ручка синяя.

При использовании конструкции В ИЕРАРХИИ необходимо учитывать, что если в параметр «Канцелярия» передать пустую ссылку, выполнение запроса замедлится, так как платформа будет проверять каждый элемент на принадлежность корню.

ПОМЕСТИТЬ – Данный оператор помещает результат во временную таблицу. Пример запроса:


	ВЫБРАТЬ
		Пользователи.Ссылка КАК Ссылка,
		Пользователи.Родитель КАК Родитель,
		Пользователи.Наименование КАК Наименование
	ПОМЕСТИТЬ ОтобранныеПользователи
	ИЗ
		Справочник.Пользователи КАК Пользователи
	ГДЕ
		Пользователи.Ссылка = &Справочник;
	ВЫБРАТЬ
		ОтобранныеПользователи.Ссылка КАК Ссылка,
		ОтобранныеПользователи.Родитель КАК Родитель,
		ОтобранныеПользователи.Наименование КАК Наименование
	ИЗ
		ОтобранныеПользователи КАК ОтобранныеПользователи

Данный запрос на SQL будет выполнен несколькими запросами:

Создание временной таблицы (платформа умеет «переиспользовать» ранее созданные временные таблицы, поэтому создание происходит не всегда);
Помещение данных во временную таблицу;
Выполнение основного запроса, а именно SEL ECT из этой временной таблицы;
Уничтожение/очистка временной таблицы.

Временная таблица может быть уничтожена явно, через конструкцию УНИЧТОЖИТЬ, либо неявно – при закрытии менеджера временных таблиц.

У объекта «Запрос» встроенного языка программирования есть свойство «МенеджерВременныхТаблиц», которое предназначено для работы с временными таблицами. Пример кода:


	 МВТ = Новый МенеджерВременныхТаблиц();
	 Запрос = Новый Запрос;
	 Запрос.МенеджерВременныхТаблиц = МВТ;

После выполнения запроса переменную МВТ можно использовать второй раз в другом запросе, что, несомненно, является еще одним плюсом использования временных таблиц. В данном случае временная таблица из базы будет удалена при вызове метода «Закрыть»…


	 МВТ.Закрыть();

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

ИНДЕКСИРОВАТЬ ПО – этот оператор применяется совместно с оператором ПОМЕСТИТЬ. При создании временной таблицы этим оператором можно проиндексировать создаваемую таблицу, что существенно ускоряет работу с ней (но только, если индекс подходит под ваш запрос).

Особенности некоторых операторов языка запросов

ДЛЯ ИЗМЕНЕНИЯ – данный оператор предназначен для блокировки определенной таблицы запроса (или всех таблиц, которые участвуют в запросе). Блокировка осуществляется наложением U блокировки на таблицу. На SQL это реализуется через hint UPDLOCK. Данная конструкция необходима для предотвращения блокировок типа deadlock. Пример запроса с конструкцией ДЛЯ ИЗМЕНЕНИЯ:


	ВЫБРАТЬ
		Пользователи.Ссылка КАК Ссылка,
		Пользователи.Родитель КАК Родитель,
		Пользователи.Наименование КАК Наименование
	ИЗ
		Справочник.Пользователи КАК Пользователи
			ЛЕВОЕ СОЕДИНЕНИЕ Справочник.РФК КАК РФК
			ПО Пользователи.РФК = РФК.Ссылка
	ДЛЯ ИЗМЕНЕНИЯ
		Справочник.Пользователи

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

СОЕДИНЕНИЕ – запрос поддерживает соединения ЛЕВОЕ/ПРАВОЕ, ПОЛНОЕ, ВНУТРЕННЕЕ, что соответствует соединениям в SQL – LEFT/RIGHT JOIN, OUTER JOIN, INNER JOIN.

Однако при использовании конструктора запросов вы не сможете сделать ПРАВОЕ СОЕДИНЕНИЕ. Конструктор просто будет менять местами таблицы, но оператор будет всегда левый. По этой причине в 1С никогда не встретишь применения правого соединения.

Синтаксически соединение выглядит так:


	ВЫБРАТЬ
		Таблица1.Ссылка КАК Ссылка
	ИЗ
		Справочник.Справочник1 КАК Таблица1
			ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Справочник2 КАК Таблица2
			ПО Таблица1.Реквизит = Таблица2.Реквизит

В языке запросов 1С отсутствует оператор для соединения декартова произведения (CROSS JOIN). Однако отсутствие оператора не означает, что язык запросов не поддерживает такого соединения. Соединить таблицы при необходимости можно таким образом:


	ВЫБРАТЬ
		Таблица1.Ссылка КАК Ссылка
	ИЗ
		Справочник.Справочник1 КАК Таблица1
			ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Справочник2 КАК Таблица2
			ПО ИСТИНА

Как видно из примера, задан ключ соединения ПО ИСТИНА, то есть каждая строка одной таблицы соответствует строке другой. Тип соединения (ЛЕВОЕ, ПРАВОЕ, ПОЛНОЕ, ВНУТРЕННЕЕ) не важен, если у вас есть строки в обеих таблицах, но если в какой-то из таблиц нет строк (пуская таблица) – результат будет отличаться. Например, при использовании ВНУТРЕННЕЕ соединение результат будет пустой. При использовании ЛЕВОЕ/ПРАВОЕ соединение в результате будет или не будет данных в зависимости от того, к какой таблице мы присоединяемся – с данными или нет. При использовании ПОЛНОГО соединения данные будут всегда (естественно, только одной таблицы, так как в другой пустота), выбор типа соединения зависит от конкретной прикладной задачи.

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

ПОДОБНО. В отличие от аналогичного оператора языка SQL – LIKE, шаблон для ПОДОБНО можно задать, используя только некоторые спец символы:

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

ИТОГИ ПО аналогом на SQL можно назвать оператор ROLLUP. Пример использования оператора ИТОГИ:


	ВЫБРАТЬ
		Товары.Цена КАК Цена,
		Товары.Товар КАК Товар
	ИЗ
		Справочник.Номенклатура КАК Товары
	ИТОГИ
		СРЕДНЕЕ(Цена)
	ПО
		Товар

Результат будет такой:

Товар	Цена
Кровать	9833,333
Кровать	9000
Кровать	7500
Кровать	13000
Утюг	2140
Утюг	1500
Утюг	2000
Утюг	2500
Утюг	3500
Утюг	1200
Ручка	51,2
Ручка	100
Ручка	70
Ручка	10
Ручка	55
Ручка	21

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

Работа с пакетными запросами

1С позволяет работать с пакетами запросов. В пакетном запросе тексты запросов разделяются точкой с запятой (;). Выполнение пакетного запроса 1С осуществляется последовательно. Пример текста пакетного запроса:


	ВЫБРАТЬ
		Пользователи.Ссылка КАК Ссылка,
		Пользователи.Родитель КАК Родитель,
		Пользователи.Наименование КАК Наименование
	ИЗ
		Справочник.Пользователи КАК Пользователи
	;
 

	ВЫБРАТЬ
		ГрафикРаботы.Пользователь КАК Пользователь,
		ГрафикРаботы.Дата КАК Дата,
		ГрафикРаботы.РабочихЧасов КАК РабочихЧасов
	ИЗ
		РегистрСведений.ГрафикРаботы КАК ГрафикРаботы

Для получения результата всех запросов, входящих в пакет, необходимо воспользоваться методом объекта запроса «ВыполнитьПакет», вместо «Выполнить». Данный метод последовательно выполняет все запросы. Результат запроса – массив результатов для каждого запроса из пакета, а последовательность расположения в массиве такая же, как последовательность запросов в тексте пакета.

Виртуальные таблицы

Рассматривая язык запросов, стоит упомянуть о такой особенности, как виртуальные таблицы. Виртуальные таблицы отсутствуют в базе данных, это своеобразная обертка, которая выполняется на стороне СУБД как запрос с использованием подзапросов. Пример запроса 1С с использованием виртуальных таблиц:


	ВЫБРАТЬ
		РегистрОбязательствОбороты.Обязательство КАК Обязательство
	ИЗ
		РегистрНакопления.РегистрОбязательств.Обороты() КАК  
	РегистрОбязательствОбороты

Такой запрос на СУБД будет выглядеть так:


	SEL ECT
	T1.Fld25931RRef
	FR OM (SELECT
	T2._Fld25931RRef AS Fld25931RRef,
	CAST(SUM(T2._Fld25936) AS NUMERIC(38, 8)) AS Fld25936Turnover_,
	CAST(SUM(T2._Fld25937) AS NUMERIC(38, 8)) AS Fld25937Turnover_
	FR OM dbo._AccumRgTn25938 T2
	WH ERE ((T2._Fld949 = @P1)) AND ((T2._Fld25936  @P2 OR T2._Fld25937      @P3))
	GROUP BY T2._Fld25931RRef
	HAVING (CAST(SUM(T2._Fld25936) AS NUMERIC(38, 8)))  0.0 OR 
	(CAST(SUM(T2._Fld25937) AS NUMERIC(38, 8)))  0.0) T1

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

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

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

В тексте запроса это выглядит так:


	РегистрНакопления.РегистрОбязательств.Обороты(, , , Операция =     &Операция) КАК 
	РегистрОбязательствОбороты

Конструктор запросов

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

Визуально конструктор запросов выглядит так:

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

Конструктор запросов является очень гибким визуальным инструментом для создания запросов любой сложности. Он доступен только в режиме конфигуратора. В режиме Предприятия есть так называемая «Консоль запросов» – это внешняя обработка, поставляемая на диске ИТС. Для управляемого приложения консоль запросов можно скачать на сайте its.1c.ru.

Описание работы в конструкторе запросов выходит за рамки тематики данной статьи, поэтому подробно рассматриваться не будет.

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

Ниже приведен список основных причин (но не всех), которые приводят к замедлению выполнения запроса.

Использования соединения с подзапросами

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

Использование виртуальных таблиц в соединениях запроса

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

Использование условий в запросе, неподходящих под существующие индексы

Если в условиях запроса (в операторе ГДЕ или в условиях виртуальной таблицы) используются поля, которые не все входят в индекс, данный запрос будет выполнен с использованием SQL конструкции table scan или index scan (полностью или частично). Это скажется не только на времени выполнения запроса, но также будет наложена избыточная S блокировка на лишние строки, что в свою очередь может привести к эскалации блокировок, то есть будет заблокирована вся таблица.

Использование ИЛИ в условиях запроса

Использование логического оператора ИЛИ в конструкции ГДЕ может также приводить к сканированию таблицы (table scan). Это происходит из-за того, что СУБД не может корректно использовать индекс. Вместо ИЛИ можно применить конструкцию ОБЪЕДИНИТЬ ВСЕ.

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

Не рекомендуется получать значения через точку (в конструкции ВЫБРАТЬ, ГДЕ), поскольку если реквизит объекта окажется составным типом, соединение будет происходить с каждой таблицей, входящей в этот составной тип. В результате запрос на СУБД будет значительно усложнен, это может помешать оптимизатору в выборе корректного плана выполнения запроса.

Рассказать друзьям

Другие статьи по теме

Основные справочники 1С 8.3: создание и изменение элементов

Ввод начальных остатков в 1С 8.3 Бухгалтерия

Учет НДФЛ в 1С 8.3

Все статьи

Комментарии